search

dimitri / pgcopydb

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

Clone fails with multiple "ERROR: cannot truncate only a partitioned table" #856

Closed skel0 closed 3 months ago

skel0 commented 3 months ago

Hi,

My scenario: Clone from PostgreSQL v15.2 running on CentOS 7.9 to Azure Database for PostgreSQL v16.2 Using pgcopydb 0.16.35.g30d7bcd from docker image.

I am getting errors almost at the end of the clone process for all partitioned tables. Seems that pgcopydb tries to do a truncate on the master table using "truncate only". The partitions have been copied. Just guess that pgcopydb tries to copy again the master tables and realizes that there is data (since the partitions have been copied earlier, seems that all are ok) and tries to truncate the master tables using "truncate only" which fails. All tables showing up in the errors are partitioned tables. The plain tables have been copied correctly.

This was reported also here:

Tried the workaround of adding the master tables in the [exclude-table-data] section of the filter file but didn't work (this workaround worked for me using pgcopydb 0.10 and I could sort this error out for this database with that version):

    [exclude-table-data]
    anonymschema.anonymtable
    ...

In the filter file I also configured to exclude some schemas...

    [exclude-schema]
    schema
    schema_0702
    schema_0703
    schema_0704
    schema_0716
    schema_test 
    schema_old

...that part worked perfectly (indeed those show-up in the command line below):

