Requirements

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

Concept

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.

START STREAMING

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

Create primary database server

...

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

END STREAMING

Monitoring

To monitor the databases you can for example use pgwatch2

Configure primary server

1. inistiate 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 Logical Replication

1. Create a publication on the Publish Server

2. Create a subscription on the Subscriber Server

3. Test the replication

Troubleshooting

initdb: command not found

The initdb is located in the installation directory of Postgres.

Fix for Ubuntu:

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

# now you can run ...
initdb