Re: How to interpret 'depends on' errors in pg_restore?
От | Adrian Klaver |
---|---|
Тема | Re: How to interpret 'depends on' errors in pg_restore? |
Дата | |
Msg-id | cdaa9e0c-0902-4931-b366-48122d72f2c2@aklaver.com обсуждение исходный текст |
Ответ на | Re: How to interpret 'depends on' errors in pg_restore? (Fire Emerald <fire.github@gmail.com>) |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления:
Следующее
От: HORDER PhilipДата:
Сообщение: Restore of a reference database kills the auto analyze processing.