March 11, 2018postgresql 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 :
- 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:
- 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)
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  LOG: listening on IPv4 address "127.0.0.1", port 5432 2018-03-11 19:08:55.777 UTC  LOG: could not bind IPv6 address "::1": Cannot assign requested address 2018-03-11 19:08:55.777 UTC  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  LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2018-03-11 19:08:55.820 UTC  LOG: database system was interrupted; last known up at 2018-03-11 18:58:20 UTC 2018-03-11 19:08:56.023 UTC  LOG: entering standby mode 2018-03-11 19:08:56.034 UTC  LOG: redo starts at 0/4000028 2018-03-11 19:08:56.039 UTC  LOG: consistent recovery state reached at 0/40000F8 2018-03-11 19:08:56.040 UTC  LOG: database system is ready to accept read only connections 2018-03-11 19:08:56.071 UTC  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