Subject: Re: pg_upgrde failed : logical replication : alter_subscription_add_log

From: Amit Kapila <[email-masked]>
Date: Fri, 12 Apr 2024 08:38:38 +0530

On Fri, Apr 12, 2024 at 6:18 AM Perumal Raj <[email-masked]> 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.

---

From: Amit Kapila <[email-masked]>
Date: Fri, 12 Apr 2024 15:55:57 +0530

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

On Fri, Apr 12, 2024 at 10:44 AM Perumal Raj <[email-masked]> 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].

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