Обсуждение: [MASSMAIL]How to interpret 'depends on' errors in pg_restore?
Hello everyone,
I created a database dump in postgres 'custom' format using: pg_dump -d origin --data-only -Fc > file.dump
Then i did a pg_restore -d target --verbose -Fc file.dump and saw in the output this:
5145 0 730750 TABLE subpartitions backends_y2024w03 userA
; depends on: 237
.... and so on ...
Nothing was restored. The tables mentioned in the output do all exist - but in a different database, thus the "internal id's" - perhaps thats what "depends on" refers to - are in fact different but the id's should not matter, as the table names are important and they all exist.
How to interpret the "depends on" errors which lead to nothing beeing imported? and is there a way to tell pg_restore to skip those depends on checks?
When i created a sql dump with inserts, everything worked but these dumps are not that efficient.
Best regards,
Christian
Fire Emerald <fire.github@gmail.com> writes: > Then i did a pg_restore -d target --verbose -Fc file.dump and saw in the > output this: > 5145 0 730750 TABLE subpartitions backends_y2024w03 userA > ; depends on: 237 > .... and so on ... That is not an error, it's just verbose display of one of the items in the dump. > Nothing was restored. You would need to show us the actual errors. (Suggestion: leave off --verbose, it's just clutter.) A guess though is that the import failed because of foreign key constraints. --data-only mode is not good at ordering the table loads to ensure that FK constraints are satisfied on-the-fly. regards, tom lane
Am 28. März 2024 15:00:06 schrieb Tom Lane <tgl@sss.pgh.pa.us>:
Fire Emerald <fire.github@gmail.com> writes:Then i did a pg_restore -d target --verbose -Fc file.dump and saw in theoutput this:5145 0 730750 TABLE subpartitions backends_y2024w03 userA; depends on: 237.... and so on ...That is not an error, it's just verbose display of one of the itemsin the dump.
Well, I know it's not an error, but it's everything i got. There was no error shown. The command completed, but without anything imported.
Nothing was restored.You would need to show us the actual errors. (Suggestion:leave off --verbose, it's just clutter.) A guess though isthat the import failed because of foreign key constraints.--data-only mode is not good at ordering the table loads toensure that FK constraints are satisfied on-the-fly.regards, tom lane
As i said, the same import but with INSERT INTOs worked without any issues. So no, there are no FK constraints failing.
But the target and source table had partitioned tables attached, using ATTACH PARTITION.
The schema was like:
db1 schema1 public table1 (links to the listed below)
db1 schema1 subpartitions backends_y2024w03
db1 schema1 subpartitions backends_y2024w04
db1 schema1 subpartitions backends_y2024w05
The partitioning must be the problem somehow.
Fire Emerald <fire.github@gmail.com> writes: > The partitioning must be the problem somehow. [ shrug... ] You're still not providing any details that would let somebody else reproduce or diagnose the problem. https://wiki.postgresql.org/wiki/Guide_to_reporting_problems regards, tom lane
On 3/28/24 08:57, Fire Emerald wrote: > Am 28. März 2024 15:00:06 schrieb Tom Lane <tgl@sss.pgh.pa.us>: > >> Fire Emerald <fire.github@gmail.com> writes: >>> Then i did a pg_restore -d target --verbose -Fc file.dump and saw in the >>> output this: >> >>> 5145 0 730750 TABLE subpartitions backends_y2024w03 userA >>> ; depends on: 237 >>> .... and so on ... >> >> That is not an error, it's just verbose display of one of the items >> in the dump. > > Well, I know it's not an error, but it's everything i got. There was no > error shown. The command completed, but without anything imported. Look in the Postgres log to see if there is more information. -- Adrian Klaver adrian.klaver@aklaver.com
I didn't used pg_dump/restore until today and finally found my mistake which lead to the "problem" described below.
The output "depends on" comes from the -l (l as Lima) flag, what i wanted was the -1 (number one) flag, which stands for single transaction in pg_restore. As -l does not execute anything, nothing was logged in the postgres server log and none error was shown anywhere.
Both chars looked so identical in my editors/shells that i thought i used -1, in fact using -l.
It's always the tiny obvious thing, which we do not see.
Best regards,
Chris
Am 28. März 2024 16:57:04 schrieb Fire Emerald <fire.github@gmail.com>:
Am 28. März 2024 15:00:06 schrieb Tom Lane <tgl@sss.pgh.pa.us>:Fire Emerald <fire.github@gmail.com> writes:Then i did a pg_restore -d target --verbose -Fc file.dump and saw in theoutput this:5145 0 730750 TABLE subpartitions backends_y2024w03 userA; depends on: 237.... and so on ...That is not an error, it's just verbose display of one of the itemsin the dump.Well, I know it's not an error, but it's everything i got. There was no error shown. The command completed, but without anything imported.Nothing was restored.You would need to show us the actual errors. (Suggestion:leave off --verbose, it's just clutter.) A guess though isthat the import failed because of foreign key constraints.--data-only mode is not good at ordering the table loads toensure that FK constraints are satisfied on-the-fly.regards, tom laneAs i said, the same import but with INSERT INTOs worked without any issues. So no, there are no FK constraints failing.But the target and source table had partitioned tables attached, using ATTACH PARTITION.The schema was like:db1 schema1 public table1 (links to the listed below)db1 schema1 subpartitions backends_y2024w03db1 schema1 subpartitions backends_y2024w04db1 schema1 subpartitions backends_y2024w05The partitioning must be the problem somehow.
On 5/2/24 02:20, Fire Emerald wrote: > I didn't used pg_dump/restore until today and finally found my mistake > which lead to the "problem" described below. > > The output "depends on" comes from the -l (l as Lima) flag, what i > wanted was the -1 (number one) flag, which stands for single transaction > in pg_restore. As -l does not execute anything, nothing was logged in > the postgres server log and none error was shown anywhere. -l does indeed execute something per: https://www.postgresql.org/docs/current/app-pgrestore.html " -l --list List the table of contents of the archive. The output of this operation can be used as input to the -L option. Note that if filtering switches such as -n or -t are used with -l, they will restrict the items listed. " As example: pg_restore -l redmine41_14_032124.out ; ; Archive created at 2024-03-21 01:00:01 PDT ; dbname: redmine ; TOC Entries: 455 ; Compression: -1 ; Dump Version: 1.14-0 ; Format: CUSTOM ; Integer: 4 bytes ; Offset: 8 bytes ; Dumped from database version: 14.11 (Ubuntu 14.11-1.pgdg22.04+1) ; Dumped by pg_dump version: 14.11 (Ubuntu 14.11-1.pgdg22.04+1) ; ; ; Selected TOC Entries: ; 209; 1259 17070 TABLE public ar_internal_metadata redmine 210; 1259 17075 TABLE public attachments redmine 211; 1259 17088 SEQUENCE public attachments_id_seq redmine 4069; 0 0 SEQUENCE OWNED BY public attachments_id_seq redmine ... Generally you want to redirect that to a file with -f <some_name> with the goal of using it with: " -L list-file --use-list=list-file Restore only those archive elements that are listed in list-file, and restore them in the order they appear in the file. Note that if filtering switches such as -n or -t are used with -L, they will further restrict the items restored. list-file is normally created by editing the output of a previous -l operation. Lines can be moved or removed, and can also be commented out by placing a semicolon (;) at the start of the line. See below for examples. " You instead redirected the output to the target database and that led to your errors. > > Both chars looked so identical in my editors/shells that i thought i > used -1, in fact using -l. > > It's always the tiny obvious thing, which we do not see. > > Best regards, > Chris > > Am 28. März 2024 16:57:04 schrieb Fire Emerald <fire.github@gmail.com>: > >> Am 28. März 2024 15:00:06 schrieb Tom Lane <tgl@sss.pgh.pa.us>: >> >>> Fire Emerald <fire.github@gmail.com> writes: >>>> Then i did a pg_restore -d target --verbose -Fc file.dump and saw in >>>> the >>>> output this: >>> >>>> 5145 0 730750 TABLE subpartitions backends_y2024w03 userA >>>> ; depends on: 237 >>>> .... and so on ... >>> >>> That is not an error, it's just verbose display of one of the items >>> in the dump. >> >> Well, I know it's not an error, but it's everything i got. There was >> no error shown. The command completed, but without anything imported. >> >>>> Nothing was restored. >>> >>> You would need to show us the actual errors. (Suggestion: >>> leave off --verbose, it's just clutter.) A guess though is >>> that the import failed because of foreign key constraints. >>> --data-only mode is not good at ordering the table loads to >>> ensure that FK constraints are satisfied on-the-fly. >>> >>> regards, tom lane >> >> As i said, the same import but with INSERT INTOs worked without any >> issues. So no, there are no FK constraints failing. >> >> *But* the target and source table had partitioned tables attached, >> using ATTACH PARTITION. >> >> The schema was like: >> db1 schema1 public table1 (links to the listed below) >> db1 schema1 subpartitions backends_y2024w03 >> db1 schema1 subpartitions backends_y2024w04 >> db1 schema1 subpartitions backends_y2024w05 >> >> The partitioning must be the problem somehow. > -- Adrian Klaver adrian.klaver@aklaver.com