PostgreSQL High Availability: 5 Comprehensive Aspects

Businesses deal with millions of transactions daily and rely on databases to monitor and store their data. PostgreSQL is also one such tool that allows companies to maintain their structured and unstructured data simultaneously. The PostgreSQL High Availability feature is one of the reasons behind its immense popularity. High Availability represents PostgreSQL’s ability to withstand server crashes and system failure. Since data is precious for business, companies leverage the robust features of PostgreSQL to store their day-to-day information.

This article will introduce you to PostgreSQL and list its key features. It will also explain the importance of PostgreSQL High Availability and provides the steps required to set it up. Furthermore, the article will discuss replication and load sharing in PostgreSQL. Read along to learn more about this platform and understand its benefits!

What is PostgreSQL?

PostgreSQL is an operational RDBMS (Relational Database Management System). This open-source and object-oriented platform allows you to work with both non-relational & relational queries by leveraging the JSON (JavaScript Object Notation) format. This platform is ideal for managing a variety of workloads ranging from individual machines to large Cloud-based Data Warehouses. Furthermore, PostgreSQL strictly follows SQL standards.

Even though PostgreSQL was launched much before its peers, the platform is still considered among the most popular and reliable DBMS by database professionals. Moreover, it supports multiple extensions using which you can customize the database to suit your needs without tampering with the core database. PostgreSQL also facilitates user-defined data types and allows you to generate new XML data queries. Furthermore, the PostgreSQL streaming replication acts as a powerful backup solution for your data.

Key Features of PostgreSQL

The following features are responsible for PostgreSQL’s popularity:

• Open Source: PostgreSQL’s open-source availability offers you unlimited data storage without any functionality constraints. Moreover, this platform does not impose any kind of distribution-based restrictions on your stored data. 
• Integrations: Using PostgreSQL is easy as it offers language support for numerous programming languages such as C, C++, Phyton, Java, etc.
• Multiversion Concurrency Control: A very critical PostgreSQL feature, especially for big companies. With multi-version concurrency control, you can allow multiple users to have access to a common database and deploy read and write operations concurrently.  
• No vendor lock-in: Since PostgreSQL works on a free data distribution policy, it has a multitude of companies that provide services for Postgres. This eliminates any situation of vendor lock-in. 

To learn more about the features of PostgreSQL.

Importance of PostgreSQL High Availability

It contains the measure of a system’s resilience in the face of infrastructure failure. PostgreSQL maintains the high availability of its clusters by ensuring that a secondary server will take over if the primary server crashes. 

The importance of PostgreSQL High Availability is that it ensures that there is no long-lasting effect of a failure in the server or the system. Moreover, it pushes you to monitor and maintain the good health of backend servers. In the event of a sudden failure, PostgreSQL High Availability ensures that failover will be standard ideally and all resources will be split geographically. 

Architectures Needed for PostgreSQL

You can deploy PostgreSQL high Availability via the following 2 architectures:

• Primary-Standby Architecture
• Primary Primary Architecture

1) Primary-Standby Architectures

The Primary-Standby architecture is the basic structure that you can deploy to achieve PostgreSQL High Availability. It contains one primary database coupled with one or more Standby servers. Moreover, both of these databases will be synchronized with the Primary node. So, if you face a situation in which the Primary server fails, you can rely on the Standby server to provide almost all of the data stored in your Primary server. server.

You can deploy the following 2 forms of Standby databases depending on the nature of the replication:

• Logical Standbys: SQL statements are responsible to replicate data between Primary and Standby databases.
• Physical Standbys: Internal Data structure needs transformation to replicate data between Primary and Standby databases.

PostgreSQL relies on a stream of WALs (Write-ahead Logs) to ensure database synchronization.-

2) Primary-Primary Architectures

The Primary-Primary architecture is designed to minimize error impact on a node by distributing its burden among other nodes. Moreover, this distribution will not degrade the performance of other nodes by much. This architecture serves a dual purpose as it creates a high availability environment and scales horizontally.

However, PostgreSQL High Availability does not support this setup “natively”. This implies, that you will need to use third-party tools to implement Primary-Primary architecture.

Steps to Deploy PostgreSQL High Availability

You can implement the PostgreSQL High Availability setup using the following steps:

• Step 1: Set up 2 Compute Engine Instances to run PostgreSQL
• Step 2: Generate a new table for the Guestbook Application
• Step 3: Configure the Primary Server
• Step 4: Create a Primary Server Backup on the Standby Server
• Step 5: Configure the Standby Server to Run in Hot Standby Mode

Step 1: Set up 2 Compute Engine Instances to run PostgreSQL

Log into your PostgreSQL account and build a primary server. Next, create a simple table to conduct testing. This table will store entries for the website’s guestbook and allows visitors to leave a comment. The data field includes the visitor’s email address, a serial ID, and current date and time.

Step 2: Generate a new table for the Guestbook Application

Open the SSH terminal and on the primary server write:

$ sudo -s$ 
sudo -u postgres psql postgres
CREATE TABLE guestbook (visitor_email text, vistor_id serial, date timestamp, message text);
INSERT INTO guestbook (visitor_email, date, message) VALUES (jim@gmail.com', current_date, 'This is a test.');
Enter q to exit PSQL

Moreover, do not exit the root shell yet. You’ll have to rely on the root shell throughout this process.

Step 3: Configure the Primary Server

Now, to perform the required replication, PostgreSQL needs a user “ role”, which will have special permissions. Go to the primary server, run the below command:

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

Next, create a directory and store archive files. Use the following path in one of the configuration files:

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

You also need to add an entry to the user to enable replication.

$ nano ../../etc/postgresql/9.3/main/pg_hba.conf

Now add the following code and replace [standby-IP] with the external IP address of the standby server:

host     replication     repuser         [standby-IP]/32        md5

Finally, save and close the file.

This file will contain the main settings required for PostgreSQL. Now enter the below command for the primary server:

$ nano ../../etc/postgresql/9.3/main/postgresql.conf

In the WRITE AHEAD LOG section, in the Settings section, change the WAL level:

wal_level = hot_standby

Next, navigate to the Archiving section, and change the archive mode:

archive_mode = on

For Windows, write:

archive_command = 'copy "%p" "C:serverarchivedir%f"

Moreover, go to the REPLICATION section, and in the Sending Server(s) section, modify the value for the maximum number of WAL sender processes:

max_wal_senders = 3

For this article, the value of 3 is sufficient to enable backup and replication. Save the file and close it.

Now, restart the server to apply the newly made changes. Enter the following command:

$ sudo service postgresql restart

Step 4: Create a Primary Server Backup on the Standby Server

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.

Ensure that you’re writing commands in the root shell. Go to the SSH terminal for the standby server, enter the following command:

$ sudo -s

The backup utility will not overwrite your existing files. Therefore, you have to rename the data directory present on your standby server using the following command:

$ mv ../../var/lib/postgresql/9.3/main ../../var/lib/postgresql/9.3/main_old

Again run the backup utility and replace [primary-IP] with your external IP address of the primary server as follows:

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

You need to submit the username and password manually.

Step 5: Configure the Standby Server to Run in Hot Standby Mode

Now you must configure the standby server. Go to postgresql.conf and create a new configuration file with the name recovery.conf. Next, edit the file and enter the following command in the terminal:

$ nano ../../etc/postgresql/9.3/main/postgresql.conf

It is crucial that you now visit the REPLICATION section present in the Standby Servers section and then turn on Hot Standby by uncommenting the line:

hot_standby = on

Save and close the file.

Next, copy your sample recovery file to the accurate location by typing the following code in the terminal for the standby server:

$ cp -avr ../../usr/share/postgresql/9.3/recovery.conf.sample /../../var/lib/postgresql/9.3/main/recovery.conf

Edit the recovery file:

$ nano /../../var/lib/postgresql/9.3/main/recovery.conf

In the STANDBY SERVER PARAMETERS section, change the standby mode:

standby_mode = on

Now, set your connection string to the primary server by replacing [primary-external-IP] with your external IP address of the primary server. Similarly, replace [password] with your password for the user named repuser.

primary_conninfo = ‘host=[primary-external-IP] port=5432 user=repuser password=[password]’

Finally, save and close the file. Your PostgreSQL High Availability is now in place. Open the terminal for standby server, enter the following command:

$ service postgresql start

Load Balancing & Replication in PostgreSQL

The high popularity of PostgreSQL is largely due to its ability to perform the following 2 processes:

• Load Balancing in PostgreSQL High Availability
• Data Replication in PostgreSQL High Availability

Load Balancing in PostgreSQL High Availability

Load balancers are tools that you can use to monitor and manage your application traffic and optimally utilize your database architecture. Moreover, these tools can evenly distribute the workload among your database servers and at the same time direct the application traffic towards available/healthy nodes. You can understand better from the following 2 examples of load balancers: examples:

• HAProxy: The HAProxy load balancer works to distribute traffic from one point of origin to single or multiple destinations and can also specify rules or protocols for such processes. In case a destinations stop responding, it is marked as offline and the traffic is directed towards other available destinations.
• Keepalived: The Keepalived load balancer provides a service of configuring a virtual IP address in an active (or passive) collection of servers. This virtual IP address is further given to an active server and is automatically transferred to the passive server if the first option fails. 

Data Replication in PostgreSQL High Availability

Data replication with PostgreSQL High Availability is present in the following 2 forms:

• Streaming Replication: This technique ships the WAL records and then apply them to the standby server. It conveys, what data you need to add or change in which file. As a result, it modifies the standby server into a bit by bit copy of the master server However, it comes along with certain limitations. The streaming replication is unable to perform the copying process in a different version. Moreover, it does not allow you to modify the standby server.

• Logical Replication: Logical replication was designed to overcome the shortcomings of streaming replication. This process also utilizes the WAL file information, but it first decodes it into smaller logical changes. This implies it doesn’t know which byte has changed, but exactly knows, which table contains which table.

Benefits of PostgreSQL Availability

PostgreSQL High Availability provides you with the following benefits:

• Implementing the PostgreSQL High Availability is easy and you do not require any prior or extra training to use it. Moreover, the maintenance of this tool and its administration is seamless for both embedded and enterprise levels.

• The Write-ahead Logging in PostgreSQL ensures a fault-tolerant mechanism which furthers High Availability.

• PostgreSQL source code is openly available and therefore allows you the freedom to implement, modify, and customize it as per your business needs.

Conclusion

The article introduced you to PostgreSQL and explained its key features. It also explained the importance of PostgreSQL High Availability. The article further elaborated on the steps that you can use to set up the feature. It also discussed the concept of load balancing and replication in PostgreSQL.

Explore about Migrating from PostgreSQL to SQL Server

Share your understanding of the PostgreSQL Cluster High Availability in the comments below!