PostgreSQL Replication: A Comprehensive Guide

Like any owner of a website will tell that, losing data and slowdowns, even when in tiny doses, can be catastrophic. They can hit the unprepared anytime and result in a decrease in productivity, accessibility, as well as a loss of trust in your product.

To ensure the security of your site you must create security measures to protect against downtime or data loss.

What is PostgreSQL Replication?

PostgreSQL replication is the act of the transfer of data from one PostgreSQL database server to another. The source database server is also referred to as the "primary" server. On the other hand, the database server receiving the copy of data is also known as"the "replica" server.

The PostgreSQL database follows a straightforward replication system, in which all writing is routed to a central node. This primary node is able to implement these changes and send these changes to the secondary nodes.

What is Automatic Failover?

Once physical streaming replication has been configured within PostgreSQL and failover is enabled, it is possible if the database's primary server is not functioning. Failover is used to define the process of recovery, and it can take a while, because it lacks the tools that are built into it to identify servers that fail.

When you set up failover replication, you all but guarantee high availability by ensuring that standbys are available if the primary server does fail.

Advantages of Using PostgreSQL Replication

Here are the top benefits to leveraging PostgreSQL replication:

• Migration of data Avail PostgreSQL replication to facilitate data migration by modifying the equipment on the database server or installation of the system.
• Fault tolerance When the primary server is not working it can be replaced by a standby server that will act as a server because the information contained on both servers are the same.
• Transcripts for online transaction (OLTP) performance It is possible to improve the processing speed of transactions and processing time of an OLTP system by taking away reporting query load. Transaction processing time is the duration it takes for the query you want to run to be completed before a transaction has been completed.
• System testing in parallel: While upgrading the system you need to make sure that the system fares well using the existing database, which is the need to test with an existing database before the deployment.

What is the process? PostgreSQL Replication Works

1. Volume-level replication to replicate on the storage layer starting from primary node to the secondary before backing it up to blob/S3 storage.
2. streaming in PostgreSQL to replicate data from the primary server to the second node. This is after which it is backed until blob/S3 storage.
3. Backups incrementally of the main node and saving them to S3 and then constructing a secondary node using S3. The secondary node should be close to the primary, you can begin streaming using the primary node.

Approach 1: Streaming

PostgreSQL streaming replication also known as WAL replication can be set up seamlessly following the installation of PostgreSQL on every server. This approach to replication is built on the idea of moving the WAL files from the primary to the database to be used for target.

You can implement PostgreSQL streaming replication using a primary-secondary configuration. Primary server is the instance in charge of the primary database as well as every operation. The secondary server acts as an additional instance and performs any changes to the primary database on itself, generating an identical copy of the process. The primary serves as the server that reads and writes, whereas the secondary server is merely only a read-only.

For this approach the configuration must be done for both the primary and the standby node. The following sections will elucidate how to configure these two devices easily.

Configuring Primary Node

It is possible to configure the primary node that is responsible for stream replication through these steps:

Step 1: Initialize the Database

To initialize the database, you may use the initidb command. Next, you can establish a new user that has permission to replicate by using one of the commands below:

CREATE USER LOGIN ENCRYPTED PASSWORD '';

The user will have to enter a username and password to access the query. The replication keyword is used to grant the user needed privileges. For instance, a query could be something like:

CREATE USER rep_user SECURE LOGIN PASSWORD "rep_pass"

Step 2: Configure the Streaming Properties

The next step is to configure the streaming parameters using the configuration file for PostgreSQL ( postgresql.conf) and modify it as follows:

Wal_level = logicalwal_log_hints = onmax_wal_senders = 8.max_wal_size = 1GBthe hot standby is on

A little history of what parameters were utilized in the earlier snippet of code:

• log_hints for wal This parameter is required for the pg_rewind feature that is helpful when the standby server's not in sync with its main server.
• The wal_level parameter can be used to enable this parameter to allow PostgreSQL streaming replication. There are possible values including minimum, replica, and the logical.
• max_wal_size  It is used to define the dimensions of WAL log files that are kept in log file.
• hot-standby The user may make use of this parameter to establish an on-read connection to the secondary when it's turned on.
• max_wal_senders The user can utilize max_wal_senders to indicate your maximum concurrent connection that can be created using standby servers.

Step 3: Create New Entry

Once you've changed your parameters within the postgresql.conf file, adding a replication entry within the pg_hba.conf file can let the servers connect with one another for the purpose of replication.

It is usually found in the document in the directory for data of PostgreSQL. It is possible to use the following code snippet for this:

