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

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(dot)github(at)gmail(dot)com>:
>
>> Am 28. März 2024 15:00:06 schrieb Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>>
>>> Fire Emerald <fire(dot)github(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com