Basic Concepts
A single operating system can run multiple PostgreSQL instances, each with its own configuration file, data directory, and port. Each instance can host multiple databases, and each database can contain multiple tables. Docker makes it easy to create multiple PostgreSQL instances.
Installing the Primary and Replica Servers
docker run --name postgres1 -e POSTGRES_PASSWORD=postgres -d -p 5431:5432 -v ${pwd}/postgres1/data:/var/lib/postgresql/data postgres
docker run --name postgres2 -e POSTGRES_PASSWORD=postgres -d -p 5432:5432 -v ${pwd}/postgres2/data:/var/lib/postgresql/data postgres
Logical Replication
Logical replication is a method of replicating data objects and their changes based on a replication identity (usually the primary key). We use the term “logical” in contrast to physical replication. Logical replication uses a publish and subscribe model, in which one or more subscribers subscribe to one or more publications on a publisher node. Subscribers pull data from the publications they subscribe to, and may subsequently re-publish the data to allow cascading replication or more complex configurations.
Creating the Primary and Replica Databases
Create the database mydb1 in postgres1.
CREATE DATABASE mydb1;
Create the database mydb2 in postgres2.
CREATE DATABASE mydb2;
Create a table mytable with the same structure in both the mydb1 and mydb2 databases.
CREATE TABLE mytable (id int PRIMARY KEY, name text NOT NULL);
Setting the Server WAL Level to Logical
Set the server WAL level to logical in the mydb1 database of postgres1.
ALTER SYSTEM SET wal_level = logical;
Set the server WAL level to logical in the mydb2 database of postgres2.
ALTER SYSTEM SET wal_level = logical;
Creating a Publication on the Primary
Create a publication in the mydb1 primary database of postgres1.
Create a publication for all tables.
CREATE PUBLICATION my_pub FOR ALL TABLES;
Create a publication for specific tables.
CREATE PUBLICATION my_pub FOR TABLE mytable;
CREATE PUBLICATION my_pub FOR TABLE mytable1, mytable2;
Creating a Subscription on the Replica
Create a subscription in the mydb2 replica database of postgres2.
CREATE SUBSCRIPTION my_sub CONNECTION 'dbname=mydb1 host=postgres1_ip_address port=5431 user=postgres password=postgres' PUBLICATION my_pub
Testing Logical Replication
Create the table mytable in both the mdb1 database on the postgres1 primary and the mydb2 database on the postgres2 replica.
Insert data into mydb1 on the postgres1 primary.
INSERT INTO mytable VALUES (1,'hello');
Query the data in mydb2 on the postgres2 replica.
SELECT * FROM mytable;
Notes
For newly created tables, the publication and subscription must be recreated. If the publication was created with FOR ALL TABLES, new tables are added to the publication automatically, but the subscription still needs to be recreated.
Alternatively, you can use graphical tools such as pgAdmin to create publications and subscriptions.
Streaming Replication
Streaming replication is a physical replication method that replicates data by transferring WAL records from one server to another. Streaming replication is a built-in feature of PostgreSQL and can replicate data across different servers.
Primary Server Configuration
Create a replication user on postgres1 to avoid the security issues of using a superuser. Create a user named testuser, granting it only login and replication privileges, with the password testpwd.
CREATE ROLE testuser LOGIN REPLICATION ENCRYPTED PASSWORD 'testpwd';
Modify the pg_hba.conf file on postgres1 to allow the testuser user to connect to the primary server.
host replication testuser all scram-sha-256
Replica Server Configuration
export PGPASSWORD='testpwd' && pg_basebackup -h postgres1_ip_address -p 5431 -D /var/lib/postgresql/data/my_data_backup -U testuser -P -X stream -R
The command above backs up the data from postgres1 into the /var/lib/postgresql/data/my_data_backup directory. -X stream means streaming replication is used, and -R means the replica server starts automatically once the backup completes. From then on, data from postgres1 is synchronized to postgres2 automatically, with no manual steps required.
This command also generates a file named standby.signal in the my_data_backup directory, indicating that the replica server has started, and adds a primary_conninfo setting to the postgresql.auto.conf file, which holds the connection information for the primary server.
Replace the data directory of postgres2 by moving the backed-up data directory into postgres2’s data directory location.
rm -rf /var/lib/postgresql/data
mv /var/lib/postgresql/data/my_data_backup /var/lib/postgresql/data
Differences Between Asynchronous and Synchronous Streaming Replication
In asynchronous replication, the primary server sends WAL records to the replica but does not wait for the replica’s acknowledgment, continuing to process other transactions instead. This improves primary server performance but may lose data.
In synchronous replication, the primary server sends WAL records to the replica and waits for its acknowledgment; only after the replica confirms does the primary continue processing other transactions. This guarantees data consistency but reduces primary server performance.
Configuring Synchronous Replication on the Primary
Run the following command on postgres1.
ALTER SYSTEM SET synchronous_standby_names = 'standby2';
Replica Server Configuration
Modify the postgresql.auto.conf file in postgres2’s data directory and add the following setting.
primary_conninfo = 'application_name=standby2'