PostgreSQL Replication: A Detailed Guide

As any website owner will tell you, data loss and downtime, even in minimal doses, can be catastrophic. They can strike the unprepared at any time, leading to reduced productivity, accessibility and confidence in the product.

In order to protect the integrity of your website, it is essential to have safeguards against the possibility of downtime or data loss.

That's where data replication comes in.

Data replication is an automated backup process in which your data is repeatedly copied from your main database to another remote location to be kept safe. It is an integral technology for any website or application running a database server . You can also leverage the replicated database to process read-only SQL, allowing more processes to run within the system.

Configuring replication between two databases provides fault tolerance against unexpected setbacks. It is considered the best strategy to achieve high availability during disasters.

In this article, we will dive into the different strategies that can be implemented by backend developers for smooth PostgreSQL replication.

What is PostgreSQL replication?

PostgreSQL replication is defined as the process of copying data from one PostgreSQL database server to another server. The source database server is also known as the “primary” server, while the database server that receives the copied data is known as the “replica” server.

The PostgreSQL database follows a simple replication model, where all writes go to a primary node. The primary node can then apply these changes and transmit them to the secondary node.

What is Automatic Failover?

Failover is a method of recovering data if the primary server fails for any reason. As long as you've configured PostreSQL to manage your physical streaming replication, you – and your users – are protected from downtime due to a primary server snafu.

Note that the failover process may take some time to configure and initiate. There are no built-in tools for monitoring and scanning for server failures in PostgreSQL, so you'll need to get creative.

Fortunately, you don't need to be dependent on PostgreSQL for failover. There are dedicated tools that allow automatic failover and automatic switching to standby mode, reducing database downtime .

By configuring failover replication, you ensure high availability by ensuring that standbys are available if the primary server ever fails.

Benefits of using PostgreSQL replication

Here are some of the key benefits of leveraging PostgreSQL replication:

• Data migration: You can leverage PostgreSQL replication for data migration through a change in database server hardware or through system deployment.
• Fault tolerance: If the primary server fails, the standby server can act as a server because the data contained for both the primary and standby servers is the same.
• Online transactional processing (OLTP) performance: You can improve the transaction processing time and query time of an OLTP system by removing the query burden from reports. Transaction processing time is the duration it takes for a given query to execute before a transaction is completed.
• Testing the system in parallel: When upgrading a new system, you need to make sure that the system works well with existing data, hence the need to test with a copy of the production database before deployment.

How PostgreSQL replication works

In general, people believe that when you are dabbling in a primary and secondary architecture, there is only one way to configure backups and replication. PostgreSQL implementations, however, can follow any of these three methods :

1. Streaming replication: Replicates data from primary to secondary node, then copies data to Amazon S3 or Azure Blob for backup storage.
2. Volume-level replication: Replicates data across the storage tier, starting from the primary to secondary node, then copies the data to Amazon S3 or Azure Blob for backup storage.
3. Incremental backups: Replicates data from the primary node while building a new secondary node from Amazon S3 or Azure Blob storage, allowing streaming directly from the primary node.

Method 1: Streaming

PostgreSQL replication also known as WAL replication can be configured seamlessly after installing PostgreSQL on all servers. This replication approach is based on moving the WAL files from the primary database to the target database.

You can implement PostgreSQL replication through a secondary primary configuration. The primary server is the main instance that handles the primary database and all its operations. The secondary server acts as a supplementary instance and executes all changes made to the primary database on itself, generating an identical copy in the process. The primary server is the read/write server while the secondary server is read-only.

For this method, you need to configure both the primary node and the standby node. The following sections will elucidate the steps involved in setting them up with ease.

Configuring the Primary Node

You can configure the primary node for streaming replication by performing the following steps:

Step 1: Initialize the database

To initialize the database , you can use the utility command initdb. You can then create a new user with replication privileges using the following command:

CREATE USER 'example_username' REPLICATION LOGIN ENCRYPTED PASSWORD 'example_password';        

The user will have to provide a password and username for the query in question. The replication keyword is used to give the user the necessary privileges. An example query would look something like this:

CREATE USER 'rep_username' REPLICATION LOGIN ENCRYPTED PASSWORD 'rep_password';        

Step 2: Configure Streaming properties

Next, you can configure the streaming properties with the PostgreSQL configuration file ( postgresql.conf ) which can be modified as follows:

wal_level = logical
wal_log_hints = on
max_wal_senders = 8
max_wal_size = 1GB
hot_standby = on        

Here's a little background around the parameters used in the previous snippet:

• wal_log_hints: This parameter is required for the capability pg_rewindwhich comes in handy when the standby server is out of sync with the primary server.
• wal_level: You can use this parameter to enable PostgreSQL stream replication, with possible values including minimal, replica, or logical.
• max_wal_size: This can be used to specify the size of WAL files that can be retained in log files.
• hot_standby: You can use this parameter for a read connection to the secondary when it is set to ON.
• max_wal_senders: You can use max_wal_sendersto specify the maximum number of simultaneous connections that can be established with the standby servers.

Step 3: Creating new entry

After you have modified the parameters in the postgresql.conf file , a new replication entry in the pg_hba.conf file can allow the servers to establish a connection to each other for replication.

You can usually find this file in the PostgreSQL data directory. You can use the following code snippet for the same:

host replication rep_user IPaddress md5        

Once the code snippet is executed, the primary server allows a named user rep_userto connect and act as a standby server, using the specified IP for replication. For example, the IP specified for replication:

host replication rep_user 192.168.0.22/32 md5        

Configuring the standby node

To configure the standby node for streaming replication, follow these steps:

Step 1: Backup the primary node

To configure the standby node, use the utility pg_basebackupto generate a backup of the primary node. This will serve as a starting point for the standby node. You can use this utility with the following syntax:

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

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

• -h: You can use this to mention the primary host.
• -D: This parameter indicates the directory you are currently working in.
• -C: You can use this to define checkpoints.
• -X: This parameter can be used to include the required transactional log files.
• -W: You can use this parameter to prompt the user for a password before creating a link to the database.

Step 2: Prepare the replication configuration file

Next, you need to verify that the replication configuration file exists. If it doesn't exist, you can generate the replication configuration file as recovery.conf .

You must create this file in the data directory of your PostgreSQL installation. You can generate it automatically using the option -Rwithin the utility pg_basebackup.

The recovery.conf file must contain the following commands:

standby_mode = 'on' primary_conninfo = 'host=<master host> port=<postgres_port> user=<replication_user> password=<password> application_name="host_name"'' recovery_target_timeline = 'latest' (mais recente)        

The parameters used in the above commands are as follows:

• primary_conninfo: You can use this to make a connection between primary and secondary servers by leveraging a connection string.
• standby_mode: This parameter can cause the primary server to start as standby when powered on.
• recovery_target_timeline: You can use this to set the recovery time.

To configure a connection, you need to provide the username, IP address, and password as values for the primary_conninfo parameter. For example:

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

Step 3: Restart the secondary server

Finally, you can restart the secondary server to complete the configuration process.

However, streaming replication comes with several challenges, such as:

• Multiple PostgreSQL clients (written in different programming languages) talk to a single endpoint. When the primary node fails, these clients will continue to retry the same DNS or IP name. This makes the failover visible to the application.
• PostgreSQL replication does not come with built-in failover and monitoring. When the primary node fails, you need to promote a secondary to be the new primary. This promotion needs to be executed in such a way that clients write to only one primary node, and they do not observe data inconsistencies.
• PostgreSQL replicates all of your state. When you need to develop a new secondary node, the secondary needs to recap the entire state change history of the primary node, which is resource intensive and makes it expensive to eliminate head nodes and create new ones.

Method 2: Replicated Block Device

The replicated block device method relies on disk mirroring (also known as volume replication). In this approach, changes are written to a persistent volume that is synchronously mirrored to another volume.

The additional benefit of this method is its compatibility and data durability in cloud environments with all relational databases, including PostgreSQL, MySQL, and SQL Server , to name a few.

However, the disk mirroring approach to PostgreSQL replication requires you to replicate both the WAL log and the table data. Since each write to the database now has to go through the network synchronously, you cannot afford to lose a single byte as this could leave your database in a corrupt state.

This method is typically leveraged using Azure PostgreSQL and Amazon RDS.

Method 3: WAL

WAL consists of segment files (16 MB by default). Each segment has one or more records. A log sequence record (LSN) is a pointer to a record in the WAL, letting you know the position/location where the record was saved in the log file.

A standby server uses WAL segments – also known as XLOGS in PostgreSQL terminology – to continually replicate changes from its primary server. You can use write-ahead logging to ensure durability and atomicity in a DBMS by serializing chunks of byte-array data (each with a unique LSN) to stable storage before they are applied to a database.

