PostgreSQL — High Availability, Read Replica Methodology, Streaming Replication and Replica Promotion

Hi, In this new article series which is related with PostgreSQL, I am going to explain below subjects and answer questions.

• What is highly available database system?
• What is Read Replica Database, Why we need Replicas, Does it important for Disaster Recovery and Critical importance for Multi Zone database ?
• How to implement replica mechanism in PostgreSQL and Streaming Replication?
• How to promote read replica to standalone instance ?

Highly Available Database System

A highly available database is a database system designed to provide uninterrupted access to data and services despite hardware, software, or network failures. In other words, it is a database system that ensures that the data is always available, even if one or more components of the system fail.

To achieve high availability, a highly available database typically employs redundant hardware, such as clustered servers, storage devices, or network infrastructure, and employs techniques such as data replication, automatic failover, load balancing, and monitoring to ensure that the system is always available and responsive to user requests.

Read Replica Database

A read replica database is a type of database replication where a copy of a primary database is created and maintained in near real-time. The read replica database can then be used to offload read traffic from the primary database, improving performance and reducing load.

In this setup, any changes made to the primary database are automatically propagated to the read replica, ensuring that the data is always up-to-date.However, changes made to the read replica database do not affect the primary database.

Read replicas are commonly used in scenarios where the primary database is heavily used for both read and write operations, and high read traffic can cause performance issues. By creating one or more read replicas, read operations can be redirected to the replicas, leaving the primary database to focus on write operations.

Read replicas can also provide increased availability and disaster recovery. If the primary database fails, a read replica can be promoted to become the new primary database, minimizing downtime and data loss.

In a multi-zone database setup, the primary database is typically located in one zone or region, while read replicas are created in other zones or regions. By replicating data to read replicas in multiple zones, you can provide low-latency read access to users in those regions and also provide failover capabilities in case of an outage in one of the zones.

Implement replica mechanism in PostgreSQL and Streaming Replication

Streaming replication is a feature in PostgreSQL that allows for the replication of data from a master server to one or more standby servers in near real-time. It works by continuously streaming the write-ahead log (WAL) from the master server to the standby servers over a TCP/IP connection.

When a transaction is committed on the master server, the changes are written to the WAL, which is then sent to the standby servers. The standby servers apply the changes to their local copy of the database, keeping it in sync with the master server. In the event of a failure of the master server, one of the standby servers can be promoted to take over as the new master.

So lets start with master database configuration to provide replication mechanism.

Step — M1

To perform replication, Postgres requires a user, also called a role, with special permissions. On the primary server, run the following command:

$ sudo -u postgres createuser -U postgres repuser -P -c 5 --replication

• “sudo -u postgres” ensures that the createuser command runs as the user “postgres”. Otherwise, Postgres will try to run the command by using peer authentication, which means the command will run under your Ubuntu user account. This account probably doesn’t have the right privileges to create the new user, which would cause an error.
• The “-U” option tells the createuser command to use the user postgres to create a new user.
• The name of the new user is “repuser”.
• “-P” prompts you for the new user’s password.
• “-c” sets a limit for the number of connections for the new user.
• “- -replication” grants the REPLICATION privilege to the user named repuser.

Step — M2

Create a directory to store archive files. This directory is a subdirectory of the cluster’s data directory. In my case this directory is “/var/lib/postgresql/main” but you should check your data directory. You can do it with “ps aux | grep postgres” command and see the postgres service and then check the postgres start command. “-D” parameter specifies your data directory. Also “- -config-file” parameter specifies postgresql.conf configuration file path. (We will use this config file in the next steps)

$ mkdir -p /var/lib/postgresql/12/main/mnt/server/archivedir

Step — M3

Now we will edit “postgresql.conf” file. This configuration file contains the main settings for Postgres. In this config file update parameters like below.

wal_level = replica
archive_mode = on
archive_command = 'test ! -f /var/lib/postgresql/12/main/mnt/server/archivedir/%f && cp %p /var/lib/postgresql/12/main/mnt/server/archivedir/%f'
max_wal_senders = 3

Be carefull while setting “archive_command” and “max_wal_senders” because archive command perspective you must write your archive directory which is created by Step-M2. For the wal sender parameters, you should set it according to your case because it represents number of WAL sender processes. If you create to much proceses it can consume more memory.

Step — M4

We must update to “pg_hba.conf” file to allow replication connection for the “repuser” to the enable replication.

# Allow replication connections
host     replication     repuser         [standby-IP]/32        md5

Step — M5

Restart your postgresql server.

$sudo service postgresql restart

Now lets continue with standby server configuration.

Step — S1

Before making changes on the standby server, stop the service.

$ sudo service postgresql stop

Step — S2

In this step we will run the backup utility , named pg_basebackup, will copy files from the data directory on the primary server to the same directory on the standby server.

The backup utility won’t overwrite existing files, so you must rename the data directory on the standby server

$ mv /var/lib/postgresql/12/main /var/lib/postgresql/12/main_old

Run the backup utility. Replace [primary IP] with the external IP address of the primary server.

$ sudo -u postgres pg_basebackup -h [primary IP] -D /var/lib/postgresql/12/main -U repuser -v -P --wal-method=stream -R 

There is an important file (standby.signal) that must exist in a standby data directory to help postgres determine its state as a standby.It is automatically created when you use the “-R” option while taking pg_basebackup otherwise use touch to create this empty file.

Step — S3

Update “postgresql.con” file for the standby server with below parameters.

hot_standby = on
primary_conninfo = 'host=[primary-external-IP] port=5432 user=repuser password=[password]'
data_sync_retry = on

Step — S4

Start your standby postgres server.

$ service postgresql start

Seeing The Replication at Work

$ sudo -u postgres psql postgres

CREATE TABLE cpt_team (email text, vistor_id serial, date timestamp, message text);
INSERT INTO cpt_team (email, date, message) VALUES ( 'myoda@gmail.com', current_date, 'Now we are replicating.');

Switch back to the standby server terminal and use command in the above.

select * from cpt_team;

You should now see that the standby server has received the update from the primary server.

Promote Read Replica to Standalone Instance

In PostgreSQL, you can promote a read replica to a master by following these steps:

1. Connect to the read replica as a superuser using psql.

2. Check the replication status on the read replica by running the following command:

SELECT * FROM pg_stat_replication;

This will show the status of the replication connection to the primary database. You should see that replication has stopped.

3. Once you have verified that replication has stopped, promote the read replica to a master by running the following command:

SELECT pg_promote();

This will promote the read replica to a master, and it will now accept read and write queries.

In the next article I will explain Point in Time Recovery and Incremental Backup.

Github : https://github.com/menendes

Linkedin : https://www.linkedin.com/in/ibrahim-halil-koyuncu-b1030516a/

References

Chapter 27. High Availability, Load Balancing, and Replication

How to set up PostgreSQL for high availability and replication with Hot Standby | Google Cloud…