PostgreSQL-11.3-master-slave stream replication + manual master-slave switch

1 Summary

Use PostgreSQL 11.3 to create two nodes: node1 and node2; configure master-slave stream replication, and then make a manual switch (failover). To simplify the configuration process, two nodes are on the same physical machine.

First, the master-slave synchronous flow replication is established. At first, node1 is the Primary node and node2 is the Standby node.

Next, the simulation master node (node1) fails to work, and the slave node (node2) is manually switched to the master node. Then the original master node (node1) is added to the system as a slave node At this time, node2 is the primary and node1 is the standby.

Finally, the simulation master node (node2) can not work, and the slave node (node1) is manually switched to the master node. Then node2 is added to the system as a slave node At this time, node1 is the primary and node2 is the standby.

2 create node1 and node2, and configure master-slave stream replication

The parent directory is:


Two subdirectories, testdb113 and testdb113b, belong to two nodes, node1 and node2

 testdb113  --> node1
 testdb113b --> node2

2.1 create primary node1

mkdir testdb113
initdb -D ./testdb113
pg_ctl -D ./testdb113 start

2.1.1 node1: create a database account for stream replication

  • Create the account 'replicauser', which exists in node1 and node2.


2.1.2 node1: create archive directory

mkdir ./testdb113/archive

2.1.3 node1: set parameters in the configuration file (postgresql.conf)

#for the primary wal_level = replica 
synchronous_commit = remote_apply 
archive_mode = on 
archive_command = 'cp %p /Users/tom/testdb113/archive/%f'max_wal_senders =  10 
wal_keep_segments = 10 
synchronous_standby_names = 'pgslave001'#for the standbyhot_standby = on

2.1.4 node1: edit the file pg_hba.conf, and set the legal address:

  • add following to then end of pg_hba.conf

host     replication    replicauser            md5
host     replication    replicauser          ::1/128                 md5

2.1.5 node1: pre edit file: recovery.done

  • Note: this file will be used later. When node1 becomes a slave node, you need to rename recovery.done to recovery.conf

standby_mode = 'on'recovery_target_timeline = 'latest'primary_conninfo = 'host=localhost port=6432 user=replicauser password=abcdtest application_name=pgslave001'restore_command = 'cp /Users/tom/testdb113b/archive/%f %p'trigger_file = '/tmp/postgresql.trigger.5432'

2.2 create and configure a slave node (node2)

mkdir testdb113b
pg_basebackup  -D ./testdb113bchmod -R 700 ./testdb113b

2.2.1 node2: edit the file postgresql.conf and set parameters

  • This file is from node1 (due to the use of PG ﹣ basebackup), only the individual parameters need to be changed.

#for the primary port = 6432 
wal_level = replica 
synchronous_commit = remote_apply 
archive_mode = on 
archive_command = 'cp %p /Users/tom/testdb113b/archive/%f'max_wal_senders =  2 
wal_keep_segments = 10 
synchronous_standby_names = 'pgslave001'#for the standbyhot_standby = on

2.2.2 node2: create archive directory

  • Ensure archive directory exists

mkdir ./testdb113b/archive

2.2.3 node2: check / edit the file PG? HB a.conf

  • This file is from node1 and does not need to be edited

2.2.4 node2: create / edit the file recovery.conf

  • When PG starts, if the file recovery.conf exists, it works in recovery mode as a slave node. If the current node is upgraded to the primary node, the file recovery.conf will be renamed recovery.done.

standby_mode = 'on'recovery_target_timeline = 'latest'primary_conninfo = 'host=localhost port=5432 user=replicauser password=abcdtest application_name=pgslave001'restore_command = 'cp /Users/tom/testdb113/archive/%f %p'trigger_file = '/tmp/postgresql.trigger.6432'

2.3 simple test

2.3.1 restart the primary node (node1):

pg_ctl -D ./testdb113 restart

2.3.2 start slave node (node2)

pg_ctl -D ./testdb113b start

2.3.3 making data changes on node1

  • node1 supports data writing and reading

psql -d postgres
postgres=# create table test ( id int, name varchar(100));CREATE TABLEpostgres=# insert into test values(1,'1');INSERT 0 1