Rep_user for host replication IPaddress Md5

Once the code snippet gets executed, the main server will allow a user dubbed rep_user to connect and function as a standby server making use of the IP for replication. As an example:

host replica rep_user 192.168.0.22/32 md5

Setting up Standby Node

In order to set up the standby node for streaming replication, follow these steps:

Step 1: Back Up the Primary Node

In order to configure the standby Node make use of the basebackup tool to create a backup of the main node. This will serve as the base for your standby Node. This utility can be used by using the syntax as follows:

pg_basebackp -D -h -X stream -c fast -U rep_user

The parameters that are used in the syntax mentioned above are as follows:

• "H"  The -h option lets you make use of this option to identify the host that is primary.
• D The parameter is the directory that you're working in at the moment.
• -C  The -C option lets you make use of this feature to define the checkpoints.
• + X The option can be used to add the required logs for transactions.
• --W : You can make use of this parameter to ask users to enter a password prior to linking the database.

Step 2: Configure Replication Configuration File

The next step is to check if the replication configuration file exists. If not it is possible to generate the configuration file to replicate in the form of recovery.conf.

It is recommended to create the file in the directory for data of the PostgreSQL installation. The file can be generated on your own through the --R option within the Pg_basebackup utility.

Its recovery.conf file should contain the following commands:

standby_mode = 'on'

primary_conninfo = 'host= port= user= password= application_name="host_name"'

recovery_target_timeline = 'latest'

The parameters that are used for the commands mentioned above are:

• primary_conninfo : You can utilize this feature to establish an connection between your two servers by making use of a connection string.
• standby_mode The parameter may trigger the main server to start as the standby when it is turned on.
• recovery_target_timeline: You can use this to set the recovery time.

To set up a connection, you need to provide your name, the IP address and password as values for principal_conninfo. In this case, for example:

 primary_conninfo = 'host=192.168.0.26 port=5432 user=rep_user password=rep_pass'

Step 3. Restart Secondary Server

Finally, you can restart the secondary server for the process of configuration to be completed.

But streaming replication is not without its challenges. with several challenges, such as:

• Different PostgreSQL clients (written in different programming languages) converse with a single endpoint. When the primary node fails the clients keep running the same DNS name or IP. This makes failover visible to the application.
• PostgreSQL replication does not come with built-in failover and monitoring. If the primary node is not working it is necessary to upgrade a secondary to be the new primary. The promotion must be done in a manner which clients can only write to one primary node and don't notice inconsistent data.
• PostgreSQL copies its entire state. When you have to build another node it must recap the whole time period of change in the state from the primary node, which is a resource-intensive process and results in it being costly to eliminate nodes in the head as well as make new one.

Approach 2: Replicated Block Device

The replication of block devices relies on mirroring disks (also known as volume replication). The modifications are recorded to an indefinite volume that is synchronously mirrored to another volume.

A further benefit of this strategy is reliability and compatibility on cloud platforms with any relational databases. This includes PostgreSQL, MySQL, and SQL Server, to name some.

However, the disk-mirroring approach for PostgreSQL replication requires you to replicate the logs and table information. As each writing to your database is now required to be transmitted over the network at asynchronous rate and you don't want to lose a single byte and leave your database in a corrupt condition.

The approach typically is implemented by using Azure PostgreSQL as well as Amazon RDS.

Approach 3: WAL

WAL comprises segment files (16 MB in default). Each segment contains several records. A log sequence record (LSN) represents a reference to an entry in WAL, letting you know that the exact location where the file was saved in the log file.

Standby servers make use of WAL segments -- also called XLOGS within PostgreSQL terminology -- to continuously replicate changes from its main server. Write-ahead logs can be used to grant durability and atomicity within an DBMS by serializing chunks data in byte-array format (each one with a unique LSN) to stable storage before they get applied to a database.

The application of a modification on a database could cause a number of operation on the file system. The most important issue that comes up is how a database can assure atomicity in the event of server failure due to an outage in power while it was in the process of an upgrade. Once a database is booted it will begin a restart or replay procedure that can read the available WAL segments and then compare them with the LSN stored on every data page (every data page is identified with that LSN of the most recent WAL record that affects the page).

Log Shipping-Based Replication based on Logs (Block Level)

Streaming replication refines the process of shipping logs. In contrast to waiting to the WAL switch to activate, records are sent as they get created, thus decreasing replication delay.

Streaming replication is also superior to log shipping since standby servers connect to the primary server over the network, using the replication protocol. The main server will then forward WAL records directly through the connection, without needing to depend on scripts provided by the end-user.

