Subject: Re: How to interpret 'depends on' errors in pg_restore?

From: Tom Lane <[email-masked]>
Date: Thu, 28 Mar 2024 10:00:06 -0400

Fire Emerald <[email-masked]> 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

---

From: Fire Emerald <[email-masked]>
Date: Thu, 28 Mar 2024 16:57:04 +0100

> That is not an error, it's just verbose display of one of the items

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.

> ensure that FK constraints are satisfied on-the-fly.

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.

---

From: Tom Lane <[email-masked]>
Date: Thu, 28 Mar 2024 12:03:58 -0400

> 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

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 28 Mar 2024 09:19:28 -0700

> error shown. The command completed, but without anything imported.

Look in the Postgres log to see if there is more information.

-- 
Adrian Klaver
[email-masked]

---

From: Fire Emerald <[email-masked]>
Date: Thu, 02 May 2024 11:20:48 +0200

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

---

From: Adrian Klaver <[email-masked]>
Date: Thu, 2 May 2024 07:43:21 -0700

On 5/2/24 02:20, Fire Emerald wrote:
> 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.
> 
>> 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.
>>>
>> 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: