Re: extract ddl to devops pipeline

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(dot)klaver(at)aklaver(dot)com