Log Shipping-Based Replication (File Level)

Log shipping can be defined as the transfer of log data to another PostgreSQL server in order to create a standby server by replaying WAL files. The server is set to function in recovery mode and its sole purpose is to apply any new WAL file as they come up.

Do you want to know the ways we have increased volume by more than 1000%?

Join 20,000+ others who receive our newsletter every week with insider WordPress tricks!

The secondary server is then a warm backup of the primary PostgreSQL server. It is also able as a read replica, where it can offer read-only queries, also referred to as hot standby.

Continuous WAL Archiving

The process of duplicating WAL file as they are created into any location other that the pg_wal subdirectory in order to archive it is referred to as WAL archiving. PostgreSQL calls an application script specified by the user to archive whenever the WAL file gets made.

The script can leverage to use the scp command to copy the files to several locations, for example, an NFS mount. Once archived, WAL segment files are used to retrieve the database at any given moment in time.

Other log-based configurations can be found:

• Synchronous replica When every synchronous replication transaction gets committed, the primary server waits until standbys verify that they have obtained the data. The benefit of this configuration is that there won't be any conflicts caused due to parallel processing of writing.
• Synchronous multi-master replica: Here, every server is able to accept writes as well as modified data being transferred from the server that originally created it to every other server before each transaction gets committed. This utilizes the 2PC protocol and adheres to the rule of all or none.

WAL Streaming Protocol Details

The WAL receiver running from the server that is standby uses the connection information that are provided by the primary_conninfo parameter in recovery.conf and connects to the primary server through leveraging a TCP/IP connection.

WAL sender is an additional process that runs on the primary server and is in charge of sending WAL data on to the server standby once they're created. The WAL receiver will save the WAL entries within WAL like they were made by the activities of local clients.

After the WAL entries are in the WAL segment files The standby server plays the WAL to ensure that both primary as well as standby remain always up-to-date.

Elements of PostgreSQL Replication

In this article in this article, you'll be able to gain a greater knowledge of the most commonly-used model (single-master or multi-master replication) and kinds (physical and logical replication), and methods (synchronous and Asynchronous) of PostgreSQL replication.

Modells and Replication Models in PostgreSQL Databases

Scalability refers to the addition of more equipment or resources to existing nodes in order to increase the ability of the database to manage and store data which can be achieved in both directions. PostgreSQL replication can be an illustration of horizontal scalability, which is more challenging to achieve than vertical scale. We can achieve horizontal scalability typically through one-master replication (SMR) and multi-master replication (MMR).

Single-master replication permits data to be changed by a single server that is replicated to one or more nodes. The replicated tables in the replica database don't have the ability to take any modifications, except those from the primary server. However, even if they do accept changes not, those changes won't be copied back to the original server.

In most cases SMR suffices to run the program since it's simpler to manage and configure and there are no risks of conflicts. Single-master replication can also be unidirectional as replication data flows mostly in one direction, from the primary database replica database.

In some cases, SMR alone may not be sufficient, and it may be necessary to install MMR. MMR lets more than one node to act as the principal node. Any changes to rows of tables within more than one primary database are replicated to their counterpart tables in every other primary database. This model is where conflicts resolution strategies are typically used to prevent problems such as the duplicate keys in primary databases.

There are a few advantages when making use of MMR that include:

• In the case of host malfunction, the other hosts will still be able to offer update and insertion services.
• The nodes that are the primary ones are distributed across different places, and the risk of failure for all primary nodes is very low.
• Possibility to utilize a wide area networks (WAN) of primary databases that can be geographically close to clients in groups, yet maintain data consistency across the entire network.

However, the downside of MMR is that it's not as simple. MMR is its complexity and its difficulty to resolve conflict.

As the BDR application records transactions for other nodes, this operation can fail when there's a conflict between the transaction applying and the transaction made on the receiving node.

The PostgreSQL Replication Types

There are two different types of PostgreSQL replication: logical and physical replication.

An easy logical action "initdb" will carry out the physical operation of making a base directory to an entire cluster. In the same way, a basic logical operation "CREATE A DATABASE" could carry out the physical process to create a subdirectory inside the base directory.

Physical replication generally is concerned with directories and files. The system doesn't understand what directories are. These methods are used to maintain a full duplicate of the complete data of a single cluster. They are typically stored on another device, and they are performed at the file system level or at the disk level, and employ exact block addresses.

