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.git...@gmail.com>:
Am 28. März 2024 15:00:06 schrieb Tom Lane <t...@sss.pgh.pa.us>:
Fire Emerald <fire.git...@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.kla...@aklaver.com
