In this article, we will explain how to configure streaming replication in postgres from existing primary database to a new standby database( slave).

ENVIRONMENT DETAILS:
PRIMARY SERVER= 10.20.30.40
STANDBY SERVER =10.20.30.41
REPLICATION MODE- ASYNC

NOTE – We are using edb postgres advanced server for our environment. You can use the postgres open version also. Steps are   same for both database flavours.

Configure master server for streaming replication:

1. Validate the primary database server:



postgres=# \list+
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
edb.     | postgres | UTF8 | C | C | | 2268 MB | pg_default |
postgres | postgres | UTF8 | C | C | | 4132 MB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C | C | =c/postgres +| 7601 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C | C | =c/postgres +| 7601 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)

postgres=# \conninfo
You are connected to database "postgres" as user "enterprisedb" via socket in "/tmp" at port "5444".

3. Update pg_hba.conf file primary server

Add host entries for standby server(10.20.30.41) in primary sever(10.20.30.410) pg_hba.conf file:



updated pg_hba.conf file:


# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32           ident
host    all             all             10.20.30.41/32         md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     all             10.20.30.41/32          md5


4. Update postgres.conf file in primary server.

Update the postgrs.conf file with below values.


postgres=# show config_file;
config_file
---------------------------------
/postdata/data/postgresql.conf
(1 row)



vi /pgdata/data/postgresql.conf


archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /postdata/pg_archive/%f'         # command to use to archive a logfile segment
max_wal_senders = 10            # max number of walsender processes
                                # (change requires restart)
wal_keep_segments = 50          # in logfile segments; 0 disables
wal_level = replica                     # minimal, replica, or logical


5. Restart postgres services:



systemctl stop edb-as-11
systemctl start edb-as-11


CONFIGURE STANDBY SERVER FOR STREAMING REPLICATION:

1. Install postgres binary on standby server.

On the standby server , just install the postgres binary. No need to initialise the postgres cluster.

Install EDB postgres on Linux.

2. Restore full backup of master on standby server.


- Run below command on standby server
-- here 10.20.30.40 is primary server ip 

 standby$ /usr/edb/as11/bin/pg_basebackup -D /pgdata/edbdata/ -X fetch -p 5444 -U enterprisedb -h 10.20.30.40 -R

Now check whether files are available under /pgdata/edbdata directory in standby server.

4. Check the content of recovery.conf file.

Verify the recovery.conf file and add parameters if anything is missing.


cat /postdata/edbdata/recovery.conf

[enterprisedb@SCPrLBTB77 edbdata]$ cat recovery.conf
standby_mode = 'on'
primary_conninfo = 'user=enterprisedb password=edb#pgadmindb host=10.20.30.40 port=5444 sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any'
promote_trigger_file='/postdata/edbdata/trigg.file' 
recovery_target_timeline=’latest’

4. Update the pg_hba.conf file.

Add host entries for primary server(10.20.30.40) in standby sever(10.20.30.41) pg_hba.conf file:



# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
host    all             all             10.20.30.40/32          md5
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            ident
host    replication     all             ::1/128                 ident
host    replication     all             10.20.30.40/32          md5

 

5. Start the postgres services in standby server.



systemctl stop edb-as-11
systemctl start edb-as-11

6. Verify the streaming replication:



-- Run on this primary server for outgoing replication details

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+---------------------------------
pid              | 18556
usesysid.        | 10
usename          | enterprisedb
application_name | walreceiver
client_addr      | 10.20.76.12
client_hostname  |
client_port      | 44244
backend_start    | 27-MAY-21 13:56:30.131681 +03:00
backend_xmin     |
state            | streaming
sent_lsn.        | 0/401F658
write_lsn        | 0/401F658
flush_lsn        | 0/401F658
replay_lsn.      | 0/401F658
write_lag        |
flush_lag        |
replay_lag.      |
sync_priority.   | 0
sync_state       | async

-- Run below queries on standby db server: 

postgres=# select pg_is_wal_replay_paused();

pg_is_wal_replay_paused
-------------------------
f

Note - f means , recovery is running fine. t means it is stopped.


postgres=# select pg_last_wal_receive_lsn(), pg_last_wal_replay_lsn(), pg_last_xact_replay_timestamp();
-[ RECORD 1 ]-----------------+---------------------------------
pg_last_wal_receive_lsn       | 0/401F658
pg_last_wal_replay_lsn        | 0/401F658
pg_last_xact_replay_timestamp | 27-MAY-21 16:26:18.704299 +03:00


postgres=# select * from pg_stat_wal_receiver;
-[ RECORD 1 ]---------+------------------------------------------------------------------------
pid                   | 7933
status                | streaming
receive_start_lsn     | 0/4000000
receive_start_tli     | 1
received_lsn          | 0/401F658
received_tli          | 1
last_msg_send_time.   | 27-MAY-21 20:29:39.599389 +03:00
last_msg_receipt_time | 27-MAY-21 20:29:39.599599 +03:00
latest_end_lsn.       | 0/401F658
latest_end_time       | 27-MAY-21 16:31:20.815183 +03:00
slot_name             |
sender_host           | 10.20.30.40
sender_port           | 5444
conninfo | user=enterprisedb passfile=/postdata/enterprisedb/.pgpass dbname=replication host=10.20.30.40 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 krbsrvname=postgres target_session_attrs=any
 


Our replication setup has been completed.

In the next article , we will explain how to setup EDB failover manager.