Applying a mutation to a database can lead to multiple file system operations. A pertinent question that arises is how a database can ensure atomicity in the event of a server failure due to a power outage while it is in the middle of a file system update. When a database starts, it initiates an initialization or replay process that can read the available WAL segments and compare them to the LSN stored in each data page (each data page is marked with the LSN of the last WAL record it affects the page).

Log shipping-based replication (block level)

Streaming replication refines the log shipping process. Unlike waiting for the WAL key, records are sent as they are created, thus decreasing replication delay.

Streaming replication also outperforms log shipping because the standby server connects to the primary server over the network, leveraging a replication protocol. The primary server can then send WAL records directly over this connection without having to rely on scripts provided by the end user.

Log shipping-based replication (file level)

Log shipping is defined as copying log files to another PostgreSQL server to generate another standby server by replaying WAL files. This server is configured to work in recovery mode, and its sole purpose is to apply any new WAL files as they appear.

This secondary server then becomes a hot backup of the primary PostgreSQL server. It can also be configured to be a read replica, where it can offer read-only queries, also called hot standby.

Continuous WAL Archiving

Duplication of WAL files as they are created in any location other than the subdirectory pg_walto archive them is known as WAL archiving. PostgreSQL will call a user-given script for archiving each time a WAL file is created.

The script can leverage the command scpto duplicate the file to one or more locations, such as an NFS mount. Once archived, WAL segment files can be leveraged to recover the database at any point in time.

Other log-based settings include:

• Synchronous replication: Before each synchronous replication transaction is committed, the primary server waits for the standbys to confirm that they have received the data. The benefit of this configuration is that there will be no conflicts caused due to parallel writing processes.
• Multi-master synchronous replication: Here, each server can accept write requests, and the modified data is transmitted from the original server to all other servers before each transaction is committed. It leverages the 2PC protocol and adheres to the “all or nothing” rule.

WAL Streaming Protocol Details

A process known as the WAL receiver, running on the standby server, takes advantage of the connection details provided in the recovery.conf primary_conninfo parameter and connects to the primary server leveraging a TCP/IP connection.

To start streaming replication, the frontend can send the replication parameter within the initialization message. A boolean value of true, yes, 1, or ON lets the backend know that it needs to enter physical replication walsender mode.

The WAL sender is another process that runs on the primary server and is in charge of sending WAL records to the standby server as they are generated. The WAL receiver saves WAL records to the WAL as if they were created by client activity from locally connected clients.

Once the WAL records arrive in the WAL segment files, the standby server continues to constantly replay the WAL so that the primary and standby are up to date.

Elements of PostgreSQL replication

In this section, you will gain a deeper understanding of the commonly used models (single-master and multi-master replication), types (physical and logical replication), and modes (synchronous and asynchronous) of PostgreSQL replication.

PostgreSQL database replication models

Scalability means adding more resources/hardware to existing nodes to increase the database's ability to store and process more data that can be reached horizontally and vertically. PostgreSQL replication is an example of horizontal scaling that is much more difficult to implement than vertical scaling. We can achieve horizontal scalability mainly by single-master replication (SMR) and multi-master replication (MMR).

Multi-master replication allows data to be modified only on a single node, and these modifications are replicated on one or more nodes. Replicated tables in the replica database are not allowed to accept any changes except those from the primary server. Even if they accept, the changes are not replicated back to the primary server.

Most of the time, SMR is sufficient for the application because it is less complicated to configure and manage with no chances of conflicts. Single-master replication is also unidirectional, since replication data flows in one direction primarily, from the primary database to the replica database.

In some cases, SMR alone may not be enough, and you may need to implement MMR. MMR allows more than one node to act as the primary node. Changes to table rows in more than one designated primary database are replicated to their counterpart tables in all other primary databases. In this model, conflict resolution schemes are often employed to avoid problems such as duplication of primary keys.

There are some advantages to using MMR, namely:

• In case of host failure, other hosts can still provide update and insertion services.
• The primary nodes are spread across several different locations, so the chance of failure of all the primary nodes is very small.
• Ability to employ a wide area network (WAN) of primary databases that can be geographically close to customer groups but maintain data consistency across the network.

However, the downside of implementing MMR is its complexity and difficulty in resolving conflicts.

Various branches and applications provide MMR solutions as PostgreSQL does not support it natively. These solutions can be open-source , free or paid. One such extension is bidirectional replication (BDR) which is asynchronous and is based on PostgreSQL's logical decoding function.

Because BDR enforcement replays transactions on other nodes, the replay operation may fail if there is a conflict between the transaction being applied and the committed transaction on the receiving node.

