How To Configure PostgreSQL 12 Streaming Replication in CentOS 8

PostgreSQL database helps a number of replication options to construct high-availability, scalable, fault-tolerant purposes, one in every of which is Write-Forward Log (WAL) Delivery. This resolution permits for a standby server to be carried out utilizing file-based log transport or streaming replication, or the place attainable, a mixture of each approaches.

With streaming replication, a standby (replication slave) database server is configured to hook up with the grasp/major server, which streams WAL data to the standby as they’re generated, with out ready for the WAL file to be crammed.

By default, streaming replication is asynchronous the place information is written to the standby server(s) after a transaction has been dedicated on the first server. This implies that there’s a small delay between committing a transaction within the grasp server and the adjustments changing into seen within the standby server. One draw back of this method is that in case the grasp server crashes, any uncommitted transactions is probably not replicated and this could trigger information loss.

This information reveals the right way to arrange a Postgresql 12 master-standby streaming replication on CentOS 8. We are going to use “replication slots” for the standby as an answer to keep away from the grasp server from recycling previous WAL segments earlier than the standby has acquired them.

Be aware that in comparison with different strategies, replication slots retain solely the variety of segments recognized to be wanted.

Testing Surroundings:

This information assumes you related to your grasp and standby database servers as the basis by way of SSH (use Sudo command the place crucial in case you are related as a traditional consumer with administrative rights):

Postgresql grasp database server: 10.20.20.9
Postgresql standby database server: 10.20.20.8

Each database servers will need to have Postgresql 12 put in, in any other case, see: The way to Set up PostgreSQL and pgAdmin in CentOS 8.

Be aware: PostgreSQL 12 comes with main adjustments to replication implementation and configuration akin to alternative of restoration.conf and the conversion of restoration.conf parameters to regular PostgreSQL configuration parameters, making it a lot simpler to configure cluster replication.

Step 1: Configuring the PostgreSQL Grasp/Main Database Server

1. On the grasp server, swap to the postgres system account and configure the IP handle(es) on which the grasp server will hearken to for connections from shoppers.

On this case, we are going to use * that means all.

# su – postgres
$ psql -c “ALTER SYSTEM SET listen_addresses TO ‘*’;”

The ALTER SYSTEM SET SQL command is a strong characteristic to vary a server’s configuration parameters, instantly with a SQL question. The configurations are saved within the postgresql.conf.auto file positioned on the root of knowledge folder (e.g /var/lib/pgsql/12/information/) and browse addition to these saved in postgresql.conf. However configurations within the former take priority over these within the later and different associated information.

Configure IP Addresses on PostgreSQL Master

Configure IP Addresses on PostgreSQL Grasp

2. Then create a replication function that can be used for connections from the standby server to the grasp server, utilizing the createuser program. Within the following command, the -P flag prompts for a password for the brand new function and -e echoes the instructions that createuser generates and sends to the database server.

# su – postgres
$ createuser –replication -P -e replicator
$ exit

Create Replication User on Pgmaster

Create Replication Consumer on Pgmaster

3. Then enter the next entry on the finish of the /var/lib/pgsql/12/information/pg_hba.conf shopper authentication configuration file with the database subject set to replication as proven within the screenshot.

host replication replicator 10.20.20.8/24 md5

Configure Replication Authentication

Configure Replication Authentication

4. Now restart the Postgres12 service utilizing the next systemctl command to use the adjustments.

# systemctl restart postgresql-12.service

5. Subsequent, when you’ve got the firewalld service working, you want to add the Postgresql service within the firewalld configuration to permit requests from the standby server to the grasp.

# firewall-cmd –add-service=postgresql –permanent
# firewall-cmd –reload

Step 2: Making a Base Backup to Bootstrap the Standby Server

6. Subsequent, you want to make a base backup of the grasp server from the standby server; this helps to bootstrap the standby server. You must cease the postgresql 12 service on the standby server, swap to the postgres consumer account, backup the information listing (/var/lib/pgsql/12/information/), then delete every part underneath it as proven, earlier than taking the bottom backup.

