On 3/6/24 13:18, Lorusso Domenico wrote:
> Hello guys,
> I need to export the DDL (tables, funcitons views) of some schemas (with
> cross references) to load them in a pipeline like DevOps ready.
>
> Problem: export/backup doesn't care about the appropriate sequence of
> objet because these stuff will be solved by import phase.
pg_dump -Fc ... -f dump_file.out
From here
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.
"
Then:
pg_restore -l -f toc_list.txt dump_file.out
Then from link above:
"
-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.
"
Open toc_list.txt in text editor and comment(;) out the items you don't
want and then feed the edited TOC back to pg_restore as:
pg_restore -L toc_list.txt -f edited_dump.sql dump_file.out
Where edited_dump.sql will be a plain text dump file with the DDL
statements.
>
> So there is a way to automatically generate DDL in the right order?
>
> I mean, if function Foo reference in input/output definition (also i
> declare section?) to table Bar, I've need to create the table before the
> function.
> And if Bar uses function Lir (in trigger? check? else?), Lir must be
> create before Bar.
>
--
Adrian Klaver
adrian.klaver@aklaver.com