...

Each facility has a database server. For each site the facility library is the main library. The libraries from other facilities are replications. These replications are read-only. This means that every site is independent but still benefits from the work and libraries another facility is doing.

In Postgres terms:
A logical replication is the preferred way to go, since each facility can have their own database setup and also different version of Postgres without running into any issues.
That’s not possible Physical replication, since the Postgres version as well as the folder/file structure needs to be identical. With logical we have one Publisher and as many Subscribers as we like. The Subscribers will be the Postgres databases in the other facilities.

Requirements

In each facility you have a database server up and running with Postgres installed.Use a Streaming Replication for Multi-Site setups.

Requirements

In each facility you have a database server up and running with Postgres installed.

START STREAMING

Create primary database server

Configure primary server

1. change postgres.conf to enable networking

2. create a replication user

3. allow remote access in pg_hba.conf

initdb -D /var/lib/postgresql/daselement-munich

change postgres.conf to enable networking

vim /var/lib/postgresql/daselement-munich/postgres.conf

# uncomment and change the line:
listen_addresses = '*'

>>> remove? # and change the port to 5433 to avoid conflicts with production port 5432
>>> remove? #port = 5433

# start the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-data start

create a replication user

# create replication user
psql --port 5432 postgres

[local]:5432 postgres=# create user repuser replication;

allow remote access in pg_hba.conf

add line to allow access for the replication user

vim /var/lib/postgresql/daselement-data/pg_hba.conf

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# IPv4 local connections
host    all             repuser             localhost               trust

# restart the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-munich restart

The primary system is now ready. Let’s move on to the replica system!

Create replica database server

# create the replica Postgres server
# this command pg_basebackup copies the files from the primary database

pg_basebackup -h db-munich -U repuser --checkpoint=fast -D /var/lib/postgresql/daselement-munich-replica/ -R --slot=daselement_munich -C --port 5432

In the replica directory you will find these two files

standby.signal

this file defines that the database that it is a replica

postgresql.auto.conf

contains information to connect to the primary server

Now start the replica server

# start the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-munich-replica

END STREAMING

Monitoring

...

Setup VMs

install Postgres

START logical replication

Configure Logical Replication

1. initiate 2 new PostgresSQL database clusters

2. Configure Publisher server with “wal_level=logical“

3. Start the instance

4. Create a database and the tables

Configure primary server

# create database cluster
initdb -D /var/lib/postgresql/daselement-munich_publication

...

1. Create a publication on the Publish Server

2. Create a subscription on the Subscriber Server

3. Test the replication

END logical replication

Troubleshooting

userful command

...

# create a symbolic link to initdb to access the command
sudo ln -s /usr/lib/postgresql/14/bin/pg_ctl /usr/local/bin

# now you can run ...
pg_ctl

Unable to

...

connect to server; FATAL: role “postgres“ does not exist

Seems like there is no user in the database called “postgres”.
To create a user use this command:

...

GRANT ALLPRIVILEGES ON ALL TABLES IN SCHEMA public to user_replication;

START STREAMING

This is for a streaming replication! We want to have a logical replication.

Create primary database server

initdb -D /var/lib/postgresql/daselement-munich

Configure primary server

1. change postgres.conf to enable networking

2. create a replication user

3. allow remote access in pg_hba.conf

change postgres.conf to enable networking

vim /var/lib/postgresql/daselement-munich/postgres.conf

# uncomment and change the line:
listen_addresses = '*'

# and change the port to 5433 to avoid conflicts with production port 5432
port = 5433

# start the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-data start

...

replication

...

# create replication user
psql --port 5433 postgres

[local]:5433 postgres=# create user repuser replication;

allow remote access in pg_hba.conf

add line to allow access for the replication user

vim /var/lib/postgresql/daselement-data/pg_hba.conf

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
# IPv4 local connections
host    all             repuser             localhost               trust

# restart the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-munich restart

The primary system is now ready. Let’s move on to the replica system!

Create replica database server

# create the replica Postgres server
# this command pg_basebackup copies the files from the primary database

pg_basebackup -h db-munich -U repuser --checkpoint=fast -D /var/lib/postgresql/daselement-munich-replica/ -R --slot=daselement_munich -C --port 5433

In the replica directory you will find these two files

standby.signal

this file defines that the database that it is a replica

postgresql.auto.conf

contains information to connect to the primary server

Now start the replica server

# start the Postgres server
pg_ctl -D /var/lib/postgresql/daselement-munich-replica

...