1 Overview

We'll build two PostgreSQL database nodes, node1 and node2, then try some manually failover testing to understand how the failover works. In the testing, we use PostgreSQL 11.3 .

The first step is to build two PostgreSQL nodes to work in hot-standy mode. To make things simple, we build two PostgrSQL nodes (node1, node2) on the same physical machine. The Primary is node1 and the Standby is node2. They work in hot-standy mode using synchronized stream replcation.

The next step is to simulate node1 failure, and then try to promote node2 as the Primary. After node2 is promoted, we bring node1 back and start node1 as a Standby.

At last, we simulate node2 failure and try to promote node1 as the Primary. We'll then bring node2 back as a Standby.

2 Build the Environment

The parent directory is:

/Users/tom

two folders for two nodes( node1, node2 ):

 testdb113  --> node1
 testdb113b --> node2

2.1 Create node1, the Primary

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

2.1.1 node1: create the user for stream replication

User 'replicauser' will exist on both the Primary and the Standby.

CREATE ROLE replicauser WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'abcdtest';

2.1.2 node1: create the archive folder

mkdir ./testdb113/archive

2.1.3 node1: edit 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 standby
hot_standby = on

2.1.4 node1: edit pg_hba.conf

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.4 node1: create/edit recovery.done

Note: this file is used later when node1 is a Standby. We'll move 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 the Standby(node2)

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

2.2.1 node2: edit postgresql.conf

This file comes from node1, so just modify a few of parameters.

#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 standby
hot_standby = on

2.2.2 node2: create the archive folder

make sure this fold exists.

mkdir ./testdb113b/archive

2.2.3 node2: check pg_hba.conf

This file comes from the node1, and there is no need to change this file.

2.2.4 node2: create/edit recovery.conf

When pg starts, if it sees recovery.conf, it'll work in recovery mode. After promotion, it changes recovery.conf to 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.2.5 Restart the Primary(node1):

pg_ctl -D ./testdb113 restart

2.2.6 Start the Standby(node2)

pg_ctl -D ./testdb113b start

2.2.7 Make Change on node1

Since node1 is a Primary node, it support both read and write.

psql -d postgres

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

2.2.8 Read from node2

As a Standby, node2 only support reading.

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

3 When node1 is out of service, promote node2 as the Prmary

3.1 shutdown node1, the primary node

After node1 is stopped, we have only node2 working and node2 is a Standby which means it is readi-only.

pg_ctl -D ./testdb113 stop

3.1.1 trying to connect to node1 fails

node1 is 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 connecting to node2 succeeds, inserting to node2 fails

node2 is alive , 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: promote node2

After promotion, node2 is the new Primary, and supports both read and write.

touch /tmp/postgresql.trigger.6432

3.3 node2: inserting to node2 blocks

node2 can insert data successfull. But since nodes2 is configured with synchronized stream replication, it waits for the remote apply forever.

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

3.4 node1: create recovery.conf for node1

We use recovery.done to create recovery.conf quickly.

mv recovery.done recovery.conf

3.5 node1: start node1 as a standby

now we have two nodes, node2 is the Primary and node1 is the Standby.

pg_ctl -D ./testdb113 start

3.6 inserting at node2 finishes

this is because node1 applies change and responses to node2 with the latest LSN it applies.

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

3.7 read from node1 and node2

reading data from both nodes, we get the same result.

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

4 When node2 is out of service, promote node1 as the Prmary

4.1 shutdown node2

pg_ctl -D ./testdb113b stop

4.1.1 trying to connect to node2 fails

node2 is 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: connecting to node1 succeeds, inserting to node1 fails

node1 is alive , but 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: promote node1

touch /tmp/postgresql.trigger.5432

4.3 node1: inserting blocks

node1 is waiting for the remote-apply.

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

4.4 node2: create recovery.conf

mv recovery.done recovery.conf

4.5 node2: start node2 as a Standby

pg_ctl -D ./testdb113b start

4.6 node1: inserting returns

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

INSERT 0 1

4.7 node1 and node2: read from table

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

5 The Automatic Solutions

To promote a Standby node, we can either use the trigger file, or use the 'pg_ctl promote' command. When the Primary node is not working, we can promote a Standby node manually.

You may need an automatic solution to monitor all the database nodes and do failover/switchover automatically. You can find lots of such solutions from the internet, or you can write your own.

rudi

PostgreSQL-113-try-the-hot-standby-failover