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
Re: How to interpret 'depends on' errors in pg_restore?
Am 28. März 2024 15:00:06 schrieb Tom Lane : Fire Emerald 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.
Re: How to interpret 'depends on' errors in pg_restore?
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 : Am 28. März 2024 15:00:06 schrieb Tom Lane : Fire Emerald 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.
