Re: Migrate schemas - Mailing list pgsql-general

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