2024-07-29 18:20:23.792 63 INFO   main.c:136                Running pgcopydb version 0.16.35.g30d7bcd from "/usr/local/bin/pgcopydb"
2024-07-29 18:20:23.794 63 INFO   cli_common.c:1215         [SOURCE] Copying database from "postgres://sourceuser@A.B.C.D:5432/mydatabase?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
2024-07-29 18:20:23.794 63 INFO   cli_common.c:1216         [TARGET] Copying database into "postgres://targetuser@X.Y.Z.T:5432/mydatabase?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60"
2024-07-29 18:20:23.821 63 INFO   copydb.c:105              Using work dir "/tmp/pgcopydb"
2024-07-29 18:20:23.873 63 INFO   snapshot.c:107            Exported snapshot "0000000F-00001525-1" from the source database
2024-07-29 18:20:23.882 68 INFO   cli_clone_follow.c:542    STEP 1: fetch source database tables, indexes, and sequences
2024-07-29 18:20:32.016 68 INFO   copydb_schema.c:761       Fetched information for 4575 tables (including 0 tables split in 0 partitions total), with an estimated total of 841 million tuples and 360 GB on-disk
2024-07-29 18:20:33.387 68 INFO   copydb_schema.c:968       Fetched information for 7620 indexes (supporting 42 constraints)
2024-07-29 18:20:33.400 68 INFO   sequences.c:78            Fetching information for 0 sequences
2024-07-29 18:20:33.408 68 INFO   copydb_schema.c:1122      Fetched information for 4 extensions
2024-07-29 18:20:41.484 68 INFO   copydb_schema.c:1538      Found 0 indexes (supporting 0 constraints) in the target database
2024-07-29 18:20:41.486 68 INFO   cli_clone_follow.c:583    STEP 2: dump the source database schema (pre/post data)
2024-07-29 18:20:41.486 68 INFO   pgcmd.c:475                /usr/bin/pg_dump -Fc --snapshot 0000000F-00001525-1 --section=pre-data --section=post-data --exclude-schema schema --exclude-schema schema_0702 --exclude-schema schema_0703 --exclude-schema schema_0704 --exclude-schema schema_0716 --exclude-schema schema_test --exclude-schema schema_old --exclude-schema schema --file /tmp/pgcopydb/schema/schema.dump 'postgres://sourceuser@A.B.C.D:5432/mydatabase?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60'
2024-07-29 18:20:51.642 68 INFO   cli_clone_follow.c:591    STEP 3: restore the pre-data section to the target database
2024-07-29 18:20:52.204 68 INFO   pgcmd.c:1008               /usr/bin/pg_restore --dbname 'postgres://targetuser@X.Y.Z.T:5432/mydatabase?keepalives=1&keepalives_idle=10&keepalives_interval=10&keepalives_count=60' --section pre-data --jobs 12 --no-acl --exclude-schema schema --exclude-schema schema_0702 --exclude-schema schema_0703 --exclude-schema schema_0704 --exclude-schema schema_0716 --exclude-schema schema_test --exclude-schema schema_old --exclude-schema schema --use-list /tmp/pgcopydb/schema/pre-filtered.list /tmp/pgcopydb/schema/schema.dump
2024-07-29 18:22:03.083 86 INFO   indexes.c:182             STEP 6: starting 12 CREATE INDEX processes
2024-07-29 18:22:03.083 86 INFO   indexes.c:183             STEP 7: constraints are built by the CREATE INDEX processes
2024-07-29 18:22:03.085 87 INFO   vacuum.c:143              STEP 8: starting 12 VACUUM processes
2024-07-29 18:22:03.089 85 INFO   table-data.c:655          STEP 4: starting 12 table-data COPY processes
2024-07-29 18:22:03.166 68 INFO   blobs.c:74                Skipping large objects: none found.
2024-07-29 18:22:03.167 68 INFO   sequences.c:194           STEP 9: reset sequences values
2024-07-29 18:22:03.168 125 INFO   sequences.c:290           Set sequences values on the target database
2024-07-29 18:22:03.194 84 INFO   extensions.c:294          COPY extension "postgis" configuration table public.spatial_ref_sys
2024-07-29 19:07:57.480 118 ERROR  pgsql.c:2331              [TARGET 104945] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.480 118 ERROR  pgsql.c:2331              [TARGET 104945] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.480 118 ERROR  pgsql.c:2331              [TARGET 104945] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.480 118 ERROR  pgsql.c:2342              [TARGET 104945] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.480 118 ERROR  pgsql.c:2350              [TARGET 104945] SQL params:
2024-07-29 19:07:57.480 118 ERROR  table-data.c:784          Failed to copy data for table with oid 62277120 and part number 0, see above for details
2024-07-29 19:07:57.498 116 ERROR  pgsql.c:2331              [TARGET 104950] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.498 116 ERROR  pgsql.c:2331              [TARGET 104950] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.498 116 ERROR  pgsql.c:2331              [TARGET 104950] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.498 116 ERROR  pgsql.c:2342              [TARGET 104950] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.498 116 ERROR  pgsql.c:2350              [TARGET 104950] SQL params:
2024-07-29 19:07:57.498 116 ERROR  table-data.c:784          Failed to copy data for table with oid 62277117 and part number 0, see above for details
2024-07-29 19:07:57.498 115 ERROR  pgsql.c:2331              [TARGET 104949] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.498 115 ERROR  pgsql.c:2331              [TARGET 104949] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.498 115 ERROR  pgsql.c:2331              [TARGET 104949] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.498 115 ERROR  pgsql.c:2342              [TARGET 104949] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.498 115 ERROR  pgsql.c:2350              [TARGET 104949] SQL params:
2024-07-29 19:07:57.498 115 ERROR  table-data.c:784          Failed to copy data for table with oid 62277133 and part number 0, see above for details
2024-07-29 19:07:57.504 123 ERROR  pgsql.c:2331              [TARGET 104955] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.504 123 ERROR  pgsql.c:2331              [TARGET 104955] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.504 123 ERROR  pgsql.c:2331              [TARGET 104955] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.504 123 ERROR  pgsql.c:2342              [TARGET 104955] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.504 123 ERROR  pgsql.c:2350              [TARGET 104955] SQL params:
2024-07-29 19:07:57.504 123 ERROR  table-data.c:784          Failed to copy data for table with oid 62277136 and part number 0, see above for details
2024-07-29 19:07:57.558 117 ERROR  pgsql.c:2331              [TARGET 104947] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.558 117 ERROR  pgsql.c:2331              [TARGET 104947] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.558 117 ERROR  pgsql.c:2331              [TARGET 104947] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.558 117 ERROR  pgsql.c:2342              [TARGET 104947] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.558 117 ERROR  pgsql.c:2350              [TARGET 104947] SQL params:
2024-07-29 19:07:57.558 117 ERROR  table-data.c:784          Failed to copy data for table with oid 62277139 and part number 0, see above for details
2024-07-29 19:07:57.604 120 ERROR  pgsql.c:2331              [TARGET 104952] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.604 120 ERROR  pgsql.c:2331              [TARGET 104952] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.604 120 ERROR  pgsql.c:2331              [TARGET 104952] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.604 120 ERROR  pgsql.c:2342              [TARGET 104952] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.604 120 ERROR  pgsql.c:2350              [TARGET 104952] SQL params:
2024-07-29 19:07:57.604 120 ERROR  table-data.c:784          Failed to copy data for table with oid 62277142 and part number 0, see above for details
2024-07-29 19:07:57.638 121 ERROR  pgsql.c:2331              [TARGET 104954] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.638 121 ERROR  pgsql.c:2331              [TARGET 104954] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.638 121 ERROR  pgsql.c:2331              [TARGET 104954] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.638 121 ERROR  pgsql.c:2342              [TARGET 104954] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.638 121 ERROR  pgsql.c:2350              [TARGET 104954] SQL params:
2024-07-29 19:07:57.638 121 ERROR  table-data.c:784          Failed to copy data for table with oid 62277145 and part number 0, see above for details
2024-07-29 19:07:57.662 118 ERROR  pgsql.c:2331              [TARGET 104945] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2024-07-29 19:07:57.663 118 ERROR  pgsql.c:2331              [TARGET 104945] ERROR:  current transaction is aborted, commands ignored until end of transaction block
2024-07-29 19:07:57.663 118 ERROR  pgsql.c:2342              [TARGET 104945] SQL query: select has_table_privilege($1, $2);
2024-07-29 19:07:57.663 118 ERROR  pgsql.c:2350              [TARGET 104945] SQL params: 'anonymschema.anonymtable', 'TRUNCATE'
2024-07-29 19:07:57.663 118 ERROR  pgsql.c:1364              Failed to query privileges for table "anonymschema.anonymtable"
2024-07-29 19:07:57.663 118 ERROR  table-data.c:784          Failed to copy data for table with oid 62277153 and part number 0, see above for details
2024-07-29 19:07:57.665 122 ERROR  pgsql.c:2331              [TARGET 104944] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.665 122 ERROR  pgsql.c:2331              [TARGET 104944] HINT:  Do not specify the ONLY keyword, or use TRUNCATE ONLY on the partitions directly.
2024-07-29 19:07:57.665 122 ERROR  pgsql.c:2331              [TARGET 104944] ERROR:  cannot truncate only a partitioned table
2024-07-29 19:07:57.665 122 ERROR  pgsql.c:2342              [TARGET 104944] SQL query: TRUNCATE ONLY anonymschema.anonymtable
2024-07-29 19:07:57.665 122 ERROR  pgsql.c:2350              [TARGET 104944] SQL params:
2024-07-29 19:07:57.665 122 ERROR  table-data.c:784          Failed to copy data for table with oid 62277148 and part number 0, see above for details
...
...
...

Any suggestion to solve this?

Update: I am getting same error with other databases which are similar in terms of structure.

Thanks in advance!

marikkan-microsoft commented 3 months ago

Seems to be the same issue as: https://github.com/dimitri/pgcopydb/issues/700

skel0 commented 3 months ago

Hi,

Just one comment. I already tried the workaround, which is to include the header partitioned tables in the [exclude-table-data] section, but still getting the error for some tables that are listed in the mentioned section. Don't know why pgcopydb is not skipping some of the tables mentioned in the [exclude-table-data] section (in my case 12 out of 68 are not excluded and still causing the errors).

Thanks.

dimitri commented 3 months ago

Hi @skel0 ; please try again with a fresh build from the current sources, where I believe your issue might be fixed entirely thanks to today's merge. Also consider adding more information in this issue, allowing to re-open it if more work is needed. Thanks

skel0 commented 3 months ago

Hi @dimitri,

I can confirm that now, using the latest commit, the clone process works like a charm :)

Thanks for your support!

Regards,

JL