From: Tom Lane <tgl@sss.pgh.pa.us>
To: Guillaume Lelarge <guillaume@lelarge.info>
Cc: Fabrice Chapuis <fabrice636861@gmail.com>
Cc: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: pg_restore option --clean
Date: Wed, 21 Feb 2024 10:31:57 -0500
Message-ID: <81491.1708529517@sss.pgh.pa.us> (raw)
In-Reply-To: <CAECtzeX9w6LZt5shybtEuHAS5aJXRWfCw9sTK5vOp0DSxjvE3Q@mail.gmail.com>
References: <CAA5-nLDfH=sHJvcudAUKn8eG=EoC4rtemr8Z9OZUrJKP_jsfcw@mail.gmail.com>
<CAA5-nLBotUBw2eoK+_-O4P5NTDNHoYzkOgntJHv5=G7dtG0Vdw@mail.gmail.com>
<DM4PR19MB5978387EE25F4EDA640AF391D3572@DM4PR19MB5978.namprd19.prod.outlook.com>
<CAA5-nLAvRTdqdpX_M=oWUxdUDcAUKa-PrCS-pbhAeP9KJ5XmKA@mail.gmail.com>
<CAECtzeX9w6LZt5shybtEuHAS5aJXRWfCw9sTK5vOp0DSxjvE3Q@mail.gmail.com>
Guillaume Lelarge <guillaume@lelarge.info> writes:
> pg_restore won't drop objects in cascade. There's no option for that. I'd
> guess the reason is that --clean only cleans the object it will restore. If
> other objects depend on it, pg_restore has no way to know how to recreate
> them, and you would end up with a not completely restored database.
Yeah. The expectation is that --clean will issue the DROP commands
in reverse dependency order, so that no step would require CASCADE.
If one did, it'd imply that pg_dump failed to catalog all the
dependencies in the database, which would be a bug we'd want to know
about.
Now, this theory does fail in at least two practical cases:
* You're trying to use --clean with a selective restore.
* You're trying to restore into a database that has more or
different objects than the source DB did.
But in both cases, blindly using CASCADE seems like a bad idea.
You'd end up with a database that's missing some objects, and
you won't know which ones or how to put them back.
regards, tom lane
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 using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: tgl@sss.pgh.pa.us, guillaume@lelarge.info, fabrice636861@gmail.com, sarwarmd02@outlook.com, pgsql-admin@lists.postgresql.org
Subject: Re: pg_restore option --clean
In-Reply-To: <81491.1708529517@sss.pgh.pa.us>
* 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