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=#

WARNING: canceling wait for synchronous replication due to user request DETAIL: The transaction has already committed locally, but might not have been replicated to the standby

-------Error--------------

postgres=# INSERT INTO said VALUES ('3954','Saiduzzaman');


^CCancel request sent
WARNING:  canceling wait for synchronous replication due to user request
DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
INSERT 0 1
postgres=# exit


Reason

---In Primary--
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 on
(1 row)

postgres=#
postgres=# select * from pg_stat_replication ;
  pid  | usesysid | usename | application_name |   client_addr   | client_hostname | client_port |         backend_start
        | backend_xmin |   state   | sent_location | write_location | flush_location | replay_location | sync_priority |
sync_state
-------+----------+---------+------------------+-----------------+-----------------+-------------+-----------------------
--------+--------------+-----------+---------------+----------------+----------------+-----------------+---------------+-
-----------
 27148 |    16393 | repuser | walreceiver      | 192.168.159.134 |                 |       40704 | 2017-12-05 01:03:43.09
7386-05 |              | streaming | 0/23015F48    | 0/23015F48     | 0/23015F48     | 0/23015F48      |             0 |
async
(1 row)

postgres=#

postgres=# select application_name, sync_state from pg_stat_replication;
 application_name | sync_state
------------------+------------
 walreceiver      | async
(1 row)

postgres=#

postgres=# show synchronous_standby_names;
 synchronous_standby_names
---------------------------
 pgslave001
(1 row)


---In Standby--
postgres=# show synchronous_commit;
 synchronous_commit
--------------------
 local
(1 row)

postgres=#


------Solution---------

Changing synchronous_standby_names to 'walreceiver' resolved the error.
[root@pg_primary data]# vi postgresql.conf

#synchronous_standby_names = 'pgslave001'

synchronous_standby_names = 'walreceiver'

[root@pg_primary data]# systemctl stop postgresql-9.5.service
[root@pg_primary data]# systemctl start postgresql-9.5.service
[root@pg_primary data]# su - postgres
Last login: Tue Dec  5 01:04:46 EST 2017 on pts/0
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# INSERT INTO said VALUES ('3690','Nabiul');
INSERT 0 1
postgres=# INSERT INTO said VALUES ('3690','Nabiul');
INSERT 0 1

"/var/lib/pgsql/data" is missing or empty. in postgresql Database

Problem: When trying to check the service status of  postgresql  as root on RHEL 7 server, i got the following error message

[root@localhost RHEL-7.0 Server.x86_64]# systemctl status postgresql.service 
postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
   Active: failed (Result: exit-code) since Mon 2017-12-04 01:37:16 EST; 7s ago
  Process: 14151 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)

Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: "/var/lib/pgsql/data" is missing or empty.
Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: Use "postgresql-setup initdb" to initialize the database cluster.
Dec 04 01:37:16 localhost.localdomain postgresql-check-db-dir[14151]: See /usr/share/doc/postgresql-9.2.7/README.rpm-dist for mor...ion.
Dec 04 01:37:16 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1
Dec 04 01:37:16 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server.
Dec 04 01:37:16 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state.
Hint: Some lines were ellipsized, use -l to show in full.
[root@localhost RHEL-7.0 Server.x86_64]#

Reason:

initdb creates a new PostgreSQL database cluster. A database cluster is a collection of databases that are managed by a single server instance.

Creating a database cluster consists of creating the directories in which the database data will live, generating the shared catalog tables (tables that belong to the whole cluster rather than to any particular database), and creating the template1 and postgres databases. When you later create a new database, everything in the template1 database is copied. (Therefore, anything installed in template1 is automatically copied into each database created later.) The postgres database is a default database meant for use by users, utilities and third party applications.

Solution:


[root@localhost RHEL-7.0 Server.x86_64]# service postgresql initdb
Hint: the preferred way to do this is now "postgresql-setup initdb"
Initializing database ... OK

[root@localhost RHEL-7.0 Server.x86_64]#

[root@localhost RHEL-7.0 Server.x86_64]# systemctl stop postgresql.service
[root@localhost RHEL-7.0 Server.x86_64]# systemctl start postgresql.service
[root@localhost RHEL-7.0 Server.x86_64]# systemctl status postgresql.service
postgresql.service - PostgreSQL database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
   Active: active (running) since Mon 2017-12-04 01:40:51 EST; 4s ago
  Process: 46413 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
  Process: 46408 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 46418 (postgres)
   CGroup: /system.slice/postgresql.service
           ├─46418 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
           ├─46419 postgres: logger process 
           ├─46421 postgres: checkpointer process 
           ├─46422 postgres: writer process 
           ├─46423 postgres: wal writer process 
           ├─46424 postgres: autovacuum launcher process 
           └─46425 postgres: stats collector process 

Dec 04 01:40:50 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
Dec 04 01:40:51 localhost.localdomain systemd[1]: Started PostgreSQL database server.
[root@localhost RHEL-7.0 Server.x86_64]#