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:
/Users/tom
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.
CREATE ROLE replicauser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'abcdtest';
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 127.0.0.1/32 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: http://jz.lieju.com/xuankeyiyuan/37572153.htm
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 postgres=#
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: http://jz.lieju.com/xuankeyiyuan/37571889.htm
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 postgres=#
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'.