Types of PostgreSQL replication

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

A simple logical initdb operation would perform the physical operation of creating a base directory for a cluster. Likewise, a simple logical operation would perform the physicalCREATE DATABASE ?operation of creating a subdirectory in the base directory.

Physical replication generally deals with files and directories. It doesn't know what these files and directories represent. These methods are used to maintain a complete copy of a single cluster's entire data, typically on another machine, and are done at the file system level or disk level and use exact block addresses.

Logical replication is a way of reproducing data entities and their modifications, based on their replication identity (usually a primary key). Unlike physical replication, it deals with databases, tables, and DML operations and is done at the database cluster level. It uses a publish - subscribe model where one or more subscribers subscribe to one or more publications on a publisher node .

The replication process begins by taking a snapshot of the data in the publisher's database and then copying it to the subscriber. Subscribers pull data from the publications they subscribe to and can re-publish the data later to enable cascading replication or more complex configurations. The subscriber applies data in the same order as the publisher so that transactional consistency is guaranteed for publications within a single subscription, also known as transactional replication.

Typical use cases for logical replication are:

• Sending incremental changes to a single database (or a subset of a database) to subscribers as they occur.
• Share a subset of the database between multiple databases.
• Trigger the triggering of individual changes as they reach the subscriber.
• Consolidation of multiple databases into one.
• Providing access to replicated data for different groups of users.

The subscriber database behaves in the same way as any other PostgreSQL instance and can be used as an editor for other databases defining their publications.

When the subscriber is treated as read-only per application, there will be no conflicts from a single signature. On the other hand, if there are other writes made by an application or by other subscribers to the same set of tables, conflicts may arise.

PostgreSQL supports both mechanisms simultaneously. Logical replication allows fine-grained control over both data replication and security.

Replication modes

There are mainly two modes of PostgreSQL replication: synchronous and asynchronous. Synchronous replication allows data to be written to the primary and secondary server at the same time, while asynchronous replication ensures that data is first written to the host and then copied to the secondary server.

In synchronous replication mode, transactions on the primary database are considered complete only when these changes have been replicated to all replicas. The replica servers must all be available at all times for transactions to complete on the primary database. Synchronous mode of replication is used in high-end transactional environments with immediate failover requirements.

In asynchronous mode, transactions on the primary server can be declared complete when changes have been made only on the primary server. These changes are then replicated to replicas later in time. Replica servers can remain out of sync for a certain duration, called replication lag. In the event of a failure, data loss may occur, but the overhead provided by asynchronous replication is small, so it is acceptable in most cases (does not overload the host). Failover from the primary database to the secondary database takes longer than synchronous replication.

How to configure PostgreSQL replication

For this section, we will be demonstrating how to configure the PostgreSQL replication process on a Linux operating system. For this example, we will be using Ubuntu 18.04 LTS and PostgreSQL 10.

Important

Make sure you have installed Ubuntu Linux server. The steps to install PostgreSQL on a Linux server are mentioned below, followed by configuring replication on the primary and secondary server.

Lets go to what matters!

Installation

You will start by installing PostgreSQL on Linux with these steps:

1. Firstly, you would have to import the PostgreSQL signing key by typing the command below in the terminal:wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O- | sudo apt-key add -
2. Then, add the PostgreSQL repository by typing the command below in the terminal:echo "deb https://apt.postgresql.org/pub/repos/apt/ bionic-pgdg main" | sudo tee /etc/apt/sources.list.d/postgresql.list
3. Update the Repository Index by typing the following command in the terminal:sudo apt-get update
4. Install the PostgreSQL package using the apt command:sudo apt-get install -y postgresql-10
5. Finally, set the password for the PostgreSQL user using the following command:sudo passwd postgres

PostgreSQL installation is mandatory for both primary and secondary servers before starting the PostgreSQL replication process.

Once you have configured PostgreSQL on both servers, you can move on to configuring primary and secondary server replication.

Configuring replication on the primary server

Perform these steps once you have installed PostgreSQL on both the primary and secondary servers.

1. Firstly, login to the PostgreSQL database with the following command:su - postgres
2. Create a replicator user with the following command:psql -c "CREATEUSER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD'YOUR_PASSWORD';"
3. Edit pg_hba.cnf with any nano application on Ubuntu and add the following configuration:nano /etc/postgresql/10/main/pg_hba.confTo configure the file, use the following command:host replication replication MasterIP/24 md5
4. Open and edit postgresql.conf and place the following configuration on the primary server:nano /etc/postgresql/10/main/postgresql.confUse the following settings: listen_addresses = 'localhost,MasterIP' wal_level = replica wal_keep_segments = 64 max_wal_senders = 10
5. Finally, restart PostgreSQL on the main server:

