Re: There should be a way to use the force flag when restoring databases

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