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