Обсуждение: extract ddl to devops pipeline
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.
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.
> On Mar 6, 2024, at 13:18, Lorusso Domenico <domenico.l76@gmail.com> wrote: > So there is a way to automatically generate DDL in the right order? Standard pg_dump creates files that are in the proper order, although if you exclusive some tables or schemas from the backup,those might cause errors if references from the objects you *do* import.
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