Streaming replication with PostgreSQL 10

Streaming replication with PostgreSQL 10

In this post, i will explain how to setup a streaming replication with PostgreSQL 10. I will not explain how to install PostgreSQL 10 on your system.

My setup is :

• master server : ip 172.17.0.2
• slave server : ip 172.17.0.3

 

Securise your communications and use SSL

This step is not mandatory but recommended. If you already have a SSL certificate, skip the first step.

• Generate a self signed certificate, see my previous post
• Setup SSL on PostgreSQL:
  1. Copy your private key and your certificate in the directory of your choice. Be carefull that the postgresql user can read them (usually user postgres on Linux or _postgresql on OpenBSD)
  2. Edit the file postgresql.conf and change these lines:

1. 1. ssl = on
   2. ssl_cert_file = '/etc/ssl/postgresql/cert/server.crt'
   3. ssl_key_file = '/etc/ssl/postgresql/private/server.key'

Of course, change the directory by yours. If you don’t specify a directory but only the filename, PostgreSQL will search them in the PGDATA directory.

 

Configure the master

• Create a role dedicated to the replication

• postgres=# CREATE ROLE replicate WITH REPLICATION LOGIN ;
• CREATE ROLE
• postgres=# set password_encryption = 'scram-sha-256';
• SET
• postgres=# \password replicate
• Enter new password:
• Enter it again:

 

• Verify that your PostgreSQL server listen on your interface. Edit postgresql.conf and change this line

• #listen_addresses = 'localhost'

by something like this

listen_addresses = '*'

#or

listen_addresses = 'xxx.xxx.xxx.xxx'

 

• Change the parameters for the streaming replication in postgresql.conf

• wal_level = replica
• max_wal_senders = 3 # max number of walsender processes
• wal_keep_segments = 64 # in logfile segments, 16MB each; 0 disables

If you think that the number you put in wal_keep_segments is enough, you can stop here. But if you are not sure, you should configure the archive_mode’ to store the wal segments.

archive_mode = on

archive_command = 'rsync -a %p postgres@slave:/home/postgresql_wal/%f'

   # placeholders: %p = path of file to archive

   #               %f = file name only

The archive_command will copy the wal segments on a directory that must be accessible by the standby server. In the example above, i use the rsync command to copy them directly on the standby itself.

Warning : if, like me, you use rsync, be sure to configure the ssh access by keys !! 
 

• Now allow your slave(s) to connect to the master. Edit pg_hba.conf and add something like this:

• hostssl     replication     replicate       xxx.xxx.xxx.xxx/yy      scram-sha-256

Replace xxx.xxx.xxx.xxx/yy by the ip of your slave or maybe by the subnet used by your slave if you want to have many. 
 

• Restart your master server

 

Setup the slave

Now that your master is ready, it’s time to configure the slave.

• Stop postgresql on the slave 
   
• Edit your postgresql.conf and pg_hba.conf and report the changes you made on the master (like this, your slave will have the same configuration and could act as a master) 
   
• Edit your postgresql.conf and change this line :

• hot_standby = on

 

• Go to your PGDATA directory and delete all the files. WARNING : if the files postgresql.conf and pg_hba.conf are in this directory, you must backup them (same for the certificate files) 
   
• Now we will copy all the data from the master with the pg_basebackup command. You must run this command as the postgresql user (postgres on Debian, _postgresql on OpenBSD for example)

• # su - postgres
• $ pg_basebackup -h 172.17.0.2 -D /var/lib/postgresql/10/main/ -P -U replicate --wal-method=stream
• Password:
• 23908/23908 kB (100%), 1/1 tablespace

Now, all your master’s data are copied on the slave. 
 

• Now create a file recovery.conf in your PGDATA directory

• standby_mode          = 'on'
• primary_conninfo      = 'host=172.17.0.2 port=5432 user=replicate password=MySuperPassword'
• trigger_file = '/tmp/MasterNow'
• #restore_command = 'cp /home/postgresql_wal/%f "%p"'

Here is an explanation for each line :

• • standby_mode=on : specifies that the server must start as a standby server
  • primary_conninfo : the parameters to use to connect to the master
  • trigger_file : if this file exists, the server will stop the replication and act as a master
  • restore_command : this command is only needed if you have used the archive_command on the master 
     
• Start the postgresql server

• 2018-03-11 19:08:55.777 UTC [8789] LOG:  listening on IPv4 address "127.0.0.1", port 5432
• 2018-03-11 19:08:55.777 UTC [8789] LOG:  could not bind IPv6 address "::1": Cannot assign requested address
• 2018-03-11 19:08:55.777 UTC [8789] HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
• 2018-03-11 19:08:55.786 UTC [8789] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
• 2018-03-11 19:08:55.820 UTC [8790] LOG:  database system was interrupted; last known up at 2018-03-11 18:58:20 UTC
• 2018-03-11 19:08:56.023 UTC [8790] LOG:  entering standby mode
• 2018-03-11 19:08:56.034 UTC [8790] LOG:  redo starts at 0/4000028
• 2018-03-11 19:08:56.039 UTC [8790] LOG:  consistent recovery state reached at 0/40000F8
• 2018-03-11 19:08:56.040 UTC [8789] LOG:  database system is ready to accept read only connections
• 2018-03-11 19:08:56.071 UTC [8794] LOG:  started streaming WAL from primary at 0/5000000 on timeline 1

Your slave is ready ! 
 

• You can see the replicate user on the master server :

• postgres=# select * from pg_stat_activity  where usename = 'replicate' ;
• -[ RECORD 1 ]----+------------------------------
• datid            |
• datname          |
• pid              | 9134
• usesysid         | 16384
• usename          | replicate
• application_name | walreceiver
• client_addr      | 172.17.0.3
• client_hostname  |
• client_port      | 45234
• backend_start    | 2018-03-11 19:08:56.049113+00
• xact_start       |
• query_start      |
• state_change     | 2018-03-11 19:08:56.071363+00
• wait_event_type  | Activity
• wait_event       | WalSenderMain
• state            | active
• backend_xid      |
• backend_xmin     |
• query            |

backend_type     | walsender