Re: Migrate schemas

On 2/8/24 16:18, Lorusso Domenico wrote:
> Hello guys,
> I have 4 schemas with cross references (e.g.: a function refers to a
> rowtype of a table of another schema, or a table invokes a function).
>
> Backup schemas by pgadmin the resulting script doesn't follow the
> correct order to ensure the object creations.
>
> There is a way to tell postgresql to check the reference at the end of
> script? Or a way to arrange DDL in the right order?

Order is not guaranteed:

https://www.postgresql.org/docs/current/app-pgdump.html

-n pattern
--schema=pattern

Dump only schemas matching pattern; this selects both the schema
itself, and all its contained objects. When this option is not
specified, all non-system schemas in the target database will be dumped.
Multiple schemas can be selected by writing multiple -n switches. The
pattern parameter is interpreted as a pattern according to the same
rules used by psql's \d commands (see Patterns), so multiple schemas can
also be selected by writing wildcard characters in the pattern. When
using wildcards, be careful to quote the pattern if needed to prevent
the shell from expanding the wildcards; see Examples below.
Note

When -n is specified, pg_dump makes no attempt to dump any other
database objects that the selected schema(s) might depend upon.
Therefore, there is no guarantee that the results of a specific-schema
dump can be successfully restored by themselves into a clean database.
Note

Non-schema objects such as large objects are not dumped when -n is
specified. You can add large objects back to the dump with the
--large-objects switch.

If you think you know the order then, see below. Though if the dumps
above where done by schema into separate files then things get complicated.

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.
-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.

Best bet is to dump the entire database.

>
> thanks'
>
>
> --
> Domenico L.
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com