Re: There should be a way to use the force flag when restoring databases - Mailing list pgsql-hackers

From Joan
Subject Re: There should be a way to use the force flag when restoring databases
Date
Msg-id CAP4s+CF0XhZFyu5X8ZqwvjAwyKqfOr5Ko1r96bo3oz_fpNMcig@mail.gmail.com
Whole thread Raw
In response to Re: There should be a way to use the force flag when restoring databases  (Gurjeet Singh <gurjeet@singh.im>)
List pgsql-hackers
HI Gurjeet, that woulld be great, all the cases where a FORCE won't apply make totally sense (either complex scenarios or permission issues)
It doesn't terminate if prepared transactions, active logical replication slots or subscriptions are present in the target database.
This will fail if the current user has no permissions to terminate other connections

Regards

Missatge de Gurjeet Singh <gurjeet@singh.im> del dia dc., 19 de jul. 2023 a les 19:28:
On Tue, Jul 18, 2023 at 12:53 AM Joan <aseques@gmail.com> wrote:
>
> Since posgres 13 there's the option to do a FORCE when dropping a database (so it disconnects current users) Documentation here: https://www.postgresql.org/docs/current/sql-dropdatabase.html
>
> I am currently using dir format for the output
>    pg_dump -d "bdname" -F d -j 4 -v -f /tmp/dir
>
> And restoring the database with
>   pg_restore -d postgres -C -c --exit-on-error -F d -j 3 -v /tmp/dir
>
> Having an option to add the FORCE option to either the generated dump by pg_dump, or in the pg_restore would be very useful when restoring the databases to another servers so it would avoid having to do scripting.
>
> In my specific case I am using this to refresh periodically a development environment with data from production servers for a small database (~200M).

Making force-drop a part of pg_dump output may be dangerous, and not
provide much flexibility at restore time.

Adding a force option to pg_restore feels like providing the right tradeoff.

The docs for 'pg_restore --create` say "Create the database before
restoring into it. If --clean is also specified, drop and recreate the
target database before connecting to it."

If we provided a force option, it may then additionally say: "If the
--clean and --force options are specified, DROP DATABASE ... WITH
FORCE command will be used to drop the database."

Using WITH FORCE is not a guarantee, as the DROP DATABASE docs clarify
the conditions under which it may fail.

Best regards,
Gurjeet
http://Gurje.et

pgsql-hackers by date:

Previous
From: Andrey Lepikhov
Date:
Subject: Re: POC: GROUP BY optimization
Next
From: "yanhui.xiong"
Date:
Subject: inconsistency between the VM page visibility status and the visibility status of the page