Background

I’ve spent the last few days equipping myself with the knowledge I need to support failover of our data tier from production to disaster recovery and vice-versa. In the lab I built a series of PostgreSQL Servers and set up replication between them. In this article I explore failover.

Note: This is an old article and contains content which is out of date.

Failover Scenario

I’m mimicking the production setup with a master (pg00001) and it’s co-located synchronously replicated hot stand-by (pg00002).

pg00001 (master) -----> pg00002 (synchronous hot stand-by)
	| \
	|  \
	|   \
	|    \
	|     ------------> pg00011 (asynchronous hot stand-by)
    |
     -----------------> pg00012 (asynchronous hot stand-by)

pg00011 and pg00012 are asynchronously replicated hot stand-by servers which if in production, would be located at a secondary datacentre. In this scenario, an issue occurs that means we are forced to failover to the DR location.

Failover Steps

With PostgreSQL 9.1 I ran through the following sequent of events to effect the failover:

Check that the hot-standby we plan to promote (pg00011) doesn’t have any lingering configuration that will make it replicate synchronously. It’s possible that this may remain from a previous failover, and although it is the final intention, promoting the hot stand-by in this configuration will prevent any updates or inserts from the application reaching a commited state until the hot stand-bys are re-staged. Edit the PostgreSQL configuration:

postgres@pg00011:~$ vi /etc/postgresql/9.1/main/postgresql.conf

Find the following line and amend as follows:

postgres@pg00011:~$ synchronous_standby_names = ''

Save changes and close the file. Reload postgress on this hot stand-by server:

postgres@pg00011:~$ service postgresql reload

Stop postgres on master (*assuming application tier switched off)

postgres@pg00001:~# service postgresql stop

Create recovery trigger file on slave that is to become master (pg00011):

postgres@pg00011:~# touch /tmp/postgressql.trigger.5432

Quickly check the log files on the new master:

postgres@pg00011:~$ tail /var/log/postgresql/postgresql-9.1-main.log
2016-01-21 09:08:22 GMT LOG:  trigger file found: /tmp/postgressql.trigger.5432
2016-01-21 09:08:22 GMT LOG:  redo done at 0/14000020
2016-01-21 09:08:22 GMT LOG:  last completed transaction was at log time 2016-01-20 15:45:13.719496+00
2016-01-21 09:08:22 GMT LOG:  selected new timeline ID: 2
2016-01-21 09:08:23 GMT LOG:  archive recovery complete
2016-01-21 09:08:23 GMT LOG:  database system is ready to accept connections
2016-01-21 09:08:23 GMT LOG:  autovacuum launcher started

Stop postgres on other slaves - those that are to remain hot stand-by (pg00002,pg0012)

postgres@pg00002:~# service postgresql stop
postgres@pg00012:~# service postgresql stop

Archive main cluster on new synchronous hot stand-by (pg0012)

postgres@pg00012:~# mv 9.1/main 9.1/zz_main

Or if confident / insufficient space etc. remove main cluster:

postgres@pg00012:~# rm 9.1/main 9.1/zz_main

On new synchronous hot stand-by (pg00012), pg_basebackup the new master (pg00011)

postgres@pg00012:~# pg_basebackup -h pg00011 -U repl -D 9.1/main
postgres@pg00012:~# rm 9.1/main/recovery.done

Rsync the backup of master to each of the asynchronous slaves:

postgres@pg00012:~# rsync -zr --inplace --delete 9.1/main pg00001:/var/lib/postgresql/9.1
postgres@pg00001:~# rsync -zr --inplace --delete 9.1/main pg00002:/var/lib/postgresql/9.1

For each hot stand-by slave (pg00001,pg00002,pg00012), create a /var/lib/postgressql/9.1/recovery.conf as follows:

postgres@pg00001:~$ vi 9.1/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=pg00011 port=5432 user=repl password=Password1 application_name=pg00001_main'
trigger_file = '/tmp/postgressql.trigger.5432'
recovery_target_timeline = 'latest'
	
postgres@pg00002:~$ vi 9.1/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=pg00011 port=5432 user=repl password=Password1 application_name=pg00002_main'
trigger_file = '/tmp/postgressql.trigger.5432'
recovery_target_timeline = 'latest'

postgres@pg00012:~$ vi 9.1/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host=pg00011 port=5432 user=repl password=Password1 application_name=pg00012_main'
trigger_file = '/tmp/postgressql.trigger.5432'
recovery_target_timeline = 'latest'

Copy postmaster.opts from zz_main on pg00012 to main or recreate the postmaster.opts file as follows:

