search

2ndQuadrant / pglogical

Logical Replication extension for PostgreSQL 15, 14, 13, 12, 11, 10, 9.6, 9.5, 9.4 (Postgres), providing much faster replication than Slony, Bucardo or Londiste, as well as cross-version upgrades.
http://2ndquadrant.com/en/resources/pglogical/
Other
989 stars 152 forks source link

cloudsql migration issue caused by pglogical? #411

Closed AzyCrw4282 closed 1 year ago

AzyCrw4282 commented 1 year ago

I am working on db migration from on-premise DB to cloudsql. I have followed the docs to setup everything as required but I keep getting this error

finished setup replication with errors: [postgres]: error importing schema: failed to restore schema: stderr=pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 2; 3079 20871 EXTENSION pglogical_origin (no owner) pg_restore: error: could not execute query: ERROR: Could not open extension control file: "/.../share/extension/pglogical_origin.control": No such file or directory Command was: CREATE EXTENSION IF NOT EXISTS pglogical_origin WITH SCHEMA pglogical_origin; pg_restore: from TOC entry 3085; 0 0 COMMENT EXTENSION pglogical_origin pg_restore: error: could not execute query: ERROR: extension "pglogical_origin" does not exist Command was: COMMENT ON EXTENSION pglogical_origin IS 'Dummy extension for compatibility when upgrading from Postgres 9.4'; pg_restore: warning: errors ignored on restore: 2 , stdout=; [netbox]: error importing schema: failed to restore schema: stderr=pg_restore: while PROCESSING TOC: pg_restore: from TOC entry 3; 3079 20868 EXTENSION pglogical_origin (no owner) pg_restore: error: could not execute query: ERROR: Could not open extension control file: "/.../share/extension/pglogical_origin.control": No such file or directory Command was: CREATE EXTENSION IF NOT EXISTS pglogical_origin WITH SCHEMA pglogical_origin; pg_restore: from TOC entry 5782; 0 0 COMMENT EXTENSION pglogical_origin pg_restore: error: could not execute query: ERROR: extension "pglogical_origin" does not exist Command was: COMMENT ON EXTENSION pglogical_origin IS 'Dummy extension for compatibility when upgrading from Postgres 9.4'; pg_restore: warning: errors ignored on restore: 2 , stdout=

I am using version 12 on my external and 14 on cloudsql. version mismatch shouldnt be an issue.

It seems to point to this file https://github.com/2ndQuadrant/pglogical/blob/REL2_x_STABLE/pglogical_origin.control

Any idea if this is a bug or issue with my setup (the latter being exactly whats shown on docs here https://cloud.google.com/database-migration/docs/postgres/configure-source-database#on-premise-self-managed-postgresql

eulerto commented 1 year ago

pglogical_origin extension is a dummy extension to support migration from PostgreSQL 9.4. Your current version doesn't need it. If you create this extension in version 14, your setup should succeed (if it is the only problem you have). After migration, you should DROP EXTENSION pglogical_origin.

AzyCrw4282 commented 1 year ago

thanks. it turned out to be due to docker volume issue that i was reusing. so its no longer an issue. Best