Table of Contents

How to Setup PostgreSQL Replication

PostgreSQL, often referred to as “Postgres,” is a powerful open-source object-relational database management system (DBMS). It was first released in 1996 and has since become one of the most popular and widely used database systems, particularly in the web development industry.

One of the key features of PostgreSQL is its ability to handle large amounts of data while maintaining consistency and reliability. It uses a robust transactional model that ensures that all database changes are recorded in a secure and reliable way. This makes it an ideal choice for applications that require high levels of data integrity and reliability, such as financial and banking systems.

PostgreSQL also supports a wide range of data types, including structured, semi-structured, and unstructured data. This means that it can handle complex data sets and supports advanced data processing capabilities, such as geospatial data and JSON data.

Another advantage of PostgreSQL is its extensibility. It supports a range of extensions that can be used to enhance its functionality and integrate it with other technologies. For example, the PostGIS extension can be used to add geospatial functionality to the database, while the pgAdmin extension provides a graphical user interface for managing PostgreSQL databases.

Overall, PostgreSQL is a robust and versatile database system that is well-suited to a range of applications. Its open-source nature, combined with its powerful features and reliability, has made it a popular choice among developers and businesses alike.

In this article, I will tell you the best way to arrangement PostgreSQL 11 replication. We should get everything rolling.

Physical PostgreSQL Replication

Physical replication in PostgreSQL is a process where data from one PostgreSQL server is continuously copied or mirrored to one or more standby servers. This process is also known as streaming replication and is a key feature of PostgreSQL that enables high availability and disaster recovery.

In physical replication, data is continuously copied from the primary server to one or more standby servers. The standby servers can be set up to act as hot standby servers, which means that they can be used for read-only queries or failover in the event of a primary server failure. In the event of a primary server failure, the standby server can be promoted to the new primary server, allowing the application to continue running without interruption.

To set up physical replication in PostgreSQL, the following steps are typically involved:

Enable the replication feature on the primary server by setting the appropriate configuration parameters in the postgresql.conf file.
Create a replication user on the primary server and grant it the necessary permissions to perform replication tasks.
Configure the standby servers to receive data from the primary server using the recovery.conf file. This file contains information about the primary server, such as its IP address and replication user credentials.
Start the replication process on the standby servers by using the pg_basebackup utility, which creates a base backup of the primary server and starts streaming changes from the primary server to the standby servers.
Monitor the replication process using tools such as pg_stat_replication and pg_stat_activity to ensure that the standby servers are up to date and can be used for failover.

Overall, physical replication in PostgreSQL is a powerful feature that provides high availability and disaster recovery capabilities for mission-critical applications. By continuously copying data from the primary server to standby servers, physical replication enables applications to continue running even in the event of a primary server failure.

Logical PostgreSQL Replication

Logical replication in PostgreSQL is a process that enables the replication of data between databases based on logical changes to the data rather than physical changes. This means that the replicated data can be transformed or filtered before it is copied to the target database. Logical replication is useful for scenarios such as data warehousing, real-time analytics, and sharing data between different applications.

In logical replication, the source database sends a stream of logical changes to the target database. These logical changes are then transformed and applied to the target database using SQL statements. This enables more flexibility in how the data is replicated and provides the ability to filter or transform the data as needed.

To set up logical replication in PostgreSQL, the following steps are typically involved:

Enable the logical replication feature on both the source and target databases by setting the appropriate configuration parameters in the postgresql.conf file.
Create a publication on the source database, which defines the tables and columns that will be replicated.
Create a subscription on the target database, which defines the source database and publication that will be used for replication.
Start the replication process by using the pg_create_logical_replication_slot function to create a replication slot on the source database.
Monitor the replication process using tools such as pg_stat_subscription and pg_stat_replication to ensure that the replication is up to date and functioning properly.

Overall, logical replication in PostgreSQL is a powerful feature that enables the replication of data between databases based on logical changes to the data. This provides more flexibility and control over how the data is replicated, enabling scenarios such as data warehousing, real-time analytics, and sharing data between different applications.

How Does Replication Works in PostgreSQL

Replication in PostgreSQL is the process of creating and maintaining copies of a database on one or more standby servers, ensuring that data is available and up-to-date in the event of a failure or disaster. PostgreSQL provides two types of replication: physical replication and logical replication.

Physical replication in PostgreSQL is a process where data from one PostgreSQL server is continuously copied or mirrored to one or more standby servers. In this process, changes to the primary database are written to a replication log, which is then streamed to the standby server. The standby server applies these changes to its own copy of the database, ensuring that it is in sync with the primary database. Physical replication provides a high level of data consistency and reliability, making it suitable for mission-critical applications.

Logical replication, on the other hand, is based on logical changes made to the data rather than physical changes. In logical replication, the source database sends a stream of logical changes to the target database. These logical changes are then transformed and applied to the target database using SQL statements. Logical replication provides more flexibility in how the data is replicated and enables scenarios such as data warehousing, real-time analytics, and sharing data between different applications.

Both physical and logical replication in PostgreSQL use a primary/standby model, where the primary database is the source of the replicated data and the standby servers are the targets of the replication. The standby servers can be configured to act as hot standby servers, which means that they can be used for read-only queries or failover in the event of a primary server failure. In the event of a primary server failure, the standby server can be promoted to the new primary server, allowing the application to continue running without interruption.

