step by step to switch over Primary to Standby and switch back to Primary in PostgreSQL Database

Switchover/Switchback in PostgreSQL 9.5 Database

Step 1: Shutdown postgresql service of Primary Database

[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:44:38 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             1 | sync
(1 row)

-bash-4.2$ exit
logout
[root@pg_primary ~]# systemctl stop postgresql-9.5.service
[root@pg_primary ~]#

Step 2: Check for sync status and recovery status of Standby before promoting it:

[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:42:01 EST 2017 on pts/1
-bash-4.2$ psql -c 'select pg_last_xlog_receive_location() "receive_location",pg_last_xlog_replay_location() "replay_location",pg_is_in_recovery() "recovery_status";'
 receive_location | replay_location | recovery_status
------------------+-----------------+-----------------
 0/40000098       | 0/40000098      | t
(1 row)

-bash-4.2$
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)

-bash-4.2$

Step 3: Open the Standby as new Primary by pg_ctl promote or creating a trigger file.

-bash-4.2$ grep trigger_file /var/lib/pgsql/9.5/data/recovery.conf
trigger_file = '/tmp/postgresql.trigger.5432'
-bash-4.2$ touch /tmp/postgresql.trigger.5432
-bash-4.2$ psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

-bash-4.2$

-bash-4.2$ tail -f /var/lib/pgsql/9.5/data/pg_log/postgresql-Mon.log
< 2017-12-11 06:29:54.114 EST >LOG:  redo done at 0/40000028
< 2017-12-11 06:29:54.115 EST >LOG:  last completed transaction was at log time 2017-12-11 05:55:43.441569-05
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/000000010000000000000040’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-11 06:29:54.138 EST >LOG:  selected new timeline ID: 2
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000001.history’: No such file or directory
< 2017-12-11 06:29:54.492 EST >LOG:  archive recovery complete
< 2017-12-11 06:29:54.830 EST >LOG:  MultiXact member wraparound protections are now enabled
< 2017-12-11 06:29:54.911 EST >LOG:  database system is ready to accept connections
< 2017-12-11 06:29:54.936 EST >LOG:  autovacuum launcher started

---Now Standby become Primary Database----
[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:42:01 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# select * from sebl;
 org_name | address
----------+---------
(0 rows)

postgres=# insert into sebl values('Southeast Bank Ltd.','Eunoos Center');
INSERT 0 1
postgres=#

Standby has been promoted as master and a new timeline followed which you can notice in logs

Note: In Primary (Old Standby) recovery.conf file become recovery.done after switchover

--===========************=========---------
Prepare New Standby for data sync with Primary
--===========************=========---------
Step 4. Restart old Primary as standby and allow to follow the new timeline by passing "recovery_target_timline='latest'" in $PGDATA/recovery.conf file.

-bash-4.2$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.135 port=5432 user=repuser password=repuser@application_name=pgslave001'
restore_command = 'cp /var/lib/pgsql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup -d /var/lib/pgsql/9.5/main/archive %r  >> cleanup.log'
-bash-4.2$


Slave Server (old Primary) Configurations: postgresql.conf:

hot_standby = on






Step 5. Verify the new Standby status.

-bash-4.2$ psql -p 5432 -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)

-bash-4.2$
-------------Testing------
1. Insert data in primary
2. Check the data in primary and standby
3. try to insert data in standby

--=====================================================--
--------=========Switchback Steps==========--------------
--=====================================================--

Step 1: Check the Sync Status in Primary

postgres=# insert into sebl values('Prime Bank Ltd','Motijill');
INSERT 0 1
postgres=# select application_name, state, sync_priority, sync_state from pg_stat_replication;
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             0 | async
(1 row)

postgres=#

Step 2: Shutdown postgresql service of Primary Database

[root@pg_standby data]# systemctl stop postgresql-9.5.service
[root@pg_standby data]#

Step 3: Check for sync status and recovery status of Standby before promoting it:

[root@pg_primary data]# su - postgres
Last login: Wed Dec 13 00:15:09 EST 2017 on pts/3
-bash-4.2$ psql -c 'select pg_last_xlog_receive_location() "receive_location",pg_last_xlog_replay_location() "replay_location",pg_is_in_recovery() "recovery_status";'
 receive_location | replay_location | recovery_status
------------------+-----------------+-----------------
 0/4A000098       | 0/4A000098      | t
(1 row)

-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name | state | sync_priority | sync_state
------------------+-------+---------------+------------
(0 rows)

-bash-4.2$


Step 4: Open the Standby as new Primary by pg_ctl promote or creating a trigger file.

-bash-4.2$ grep trigger_file /var/lib/pgsql/9.5/data/recovery.conf
trigger_file = '/tmp/postgresql.trigger.5432'
-bash-4.2$ touch /tmp/postgresql.trigger.5432
-bash-4.2$ cat /tmp/postgresql.trigger.5432
cat: /tmp/postgresql.trigger.5432: No such file or directory
-bash-4.2$ psql -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 f
(1 row)

-bash-4.2$ cat /tmp/postgresql.trigger.5432
cat: /tmp/postgresql.trigger.5432: No such file or directory
-bash-4.2$

-----Check the log file of new Primary---

[root@pg_primary pg_log]# tail -f postgresql-Wed.log
< 2017-12-13 00:36:38.466 EST >LOG:  redo done at 0/4A000028
< 2017-12-13 00:36:38.466 EST >LOG:  last completed transaction was at log time 2017-12-13 00:30:26.653672-05
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002000000000000004A’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:36:38.488 EST >LOG:  selected new timeline ID: 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-13 00:36:38.615 EST >LOG:  archive recovery complete
< 2017-12-13 00:36:38.621 EST >LOG:  MultiXact member wraparound protections are now enabled
< 2017-12-13 00:36:38.623 EST >LOG:  database system is ready to accept connections
< 2017-12-13 00:36:38.624 EST >LOG:  autovacuum launcher started

---Now Standby become Primary Database----

[root@pg_primary data]# su - postgres
Last login: Wed Dec 13 00:34:10 EST 2017 on pts/3
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# select * from sebl;
      org_name       |    address
---------------------+---------------
 Southeast Bank Ltd. | Eunoos Center
 Estern Bank Ltd.    | Gulshan
 Bangladesh Bank     | Motijill
 Prime Bank Ltd      | Motijill
(4 rows)

postgres=# insert into sebl values('City Bank Ltd','Gulshan');
^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=# select * from sebl;
      org_name       |    address
---------------------+---------------
 Southeast Bank Ltd. | Eunoos Center
 Estern Bank Ltd.    | Gulshan
 Bangladesh Bank     | Motijill
 Prime Bank Ltd      | Motijill
 City Bank Ltd       | Gulshan
(5 rows)

postgres=#

Standby has been promoted as master and a new timeline followed which you can notice in logs
Note: In Primary (Old Standby) recovery.conf file become recovery.done after switchover
--***************--
[root@pg_primary data]# ls -lrt reco*
-rw-r--r-- 1 postgres postgres 607 Dec 11 23:11 recovery.done
[root@pg_primary data]# pwd
/var/lib/pgsql/9.5/data
[root@pg_primary data]#

--===========************=========---------
Step 5: Prepare New Standby for data sync with Primary
--===========************=========---------

Restart old Primary as standby and allow to follow the new timeline by passing "recovery_target_timline='latest'" in $PGDATA/recovery.conf file.

-bash-4.2$ cat recovery.conf
recovery_target_timeline = 'latest'
standby_mode = 'on'
primary_conninfo = 'host=192.168.159.135 port=5432 user=repuser password=repuser@application_name=pgslave001'
restore_command = 'cp /var/lib/pgsql/9.5/main/archive/%f %p'
trigger_file = '/tmp/postgresql.trigger.5432'
archive_cleanup_command = '/usr/pgsql-9.5/bin/pg_archivecleanup -d /var/lib/pgsql/9.5/main/archive %r  >> cleanup.log'
-bash-4.2$


Slave Server (old Primary) Configurations: postgresql.conf:

hot_standby = on

Step 6: Start Postgresql service in Standby

[root@pg_standby data]# systemctl start postgresql-9.5.service
[root@pg_standby data]#

---Check the log file of Standby and Primary---------

-------------Standby-----------
[root@pg_standby pg_log]# tail -f postgresql-Wed.log
< 2017-12-13 00:53:13.857 EST >LOG:  replication terminated by primary server
< 2017-12-13 00:53:13.857 EST >DETAIL:  End of WAL reached on timeline 2 at 0/4A000098.
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000004.history’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:53:13.897 EST >LOG:  new target timeline is 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003000000000000004A’: No such file or directory
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002000000000000004A’: No such file or directory
< 2017-12-13 00:53:13.903 EST >LOG:  restarted WAL streaming at 0/4A000000 on timeline 3
< 2017-12-13 00:53:17.037 EST >LOG:  redo starts at 0/4A000098

------------Primary---------
[root@pg_primary pg_log]# tail -f postgresql-Wed.log
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000003.history’: No such file or directory
< 2017-12-13 00:36:38.488 EST >LOG:  selected new timeline ID: 3
cp: cannot stat ‘/var/lib/pgsql/9.5/main/archive/00000002.history’: No such file or directory
< 2017-12-13 00:36:38.615 EST >LOG:  archive recovery complete
< 2017-12-13 00:36:38.621 EST >LOG:  MultiXact member wraparound protections are now enabled
< 2017-12-13 00:36:38.623 EST >LOG:  database system is ready to accept connections
< 2017-12-13 00:36:38.624 EST >LOG:  autovacuum launcher started
< 2017-12-13 00:40:43.387 EST >WARNING:  canceling wait for synchronous replication due to user request
< 2017-12-13 00:40:43.387 EST >DETAIL:  The transaction has already committed locally, but might not have been replicated to the standby.
< 2017-12-13 00:53:13.298 EST >LOG:  standby "walreceiver" is now the synchronous standby with priority 1


Step 7. Verify the new Standby status.

[root@pg_standby data]# su - postgres
Last login: Wed Dec 13 00:30:07 EST 2017 on pts/1
-bash-4.2$ psql -p 5432 -c "select pg_is_in_recovery();"
 pg_is_in_recovery
-------------------
 t
(1 row)

-bash-4.2$

-------------Testing------
1. Insert data in primary
2. Check the data in primary and standby
3. try to insert data in standby

step by step to change replication status (sync or async) in postgresql database

If you don't want transactions to wait until a synchronous replica acknowledges a commit,
then you don't want synchronous replication. You want async replication and should simply
remove the synchronous_standby_names

Step 1: Check sync status in Primary Database

[root@pg_primary ~]# su - postgres
Last login: Sun Dec 10 22:45:00 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             1 | sync
(1 row)

-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             1 | sync
(1 row)

Step 2: Modify the postgresql.conf file of Primary Database

-bash-4.2$
-bash-4.2$ vim /var/lib/pgsql/9.5/data/postgresql.conf
#synchronous_standby_names = '' # standby servers that provide sync rep
#synchronous_standby_names = 'pgslave001'
synchronous_standby_names = 'walreceiver'

--Comment the Values synchronous_standby_names--
#synchronous_standby_names = 'walreceiver'

Step 3: Restart the postgresql service

[root@pg_primary ~]# systemctl stop postgresql-9.5.service
[root@pg_primary ~]# systemctl start postgresql-9.5.service


Step 4: Check Database Sync Status

[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:35:05 EST 2017 on pts/0
-bash-4.2$ psql -c "select application_name, state, sync_priority, sync_state from pg_stat_replication;"
 application_name |   state   | sync_priority | sync_state
------------------+-----------+---------------+------------
 walreceiver      | streaming |             0 | async
(1 row)

-bash-4.2$ psql -x -c "select * from pg_stat_replication;"
-[ RECORD 1 ]----+-----------------------------
pid              | 5923
usesysid         | 16393
usename          | repuser
application_name | walreceiver
client_addr      | 192.168.159.135
client_hostname  |
client_port      | 39369
backend_start    | 2017-12-11 05:39:48.74948-05
backend_xmin     |
state            | streaming
sent_location    | 0/3E0000D0
write_location   | 0/3E0000D0
flush_location   | 0/3E0000D0
replay_location  | 0/3E0000D0
sync_priority    | 0
sync_state       | async

-bash-4.2$

----Check With data Primary and Standby--------
----In Primary------
[root@pg_primary ~]# su - postgres
Last login: Mon Dec 11 05:35:05 EST 2017 on pts/0
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# insert into sebl values('NRB Bank Ltd','Gulshan');
INSERT 0 1
postgres=#

-----In Standby------
[root@pg_standby data]# su - postgres
Last login: Mon Dec 11 05:34:07 EST 2017 on pts/1
-bash-4.2$ psql
psql (9.5.10)
Type "help" for help.

postgres=# select * from sebl;
      org_name       |    address   
---------------------+---------------
 Southeast Bank Ltd. | Eunoos Center
 Estern Bank Ltd.    | Gulshan
 Bangladesh Bank     | Motijill
 Prime Bank Ltd      | Motijill
 City Bank Ltd       | Gulshan
 Brac Bank Ltd       | Gulshan
 Exim Bank Ltd       | Gulshan
 MTBL Bank Ltd       | Gulshan
 Meghna Bank Ltd     | Gulshan
 Uttara Bank Ltd     | Motijill
 UCB Bank Ltd        | Gulshan
 NRB Bank Ltd        | Gulshan
(12 rows)

postgres=#

"/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]#