Thursday 7 December 2017

Step by Step to move datafile location in PostgreSQL


Purpose: 
This procedure is tested in my lab. Before following this procedure please test it in your test environment

 
Step 1: Check current Installation location abd Data location

[root@pg_primary data]# ps -ef | grep post
postgres  2226     1  0 Dec06 ?        00:00:01 /usr/pgsql-9.5/bin/postgres -D /var/lib/pgsql/9.5/data
root      2295     1  0 Dec06 ?        00:00:00 /usr/libexec/postfix/master -w
postfix   2297  2295  0 Dec06 ?        00:00:00 qmgr -l -t unix -u
postgres  2302  2226  0 Dec06 ?        00:00:00 postgres: logger process 
postgres  2380  2226  0 Dec06 ?        00:00:00 postgres: checkpointer process 
postgres  2381  2226  0 Dec06 ?        00:00:01 postgres: writer process 
postgres  2382  2226  0 Dec06 ?        00:00:02 postgres: wal writer process 
postgres  2383  2226  0 Dec06 ?        00:00:00 postgres: autovacuum launcher process 
postgres  2384  2226  0 Dec06 ?        00:00:00 postgres: archiver process 
postgres  2385  2226  0 Dec06 ?        00:00:01 postgres: stats collector process 
root      3702  3503  0 Dec06 pts/0    00:00:00 tail -f postgresql-Wed.log
postgres  3802  2226  0 Dec06 ?        00:00:01 postgres: wal sender process repuser 192.168.159.134(57966) streaming 0/34494368
postfix  19003  2295  0 Dec06 ?        00:00:00 pickup -l -t unix -u
root     19631  3713  0 00:25 pts/1    00:00:00 grep --color=auto post
[root@pg_primary data]#
-----Or-----
[root@pg_primary ~]# su - postgres
Last login: Wed Dec  6 01:13:14 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# SHOW data_directory;
     data_directory     
-------------------------
 /var/lib/pgsql/9.5/data
(1 row)

postgres=#

Step 2: Create a directory where Postgresql will move

[root@pg_primary ~]# mkdir /postgresql
[root@pg_primary ~]# chown -R postgres:postgres /postgresql/
[root@pg_primary ~]#
[root@pg_primary ~]# chmod 700 /postgresql/

Step 3: Move PostgreSQL Installation file to new created directory

[root@pg_primary data]# rsync -av /var/lib/pgsql/ /postgresql/


9.5/main/archive/000000010000000000000036
9.5/main/archive/000000010000000000000037

sent 1491083782 bytes  received 54419 bytes  20015277.87 bytes/sec
total size is 1490755229  speedup is 1.00
[root@pg_primary data]#


Step 4) Stop the Postgresql service Take a backup of Original FILE
[root@pg_primary data]# systemctl stop postgresql-9.5.service
[root@pg_primary postgresql]# mv /var/lib/pgsql/ /var/lib/pgsql_backup
[root@pg_primary postgresql]#

Step 5) Modify the postgresql.conf file

[root@pg_primary data]# vim /postgresql/9.5/data/postgresql.conf

#data_directory = 'ConfigDir'           # use data in another directory
data_directory = '/postgresql/9.5/data/'
                                        # (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf'     # host-based authentication file
hba_file = '/postgresql/9.5/data/pg_hba.conf'
                                        # (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
ident_file = '/postgresql/9.5/data/pg_ident.conf'
                                        # (change requires restart)

Step 6) Modify the Daemon configuration file 

[root@pg_primary data]# vim /usr/lib/systemd/system/postgresql-9.5.service

#Environment=PGDATA=/var/lib/pgsql/9.5/data/
Environment=PGDATA=/postgresql/9.5/data/

Step 7) Reload the deamon and start service

[root@pg_primary data]# systemctl daemon-reload
[root@pg_primary data]# systemctl status postgresql-9.5.service
postgresql-9.5.service - PostgreSQL 9.5 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-9.5.service; enabled)
   Active: active (running) since Thu 2017-12-07 02:59:21 EST; 2s ago
     Docs: https://www.postgresql.org/docs/9.5/static/
  Process: 22590 ExecStop=/usr/pgsql-9.5/bin/pg_ctl stop -D ${PGDATA} -s -m fast (code=exited, status=0/SUCCESS)
  Process: 22606 ExecStart=/usr/pgsql-9.5/bin/pg_ctl start -D ${PGDATA} -s -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 22601 ExecStartPre=/usr/pgsql-9.5/bin/postgresql95-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 22610 (postgres)

Step 8) Now check the datafile location

[root@pg_primary data]# ps -ef | grep postgre
postgres 22610     1  0 02:59 ?        00:00:00 /usr/pgsql-9.5/bin/postgres -D /postgresql/9.5/data
postgres 22611 22610  0 02:59 ?        00:00:00 postgres: logger process 
postgres 22613 22610  0 02:59 ?        00:00:00 postgres: checkpointer process 
postgres 22614 22610  0 02:59 ?        00:00:00 postgres: writer process 
postgres 22615 22610  0 02:59 ?        00:00:00 postgres: wal writer process 
postgres 22616 22610  0 02:59 ?        00:00:00 postgres: autovacuum launcher process 
postgres 22617 22610  0 02:59 ?        00:00:00 postgres: archiver process   failed on 000000010000000000000038
postgres 22618 22610  0 02:59 ?        00:00:00 postgres: stats collector process 
root     22673  3503  0 03:00 pts/0    00:00:00 tail -f postgresql-Thu.log
postgres 22761 22610  0 03:02 ?        00:00:00 postgres: wal sender process repuser 192.168.159.134(58787) streaming 0/39000548
root     22925  3713  0 03:11 pts/1    00:00:00 grep --color=auto postgre
[root@pg_primary data]#

postgres=# SHOW data_directory;
    data_directory   
----------------------
 /postgresql/9.5/data
(1 row)

postgres=#







No comments:

Post a Comment