Streaming replication with PostgreSQL 10

悠悠 2023-10-18 22:19 122阅读 0赞

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:

1.  1.  ssl = on
    2.  ssl\_cert\_file = '/etc/ssl/postgresql/cert/server.crt'
    3.  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.

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 :

backend\_type | walsender

[post]: https://blog.raveland.org/post/self_signed/