From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Lorusso Domenico <domenico.l76@gmail.com>
To: pgsql-general@lists.postgresql.org
Subject: Re: Migrate schemas
Date: Thu, 8 Feb 2024 16:31:13 -0800
Message-ID: <ba752a6f-0caa-44b1-b83e-614f995940dc@aklaver.com> (raw)
In-Reply-To: <CAJMpnG4=OgE7THL+trmCukC60FLCGdijhrwkcK=MRTOR0s9U8w@mail.gmail.com>
References: <CAJMpnG4=OgE7THL+trmCukC60FLCGdijhrwkcK=MRTOR0s9U8w@mail.gmail.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, domenico.l76@gmail.com
Subject: Re: Migrate schemas
In-Reply-To: <ba752a6f-0caa-44b1-b83e-614f995940dc@aklaver.com>
References: <CAJMpnG4=OgE7THL+trmCukC60FLCGdijhrwkcK=MRTOR0s9U8w@mail.gmail.com> <ba752a6f-0caa-44b1-b83e-614f995940dc@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox