How to interpret 'depends on' errors in pg_restore?

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 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.

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>:

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