2.3.4 node node2 read data

  • node2 is a standby node, which can only be read but not written.

psql -d postgres -p 6432
postgres=# select * from test;
 id | name 
  1 | 1
(1 row)

postgres=# insert into test values(1,'1');
ERROR:  cannot execute INSERT in a read-only transactionpostgres=#

3. When the simulation master node node1 fails to work, upgrade the slave node node2 to the master node

3.1 stop the main node node1

  • After node1 stops, only node2 works and only supports data reading. Quality service:

pg_ctl -D ./testdb113 stop

3.1.1 attempt to connect to node node1 failed

  • Because node1 stopped.

Ruis-MacBook-Air:tom$ psql -d postgres
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

3.1.2 try to connect to node2, success; insert data to node2, failure

  • node2 is working, but read only

Ruis-MacBook-Air:~ tom$ psql -d postgres -p 6432
psql (11.3)
Type "help" for help.

postgres=# insert into test values(1,'1');
ERROR:  cannot execute INSERT in a read-only transaction

3.2 node2: upgrade node2 to master

  • After ascension, node2 becomes primary, able to read and write. How much is gastroscopy examination in Jiaozuo Guoyi gastrointestinal hospital? Affordable fee, rest assured:

touch /tmp/postgresql.trigger.6432

3.3 node2: insert data to node2, block and wait

  • Node2 is the primary node, which can insert data successfully. However, because synchronous flow replication is set up, node2 must wait for a slave node to apply data changes to the slave node, and then return the corresponding LSN to the master node.

postgres=# insert into test values(2,'2');

3.4 node1: create the file recovery.conf

  • Use the previously established file recovery.done as a template to quickly create recovery.conf.

mv recovery.done recovery.conf

3.5 node1: start node1 as the slave node (Standby)

  • After startup, there are two more nodes in the system: node2 is the master and node1 is the slave.

pg_ctl -D ./testdb113 start

3.6 insert data to node2 (see 3.3) and return successfully

  • Since node node1 joins the system as a slave node, the data change of the master node is applied, and then the LSN of the corresponding WAL is returned to the master node, so that the master node waits for the response, so that the transaction can continue.

postgres=# insert into test values(2,'2');INSERT 0 1postgres=#

3.7 read data in node1 and node2 respectively

  • Since both nodes only read data, and the stream replication works normally, all the read results are the same

postgres=# select * from test;
 id | name 
  1 | 1
  2 | 2
(2 rows)

4. When simulation node2 fails to work, promote node1 to primary

4.1 stop node2

pg_ctl -D ./testdb113b stop

4.1.1 attempt to access node2, failed

  • Because node2 has stopped

Ruis-MacBook-Air:~ tom$ psql -d postgres -p 6432
psql: could not connect to server: No such file or directory
	Is the server running locally and accepting
	connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

4.1.2 node1: connect to node1, success; insert data to node1, failure

  • node1 read-only

Ruis-MacBook-Air:~ tom$ psql -d postgres
psql (11.3)
Type "help" for help.

postgres=# insert into test values(3,'3');ERROR:  cannot execute INSERT in a read-only transaction

4.2 node1: upgrade node1 to primary

touch /tmp/postgresql.trigger.5432

4.3 node1: insert data to node1, block and wait

  • node1 successfully inserts the data, but waits for the remote application from the node

postgres=# insert into test values(3,'3');

4.4 node2: create the file recovery.conf

mv recovery.done recovery.conf

4.5 node2: start node2 as the slave node (Standby)

pg_ctl -D ./testdb113b start

4.6 node1: insert data (see 4.3) and return successfully.

postgres=# insert into test values(3,'3');INSERT 0 1

4.7 read data in node1 and node2 respectively

  • Since both nodes only read data, and the stream replication works normally, all the read results are the same

postgres=# select * from test;
 id | name 
  1 | 1
  2 | 2
  3 | 3
(3 rows)

5 automation solutions

When the master node cannot work, it is necessary to promote a slave node to the master node For manual promotion, you can use the trigger file, or use the command 'PG ﹣ CTL promote'.

Keywords: Database PostgreSQL Unix socket

Added by RedMaster on Tue, 19 Nov 2019 17:58:44 +0200