...

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

START STREAMING

Create primary database server

...

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 any default instances that are running on port 5432
port = 5433

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

...

Create a user

...

accounts

# postgres is the default postgres database
psql --port 5433 postgres

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

create a replication user

# create replication user
# postgres
is the default postgres database
psql --port 5433 postgres

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

Create a replication user

allow remote access in pg_hba.conf

...

To monitor the databases you can for example use pgwatch2

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

Edit the config to set the primary database cluster to the Logical Replication mode

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

# edit line for wal_level to logical
wal_level = logical

Now start the database cluster

# start the database cluster
pg_ctl -D /var/lib/postgresql/daselement-munich_publication start

Create Postgres Users - make sure that the Users exist in all the other databases as well

# access the database via psql
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;

# create user that is allowed to create new databases and read/write information
CREATE ROLE user_daselement LOGIN password 'password';
ALTER USER root 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
pg_ctl -D /var/lib/postgresql/daselement-munich_subscription start

2 copy schema

Postgres replication does not support the Postgres Data Definition Language (DLL) commands.
For us this means that we have to copy over the Database Schema from the Primary Database to the subscriber server. This step only needs to be done once.

# create database from primary database
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

END logical replication

Troubleshooting

Here are some helpful commands

pg_lsclusters - show all database clusters on the current machine

...

Troubleshooting

psql - commands

...

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