Overall, replication in PostgreSQL is a powerful feature that enables the creation and maintenance of copies of a database on one or more standby servers. By providing data consistency, reliability, and flexibility, replication ensures that data is available and up-to-date in the event of a failure or disaster, making it a key feature for mission-critical applications.

Network Diagram:

This is the organization graph for the PostgreSQL Master/Slave replication arrangement. Here I have two servers, postgres-master is the Master PostgreSQL server and postgres-slave is the Slave PostgreSQL server. Obviously, you can have more Slave server; however for effortlessness I will have one Slave server.

Introducing PostgreSQL

PostgreSQL is an open-source relational database management system that provides a powerful and flexible platform for building scalable and reliable applications. It was originally developed at the University of California, Berkeley in the 1980s and is now maintained by the PostgreSQL Global Development Group.

One of the key strengths of PostgreSQL is its adherence to SQL standards, making it a highly reliable and stable platform for building applications that require complex data handling, storage, and retrieval. Additionally, PostgreSQL provides advanced features such as support for multiple data types, indexing, transactions, and concurrency control, which makes it suitable for a wide range of applications, from small-scale web applications to large-scale enterprise systems.

PostgreSQL also provides a range of advanced features, including support for advanced indexing and query optimization, data replication, and data partitioning, which make it an ideal choice for high-performance and mission-critical applications.

PostgreSQL supports a wide range of programming languages, including Java, Python, C++, and Ruby, which allows developers to build applications using the programming languages they are most comfortable with. Additionally, PostgreSQL provides a range of development tools, including graphical user interfaces, command-line tools, and APIs that simplify the development process.

Overall, PostgreSQL is a highly capable and flexible database management system that provides a range of advanced features and excellent support for SQL standards. Whether you’re building a small-scale web application or a large-scale enterprise system, PostgreSQL provides a reliable and scalable platform for building and managing your data.

You need to introduce PostgreSQL on every one of the servers that will be a piece of the replication arrangement. For my situation, the two servers postgres-master, and postgres-slave.

I will tell you the best way to introduce PostgreSQL on postgres-master machine. The means are no different for the postgres-slave machines too.

On the postgres-master server

First you need to add the PostgreSQL bundle store on your Ubuntu machine. To do that, run the accompanying order:

$ echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main 11" | sudo tee /etc/apt/sources.list.d/pgsql.list

The PostgreSQL repository ought to be added, by accompanying order to add the GPG key of the PostgreSQL bundle repository:

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

Presently update the APT bundle vault reserve with the accompanying order:

$ sudo apt update

Presently introduce PostgreSQL information base server with the accompanying order:

$ sudo apt install postgresql-10

Presently press y and afterward press <Enter> to proceed.

PostgreSQL ought to be introduced.

Set secret word for the postgres client with the accompanying order:

$ sudo passwd postgres

Enter the secret password. It should be set.

Setting Up the Master PostgreSQL Server:

$ su – postgres

create a new user for replication

$ psql -c "CREATE USER replication REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'YOUR_PASSWORD';

Now open /etc/postgresql/11/pg_hba.conf in your favorite editor

$ vim /etc/postgresql/11/main/pg_hba.conf

Again open the main PostgreSQL configuration file with your favorite editor

$ vim /etc/postgresql/11/main/postgresql.conf

Presently find and change the accompanying settings. Assuming that any line is remarked out, uncomment it by removing #

listen_addresses = 'localhost,192.168.1.10'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

Now restart PostgreSQL server on your postgres-master server:

$ systemctl restart postgresql

Configuring the Slave Server:

On the postgres-slave server login as postgres client:

$ su – postgres

Stop the PostgreSQL

$ systemctl stop postgresql

Now open /etc/postgresql/11/main/pg_hba.conf with your favorite editor

$ vim /etc/postgresql/11/main/pg_hba.conf

Add the accompanying line as you did on the postgres-master server:

host    replication     replication     192.168.1.10/24   md5

Now open the main PostgreSQL configuration file with your favorite editor

$ vim /etc/postgresql/11/main/postgresql.conf

Presently find and change the accompanying settings. Assuming any line is remarked out, uncomment it eliminating #

listen_addresses = 'localhost,192.168.1.20'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64
hot_standby = on

Goto data_directory

$ cd /var/lib/postgresql/11/main

Eliminate everything from that directory

$ rm -rfv *

Now copy data from master to slave

$ pg_basebackup -h 192.168.1.10 -D /var/lib/postgresql/11/main/ -P -U replication --wal-method=fetch

Now make recovery.conf in data directory

$ vim recovery.conf

And add the following

standby_mode          = 'on'
primary_conninfo      = 'host=192.168.1.10 port=5432 user=replication password=123'
trigger_file = '/tmp/MasterNow'

Start the PostgreSQL on slave server

$ systemctl start postgresql

Conclusion

This article improves you with inside and out information about the different ideas driving the replication interaction in PostgreSQL. It gives a bit by bit complete manual for assist you with performing PostgreSQL streaming replication in the smoothest way imaginable. PostgreSQL streaming replication strategy can, notwithstanding, end up being testing particularly for beginners.

Open Source Listing