Setup a Streaming Replication With PostgreSQL 10

Sun, Mar 11, 2018
5-minute read

Warning: This post is over 365 days old. The information may be out of date.

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:
```
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.

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.

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

PostgreSQL pgen

Setup a Streaming Replication With PostgreSQL 10

Securise your communications and use SSL

Configure the master

Setup the slave

Related Posts