Feedback-ID: i76984098:Fastmail
Message-ID: <cdaa9e0c-0902-4931-b366-48122d72f2c2@aklaver.com>
Date: Thu, 2 May 2024 07:43:21 -0700
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: How to interpret 'depends on' errors in pg_restore?
To: Fire Emerald <fire.github@gmail.com>, Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@lists.postgresql.org
References: <18e84674c10.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com>
 <3571436.1711634406@sss.pgh.pa.us>
 <18e85c76c98.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com>
 <18f389b3980.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com>
Content-Language: en-US
From: Adrian Klaver <adrian.klaver@aklaver.com>
In-Reply-To: <18f389b3980.2815.a5aef60df33e8d2ac3d54c6545825f63@gmail.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/cdaa9e0c-0902-4931-b366-48122d72f2c2%40aklaver.com>
Precedence: bulk

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