DBA Hub

📋Steps in this guide1/9

How to configure streaming replication in postgres 14 - DBACLASS DBACLASS

In this article, we will explain how to configure streaming replication in postgres(14) from existing primary database to a new standby database( slave). The below steps will be same for POSTGRES 12/13/14. If you are looking for streaming replication older than postgres 12, then below link: How to setup streaming replication in postgres 11   […]

postgresql configurationintermediate
by PostgreSQL
16 views
1

1. Validate the primary database server:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
postgres=#<span style="color: #ff0000;"> <strong>\list+</strong></span>
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+-------+-----------------------+---------+------------+--------------------------------------------
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)
2

2. Update pg_hba.conf file primary server

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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
<span style="color: #ff6600;"><strong>host    all             all             10.20.30.41/32         md5
</strong></span># 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
<span style="color: #ff6600;"><strong>host    replication     all             10.20.30.41/32          md5


</strong></span>
3

3. Update postgres.conf file in primary server.

Update the postgrs.conf file with below values.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
postgres=# <span style="color: #ff0000;"><strong>show config_file;</strong></span>
config_file
---------------------------------
/postdata/data/postgresql.conf
(1 row)

<span style="color: #ff0000;"><strong>

vi /oracle/pg_data/postgresql.conf
</strong></span>

archive_mode = on               # enables archiving; off, on, or always
                                # (change requires restart)
archive_command = 'cp %p /oracle/pg_data/archive/%f' 
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
wal_log_hints = on               # also do full page writes of non-critical updates
4

4. Restart postgres services:

Code/Command (click line numbers to comment):

1
2
3
4
postgres$
/usr/pgsql-14/bin/pg_ctl stop -D /oracle/pg_data
postgres$
/usr/pgsql-14/bin/pg_ctl start -D /oracle/pg_data
5

5. Install postgres binary on standby server.

On the standby server , just install the postgres binary. No need to initialise the postgres cluster. 6. Restore full backup of master on standby server. Now check whether files are available under /oracle/pg_data directory in standby server. Apart from restoring the full backup, It will create standby.signal file and update the postgres.auto.conf file

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
total 16128
-rwxr-xr-x 1 root root  698212 Jul 10 12:17 postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 1556440 Jul 10 12:17 postgresql14-14.2-1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root  273192 Jul 10 12:17 postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm
-rwxr-xr-x 1 root root 5783224 Jul 10 12:17 postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm


[root@]# <strong>rpm -ivh postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm</strong>
warning: postgresql14-libs-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-libs-14.2-1PGDG.rhel################################# [100%]
[root@ ]# <strong>rpm -ivh postgresql14-14.2-1PGDG.rhel7.x86_64.rpm</strong>
warning: postgresql14-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-14.2-1PGDG.rhel7    ################################# [100%]
[root@ ]#<strong> rpm -ivh postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm</strong>
warning: postgresql14-server-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-server-14.2-1PGDG.rh################################# [100%]
[root@]# <strong>rpm -ivh postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm</strong>
warning: postgresql14-contrib-14.2-1PGDG.rhel7.x86_64.rpm: Header V4 DSA/SHA1 Signature, key ID 442df0f8: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql14-contrib-14.2-1PGDG.r################################# [100%]

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

 standby$<span style="color: #ff0000;"><strong>/usr/pgsql-14/bin/pg_basebackup -D /oracle/pg_data -X fetch -p 5444 -U postgres -h 172.20.30.40 -R
</strong><span style="color: #000000;">Password</span><strong><span style="color: #000000;">:
</span></strong></span>
6

7. Check the presence of standby.signal file and postgres.auto.conf file content

From postgres 12 onwards, use of recovery.conf file has been deprecated, instead there will be standby.signal file( empty file). and primary_conninfo details will be inside postgres.auto.conf -rw——- 1 postgres postgres 0 Jul 10 15:03 standby.signal -bash-4.2$ cat postgresql.auto.conf # Do not edit this file manually! # It will be overwritten by the ALTER SYSTEM command. primary_conninfo = ‘user=postgres password=postgres channel_binding=prefer host=10.20.30.40 port=5444 sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any’
7

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# 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
<span style="color: #ff6600;"><strong>host    all             all             10.20.30.40/32          md5
</strong></span># 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
<span style="color: #ff6600;"><strong>host    replication     all             10.20.30.40/32          md5

</strong></span>
8

9. Start the postgres services in standby server.

Code/Command (click line numbers to comment):

1
2
<span style="color: #ff0000;">postgres$</span><span style="color: #ff6600;"> /usr/pgsql-14/bin/pg_ctl start -D /oracle/pg_data
</span>
9

10. Verify the streaming replication:

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
<strong>

-- Run on this primary server for outgoing replication details
</strong>
postgres=# <span style="color: #ff0000;"><strong>select * from pg_stat_replication;</strong></span>
-[ RECORD 1 ]----+------------------------------
pid              | 10799
usesysid         | 10
usename          | postgres
application_name | walreceiver
client_addr      | 10.20.30.41
client_hostname  |
client_port      | 51694
backend_start    | 2022-07-10 15:07:38.993741+03
backend_xmin     |
state            | streaming
sent_lsn         | 0/3000A00
write_lsn        | 0/3000A00
flush_lsn        | 0/3000A00
replay_lsn       | 0/3000A00
write_lag        |
flush_lag        |
replay_lag       |
sync_priority    | 0
sync_state       | async
reply_time       | 2022-07-10 15:56:51.426376+03

<strong>-- Run below queries on standby db server: 
</strong>
postgres=# <span style="color: #ff0000;"><strong>select pg_is_wal_replay_paused();</strong></span>

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

<strong>Note - f means , recovery is running fine. t means it is stopped.
</strong>


postgres=# <span style="color: #ff0000;"><strong>select * from pg_stat_wal_receiver;</strong></span>
-[ RECORD 1 ]---------+------------------------------------------------------------------------
pid                   | 7792
status                | streaming
receive_start_lsn     | 0/3000000
receive_start_tli     | 1
written_lsn           | 0/3000A00
flushed_lsn           | 0/3000A00
received_tli          | 1
last_msg_send_time    | 2022-07-10 15:58:16.457471+03
last_msg_receipt_time | 2022-07-10 15:57:21.503977+03
latest_end_lsn        | 0/3000A00
latest_end_time       | 2022-07-10 15:55:16.010796+03
slot_name             |
sender_host           | 10.20.30.40
sender_port           | 5444
conninfo              | user=postgres password=******** channel_binding=prefer dbname=replication host=10.20.30.40 port=5444 fallback_application_name=walreceiver sslmode=prefer sslcompression=0 sslsni=1 ssl_min_protocol_version=TLSv1.2 gssencmode=prefer krbsrvname=postgres target_session_attrs=any

Comments (0)

Please to add comments

No comments yet. Be the first to comment!