search

dimitri / pgcopydb

Copy a Postgres database to a target Postgres server (pg_dump | pg_restore on steroids)
Other
1.08k stars 72 forks source link

Can't clone extension installed in non default schema #694

Closed plvnv closed 2 months ago

plvnv commented 4 months ago

pgcopy version Running pgcopydb version 0.15.30.gcb55ea1

source db PostgresPro (standard) 11.22.1 target db PostgresPro (standard) 13.13.1

Can't clone extensions and objects using this extensions installed in non default schema

On source database extensions install in separate schema "extensions" 

\dx
                                                       List of installed extensions
        Name        | Version |   Schema   |                                          Description                                          
--------------------+---------+------------+-----------------------------------------------------------------------------------------------
 hostname           | 1.0.0   | extensions | Get the server host name
 oracle_fdw         | 1.2     | extensions | foreign data wrapper for Oracle access
 orafce             | 4.6     | extensions | Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS
 pg_buffercache     | 1.3     | extensions | examine the shared buffer cache
 pg_repack          | 1.4.8   | extensions | Reorganize tables in PostgreSQL databases with minimal locks
 pg_sortstats       | 0.0.1   | extensions | An extension collecting statistics about sorts
 pg_stat_kcache     | 2.2.0   | extensions | Kernel statistics gathering
 pg_stat_statements | 1.6     | extensions | track execution statistics of all SQL statements executed
 pg_trgm            | 1.4     | extensions | text similarity measurement and index searching based on trigrams
 pgfincore          | 1.2     | public     | examine and manage the os buffer cache
 pgstattuple        | 1.5     | extensions | show tuple-level statistics
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
 postgres_fdw       | 1.0     | extensions | foreign-data wrapper for remote PostgreSQL servers
 tablefunc          | 1.0     | extensions | functions that manipulate whole tables, including crosstab
 uuid-ossp          | 1.1     | extensions | generate universally unique identifiers (UUIDs)

clone command pgcopydb clone --follow --restart --table-jobs 4 --index-jobs 4 --plugin wal2json --wal2json-numeric-as-string

Error log

