The key words "MUST", "MUST NOT", "REQUIRED", "SHALL", "SHALL NOT", "SHOULD", "SHOULD NOT", "RECOMMENDED", "MAY", and "OPTIONAL" in this document and all other documents within this repository are to be interpreted as described in RFC 2119.
Version 0.1.0
Logical replication in PostgreSQL allows users to perform a replication of tables by automatically opening a standby of all tables within a database for each write operation, whereas physical replication in PostgreSQL is a block level replication. In this case, each database in the master is replicated to a standby, and a standby is not open for write operations ("read-only").
With logical replication, a standby can have replication enabled from multiple masters. This could be helpful in situations where you need to replicate data from several PostgreSQL databases (OLTP) to a single PostgreSQL server for reporting and data warehousing.
One of the biggest advantages of logical over streaming replication is that logical replication allows us to replicate changes from an older version of PostgreSQL to a later version.
The following steps will show you how to stream replicated PostgreSQL data between masters and standby, as in the simplified "ASCII art":
cluster_a_master
|--> standby_a_1
|--> standby_a_2
|--> standby_a_3
To start one master and three standby instances, use the following command:
$ docker-compose up --build --scale standby_a=3"
To view the running statuses (health: starting, healhty), use the following command:
$ docker ps
Log in to your database, using the following command with a temporary available port number. You should remove the port number within a production setup, and use a docker-swarm network for your internal connection.
$ psql -h 127.0.0.1 -U postgres -p 5432
Run the PostgreSQL commands below at the prompt, and check the result each time:
SELECT * FROM pg_stat_replication;
SELECT application_name, state, sync_priority, sync_state FROM pg_stat_replication;
Create a table (in your master instance) and insert some data into it:
CREATE TABLE test_replication (data varchar(100));
INSERT INTO test_replication VALUES ('https://thecatapi.com/');
INSERT INTO test_replication VALUES ('https://www.asciiart.eu/animals/cows');
INSERT INTO test_replication VALUES ('pg_cluster replication test successful');
Log in to your container using the following commands. But replace "2a5929305ca9" with the image ID you see when you enter $ docker ps. If you like to check the images without opening the ports, as in the first master instance within docker-compose.yml, you can simply get into a docker image ID e.g. via exec -it image_id.
$ docker ps
$ docker exec -it 2a5929305ca9 sh
Log in to the different instances, and check how fast databases, tables, and data is replicated. Check the data on the 'test_replication' table with the PostgreSQL query below.
Test in your ("read-only") standby what was replicated:
SELECT * FROM test_replication;
Test the setup information of the standby or logical replicating master:
SELECT * FROM pg_stat_replication;
PostgreSQL supports the adding of extensions via SQL commands or shell scripts during the installation. By default, the repository builds are supported by PostgreSQL with HSTORE via the included SQL hstore.sql command. Any .sql file and any executable .sh script copied into the initialisation directory will be executed during the initialisation of the server - before the service is started.
Both stack.yml (for e.g. Docker Swarm with Portainer) and docker-compose.yml include a healthcheck for master and standby:
healthcheck:
test: ["CMD-SHELL", "pg_isready -U postgres"]
interval: 10s
timeout: 5s
retries: 5
- Chapter 31.1. Logical Replication - Publication (PostgreSQL documentation)
- Streaming_Replication (PostgreSQL wiki)
- Replication Between PostgreSQL Versions Using Logical Replication
- How to Setup a Logical Replication on PostgreSQL 10
- Setup a streaming replication with PostgreSQL 10
- Getting Started with PostgreSQL Streaming Replication