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 facilitiesUse a Streaming Replication for Multi-Site setups.

Requirements

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

...

...

Create primary database server

To monitor the databases you can for example use pgwatch2

Setup VMs

install Postgres

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

...

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_publication

...

change postgres.conf to enable networking

# edit postgresql.conf
vi vim /var/lib/postgresql/daselement-munich_publication/postgresqlpostgres.conf

# edit line for wal_level to logical
wal_level = logical

Now start the database cluster

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

# and change the port to 5433 to avoid conflicts with any default instances that are running on port 5432
port = 5433

# start the databasePostgres clusterserver
pg_ctl -D /var/lib/postgresql/daselement-munich_publication start

Create

...

a user accounts

# postgres accessis the databasedefault viapostgres psqldatabase
psql --port=5432 postgres

# create superuser for IT/admins and pipeline TDs
CREATE ROLE postgres LOGIN password 'postgres';
ALTER USER root CREATEDB;
ALTER USER root CREATEROLE;
ALTER USER root SUPERUSER;
ALTER USER root REPLICATION; 5433 postgres

# create user that is allowed to create new databases and read/write information
CREATE ROLE user_daselementdbuser LOGIN password 'password';
ALTER USER rootdbuser CREATEDB;

# create replication user that is used by the other facilites
CREATE ROLE user_replication WITH REPLICATION LOGIN password 'password';

# exit the database from psql
\q

Move to a supervisor/lead artists workstation.

Now create a new library inside the software. Use the user_daselement to connect to the databas.

...

Hurray, we have the main library for facility Munich running.

The next step is create a publication for this newly create database. Back to the Postgres server.

# log in to the newly created database
psql --port=5432 de_munich_vfxelements

We have to create a publication for this library/database that all the other facilities can later subscribe to.

# give the user_replication the correct priviliges to create a publication
GRANT ALL PRIVILEGES ON DATABASE de_munich_vfxelements TO user_replication;
GRANT ALLPRIVILEGES ON ALL TABLES IN SCHEMA public to user_replication;

# create publication for all tables
create publication muc_pub for all tables;
# > the expected output is: CREATE PUBLICATION

# to show all publication use this command
\dRp

Configure subscriber server

1. Create subscriber database cluster

2. copy database Schema from the primary database

3. create subscription to primary database

1 create database

Now let’s setup a database in a different facility

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

# start the database cluster
[local]:5433 postgres=# create user repuser replication;

Create a replication user

Allow remote access in pg_hba.conf

add line to allow access for the replication user

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

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

# restart the Postgres server because of changes in the config files
pg_ctl -D /var/lib/postgresql/daselement-munich_subscription start

2 copy schema

...

 restart

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

Create replica database server

Configure replication server

# create the databasereplica Postgres fromserver
primary# databasethis psql --port=5460 postgres

# create an empty database where the information will be replicated to
create database rep_de_munich_vfxelements

# expected ouptut: CREATE DATABASE

Now copy over all the information from the primary to the subscriber. Execute this command on the primary database server.

# use pg_dump to copy the data
pg_dump -s de_munich_vfxelements -p 5432 | psql -h 192.168.178.50 -p 5432 rep_de_munich_vfxelements

3 create subscription

The last step is now to subscribe to the primary facility publication.

dbname = name of the database in the munich facility (muc_pub)

host = ip-of-publisher-postgres-server (e.g. the munich postgres server)

user = must be a superuser or replication role

create subscription muc_subscription connection 'dbname=de_munich_vfxelements host=192.168.178.50 user=user_replication port=5433' publication muc_pub;

Configure Logical Replication

1. Create a publication on the Publish Server

2. Create a subscription on the Subscriber Server

3. Test the replication

Troubleshooting

userful command

...

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:

Now start the replica server:

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

Any changes made in the primary database get synced/streamed to the replication server(s). If the replication server is down it will add the changes once it’s back online!

Configure library

In the other facility copy and import the library (.lib) from main facility:

1. update the library root

2. update the database information

3. lock the library (Lock Library option) to prevent editing and ingesting of new elements!

An option is to setup the main facility to automatically sync the Proxy Files to the other facilities when an element is ingested. This can be done with an additional transcoding task and a custom command task.

# Example:
your-sync-tool <paths.proxy.directory> /path/to/other/facility
your-sync-tool <paths.filmstrip.directory> /path/to/other/facility
your-sync-tool <paths.thumbnail.directory> /path/to/other/facility

Advanced Setup

If you want to take it a step further it’s a good idea to look into these topics:

• high availability

• load balancing

• connection pooling

• automatic fail-over
  

Here is a great video tutorial using PGPool-II to deal with these topics.

Monitoring

To monitor the databases you can for example use pgwatch2

Troubleshooting

psql - commands

Can not start Postgres Database

FATAL:  could not create lock file "/var/run/postgresql/.s.PGSQL.5434.lock": Permission denied

Solution is to fix the permissions on the folder. For example like this:

sudo chmod a+w+r /var/lib/postgresql

initdb: command not found

The initdb is located in the installation directory of Postgres.

...

# 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

pg_ctl: command not found

The pg_ctl is located in the installation directory of Postgres.

...

# 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:

# access the database
psql --port=5432 postgres

# create user in the database
CREATE ROLE postgres LOGIN password 'postgres';

Permission denied for table

The subscription server outputs some errror message like this:
could not start initial contents copy for table "public.feature": ERROR: permission denied for table feature

Make sure that the User has the correct permission on the primary database server.
Can be fixed with 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

create a replication user

# 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

...