Replication slots in PostgreSQL

Up to the ninth version in PostgreSQL, WAL archiving was used to create a “warm” backup server. Version 9.0 introduced streaming replication with the ability to create a hot read-only server. In the next version of PostgreSQL 9.4, a new functionality will appear to create streaming replication called replication slots. Consider what it is and how it differs from previous methods.
To date, the first candidate for releases is available. As a test bench, 2 virtual machines for Ubuntu 14.04 were selected. The assembly and installation process is the same for the primary and backup servers. We put from the source, pre-install the necessary packages:

sudo apt-get update && sudo apt-get -y install make g++ checkinstall libreadline6-dev zlib1g-dev 

Download and unpack the archive from the repository:

 wget https://ftp.postgresql.org/pub/source/v9.4rc1/postgresql-9.4rc1.tar.gz tar xzf postgresql-9.4rc1.tar.gz 

We assemble and install the package:
')

 cd postgresql-9.4rc1/ ./configure make sudo checkinstall 

By default, binaries for working with DBMS are placed in / usr / local / pgsql /.
Add a postgres user to the system:

 sudo useradd -M postgres 

Create a directory for the cluster:

 sudo mkdir -p /data/db sudo chown postgres:postgres /data/db sudo chmod 0700 /data/db 

Next, perform the actions on the main server. Initialize the cluster:

 sudo -u postgres /usr/local/pgsql/bin/initdb -D /data/db 

In addition to the cluster structure, initdb will create default configs. Create a pg_log directory in a cluster where logs will be stored:

 sudo -u postgres mkdir /data/db/pg_log sudo -u postgres chmod 0700 /data/db/pg_log 

Add entries to pg_hba.conf for connecting users and so that the backup server can retrieve WAL logs from the main server:

 host all all 192.168.1.0/24 md5 host replication replica 192.168.1.108/32 md5 

In the postgresql.conf config, edit the parameters:
listen_addresses = '*' - listen for incoming connections on all interfaces
wal_level = hot_standby - the required format for WAL logs for replication
max_wal_senders = 2 - the number of simultaneous connections for replication
logging_collector = on - add logs to pg_log

We start our cluster:

 sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /data/db start 

Look at the state of the processes:

 ps aux | grep postgres postgres 21295 0.0 0.0 23700 604 ? Ss 13:39 0:00 postgres: logger process postgres 21297 0.0 13.6 170880 138408 ? Ss 13:39 0:01 postgres: checkpointer process postgres 21298 0.0 5.0 170784 51076 ? Ss 13:39 0:00 postgres: writer process postgres 21299 0.0 0.5 170648 5148? Ss 13:39 0:00 postgres: wal writer process postgres 21300 0.0 0.1 171052 1836 ? Ss 13:39 0:00 postgres: autovacuum launcher process postgres 21301 0.2 0.1 25924 1060 ? Ss 13:39 0:17 postgres: stats collector process 

Create a replica user with replication rights:

 /usr/local/pgsql/bin/psql -U postgres -c "create user replica with replication encrypted password '123'" 

Create a test database with data:

 /usr/local/pgsql/bin/createdb -U postgres testdb /usr/local/pgsql/bin/psql -U postgres -d testdb -c "create table testtable (id serial, data text)" /usr/local/pgsql/bin/psql -U postgres -d testdb -c "insert into testtable select nextval('testtable_id_seq'::regclass), md5(generate_series(1,1000000)::text)" 

Set up a backup server.
Create a directory for the cluster:

 sudo mkdir -p /data/db sudo chmod 0700 /data/db sudo chown postgres:postgres /data/db 

Using the pg_basebackup utility, we will make a backup copy of the main server:

 sudo -u postgres /usr/local/pgsql/bin/pg_basebackup -h 192.168.1.103 -U replica -D /data/db -X s 

pg_basebackup copies the entire contents of the cluster, including configs, so we change the hot_standby parameter to the on state in postgresql.conf
Create a recovery.conf file in the cluster directory, in which we specify the connection parameters to the main server:

 standby_mode='on' primary_conninfo='host=192.168.1.103 port=5432 user=replica password=123' 

Run the cluster on the backup server:

 sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /data/db start 

After that, the wal_sender process should start on the main server, and on the backup wal_receiver:

You can view the status of replication through the pg_stat_replication view on the main server

 testdb=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 21987 usesysid | 16384 usename | replica application_name | walreceiver client_addr | 192.168.1.108 client_hostname | client_port | 56674 backend_start | 2014-11-25 18:30:09.206434+03 backend_xmin | state | streaming sent_location | 0/5A2D8E60 write_location | 0/5A2D8E60 flush_location | 0/5A2D8E60 replay_location | 0/5A2D8E60 sync_priority | 0 sync_state | async 

