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

• 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:

     ssl = on
     ssl_cert_file = '/etc/ssl/postgresql/cert/server.crt'
     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.

```sql
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'
  

   something like this
  `bash
  sten_addresses = '*'
  r
  sten_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.

```bash
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
  

  place *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

• 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 ```