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