By Mouhamadou Diaw

PostgreSQL 12 was released a few month ago. When actually setting up a replication, there is no longer recovery.conf file in the PGDATA. Indeed all parameters of the recovery.conf should be now in the postgresql.conf file. And in the cluster data directory of the standby server, therre should be a file named standby.signal to trigger the standby mode.
In this blog I am just building a streaming replication between 2 servers to show these changes. The configuration we are using is
Primary server dbi-pg-essentials : 192.168.56.101
Standby server dbi-pg-essentials-2 : 192.168.56.102

The primary server is up and running on dbi-pg-essentials server.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
postgres@dbi-pg-essentials:/u02/pgdata/12/PG1/ [PG12] pg12
********* dbi services Ltd. *********
                  STATUS  : OPEN
         ARCHIVE_COMMAND  : test ! -f /u99/pgdata/12/archived_wal/%f && cp %p /u99/pgdata/12/archived_wal/%f
            ARCHIVE_MODE  : on
    EFFECTIVE_CACHE_SIZE  : 4096MB
                   FSYNC  : on
          SHARED_BUFFERS  : 128MB
      SYNCHRONOUS_COMMIT  : on
                WORK_MEM  : 4MB
              IS_STANDBY  : false
*************************************
postgres@dbi-pg-essentials:/u02/pgdata/12/PG1/ [PG12]

step 1 : Prepare the user for the replication on the primay server
For streaming replication, we need a user to read the WAL stream, we can do it with a superuser but it is not required. We will create a user with REPLICATION and LOGIN privileges. Contrary to the SUPERUSER privilege, the REPLICATION privilege gives very high permissions but does not allow to modifiy any data.
Here we will create a user named repliuser

1
2
3
postgres=# create user repliuser with password 'postgres'  replication ;
CREATE ROLE
postgres=#

Step 2 : Prepare the authentication on the primary server
The user used for the replication should be allowed to connect for replication. We need then to adjust the pg_hba.conf file for the two servers.

1
2
3
4
postgres@dbi-pg-essentials:/u02/pgdata/12/PG1/ [PG12] grep repliuser pg_hba.conf
host    replication     repliuser        192.168.56.101/32        md5
host    replication     repliuser        192.168.56.102/32        md5
postgres@dbi-pg-essentials:/u02/pgdata/12/PG1/ [PG12]

Step 3 : Create a replication slot on the primary server
Replication slots provide an automated way to ensure that the master does not remove WAL segments until they have been received by all standbys, and that the master does not remove rows which could cause a recovery conflict even when the standby is disconnected.

1
2
3
4
5
6
7
8
9
10
psql (12.1 dbi services build)
Type "help" for help.
postgres=# SELECT * FROM pg_create_physical_replication_slot('pg_slot_1');
 slot_name | lsn
-----------+-----
 pg_slot_1 |
(1 row)
postgres=#

Step 4 : Do a backup of the primary database and restore it on the standby
From the standby server launch the following command

1
postgres@dbi-pg-essentials-2:/u02/pgdata/12/PG1/ [PG12] pg_basebackup -h 192.168.56.101 -D /u02/pgdata/12/PG1 --wal-method=fetch -U repliuser

Step 5 : set the primary connection info for the streaming on standby side
The host name and port number of the primary, connection user name, and password are specified in the primary_conninfo. Here there is a little change as there is no longer a recovery.conf parameter. The primary_conninfo should now be specified in the postgresql.conf

1
2
3
postgres@dbi-pg-essentials-2:/u02/pgdata/12/PG1/ [PG12] grep primary postgresql.conf
primary_conninfo = 'host=192.168.56.101 port=5432 user=repliuser password=postgres'
primary_slot_name = 'pg_slot_1'                 # replication slot on sending server

Step 6 : Create the standby.signal file on standby server
In the cluster data directory of the standby, create a file standby.signal

1
2
3
postgres@dbi-pg-essentials-2:/u02/pgdata/12/PG1/ [PG12] pwd
/u02/pgdata/12/PG1
postgres@dbi-pg-essentials-2:/u02/pgdata/12/PG1/ [PG12] touch standby.signal

Step 7 : Then start the standby cluster

1
postgres@dbi-pg-essentials-2:/u02/pgdata/12/PG1/ [PG12] pg_ctl start

If everything is fine, you should fine in the alert log

1
2
2019-11-16 17:41:21.552 CET [1590] LOG:  database system is ready to accept read only connections
2019-11-16 17:41:21.612 CET [1596] LOG:  started streaming WAL from primary at 0/5000000 on timeline 1

As confirmed by dbi dmk tool, the master is now streaming to the standby server

1
2
3
4
5
6
7
8
9
10
11
12
13
14
********* dbi services Ltd. *********
                  STATUS  : OPEN
         ARCHIVE_COMMAND  : test ! -f /u99/pgdata/12/archived_wal/%f && cp %p /u99/pgdata/12/archived_wal/%f
            ARCHIVE_MODE  : on
    EFFECTIVE_CACHE_SIZE  : 4096MB
                   FSYNC  : on
          SHARED_BUFFERS  : 128MB
      SYNCHRONOUS_COMMIT  : on
                WORK_MEM  : 4MB
              IS_STANDBY  : false
               IS_MASTER  : YES, streaming to 192.168.56.102/32
*************************************
postgres@dbi-pg-essentials:/u02/pgdata/12/PG1/ [PG12]
Post Views: 1,671
Thumbnail [60x60]
by
Oracle Team