Create a docker-compose.yaml file as follows:
version: '3.1'
services:
primary:
image: postgres
container_name: primary
environment:
POSTGRES_PASSWORD: postgres
secondary:
image: postgres
container_name: secondary
environment:
POSTGRES_PASSWORD: postgres
Run docker compose up
In a new shell, run docker ps and you should see:
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
0c33e71ceba5 postgres "docker-entrypoint.s…" 7 seconds ago Up 5 seconds 5432/tcp secondary
ced074141815 postgres "docker-entrypoint.s…" 7 seconds ago Up 5 seconds 5432/tcp primary
Run docker exec -it primary bash to enter the primary container.
This container doesn't come with vim. Install it via:
apt update
apt upgrade
apt install-vim
Create a database, user, and grant access to the user:
psql -U postgres
create database app;
create user app with encrypted password 'app';
grant all privileges on database app to app;
postgres=# create database app;
CREATE DATABASE
postgres=# create user app with encrypted password 'app';
CREATE ROLE
postgres=# grant all privileges on database app to app;
GRANT
Do the same on the secondary container via docker exec -it secondary bash
On the primary, enable logical replication from the postgres superuser:
ALTER SYSTEM SET wal_level = 'logical';
This doesn't take effect until the server is restarted. On this docker container, the only way to do this is to ctrl+c the docker compose, and restart it via docker compose up.
Verify it is correct via SHOW wal_level;:
app=> show wal_level;
wal_level
-----------
logical
Perform the following as the app user.
Create this table on both the primary and secondary:
CREATE SCHEMA app;
CREATE TABLE foo(c1 int primary key, c2 int);
On the primary, insert a row:
INSERT INTO foo VALUES (1, 1);
On the primary, create a publication for this table:
CREATE PUBLICATION foo_pub FOR TABLE foo;
On the secondary, as the postgres superuser logged into the app database, create a subscription for the foo_pub publication:
CREATE SUBSCRIPTION foo_sub
CONNECTION 'host=primary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub
An alternative to using the postgres superuser is to create a user with the REPLICATION attribute on the primary, and to connect to this user from the secondary.
Now log back into the app user on the secondary.
You will see that the original row has already been replicated via select * from foo;
On the primary, insert a second row, and confirm it has been replicated to the secondary.
In another bash, stop the secondary docker container:
docker stop secondary
In the primary, insert another row.
Restart the secondary:
docker start secondary
In the secondary, select from the foo table to confirm that the row was replicated.
Check out the create publication command here.
This only publishes inserts:
create publication bar_pub
for table bar with (publish = 'insert');
Use the pg_publication table to view all publications.
Use the pg_subscription table to view all subscriptions.
You can do some interesting setups. How about inserting into the primary database and replicating to a secondary, while allowing updates to take place in the secondary database and replicating the updates to the primary?
On the primary:
CREATE TABLE foo (c1 int primary key, c2 int);
CREATE PUBLICATION foo_pub_primary FOR TABLE foo
with (publish = 'insert');
On the secondary:
CREATE TABLE foo (c1 int primary key, c2 int);
CREATE PUBLICATION foo_pub_secondary FOR TABLE foo
with (publish = 'update');
On the primary:
CREATE SUBSCRIPTION foo_sub_secondary
CONNECTION 'host=secondary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_secondary;
On the secondary:
CREATE SUBSCRIPTION foo_sub_primary
CONNECTION 'host=primary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_primary;
How about a multi-master setup, whereby writes can take place on both databases and then replicate to one another?
You need to be careful about the primary keys here. If the same primary key is written to both, it will not work.
On the primary:
CREATE TABLE foo (c1 int primary key, c2 int);
CREATE PUBLICATION foo_pub_primary FOR TABLE foo;
On the secondary:
CREATE TABLE foo (c1 int primary key, c2 int);
CREATE PUBLICATION foo_pub_secondary FOR TABLE foo;
On the primary:
CREATE SUBSCRIPTION foo_sub_secondary
CONNECTION 'host=secondary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_secondary;
On the secondary:
CREATE SUBSCRIPTION foo_sub_primary
CONNECTION 'host=primary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_primary;
The following insert into the Primary fails:
insert into foo values (1, 1);
In the postgres logs we see:
ERROR: duplicate key value violates unique constraint "foo_pkey"
And this makes sense. The insert was replicated to the secondary, where it was inserted. The secondary attempted to replicate that into the primary, where it failed.
So how can we do a multi-master replication?
One option is to add a column called db_name to each table, and make use of Postgresql's Row Filters to only replicate inserts where the db_name value corresponds to the database.
I'll create a new database for my secondary, called "secondary":
create database secondary;
grant all privileges on database secondary to app;
After logging into the secondary as the superuser:
create schema app;
set schema 'app'
We can determine the current database name via:
select current_database()
On the primary:
CREATE TABLE foo (c1 int primary key, c2 int, db_name text);
CREATE PUBLICATION foo_pub_primary FOR TABLE foo
WHERE (db_name = current_database());
Unfortunately, that fails:
DETAIL: User-defined or built-in mutable functions are not allowed.
Looks like you have to hard code it:
CREATE PUBLICATION foo_pub_primary FOR TABLE foo
WHERE (db_name = 'app');
On the secondary:
CREATE TABLE foo (c1 int primary key, c2 int, db_name text);
CREATE PUBLICATION foo_pub_secondary FOR TABLE foo
WHERE (db_name = 'secondary');
On the primary:
CREATE SUBSCRIPTION foo_sub_secondary
CONNECTION 'host=secondary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_secondary;
On the secondary:
CREATE SUBSCRIPTION foo_sub_primary
CONNECTION 'host=primary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_primary;
Now you can insert from the primary:
insert into foo values (1, 1, 'app');
And also from the secondary:
insert into foo values (2, 2, 'secondary');
Another option for multi-master replication uses sharded partitions. This is a more realistic use case.
For example, let's pretend we have two geographical regions, US and UK. We want to have a database in each region, to which clients within the region can write. However, we would additional like to replicate the regions into each region for reads.
Create the following on both the US and UK database:
CREATE TABLE foo (c1 int, c2 int, region text, primary key (c1, region))
PARTITION BY LIST (region);
CREATE TABLE foo_us PARTITION OF foo FOR VALUES IN ('US');
CREATE TABLE foo_uk PARTITION OF foo FOR VALUES IN ('UK');
If you wanted to, you could create a trigger that would automatically insert 'US' or 'UK'. I'll skip this.
On the US database, add the following publication:
CREATE PUBLICATION foo_pub_primary FOR TABLE foo_us;
On the UK database:
CREATE PUBLICATION foo_pub_secondary FOR TABLE foo_uk;
Create the subscription on the US database:
CREATE SUBSCRIPTION foo_sub_secondary
CONNECTION 'host=secondary dbname=secondary user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_secondary;
Create the subscription on the UK database:
CREATE SUBSCRIPTION foo_sub_primary
CONNECTION 'host=primary dbname=app user=postgres password=postgres application_name=foo_sub'
PUBLICATION foo_pub_primary;