7 minutes
PostgreSQL: Basic Replication with Synchronous and Asynchronous Hot Stand-by
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 a series of PostgreSQL Servers and set them up replication between them. This article describes how.
Note: This is an old article which contains content that is out of date.
The Desired Setup
pg00001 (master) -----> pg00002 (synchronous hot stand-by)
| \
| \
| \
| \
| ------------> pg00011 (asynchronous hot stand-by)
|
-----------------> pg00012 (asynchronous hot stand-by)
In this lab, pg00001 will initially be the read/write master, with each of the other servers implemented as hot stand-by servers. Any create, update or deletions executed on the master will be replicated to each of the hot stand-by servers. If this were a production set-up, one of the hot stand-by servers will be co-located with the master and the remaining two would be situated in an alternate data centre. Hence the master and co-located slave will use synchronous replication and the remote slaves will use asynchronous replication.
Prerequisites
A lab running 4 Ubuntu Server installations, each with OpenSSH Server installed to enable remote administration. I’ve also set-up a DNS server so that I can use host names instead of IP addresses.
Initial Installation
On a Debian based distro, install Postgres with apt-get:
user@pg00001:~$ sudo apt-get install -y postgresql-9.1
Then on each host edit the host-based authentication file to allow the replication and postgres users appropriate access.
user@pg00001:~$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf
Under the section “# IPv4 local connections:”, add the lines
host replication repl samenet md5
host all postgres samenet md5
Save your changes and quit the editor. Don’t forget to do this on each of the other hosts, or as the postgres user copy the file from pg00001 to each of the other hosts using scp
Tweak the postgresql.conf file
user@pg00001:~$ sudo vi /etc/postgresql/9.1/main/postgresql.conf
Uncomment and amend the following:
#listen_addresses = 'localhost'
listen_addresses = '*'
#wal_level = ''
wal_level = 'hot_standby'
#archive_mode = on
archive_mode = on
#archive_command = ''
archive_command = 'true'
#max_wal_senders = 0
max_wal_senders = 3
## Do this on pg00001 Only - It makes the replication to pg00002 synchronous
#synchronous_standby_names = ''
synchronous_standby_names = 'pg00002_main'
## /Do this on pg00001 Only
#hot_standby = off
hot_standby = on
Additional Configuration for the Master PostgreSQL Server
Create a replication user on the master (pg00001):
user@pg00001:~$ sudo -u postgres psql -c "CREATE USER repl REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD '<yourpassword>';"
Additional Configuration for the Slave PostgreSQL Servers
Modify the recovery configuration on each slave (pg00002, pg00011, pg00012):
user@pg00002:~$ sudo -u postgres vi /var/lib/postgresql/9.1/main/recovery.conf
Insert the following lines:
standby_mode = 'on'
primary_conninfo = 'host=pg00001 port=5432 user=repl password=<yourpassword> application_name=pg00002_main'
trigger_file = '/tmp/postgressql.trigger.5432'
recovery_target_timeline = 'latest'
Repeat for each of the other slaves, substituting the application name as appropriate on each of them (i.e. pg00011_main on pg00011)
Restart PostgreSQL and Verify
On each server, restart the postgresql service:
user@pg00001:~$ sudo service postgresql restart
user@pg00002:~$ sudo service postgresql restart
user@pg00011:~$ sudo service postgresql restart
user@pg00012:~$ sudo service postgresql restart
On the master, check the replication status:
user@pg00001:~$ sudo -u postgres 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 | pg00002_main | 10.10.10.52 | | 58042 | 2016-01-21 11:50:15.659503+00 | streaming | 0/18000650 | 0/18000650 | 0/18000650 | 0/18000650 | 1 | sync
12597 | 16384 | repl | pg00012_main | 10.10.10.62 | | 54584 | 2016-01-21 11:51:47.316668+00 | streaming | 0/18000650 | 0/18000650 | 0/18000650 | 0/18000650 | 0 | async
12575 | 16384 | repl | pg00011_main | 10.10.10.61 | | 40207 | 2016-01-21 11:50:46.75577+00 | streaming | 0/18000650 | 0/18000650 | 0/18000650 | 0/18000650 | 0 | async
(3 rows)
If you experiencing any problems, your starting point is the PostgreSQL log files. On the master, you should see:
postgres@pg00001:~$ tail -f /var/log/postgresql/postgresql-9.1-main.log
2016-01-27 13:12:56 GMT LOG: database system was shut down at 2016-01-27 13:12:54 GMT
2016-01-27 13:12:56 GMT LOG: database system is ready to accept connections
2016-01-27 13:12:56 GMT LOG: autovacuum launcher started
And on the slaves:
postgres@pg00011:~$ tail -f /var/log/postgresql/postgresql-9.1-main.log
2016-01-27 13:14:13 GMT LOG: database system was shut down in recovery at 2016-01-27 13:14:12 GMT
2016-01-27 13:14:13 GMT LOG: entering standby mode
2016-01-27 13:14:13 GMT LOG: consistent recovery state reached at 0/10000078
2016-01-27 13:14:13 GMT LOG: invalid record length at 0/10000078
2016-01-27 13:14:13 GMT LOG: database system is ready to accept read only connections
2016-01-27 13:14:13 GMT LOG: streaming replication successfully connected to primary
Any log messages stating “incomplete startup packet” can be ignored.
Try creating a database, some tables and inserting some data on the master database. Then:
- re-check pg_stat_replication on master
- check for the appearance of your new database on the slaves
For example, on the PostgreSQL master database:
user@pg00001:~$ sudo -u postgres psql
psql (9.1.19)
Type "help" for help.
postgres=# create database test ;
CREATE DATABASE
postgres=# postgres=# \c test
You are now connected to database "test" as user "postgres".
test=#create table repl_test (seq serial not null, name varchar not null) ;
NOTICE: CREATE TABLE will create implicit sequence "repl_test_seq_seq" for serial column "repl_test.seq"
CREATE TABLE
test=# insert into repl_test (name) values ('bodgit') ;
INSERT 0 1
test=# select * from repl_test;
seq | name
-----+--------
1 | bodgit
(1 row)
test=# 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
---------+----------+---------+------------------+-------------+-----------------+-------------+-------------------------------+-----------+---------------+----------------+----------------+-----------------+---------------+------------
1554 | 16384 | repl | pg00012_main | 10.10.10.62 | | 36035 | 2016-01-27 13:13:00.539982+00 | streaming | 0/2001DC88 | 0/2001DC88 | 0/2001DC88 | 0/2001DC88 | 0 | async
1555 | 16384 | repl | pg00011_main | 10.10.10.61 | | 54166 | 2016-01-27 13:13:00.779446+00 | streaming | 0/2001DC88 | 0/2001DC88 | 0/2001DC88 | 0/2001DC88 | 0 | async
1558 | 16384 | repl | pg00002_main | 10.10.10.52 | | 53668 | 2016-01-27 13:14:13.786833+00 | streaming | 0/2001DC88 | 0/2001DC88 | 0/2001DC88 | 0/2001DC88 | 1 | sync
(3 rows)
test=# \q
user@pg00001:~$
Then on one of the slaves:
user@pg00002:~$ sudo -u postgres psql
psql (9.1.19)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
template0 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test | postgres | UTF8 | en_GB.UTF-8 | en_GB.UTF-8 |
(7 rows)
postgres=#postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------+----------+----------
public | repl_test | table | postgres
public | repl_test_seq_seq | sequence | postgres
(2 rows)
test=# select *
test-# from repl_test ;
seq | name
-----+--------
1 | bodgit
(1 row)
test=# \q
user@pg00002:~$
Finally, give yourself a pat on the back. And then read onto the next article; PostgreSQL Replication and Failover.
Further Checks
Here are some further useful SQL snippets useful to verify that PostgreSQL replication is working.
Transaction Log (XLOG) Location
On a master PostgreSQL DB:
postgres=# SELECT pg_current_xlog_location();
pg_current_xlog_location
--------------------------
0/21022E48
(1 row)
Then on a corresponding slave:
postgres=# select pg_last_xlog_receive_location();
pg_last_xlog_receive_location
-------------------------------
0/21022E48
(1 row)
If inserts, updates and deletions are continuously being applied to your master and repliciation is asynchronous, then don’t be surprised if the XLOG locations don’t match up. You should see the XLOG location on the master continually increasing and the XLOG receive location on the slave continuously playing catch up.
Time Since Last XLOG Replay
On a slave PostgreSQL DB you can check the time elapsed since the last transaction log was applied:
postgres=# select now() - pg_last_xact_replay_timestamp() AS time_lag;
time_lag
-----------------
00:00:04.202395
(1 row)
Of course, if you are in a situation where some time has elapsed since the last insert, update or delete on the master, then this time span will reflect that, i.e if the last update to the master database was 90 minutes prior to running the above statement on a slave, then I would expect the time_lag returned to be 00:01:30.NNNNNN (HH:MM:SS).
Useful Reading
There’s a book dedicated to PostgreSQL replication that I would recommend reading before even considering PostgreSQL replication in a production environment. Appriopriately enough, it’s titled “PostgreSQL Replication”. Authored by Zoltan Boszormenyi and Hans-Jurgen Schonig, it’s probably available from any good book store. The ISBN is 978-1-84951-672-3.