public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Fire Emerald <[email protected]>
To: Tom Lane <[email protected]>
Cc: [email protected]
Subject: Re: How to interpret 'depends on' errors in pg_restore?
Date: Thu, 2 May 2024 07:43:21 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
<[email protected]>
<[email protected]>
<[email protected]>
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 <[email protected]>:
>
>> Am 28. März 2024 15:00:06 schrieb Tom Lane <[email protected]>:
>>
>>> Fire Emerald <[email protected]> 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
[email protected]
view thread (6+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: How to interpret 'depends on' errors in pg_restore?
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox