CODEWITHSUNDEEP
postgresqlpglogicalpg-upgrade
Juvette M
Juvette M

Reputation: 51

Upgrade a Postgres Database Using pg_ugrade Issues with pglogical

I have a Postgres database running on version 11. I want to upgrade to Postgres 14. This database also have logical replication set up using pglogical extension. when I run a compatibility check on both clusters, it fail pointing to pglogical extension not in shared libraires for my new installations. According to Postgres documentations on upgrade, there do not recommend I make any changes to the new installation as it will get duplicated.

From the Postgres documentation:

Install extension shared object files

Many extensions and custom modules, whether from contrib or another source, use shared object files (or DLLs), e.g., pgcrypto.so. If the old cluster used these, shared object files matching the new server binary must be installed in the new cluster, usually via operating system commands. Do not load the schema definitions, e.g., CREATE EXTENSION pgcrypto, because these will be duplicated from the old cluster. If extension updates are available, pg_upgrade will report this and create a script that can be run later to update them.

I do not want to drop pglogical from my Postgres 11 database, because it will delete all the replication sets. I am wondering if the is a work around with is problem without dropping the extension from my current production database running on Postgres 11.

[postgres@prod-host]$ /usr/pgsql-14/bin/pg_upgrade -d /var/lib/pgsql/11/data -D /var/lib/pgsql/14/data -b /usr/pgsql-11/bin -B /usr/pgsql-14/bin --check
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions                                   ok
Checking database user is the install user                  ok
Checking database connection settings                       ok
Checking for prepared transactions                          ok
Checking for system-defined composite types in user tables  ok
Checking for reg* data types in user tables                 ok
Checking for contrib/isn with bigint-passing mismatch       ok
Checking for user-defined encoding conversions              ok
Checking for user-defined postfix operators                 ok
Checking for incompatible polymorphic functions             ok
Checking for tables WITH OIDS                               ok
Checking for invalid "sql_identifier" user columns          ok
Checking for presence of required libraries                 fatal

Your installation references loadable libraries that are missing from the
new installation.  You can add these libraries to the new installation,
or remove the functions using them from the old installation.  A list of
problem libraries is in the file:
    loadable_libraries.txt

Failure, exiting
[postgres@prod-host]$ cat loadable_libraries.txt
could not load library "pglogical": ERROR:  pglogical is not in shared_preload_libraries
In database: postgres
could not load library "$libdir/pglogical": ERROR:  pglogical is not in shared_preload_libraries
In database: postgres

I drop the extension from Postgres 11 and the check was successful, but all my replications sets and the pglogical schema were deleted. I also pg_dumped the pglogical schema, dropped the extension, re-created the extension and restored the pglogical dump schema, but all the information about the replication sets was not in the tables.

pg_dump -d postgres -n pglogical -v > pglogical_schema.sql

psql -d postgres < pglogical_schema.sql

Upvotes: 0

Views: 990

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247270

You will have to install the same version of pglogical in your v14 installation. Look at the following:

ls /usr/pgsql-11/lib/pglogical*

That will show you the shared library that is installed. You need to see the same thing when you run

ls /usr/pgsql-14/lib/pglogical*

Then pg_upgrade will succeed. So what is required is not that you create the extension in the v14 database (that will be done by pg_upgrade), but you have to install the pglogical software in PostgreSQL v14.

Upvotes: 0

Related Questions