Using synchronize_structure fails when replicating from 9.4 to 9.5

[simaofreitas]

Hi

I'm trying to replicate from a 9.4 server to a 9.5 server. I created the nodes on both provider & subscriber and also added all tables from the public schema to the default replication set.

When creating the subscription with:

LECT pglogical.create_subscription(
    subscription_name := 'subscriptionname',
    provider_dsn := 'host=providerhost port=5432 dbname=dbname user=replicator password=teste',
    synchronize_structure := true
);

The command fails because it tries to create the pglogical_origin extension in the 9.5 server (that is included in the generated dump).

This extension is not available in 9.5 servers, making the synchronize_structure non usable when creating the subscription.

Any clues or workarounds?

Thanks

[PJMODOS]

Yes, I know about this issue, the only workaround I know so far is to use schema only pg_dump manually and skip the synchronize_structure.

I think we should revert the change that requires pglogical_origin to be always installed since it's really only needed on 9.4 subscriber and it breaks the online upgrade scenario for 9.4 to 9.5.

[mafonso]

I just bumped into this very same issue while doing a POC to migrate from 9.4 to 9.5.

ERROR,58P01,"could not open extension control file ""/usr/pgsql-9.5/share/extension/pglogical_origin.control"": No such file or directory",,,,,,"CREATE EXTENSION IF NOT EXISTS pglogical_origin WITH SCHEMA pglogical_origin;

I assumed that was pglogical not being very clever with the requirements between versions and created the schema manually to workaround it.

But still failling further ahead

ERROR,XX000,"could not execute command ""/usr/pgsql-9.5/bin/pg_restore --section=""pre-data"" --exit-on-error -1 -d ""host=x.y.x.t port=5432 dbname=testdb"" ""/tmp/pglogical-25988.dump""""",,,,,,,,,"pglogical apply 17027:1763399739

I think that this last step is part of synchronize_data so this last bit might not be directly related with this issue, but I'm sharing the additional info.

[ringerc]

Or just provide an empty dummy pglogical_origin that we install on 9.5​

[mafonso]

@ringerc you mean something like adding a .control file with empty module_pathname? Something like this?

comment = 'PostgreSQL Logical Replication Origin Tracking Dummy Emulation for 9.5'
default_version = '1.0.0'
module_pathname = ''
relocatable = false
schema = pglogical_origin

and with a simple select on the pglogical_origin--1.0.0.sql

Or is there an actual dummy implementation of such module? I could not find one...

[ringerc]

There isn't a dummy implementation now, but yes, that's what I'm thinking. Install a dummy module if we're on 9.5 to make pg_restore happy.

Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services

[ringerc]

Empty module_pathname wouldn't do it. We'd have a different control file we'd install for 9.5 servers that clearly said it was a dummy, and an empty sql file to run.

[mafonso]

I kinda made it work with the empty module_pathname and then an sql file with just select 1; It allowed me to create the extension manually so that pgrestore skips over it. Maybe its not the cleanest way, but it works.

"CREATE EXTENSION",2016-08-23 04:23:43 CDT,6/1425,2538,NOTICE,42710,"extension ""pglogical_origin"" already exists, skipping",,,,,,,,,"pg_restore"

If I let pg_restore run it it tries to execute create extension pglogical_origin with schema ... ,failing. I need to create pglogical extension manually anyway to be able to run the create_node and create_subscription commands, so I don't mind creating one more extension, as it is a one-off.

I'm only stuck on the data sync for now. It does not error but does not get any data across.

2016-08-23 04:23:43.889 CDT,,,31828,,57bc161f.7c54,4,,2016-08-23 04:23:43 CDT,5/0,0,INFO,00000,"synchronizing data",,,,,,,,,"pglogical apply 23141:1763399739"
2016-08-23 04:23:43.928 CDT,,,31828,,57bc161f.7c54,5,,2016-08-23 04:23:43 CDT,5/0,0,INFO,00000,"synchronizing constraints",,,,,,,,,"pglogical apply 23141:1763399739"
2016-08-23 04:23:44.282 CDT,,,31828,,57bc161f.7c54,6,,2016-08-23 04:23:43 CDT,5/0,0,INFO,00000,"finished synchronization of subscriber subscription1, ready to enter normal replication",,,,,,,,,"pglogical apply 23141:1763399739"

but that is a different topic.

[ringerc]

I think this is what @PJMODOS meant to fix with https://github.com/2ndQuadrant/pglogical/commit/5edcc89d5c8e115c40b5e7141b682d531e4a6fea, which in turn resulted in #45 . He's doing a different fix.

[PJMODOS]

should be fixed in 1.2.1