Errors and Solutions Trying to use pglogical to Synchronise Different Versions of PostgreSQL

I was trying to synchronise a PostgreSQL database from an existing (“olddb”) server running v9.4.26 (Debian release “Jessie”) sourced from https://apt-archive.postgresql.org/ to a new server (“newdb”) running v15.3 (Debian release “Bookworm”) using the pglogical plugin.

On the way I encountered errors. Here are some of them.

New Database Log /var/log/postgresql/postgresql-15-main.log

Permission Denied for Relation Tables

2023-06-17 09:57:01.801 AEST [10933] [unknown]@mytestdb ERROR:  could not get table list: ERROR:  permission denied for relation tables

You must allow the replication user access to the “pglogical” schema and tables:

GRANT ALL ON SCHEMA pglogical TO myreplicator;
GRANT ALL ON ALL TABLES IN SCHEMA pglogical TO myreplicator;
GRANT ALL ON ALL SEQUENCES IN SCHEMA pglogical TO myreplicator;

Permission Denied for Function

2023-06-17 14:14:43.205 AEST [11970] [unknown]@mytestdb ERROR:  BEGIN on target node failed: ERROR:  permission denied for function pg_replication_origin_session_setup

You must give the replicator user permission to execute this function:

GRANT EXECUTE ON FUNCTION pg_catalog.pg_replication_origin_session_setup TO myreplicator;

Permission Denied to Set Parameter

2023-06-17 14:24:47.245 AEST [12024] [unknown]@mytestdb ERROR:  BEGIN on target node failed: ERROR:  permission denied to set parameter "session_replication_role"

Apparently only SUPERUSER can change this parameter. So your replicator user on the new database needs this permission:

ALTER USER myreplicator WITH SUPERUSER;

Add a Key to Custom Keyring File with GPG

I wanted to add a PGP public key to a custom keyring, specifically /var/cache/lxc/debian/archive-key.gpg.

The command line to do this to import from STDIN (e.g. cutting and pasting followed by CTRL-D to indicate end-of-file):

sudo gpg --import --keyring /var/cache/lxc/debian/archive-key.gpg --no-default-keyring -

The –keyring filename specifies the keyring file.

But if the –no-default-keyring option is not provided then the imported key will go into the default keyring, not the keyring specified on the command line.