postgresql – pointing postgresql data to new location

Database grow over time, it out growing the space on their partition located.
in redhat 6.7, the default partition for “/” its 50GB and the rest going to “/home” partition.
when you default install postgresql-9.5 it would located in “/opt” which include in “/” partition.
for database production 50GB it’s so small. so i want change the data folder to “/home” which have TB space.

#status postgres
$ /etc/init.d/postgresql-9.5 status
pg_ctl: server is running (PID: 6309)
/opt/PostgreSQL/9.5/bin/postgres "-D" "/opt/PostgreSQL/9.5/data"

# stop postgres
$ service postgresql-9.5 stop
$ /etc/init.d/postgresql-9.5 status
pg_ctl: no server running

# change variable
$ nano /opt/PostgreSQL/9.5/

export PGDATA=/opt/PostgreSQL/9.5/data
change to
export PGDATA=/home/pg_data

# create dir
$ mkdir -p /home/pg_data

# doing rsync
$ rsync -azP /opt/PostgreSQL/9.5/data/ /home/pg_data/

# just make sure
$ mv /opt/PostgreSQL/9.5/data /opt/PostgreSQL/9.5/data_bak

# backup service script
$ mkdir -p ~/backup/ && mv /etc/rc.d/init.d/postgresql-9.5 ~/backup/

# download postgresql-9.5 file
$ wget

# move postgresql-9.5 to /etc/rc.d/init.d/
$ mv postgresql-9.5 /etc/rc.d/init.d/

# start postgresql
$ service postgresql-9.5 start

# status postgresql
$ service postgresql-9.5 status
pg_ctl: server is running (PID: 13282)
/opt/PostgreSQL/9.5/bin/postgres "-D" "/home/pg_data"

postgresql – create new tablespace

open with pgadmin, create with query

CREATE TABLESPACE your_name_tablespace
OWNER postgres
LOCATION '/your/partition/for/database/postgresql';
COMMENT ON TABLESPACE your_name_tablespace
IS 'new tablespace in partiton';

postgresql permissions failure to copy csv data


in my experience, this failure have multiple possibility and solution

1. path must absolute (possibility)

-- this code will error

psql > COPY your_table FROM 'folder/your.csv ' DELIMITER ',' HEADER;

-- this code will run

psql > COPY your_table FROM '/tmp/folder/your.csv ' DELIMITER ',' HEADER;


path information


2. copy to folder /tmp (solution)

# copy to folder /tmp

cp your.csv /tmp

# just make sure it can be read

chmod 777 /tmp/your.csv

# then run psql copy command



3. add postgres user to group which can read the file

# example the group user can read write execute file in folder is user/group vboxsf / youruser

# add postgres to vboxsf groups

$ sudo usermod -a -G vboxsf postgres

# restart postgres service

$ sudo service postgresql restart

# then run psql copy command


myserver config

image from

ubuntu install DBD::Pg, perl-postgress module database

perl to connect postgresql need module/library called DBD::Pg

# get postgres version

$ psql --version

# output: psql (PostgreSQL) 9.1.14

# install dependency

$ sudo apt-get install postgresql-server-dev-9.1

$ sudo apt-get install libpq-dev

##### install DBD::Pg using cpan

# open cpan shell

$ perl -MCPAN -e shell

cpan[1]> install DBD::Pg

Enter a valid PostgreSQL postgres major version number 9
Enter a valid PostgreSQL postgres minor version number 1
Enter a valid PostgreSQL postgres patch version number 14

Enter a valid PostgreSQL postgres bin dir /usr/lib/postgresql/9.1/bin

postgresql remote connection from lan

open file

sudo nano /etc/postgresql/9.1/main/pg_hba.conf


host    all             all               md5


host    all             all                  md5

open file

sudo nano /etc/postgresql/9.1/main/postgresql.conf

# change value listen_addresses =
# to
listen_addresses = '*'

restart postgresql

sudo service postgresql restart