Logical replication is a method to replicate data objects and their modifications, based upon their replication identity (usually a primary key). In contrast to physical replication that is based on databases, this one deals with as tables, databases, and DML operations and is done on the level of the cluster database. It employs an publish and subscribe system that allows one or more customers are subscribed to one or more publications on a publisher node.

The process begins with taking a snapshot of the data on the publisher database and then copying it into the subscribers database. Subscribers pull data from the journals they have subscribed to. The subscriber can republish the data for cascading replication, or more complex configurations. The subscriber uses the data in the same order as the publisher so it is possible to guarantee consistency in transactions for all the publications in the same subscription. This is also known as transactional replication.

The most common uses for the logical replication include:

• Making incremental changes to one database (or the subsets of a database) to subscribers as they take place.
• Sharing a subset of the database among multiple databases.
• The firing of individual changes as they are received by the subscriber.
• Combining multiple databases into one.
• Providing access to replicated data to different groups of users.

Subscriber databases function similarly to any other PostgreSQL instance and can be used to publish other databases through the definition of the publications it publishes.

If the subscriber's data is treated as read-only by the application there'll be no conflicts from a single subscription. On the other hand when there are additional writes done either by the application or by different subscribers to the same set of tables, conflicts can arise.

PostgreSQL allows both methods to be used concurrently. Logical replication gives you precise control of both data replication as well as security.

Replication Modes

There are mainly two modes of PostgreSQL replication: synchronous and Asynchronous. Synchronous replication allows data to be written to both the primary and secondary servers simultaneously whereas asynchronous replication ensures that the data first transferred to the host, and then copied to the secondary server.

In synchronous mode replication, operations on the primary database will be completed only after the changes have been replicated across all replicas. Replica servers should be operational in order for transactions to be complete in the primary. Synchronous replication is used in high-end systems for transactions with instant failure-over needs.

In asynchronous mode, transactions performed on the main server are declared complete when the changes were made on only one server. This is then replicated to the replicas in time. They can be out-of-sync for a certain duration known as a replication lag. In the event of an event of a crash, loss of data may occur, but the overhead provided by asynchronous replication is small, so it's generally acceptable (it does not overload the host). Replication between the main database and the secondary takes longer than synchronous replication.

How Do I Setup PostgreSQL Replication

For this section, we'll be demonstrating how you can set up the PostgreSQL replication on the Linux operating system. In this case, we'll be using Ubuntu 18.04 LTS and PostgreSQL 10.

Let's dig in!

Installation

The first step is to install PostgreSQL on Linux by following these steps:

1. Firstly, you'd have to transfer the PostgreSQL signing key by typing this command into the terminal:

 wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -

1. Add the PostgreSQL repository by typing this in your terminal:

 echo "deb http://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list 

1. Update the Repository Index by typing the below command on the terminal:

 sudo apt-get update 

1. Install PostgreSQL package using the appropriate command:

sudo the following: apt-get installation -y postgresql-10

1. Finally, set the password for the PostgreSQL user with the following instruction:

 sudo passwd postgres

Installation of PostgreSQL is required for both the primary and secondary server prior to beginning this PostgreSQL replicating process.

Once you've configured PostgreSQL for both the servers, it's time to move on to setting up replication on the primary as well as the second server.

Setting Up Replication in Primary Server

Complete these steps when you've set up PostgreSQL on your primary and secondary servers.

1. First, sign in to the database PostgreSQL using the below procedure:

 su - postgres

1. Create a user for replication using these commands:

 psql -c "CREATEUSER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD'YOUR_PASSWORD';"

1. Edit    pg_hba.cnf   with any nano application in Ubuntu and add this configuration to your nano application: file edit command

 nano /etc/postgresql/10/main/pg_hba.conf

For configuring the file make use of the following command:

masterIP/24 for host replication. md5

1. Edit and open postgresql.conf and then put the following configuration on the main server:

 nano /etc/postgresql/10/main/postgresql.conf

Use the following configuration settings:

 listen_addresses = 'localhost,MasterIP'
 

level = replica

 wal_keep_segments = 64
 

 max_wal_senders = 10

1. Finally, then, restart PostgreSQL as the main server:

 systemctl restart postgresql

Now you're done with the configuration in the primary server.

Configuring Replication on Secondary Server

Use these steps to set up replication in the second server:

1. Login to PostgreSQL by using the following command:

 su - postgres

1. Then stop this PostgreSQL service from running so that we can tackle it using this command:

 systemctl stop postgresql

1. Edit    pg_hba.conf   the file using this command. Then make the changes as follows:    
              Edit Command      
    

 nano /etc/postgresql/10/main/pg_hba.conf

