Thread: pg_dump'ed file contains "DROP DATABASE"
[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;
^^^^^^^^^^^^^^^^^^^^^
Was my 134 table[1] myname DB saved because it was open? If the dump file
contains the above statements, how can I be absolutely certain I won't
lose the DB? I'm obviously quite paranoid now...
[1] 3 types of tables: ~40%=8.5M rows; ~40%=33M rows; ~20%=varying sizes
Thanks,
Pierre
pf@pfortin.com writes:
> 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"
> Was my 134 table[1] myname DB saved because it was open?
Yup.
> If the dump file
> contains the above statements, how can I be absolutely certain I won't
> lose the DB?
Reading the manual is advisable. --create --clean specifies exactly
that the target database is to be dropped and recreated.
regards, tom lane
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; > ^^^^^^^^^^^^^^^^^^^^^ > > Was my 134 table[1] myname DB saved because it was open? If the dump file > contains the above statements, how can I be absolutely certain I won't > lose the DB? 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 > > -- Adrian Klaver adrian.klaver@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
>>
>>
>
On 2/20/23 11:36, pf@pfortin.com wrote: > 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. 1) If you clean(drop) all the objects in a database you have effectively got to the same point as dropping the database. > > 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. This is not on pgAdmin4 If the dump is done with the custom format the -c and -C can be done on the pg_restore end per: https://www.postgresql.org/docs/current/app-pgdump.html -c -C This option is ignored when emitting an archive (non-text) output file. For the archive formats, you can specify the option when you call pg_restore. So this: pg_restore --host "localhost" --port "5432" --username "postgres" --no-password --dbname "myname" --create --clean --verbose "dumpfile" is on you not pgAdmin4. Spend some time in the pg_dump and pg_restore docs, there is a lot going on in there. > > Thanks Tom & Adrian! > -- Adrian Klaver adrian.klaver@aklaver.com
On 2/20/23 11:36, pf@pfortin.com wrote: > 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, > > 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. It just dawned on me you might be doing all of this through the pgAdmin4 GUI. In which case from most recent documentation: https://www.pgadmin.org/docs/pgadmin4/6.20/index.html Backup Dialog: https://www.pgadmin.org/docs/pgadmin4/6.20/backup_dialog.html Options tab: " Move the switch next to Include CREATE DATABASE statement towards right position to include a command in the backup that creates a new database when restoring the backup. Move the switch next to Include DROP DATABASE statement towards right position to include a command in the backup that will drop any existing database object with the same name before recreating the object during a backup. " So the default is not to include those options. For Restore dialog: https://www.pgadmin.org/docs/pgadmin4/6.20/restore_dialog.html Options tab(for custom format): " Move the switch next to Include CREATE DATABASE statement towards right position to include a command that creates a new database before performing the restore. Move the switch next to Clean before restore towards right position to drop each existing database object (and data) before restoring. " Again the default is to not include those options. > > 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 >>> >>> >> > > -- Adrian Klaver adrian.klaver@aklaver.com
On Mon, 20 Feb 2023 15:24:23 -0800 Adrian Klaver wrote: >On 2/20/23 11:36, pf@pfortin.com wrote: >> 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, > >> >> 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. > >It just dawned on me you might be doing all of this through the >pgAdmin4 GUI. Sorry for any confusion... I get it now... A team member uses pgAdmin4 to load separate table(s) into his DB; then creates dump files (one per table) of those _individual_ tables which are uploaded to me. I maintain a complete set of tables in my DB. pgAdmin4 is never used here; the restore is done with a simple bash script which inserts the dbname and dumpfile name into the command. Those restore tables should only be created with DROP DATABASE _off_. All that should happen with my DB is to add these tables (99.9% of the time, they are totally new to me). A DROP DATABASE from the one-table per dumpfile creator is UNwanted. Looks like I really did dodge a bullet... >Again the default is to not include those options. Glad to know pgAdmin4 has those switches. Thanks again!! >> >> 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 >>>> >>>> >>> >> >> >
On 2/20/23 19:22, pf@pfortin.com wrote: > On Mon, 20 Feb 2023 15:24:23 -0800 Adrian Klaver wrote: > >> >> It just dawned on me you might be doing all of this through the >> pgAdmin4 GUI. > > Sorry for any confusion... I get it now... > > A team member uses pgAdmin4 to load separate table(s) into his DB; then > creates dump files (one per table) of those _individual_ tables which are > uploaded to me. Given that you are using pg_restore then the dump file they are creating is done with a custom format e.g. -Fc. This means when you do: pg_restore --host "localhost" --port "5432" --username "postgres" --no-password --dbname "myname" --create --clean --verbose "dumpfile" the --create in combination with --clean is going to DROP DATABASE and recreate it. > > I maintain a complete set of tables in my DB. pgAdmin4 is never used > here; the restore is done with a simple bash script which inserts the > dbname and dumpfile name into the command. Those restore tables should > only be created with DROP DATABASE _off_. As I said before the solution is going to come from your end. Do not include the --create in you pg_restore script. > > All that should happen with my DB is to add these tables (99.9% of the > time, they are totally new to me). > > A DROP DATABASE from the one-table per dumpfile creator is UNwanted. Again that is your doing and you are the one that can stop it. > > Looks like I really did dodge a bullet... > >> Again the default is to not include those options. > > Glad to know pgAdmin4 has those switches. > > Thanks again!! > -- Adrian Klaver adrian.klaver@aklaver.com