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