Configuration

Host replication masterIP 24 md5

1. Open and edit    postgresql.conf   in the secondary server and add the following configuration, or uncomment if it's commented:    Edit Command      
      
        Configuration      
    

 nano /etc/postgresql/10/main/postgresql.conf
 

 listen_addresses = 'localhost,SecondaryIP' 

 wal_keep_segments = 64
 

wal_level = replica

hot_standby is a reference to

 max_wal_senders = 10
 

SecondaryIP refers to the address of the secondary server.

1. Open the PostgreSQL Data directory from the secondary server . Remove all the items:

 cd /var/lib/postgresql/10/main

*

1. Copy PostgreSQL primary server directory data files into PostgreSQL secondary server's data directory and write this command on this secondary server

 pg_basebackup -h MasterIP -D /var/lib/postgresql/11/main/ -P -U

 replication --wal-method=fetch

1. Input the primary server's PostgreSQL password and hit enter. Next, add the following command for the recovery configuration:    Edit Command      
    

 nano /var/lib/postgresql/10/main/recovery.conf

Configuration

 standby_mode = 'on'
 primary_conninfo = 'host=MasterIP port=5432 user=replication password=YOUR_PASSWORD'
 trigger_file = '/tmp/MasterNow'
 

This is YOUR_PASSWORD, which is the password for users who are replicating on the primary server. PostgreSQL made.

1. After the password is created, it is necessary to start the second PostgreSQL database since it stopped working:

systemctl start postgresql

Checking Your Setup

After we've completed the steps, let's try the process of replication and examine the secondary server's database. To do this, we'll create a table in the primary server. We then check whether the table is replicated on the secondary server.

Let's go to work.

1. As we're creating the table in the main server, you'll need to login to the primary server:

 su - postgres
 psql

1. Now we create a simple table named 'testtable' and add data to it through the PostgreSQL commands from the terminal

 CREATE TABLE testtable (websites varchar(100));
 INSERT INTO testtable VALUES ('section.com');
 INSERT INTO testtable VALUES ('google.com');
 INSERT INTO testtable VALUES ('github.com');

1. Observe the secondary server PostgreSQL database after connecting to the second server:

 su - postgres psql

1. Now, we check whether the testtable table exists, and can return the information by using the following PostgreSQL queries using the terminal. This command basically displays the entire table.

select * from testtable;

This is the result of the test table

Websites
  
 section.com || google.com || github.com |
 

You will be able to view the same data as the one in the primary server.

If you see the above, then you have successfully carried out the replication procedure!

What are the PostgreSQL Manual Failover Steps?

Let's review the steps to perform the PostgreSQL manual failover:

1. Shut down the main server.
2. Make sure to promote the standby server by running the following command on your standby server

./pg_ctl promote -D ../sb_dataServer advertising

1. Connect to the standby server and insert a row -bash-4.2$ ./edb-psql -p 5432 edb Password: psql.bin (10.7) Type "help" for help. edb=# insert into abc values(4,'Four');

If the insert works fine, then the standby which was previously a read-only server is now promoted as the main server.

How Do I Automate Failover PostgreSQL

Automating failovers is straightforward.

You'll need to install the EDB PostgreSQL failover administrator (EFM). After downloading and installing EFM on every standby and primary node you'll be able to build an EFM Cluster, which is comprised of a primary node, and one or more standby nodes and an additional Witness node which is able to verify assertions in case of an error.

EFM constantly monitors the health of your system and provides email alerts based on system events. In the event of a malfunction, it automatically switches over to the latest standby and reconfigures all standby servers to recognize the new primary node.

It is also possible to reconfigure load balancers (such as pgPool) and stops "split-brain" (when 2 nodes each think they are primary) to prevent this from happening.

Summary

Thus, horizontal scalability is needed, that is, expanding the number of nodes in existing nodes in the network, rather than enhancing the functionality of existing nodes. This is where PostgreSQL replication enters into the scene.
 
 In this post we've talked about the various kinds of PostgreSQL replications, their benefits, replication options, how to install and PostgreSQL failover between SMR and MMR. Now let's hear from you.

What is the one you typically apply? What database function is most crucial to you and why? We'd love to read your thoughts! Share them in the comments in the section below.

Cut down on time, expenses and improve site performance by:

• 24/7 help and support assistance from WordPress Hosting experts 24/7.
• Cloudflare Enterprise integration.
• Reaching a global audience with 34 data centers across the globe.
• Optimization using the built-in Application Performance Monitoring.