From: pf@pfortin.com
To: pgsql-general@lists.postgresql.org
Cc: Rick Yost <Rick@NCDataTeam.org>
Subject: Re: pg_dump'ed file contains "DROP DATABASE"
Date: Mon, 20 Feb 2023 14:36:39 -0500
Message-ID: <20230220143639.2ff966b8@pfortin.com> (raw)
In-Reply-To: <f364655b-0dd5-7583-26ab-b27c7d689ab1@aklaver.com>
References: <20230220132705.3a81a48f.pfortin@pfortin.com>
<f364655b-0dd5-7583-26ab-b27c7d689ab1@aklaver.com>
On Mon, 20 Feb 2023 11:06:34 -0800 Adrian Klaver wrote:
>On 2/20/23 10:27, pf@pfortin.com wrote:
>> [Still a newbie; but learning fast...]
>>
>> Hi,
>>
>> A remote team member is helping out by dumping some of his tables via
>> pgAdmin4 on Windows. My DB is on Linux.
>>
>> The other day, I restored his first file with:
>> pg_restore --host "localhost" --port "5432" --username "postgres"
>> --no-password --dbname "myname" --create --clean --verbose "dumpfile"
>>
>> when I saw this:
>>
>> pg_restore: dropping DATABASE myname
>> Command was: DROP DATABASE myname;
>>
>> pg_restore: error: could not execute query:
>> ERROR: cannot drop the currently open database
>>
>> Digging into the pg_dump'ed files, I see:
>>
>> CREATE DATABASE myname WITH TEMPLATE = template0 ENCODING = 'UTF8'
>> LOCALE_PROVIDER = libc LOCALE = 'English_United States.1252';
>> DROP DATABASE myname;
>> ^^^^^^^^^^^^^^^^^^^^^
I thought the --clean applied to the table being restored. The man page
reads:
-c |||||||
--clean VVVVVVV
Clean (drop) database objects before recreating them. (Unless
--if-exists is used, this might generate some harmless error
messages, if any objects were not present in the destination
vs => database.)
so I took that to mean table; not the entire DB.
Notwithstanding the man page, my take is that the DROP DATABASE statement
needs to be eliminated at pg_dump creation by pgAdmin4. Taking this to
that mailing list.
Thanks Tom & Adrian!
>> Was my 134 table[1] myname DB saved because it was open?
Tom: Yup.
>> If the dump file
>> contains the above statements, how can I be absolutely certain I won't
>> lose the DB?
Tom:
Reading the manual is advisable. --create --clean specifies exactly
that the target database is to be dropped and recreated.
regards, tom lane
>> I'm obviously quite paranoid now...
>
>You will lose the database if you do as the docs specify for -C:
>
>"
>-C
>
>...
>
>When this option is used, the database named with -d is used only to
>issue the initial DROP DATABASE and CREATE DATABASE commands. All data
>is restored into the database name that appears in the archive.
>"
>
>
>It will then be recreated with whatever information is in "dumpfile". If
>that is the same data or new data you want then you are fine. Otherwise
>you will need to be more specific about what you are trying to achieve.
>
>
>>
>> [1] 3 types of tables: ~40%=8.5M rows; ~40%=33M rows; ~20%=varying sizes
>>
>> Thanks,
>> Pierre
>>
>>
>
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: pf@pfortin.com, Rick@NCDataTeam.org
Subject: Re: pg_dump'ed file contains "DROP DATABASE"
In-Reply-To: <20230220143639.2ff966b8@pfortin.com>
References: <20230220132705.3a81a48f.pfortin@pfortin.com> <f364655b-0dd5-7583-26ab-b27c7d689ab1@aklaver.com> <20230220143639.2ff966b8@pfortin.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