2024-03-04 16:35:28.008 38106 INFO   main.c:138                Running pgcopydb version 0.15.30.gcb55ea1 from "/opt/pgpro/std-13/bin/pgcopydb"
2024-03-04 16:35:28.010 38106 INFO   cli_common.c:1347         [SOURCE] Copying database from "postgres://localhost:5432/archive?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
2024-03-04 16:35:28.010 38106 INFO   cli_common.c:1348         [TARGET] Copying database into "postgres://localhost:6432/archive?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
2024-03-04 16:35:28.019 38106 INFO   copydb.c:105              Using work dir "/u01/tmp/pgcopydb_tmp/pgcopydb"
2024-03-04 16:35:28.752 38106 INFO   cli_clone_follow.c:221    Clean-up replication setup, per --restart
2024-03-04 16:35:28.758 38106 INFO   pgsql.c:5026              Dropping replication slot "pgcopydb"
2024-03-04 16:35:28.760 38106 INFO   ld_stream.c:2359          Removing schema pgcopydb and its objects
2024-03-04 16:35:28.760 38106 WARN   pgsql.c:860               NOTICE:  schema "pgcopydb" does not exist, skipping
2024-03-04 16:35:28.760 38106 INFO   pgsql.c:4728              Dropping replication origin "pgcopydb"
2024-03-04 16:35:28.783 38106 INFO   pgsql.c:3790              Created logical replication slot "pgcopydb" with plugin "wal2json" at 3117/37EC8B50 and exported snapshot 0000004E-00240FBD-1
2024-03-04 16:35:28.794 38106 INFO   ld_stream.c:2462          Created logical replication origin "pgcopydb" at LSN 3117/37EC8B50
2024-03-04 16:35:28.795 38134 INFO   cli_clone_follow.c:519    STEP 1: fetch source database tables, indexes, and sequences
2024-03-04 16:35:28.803 38136 INFO   ld_stream.c:639           Resuming streaming at LSN 3117/37EC8B50 from replication slot "pgcopydb"
2024-03-04 16:35:28.804 38138 INFO   ld_apply.c:354            Waiting until the pgcopydb sentinel apply is enabled
2024-03-04 16:35:28.819 38136 INFO   pgsql.c:4514              Reported write_lsn 3117/37EC8B50, flush_lsn 3117/37EC8B50, replay_lsn 0/0
2024-03-04 16:35:30.007 38134 INFO   copydb_schema.c:672       Fetched information for 503 tables (including 0 tables split in 0 partitions total), with an estimated total of 1436 million tuples and 419 GB on-disk
2024-03-04 16:35:30.149 38134 INFO   copydb_schema.c:838       Fetched information for 963 indexes (supporting 521 constraints)
2024-03-04 16:35:30.166 38134 INFO   sequences.c:65            Fetching information for 549 sequences
2024-03-04 16:35:30.437 38134 INFO   copydb_schema.c:943       Fetched information for 15 extensions
2024-03-04 16:35:30.492 38134 INFO   copydb_schema.c:1321      Found 0 indexes (supporting 0 constraints) in the target database
2024-03-04 16:35:30.500 38134 INFO   cli_clone_follow.c:560    STEP 2: dump the source database schema (pre/post data)
2024-03-04 16:35:30.501 38134 INFO   pgcmd.c:522                /opt/pgpro/std-13/bin/pg_dump -Fc --snapshot 0000004E-00240FBD-1 --section pre-data --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --file /u01/tmp/pgcopydb_tmp/pgcopydb/schema/pre.dump 'postgres://localhost:5432/archive?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60'
2024-03-04 16:35:32.398 38137 INFO   ld_transform.c:1053       Transformed 80 JSON messages into SQL file "/u01/tmp/pgcopydb_follow/pgcopydb/000000020000311700000037.sql"
2024-03-04 16:35:37.327 38134 INFO   pgcmd.c:522                /opt/pgpro/std-13/bin/pg_dump -Fc --snapshot 0000004E-00240FBD-1 --section post-data --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --exclude-schema extensions --file /u01/tmp/pgcopydb_tmp/pgcopydb/schema/post.dump 'postgres://localhost:5432/archive?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60'
2024-03-04 16:35:38.967 38136 INFO   pgsql.c:4514              Reported write_lsn 3117/39438BF0, flush_lsn 3117/37EC8B50, replay_lsn 0/0
2024-03-04 16:35:43.302 38134 INFO   cli_clone_follow.c:570    STEP 3: restore the pre-data section to the target database
2024-03-04 16:35:43.582 38134 INFO   pgcmd.c:998                /opt/pgpro/std-13/bin/pg_restore --dbname 'postgres://localhost:6432/archive?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60' --jobs 4 --use-list /u01/tmp/pgcopydb_tmp/pgcopydb/schema/pre-filtered.list /u01/tmp/pgcopydb_tmp/pgcopydb/schema/pre.dump
2024-03-04 16:35:43.669 38134 ERROR  string_utils.c:669        pg_restore: while PROCESSING TOC:
2024-03-04 16:35:43.669 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 12; 3079 63736385 EXTENSION orafce (no owner)
2024-03-04 16:35:43.669 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.669 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS orafce WITH SCHEMA extensions;
2024-03-04 16:35:43.670 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16026; 0 0 COMMENT EXTENSION orafce 
2024-03-04 16:35:43.670 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "orafce" does not exist
2024-03-04 16:35:43.670 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION orafce IS 'Functions and operators that emulate a subset of functions and packages from the Oracle RDBMS';
2024-03-04 16:35:43.675 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 9; 3079 72340308 EXTENSION pg_repack (no owner)
2024-03-04 16:35:43.675 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.675 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pg_repack WITH SCHEMA extensions;
2024-03-04 16:35:43.675 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16029; 0 0 COMMENT EXTENSION pg_repack 
2024-03-04 16:35:43.675 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pg_repack" does not exist
2024-03-04 16:35:43.675 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pg_repack IS 'Reorganize tables in PostgreSQL databases with minimal locks';
2024-03-04 16:35:43.679 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 8; 3079 72340306 EXTENSION hostname (no owner)
2024-03-04 16:35:43.679 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.679 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS hostname WITH SCHEMA extensions;
2024-03-04 16:35:43.679 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16030; 0 0 COMMENT EXTENSION hostname 
2024-03-04 16:35:43.679 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "hostname" does not exist
2024-03-04 16:35:43.679 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION hostname IS 'Get the server host name';
2024-03-04 16:35:43.682 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 2; 3079 72340262 EXTENSION oracle_fdw (no owner)
2024-03-04 16:35:43.682 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.682 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS oracle_fdw WITH SCHEMA extensions;
2024-03-04 16:35:43.683 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16031; 0 0 COMMENT EXTENSION oracle_fdw 
2024-03-04 16:35:43.683 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "oracle_fdw" does not exist
2024-03-04 16:35:43.683 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION oracle_fdw IS 'foreign data wrapper for Oracle access';
2024-03-04 16:35:43.686 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 11; 3079 43106067 EXTENSION pg_buffercache (no owner)
2024-03-04 16:35:43.686 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.686 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pg_buffercache WITH SCHEMA extensions;
2024-03-04 16:35:43.687 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16032; 0 0 COMMENT EXTENSION pg_buffercache 
2024-03-04 16:35:43.687 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pg_buffercache" does not exist
2024-03-04 16:35:43.687 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pg_buffercache IS 'examine the shared buffer cache';
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 3; 3079 72340269 EXTENSION pg_sortstats (no owner)
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        pg_restore: 
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pg_sortstats WITH SCHEMA extensions;
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16033; 0 0 COMMENT EXTENSION pg_sortstats 
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pg_sortstats" does not exist
2024-03-04 16:35:43.688 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pg_sortstats IS 'An extension collecting statistics about sorts';
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 4; 3079 72340276 EXTENSION pg_stat_statements (no owner)
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA extensions;
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16034; 0 0 COMMENT EXTENSION pg_stat_statements 
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pg_stat_statements" does not exist
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pg_stat_statements IS 'track execution statistics of all SQL statements executed';
2024-03-04 16:35:43.690 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 5; 3079 72340283 EXTENSION pg_stat_kcache (no owner)
2024-03-04 16:35:43.691 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.691 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pg_stat_kcache WITH SCHEMA extensions;
2024-03-04 16:35:43.691 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16035; 0 0 COMMENT EXTENSION pg_stat_kcache 
2024-03-04 16:35:43.691 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pg_stat_kcache" does not exist
2024-03-04 16:35:43.691 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pg_stat_kcache IS 'Kernel statistics gathering';
2024-03-04 16:35:43.694 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 15; 3079 43106158 EXTENSION pg_trgm (no owner)
2024-03-04 16:35:43.694 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.694 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA extensions;
2024-03-04 16:35:43.694 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16036; 0 0 COMMENT EXTENSION pg_trgm 
2024-03-04 16:35:43.694 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pg_trgm" does not exist
2024-03-04 16:35:43.694 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';
2024-03-04 16:35:43.719 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 6; 3079 72340296 EXTENSION pgstattuple (no owner)
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS pgstattuple WITH SCHEMA extensions;
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 16038; 0 0 COMMENT EXTENSION pgstattuple 
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "pgstattuple" does not exist
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION pgstattuple IS 'show tuple-level statistics';
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 10; 3079 25017808 EXTENSION postgres_fdw (no owner)
2024-03-04 16:35:43.720 38134 ERROR  string_utils.c:669        pg_restore: error: 
2024-03-04 16:35:43.721 38134 ERROR  string_utils.c:669        could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.721 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS postgres_fdw WITH SCHEMA extensions;
2024-03-04 16:35:43.721 38134 ERROR  string_utils.c:669        pg_restore: 
2024-03-04 16:35:43.721 38134 ERROR  string_utils.c:669        from TOC entry 16039; 0 0 COMMENT EXTENSION postgres_fdw 
2024-03-04 16:35:43.721 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "postgres_fdw" does not exist
2024-03-04 16:35:43.721 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION postgres_fdw IS 'foreign-data wrapper for remote PostgreSQL servers';
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 7; 3079 21906414 EXTENSION tablefunc (no owner)
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        pg_restore: error: 
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS tablefunc WITH SCHEMA extensions;
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        pg_restore: 
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        from TOC entry 16040; 0 0 COMMENT EXTENSION tablefunc 
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "tablefunc" does not exist
2024-03-04 16:35:43.722 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION tablefunc IS 'functions that manipulate whole tables, including crosstab';
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 14; 3079 43106109 EXTENSION uuid-ossp (no owner)
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        pg_restore: error: 
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        could not execute query: ERROR:  schema "extensions" does not exist
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        Command was: CREATE EXTENSION IF NOT EXISTS "uuid-ossp" WITH SCHEMA extensions;
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        pg_restore: 
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        from TOC entry 16041; 0 0 COMMENT EXTENSION "uuid-ossp" 
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  extension "uuid-ossp" does not exist
2024-03-04 16:35:43.723 38134 ERROR  string_utils.c:669        Command was: COMMENT ON EXTENSION "uuid-ossp" IS 'generate universally unique identifiers (UUIDs)';
2024-03-04 16:35:49.584 38136 INFO   pgsql.c:4514              Reported write_lsn 3117/39694F60, flush_lsn 3117/39438BF0, replay_lsn 0/0
2024-03-04 16:35:53.566 38134 ERROR  string_utils.c:669        pg_restore: from TOC entry 1994; 1259 63737111 VIEW ddv_deleted_documents_view archive
2024-03-04 16:35:53.566 38134 ERROR  string_utils.c:669        pg_restore: error: could not execute query: ERROR:  schema "oracle" does not exist
2024-03-04 16:35:53.566 38134 ERROR  string_utils.c:669        LINE 173:             WHEN (reg.dsdt_reg_date = (oracle.to_date('0001-...
2024-03-04 16:35:53.566 38134 ERROR  string_utils.c:669                                                         ^
2024-03-04 16:35:53.566 38134 ERROR  string_utils.c:669        Command was: CREATE VIEW archive.ddv_deleted_documents_view AS
....
AbhinavKoul commented 3 months ago

+1 - Similar Issue

OnkV commented 3 months ago

+1

dimitri commented 3 months ago

Thanks for reporting the issue and confirming it; will have a look as soon as possible. If someone wants to see about fixing the issue in a Pull Request, please consider doing so, I will spend the necessary time to review and help get to a merge.

AbhinavKoul commented 3 months ago

Quick look on our end points that the issue can be around these lines https://github.com/dimitri/pgcopydb/blob/v0.14/src/bin/pgcopydb/pgcmd.c/#L423-L447

kbarber commented 3 months ago

Hey friends,

This one may help with fixing clone: https://github.com/dimitri/pgcopydb/pull/717

This one may help with fixing copy extensions if we can work out the schemas thing: https://github.com/dimitri/pgcopydb/pull/711

Review & input welcome.

ken.

kbarber commented 3 months ago

I have these ready for review. Fix for clone: https://github.com/dimitri/pgcopydb/pull/717 and fix for copy extensions: https://github.com/dimitri/pgcopydb/pull/711.

kbarber commented 2 months ago

@plvnv we've shipped the fix for pgcopydb clone as described here: https://github.com/dimitri/pgcopydb/pull/717 if you build pgcopydb from main or wait for the 0.16 package, this should work better now.

plvnv commented 2 months ago

good news thanks, I'll check it soon and give you feedback

dimitri commented 2 months ago

Closing this issue for triage. The fix is already available in source code and building from source is covered in the docs.