systemctl restart postgresql        

You have now completed the configuration on the primary server.

Configuring replication on the secondary server

Follow these steps to configure replication on the secondary server:

1. Access PostgreSQL RDMS with the command below:su - postgres
2. Stop the PostgreSQL service from running so we can work on it with the command below:systemctl stop postgresql
3. Edit the pg_hba.conf file with this command and add the following configuration:// "Edit" command nano /etc/postgresql/10/main/pg_hba.conf // "Configuration" command host replication replication MasterIP/24 md5
4. Open and edit postgresql.conf on the secondary server and put the following configuration or uncomment if commented:nano /etc/postgresql/10/main/postgresql.conf listen_addresses = 'localhost,SecondaryIP' wal_keep_segments = 64 wal_level = replica hot_standby = on max_wal_senders = 10SecondaryIP is the secondary server address
5. Go to the PostgreSQL data directory on the secondary server and remove everything:cd /var/lib/postgresql/10/main rm -rfv *
6. Copy the files from the primary PostgreSQL server's data directory to the secondary PostgreSQL server's data directory and write this command on the secondary server:pg_basebackup -h MasterIP -D /var/lib/postgresql/11/main/ -P -U replication --wal-method=fetch
7. Enter the password for the primary PostgreSQL server and press enter. Next, add the following command to 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'</code>Here YOUR_PASSWORDis the password for the replicator user on the created PostgreSQL primary server
8. Once the password has been set, you would have to restart the PostgreSQL secondary database since it was stopped:

systemctl start postgresql        

Testing your configuration

Now that we have completed the steps, let's test the replication process and observe the secondary server database. To do this, we create a table on the primary server and see if it is reflected on the secondary server.

Let's do it.

1. Since we are creating the table on the primary server, you will need to log in to the primary server:su - postgres psql
2. Now we create a simple table called 'testtable' and insert data into the table by running the following PostgreSQL queries in 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');
3. Observe the PostgreSQL server secondary database logging into the secondary server:su - postgres psql
4. Now, we check if the table 'testtable' exists, and we can return the data by running the following PostgreSQL queries in the terminal. This command essentially displays the entire table.select * from testtable;

This is the test table output:

|  websites  |

-------------------

| section.com |

| google.com |

| github.com  |

--------------------        

You should be able to observe the same data as on the primary server.

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

What are the failover steps in the PostgreSQL manual?

Let's review the steps for a manual PostgreSQL failover:

1. Crash on primary server.
2. Promote the standby server by running the following command on the standby server:./pg_ctl promote -D ../sb_data/ server promoting
3. Connect to the promoted standby server and enter a line:-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 well, then the standby, previously a read-only server, has been promoted as the new primary server.

How to automate failover in PostgreSQL

Configuring automatic failover is easy.

You will need the EDB PostgreSQL failover manager (EFM). After downloading and installing EFM on each primary and standby node, you can create an EFM Cluster, which consists of a primary node, one or more standby nodes, and an optional witness node that confirms assertions in case of failure.

EFM continuously monitors system health and sends email alerts based on system events. When a failure occurs, it automatically switches to the most up-to-date standby and reconfigures all other standby servers to recognize the new primary node.

It also reconfigures load balancers (like pgPool) and prevents “split-brain” from occurring (when two nodes think each of them is primary).

Summary

Due to the high amount of data, scalability and security have become two of the most important criteria in database management , especially in a transaction environment. While we can improve vertical scalability by adding more resources/hardware to existing nodes, this is not always possible, often due to the cost or limitations of adding new hardware.

Therefore, horizontal scalability is necessary, which means adding more nodes to existing network nodes rather than improving the functionality of existing nodes. This is where PostgreSQL replication comes into play.

In this article, we discuss the types of PostgreSQL replications, benefits, replication modes, installation and failover of PostgreSQL between SMR and MMR. Now let's hear from you.

Which do you usually implement? Which database feature is most important to you and why? We'd love to read your thoughts! Share them in the comments section below.

Save time and costs, plus maximize site performance, with $300+ worth of enterprise-level integrations included in every Managed WordPress plan. This includes a high-performance CDN, DDoS protection, malware and hack mitigation, edge caching, and Google's fastest CPU machines. Get started with no long-term contracts, assisted migrations, and a 30-day money-back guarantee.