# systemctl cease postgresql-12.service
# su – postgres
$ cp -R /var/lib/pgsql/12/information /var/lib/pgsql/12/data_orig
$ rm -rf /var/lib/pgsql/12/information/*

7. Then use the pg_basebackup software to take the bottom backup with the suitable possession (the database system consumer i.e Postgres, inside the Postgres consumer account) and with the suitable permissions.

Within the following command, the choice:

  • -h – specifies the host which is the grasp server.
  • -D – specifies the information listing.
  • -U – specifies the connection consumer.
  • -P – allows progress reporting.
  • -v – allows verbose mode.
  • -R – allows the creation of restoration configuration: Creates a standby.sign file and append connection settings to postgresql.auto.conf underneath the information listing.
  • -X – used to incorporate the required write-ahead log information (WAL information) within the backup. A worth of stream means to stream the WAL whereas the backup is created.
  • -C – allows the creation of a replication slot named by the -S possibility earlier than beginning the backup.
  • -S – specifies the replication slot identify.

$ pg_basebackup -h 10.20.20.5 -D /var/lib/pgsql/12/information -U replicator -P -v -R -X stream -C -S pgstandby1
$ exit

Base Backup of the Master Server

Base Backup of the Grasp Server

8. When the backup course of is finished, the brand new information listing on the standby server ought to seem like that within the screenshot. A standby.sign is created and the connection settings are appended to postgresql.auto.conf. You possibly can record its contents utilizing the ls command.

# ls -l /var/lib/pgsql/12/information/

Verify Backup Data Directory

Confirm Backup Knowledge Listing

A replication slave will run in “Scorching Standby” mode if the hot_standby parameter is ready to on (the default worth) in postgresql.conf and there’s a standby.sign file current within the information listing.

9. Now again on the grasp server, it’s best to be capable of see the replication slot known as pgstandby1 if you open the pg_replication_slots view as follows.

# su – postgres
$ psql -c “SELECT * FROM pg_replication_slots;”
$ exit

Create Replication Slot

Create Replication Slot

10. To view the connection settings appended within the postgresql.auto.conf file, use the cat command.

# cat /var/lib/pgsql/12/information/postgresql.auto.conf

View Connection Settings

View Connection Settings

11. Now start regular database operations on the standby server by beginning the PostgreSQL service as follows.

# systemctl begin postgresql-12

Step 3: Testing PostgreSQL Streaming Replication

12. As soon as a connection is established efficiently between the grasp and the standby, you will notice a WAL receiver course of within the standby server with a standing of streaming, you’ll be able to examine this utilizing the pg_stat_wal_receiver view.

$ psql -c “x” -c “SELECT * FROM pg_stat_wal_receiver;”

Check WAL Receiver Process

Verify WAL Receiver Course of

and a corresponding WAL sender course of within the grasp/major server with a state of streaming and a sync_state of async, you’ll be able to examine this pg_stat_replication pg_stat_replication view.

$ psql -c “x” -c “SELECT * FROM pg_stat_replication;”

Check WAL Sender Process in Master

Verify WAL Sender Course of in Grasp

From the screenshot above, the streaming replication is asynchronous. Within the subsequent part, we are going to exhibit the right way to optionally allow synchronous replication.

13. Now check if the replication is working tremendous by making a check database within the grasp server and examine if it exists within the standby server.
[master]postgres=# CREATE DATABASE tecmint;
[standby]postgres=# l

Test Streaming Replication

Check Streaming Replication

Optionally available: Enabling Synchronous Replication

14. Synchronous replication gives the flexibility to commit a transaction (or write information) to the first database and the standby/duplicate concurrently. It solely confirms {that a} transaction is profitable when all adjustments made by the transaction have been transferred to a number of synchronous standby servers.

To allow synchronous replication, the synchronous_commit should even be set to on (which is the default worth, thus no want for any change) and also you additionally have to set the synchronous_standby_names parameter to a non-empty worth. For this information, we are going to set it to all.

$ psql -c “ALTER SYSTEM SET synchronous_standby_names TO ‘*’;”

Set Sync Standby Names in Master

Set Sync Standby Names in Grasp

15. Then reload the PostgreSQL 12 service to use the brand new adjustments.

# systemctl reload postgresql-12.service

16. Now if you question the WAL sender course of on the first server as soon as extra, it ought to present a state of streaming and a sync_state of sync.

$ psql -c “x” -c “SELECT * FROM pg_stat_replication;”

Check WAL Sender Process in Master

Verify WAL Sender Course of in Grasp

We have now come to the top of this information. We have now proven the right way to arrange PostgreSQL 12 master-standby database streaming replication in CentOS 8. We additionally lined the right way to allow synchronous replication in a PostgreSQL database cluster.

There are a lot of makes use of of replication and you may at all times choose an answer that meets your IT atmosphere and/or application-specific necessities. For extra element, go to Log-Delivery Standby Servers within the PostgreSQL 12 documentation.

Supply

Germany Devoted Server

Leave a Reply