pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Adrian Klaver <adrian.klaver@aklaver.com>
To: 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 15:24:23 -0800
Message-ID: <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com> (raw)
In-Reply-To: <20230220143639.2ff966b8@pfortin.com>
References: <20230220132705.3a81a48f.pfortin@pfortin.com>
	<f364655b-0dd5-7583-26ab-b27c7d689ab1@aklaver.com>
	<20230220143639.2ff966b8@pfortin.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






view thread (8+ messages)  latest in thread

Message-ID: <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com>
Permalink:  ../73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com/
Also on:    postgresql.org/message-id/73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com

 · 

reply

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: adrian.klaver@aklaver.com, pf@pfortin.com, Rick@NCDataTeam.org
  Subject: Re: pg_dump'ed file contains "DROP DATABASE"
  In-Reply-To: <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.com>
  References: <20230220132705.3a81a48f.pfortin@pfortin.com> <f364655b-0dd5-7583-26ab-b27c7d689ab1@aklaver.com> <20230220143639.2ff966b8@pfortin.com> <73beed04-25a2-e28b-20f8-855b81fca8ec@aklaver.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