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.klaver@aklaver.com