pg_upgrde failed : logical replication : alter_subscription_add_log

On Fri, Apr 12, 2024 at 6:18 AM Perumal Raj <perucinci@gmail.com> wrote:

I am trying to upgrade PostgreSQL (RHEL 7) from version 13.7 to 15.6 using pglogical.
My Standby(destination) machine has following rpms,

postgresql13-pglogical-3.7.16-1.el7.x86_64
pglogical_15-2.4.3-1.rhel7.x86_64

And Primary(Source) has ,

postgresql13-pglogical-3.7.16-1.el7.x86_64

pg_upgrade check mode went fine , but it failed while running real mode.

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 13027; 1255 3375648004 FUNCTION alter_subscription_add_log("text", "text", boolean, "regclass", "text"[], "text"[]) postgres
pg_restore: error: could not execute query: ERROR: could not find function "pglogical_alter_subscription_add_log" in file "/usr/pgsql-15/lib/pglogical.so"
Command was: CREATE FUNCTION "pglogical"."alter_subscription_add_log"("sub_name" "text", "log_name" "text", "log_to_file" boolean DEFAULT true, "log_to_table" "regclass" DEFAULT NULL::"regclass", "conflict_type" "text"[] DEFAULT NULL::"text"[], "conflict_resolution" "text"[] DEFAULT NULL::"text"[]) RETURNS boolean
LANGUAGE "c"
AS '$libdir/pglogical', 'pglogical_alter_subscription_add_log';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "pglogical" ADD FUNCTION "pglogical"."alter_subscription_add_log"("sub_name" "text", "log_name" "text", "log_to_file" boolean, "log_to_table" "regclass", "conflict_type" "text"[], "conflict_resolution" "text"[]);

Am I missing any packages?

We don't maintain pglogical so difficult to answer but looking at the
error (ERROR: could not find function
"pglogical_alter_subscription_add_log" in file
"/usr/pgsql-15/lib/pglogical.so"), it seems that the required function
is not present in pglogical.so. It is possible that the arguments
would have changed in newer version of pglogical or something like
that. You need to check with the maintainers of pglogical.

--
With Regards,
Amit Kapila.

Note - Please keep pgsql-hackers in CC while responding.

On Fri, Apr 12, 2024 at 10:44 AM Perumal Raj <perucinci@gmail.com> wrote:

Thanks Amit for the update,

Documentation says : https://www.postgresql.org/docs/15/upgrading.html

19.6.3. Upgrading Data via Replication

It is also possible to use logical replication methods to create a standby
server with the updated version of PostgreSQL. This is possible because
logical replication supports replication between different major versions
of PostgreSQL. The standby can be on the same computer or a different
computer. Once it has synced up with the primary server (running the older
version of PostgreSQL), you can switch primaries and make the standby the
primary and shut down the older database instance. Such a switch-over
results in only several seconds of downtime for an upgrade.

This method of upgrading can be performed using the built-in logical
replication facilities as well as using external logical replication
systems such as pglogical, Slony, Londiste, and Bucardo.

What is "built-in logical replication" ?

See docs at [1]https://www.postgresql.org/docs/devel/logical-replication.html -- With Regards, Amit Kapila.

[1]: https://www.postgresql.org/docs/devel/logical-replication.html -- With Regards, Amit Kapila
--
With Regards,
Amit Kapila