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/pg_env.sh

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 https://raw.githubusercontent.com/g3n1k/exsys/master/postgresql-9.5

# 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"
Advertisements

postgresql permissions failure to copy csv data

netsuke

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

change

host    all             all             127.0.0.1/32            md5

to

host    all             all             0.0.0.0/0               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