Setup a streaming replication with PostgreSQL 10

March 11, 2018
postgresql pgen

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 :

Securise your communications and use SSL

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

Configure the master

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 !!

Setup the slave

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

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

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

Setup a PostgreSQL cluster with repmgr and pgbouncer

December 22, 2018
PostgreSQL replication pgen repmgr pgbouncer

Mettre en place une streaming replication avec PostgreSQL 10

March 13, 2018
postgresql pgfr

OpenBSD / PostgreSQL / Authentification

November 29, 2017
openbsd postgresql pgfr