postgres@pg00001:~$ echo -e /usr/lib/postgresql/9.1/bin/postgres \"-D\" \"/var/lib/postgresql/9.1/main\" \"-c\" \"config_file=/etc/postgresql/9.1/main/postgresql.conf\" > ~/9.1/main/postmaster.opts
postgres@pg00002:~$ echo -e /usr/lib/postgresql/9.1/bin/postgres \"-D\" \"/var/lib/postgresql/9.1/main\" \"-c\" \"config_file=/etc/postgresql/9.1/main/postgresql.conf\" > ~/9.1/main/postmaster.opts
postgres@pg00012:~$ echo -e /usr/lib/postgresql/9.1/bin/postgres \"-D\" \"/var/lib/postgresql/9.1/main\" \"-c\" \"config_file=/etc/postgresql/9.1/main/postgresql.conf\" > ~/9.1/main/postmaster.opts

Symlink TLS Certs:

postgres@pg00001:~$ ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem 9.1/main/server.crt
postgres@pg00001:~$ ln -s /etc/ssl/private/ssl-cert-snakeoil.key 9.1/main/server.key
postgres@pg00002:~$ ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem 9.1/main/server.crt
postgres@pg00002:~$ ln -s /etc/ssl/private/ssl-cert-snakeoil.key 9.1/main/server.key
postgres@pg00012:~$ ln -s /etc/ssl/certs/ssl-cert-snakeoil.pem 9.1/main/server.crt
postgres@pg00012:~$ ln -s /etc/ssl/private/ssl-cert-snakeoil.key 9.1/main/server.key

Restart Postgres

postgres@pg00001:~$ service postgresql start
postgres@pg00002:~$ service postgresql start
postgres@pg00012:~$ service postgresql start

Almost there. We need to make the recently promoted master synchronously replicate to it’s co-located hot stand-by partner. That’s the reverse of the step undertaken before we promoted it.

postgres@pg00011:~$ vi /etc/postgresql/9.1/main/postgresql.conf

Find the following line and amend as follows:

postgres@pg00011:~$ synchronous_standby_names = 'pg00012_main'

Save changes and close the file. Reload postgress on this hot stand-by server:

postgres@pg00011:~$ service postgresql reload

Verify Changes:

Check log entries on synchronous slave:

postgres@pg00012:~$ tail /var/log/postgresql/postgresql-9.1-main.log
2016-01-21 11:50:15 GMT LOG:  entering standby mode
2016-01-21 11:50:15 GMT LOG:  streaming replication successfully connected to primary
2016-01-21 11:50:15 GMT LOG:  incomplete startup packet
2016-01-21 11:50:16 GMT LOG:  redo starts at 0/17000020
2016-01-21 11:50:16 GMT LOG:  consistent recovery state reached at 0/170000D8
2016-01-21 11:50:16 GMT LOG:  database system is ready to accept read only connections

Repeat the above check on the asynchronous slaves (pg00001 and pg00002).

On the new master check replication status:

postgres@pg00011:~$ psql -c "select * from pg_stat_replication"
 procpid | usesysid | usename | application_name | client_addr | client_hostname | client_port |         backend_start         |   state   | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
---------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
   12572 |    16384 | repl    | pg00012_main     | 10.10.10.62 |                 |       58042 | 2016-01-21 11:50:15.659503+00 | streaming | 0/18000650    | 0/18000650     | 0/18000650     | 0/18000650      |             1 | sync
   12597 |    16384 | repl    | pg00002_main     | 10.10.10.52 |                 |       54584 | 2016-01-21 11:51:47.316668+00 | streaming | 0/18000650    | 0/18000650     | 0/18000650     | 0/18000650      |             0 | async
   12575 |    16384 | repl    | pg00001_main     | 10.10.10.51 |                 |       40207 | 2016-01-21 11:50:46.75577+00  | streaming | 0/18000650    | 0/18000650     | 0/18000650     | 0/18000650      |             0 | async
(3 rows)

Following failover, we’re now in the situation where pg00011 is master, with it’s partner now acting as a synchronous slave. The two remote PostgreSQL servers are now asynchronous slaves.

pg00011 (master) -----> pg00012 (synchronous hot stand-by)
	| \
	|  \
	|   \
	|    \
	|     ------------> pg00001 (asynchronous hot stand-by)
    |
     -----------------> pg00002 (asynchronous hot stand-by)

Considerations

  • These steps are based on running Ubuntu 12.04.5 LTS. With a different OS YMMV.
  • The instructions are involved with replication and failover of databases within the default cluster, ‘main’
  • With PostgreSQL 9.1 it doesn’t seem possible to switch slaves to the new timeline that’s instantiated upon promoting pg00011 to master (FATAL: timeline 2 of the primary does not match recovery target timeline 1). That necessitates the pg_basebackup to reinitialize the other slaves. Apparently this flaw is ironed out in PostgreSQL 9.3 (Current version: 9.5)