It can be seen that the primary and backup servers are synchronized. Now we will generate some more test data and immediately after that we will look at the status of replication.

 testdb=# insert into testtable select nextval('testtable_id_seq'::regclass), md5(generate_series(1,1000000)::text); INSERT 0 1000000 testdb=# select * from pg_stat_replication; -[ RECORD 1 ]----+------------------------------ pid | 21987 usesysid | 16384 usename | replica application_name | walreceiver client_addr | 192.168.1.108 client_hostname | client_port | 56674 backend_start | 2014-11-25 18:30:09.206434+03 backend_xmin | state | streaming sent_location | 0/63800000 write_location | 0/63680000 flush_location | 0/63680000 replay_location | 0/6136E160 sync_priority | 0 sync_state | async 

Here we observe that the backup server has taken all the WAL logs from the main server, but has not yet managed to apply them all, so it is behind the main one. By default, in postgres replication occurs asynchronously using WAL logs, these are fixed-size binary files of 16 MB located in the pg_xlog directory. Their number can be changed using the checkpoint_segments and wal_keep_segments parameters. When the amount of changed data in the cluster exceeds the total size of WAL logs, the checkpointer process starts, which resets the WAL logs to the data files. After this, WAL logs are recreated again. In the current stable version of postgres, the primary server does not consider the state of the backup server. Therefore, if the backup is too “behind” the main one, then the main WAL logs will be recreated before the backup takes them. Let's try to simulate this situation.
Temporarily prohibit the backup server from connecting to port 5432:

 sudo iptables -A OUTPUT -m tcp -p tcp —dport 5432 -j DROP 

Generate more data on the main server:

 testdb=# insert into testtable select nextval('testtable_id_seq'::regclass), md5(generate_series(1,1000000)::text); INSERT 0 1000000 

Let's reset the iptables rule and see the logs of the backup server, in which we see such a nasty picture.

 LOG: started streaming WAL from primary at 0/78000000 on timeline 1 FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 000000010000000000000078 has already been removed 

The main server deleted the WAL logs before the backup server managed to pick them up. Now you have to re-backup the main server. The problem is that the primary server does not take into account the status of the backup. Therefore, if there are problems with the network or just a slow channel between servers, then with heavy load and / or data change on the main server, there is a risk of replication failure. A partial solution is to increase the value of the wal_keep_segments parameter, and enable WAL archiving . But in version 9.4 replication slots will appear. Consider how it works:
On the main server, create a replication slot:

 testdb=# SELECT pg_create_physical_replication_slot('standby_slot'); -[ RECORD 1 ]-----------------------+---------------- pg_create_physical_replication_slot | (standby_slot,) 

 testdb=# select * from pg_replication_slots; -[ RECORD 1 ]+------------- slot_name | standby_slot plugin | slot_type | physical datoid | database | active | f xmin | catalog_xmin | restart_lsn | 

On the backup, add to the existing contents of the file recovery.conf line
primary_slot_name = 'standby_slot'

After restarting the backup server, we will again disconnect it from the main server and generate test data on the main one that exceeds the amount of WAL logs:

 testdb=# insert into testtable select nextval('testtable_id_seq'::regclass), md5(generate_series(1,10000000)::text); 

Let's look at the parameters of WAL logs in the system, and then the actual number of log files in the pg_xlog directory:

 testdb=# show checkpoint_segments; -[ RECORD 1 ]-------+-- checkpoint_segments | 3 

 testdb=# show wal_keep_segments; -[ RECORD 1 ]-----+-- wal_keep_segments | 0 

 testdb=#\! ls /data/db/pg_xlog | wc -l 50 

To calculate the maximum number of WAL files in the system, use the formula : (2 + checkpoint_completion_target) * checkpoint_segments + 1.
However, the current number of WAL logs in the system is much higher. Replication slots store information about the number of WAL logs downloaded by each backup server. Now WAL logs will accumulate until the last backup server picks them up or until the replication slot is removed. As WAL logs are downloaded, the pg_xlog directory on the main server will decrease. Having reset the iptables rule on the backup server, we see in the logs that replication has resumed.

 testdb=#\! tail -f /data/db/pg_log/postgresql-2014-11-27_191036.log Is the server running on host "192.168.1.103" and accepting TCP/IP connections on port 5432? LOG: started streaming WAL from primary at 0/A0000000 on timeline 1 

Replication slots is a great tool that improves the reliability and convenience of replication in PostgreSQL.

Description replication slots on the official website of PostgreSQL: www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS