Thread: pg_restore to new database wants to wipe out the old database?

Hi,

v9.6.9

Why is pg_restore trying to drop my production database, when I (think I) am telling it to create the new database "Molson"?

$ cd /backup
$ pg_dump -d proddb -j 8 -Fd --no-synchronized-snapshots -Z0 -v -f proddb 2> proddb_pgdump.log

$ mv proddb Molson
$ pg_restore -vcC --if-exists --jobs=8 -d postgres Molson
pg_restore: connecting to database for restore
pg_restore: dropping DATABASE proddb
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 145485; 1262 16385 DATABASE proddb TAP
pg_restore: [archiver (db)] could not execute query: ERROR:  database "proddb" is being accessed by other users
DETAIL:  There are 3 other sessions using the database.
    Command was: DROP DATABASE IF EXISTS "proddb";
 
pg_restore: processing item 145482 ENCODING ENCODING
pg_restore: processing item 145483 STDSTRINGS STDSTRINGS
pg_restore: processing item 145484 SEARCHPATH SEARCHPATH
pg_restore: processing item 145485 DATABASE proddb
pg_restore: creating DATABASE "proddb"
pg_restore: [archiver (db)] could not execute query: ERROR:  database "proddb" already exists
    Command was: CREATE DATABASE "proddb" WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_US.UTF-8' LC_CTYPE = 'en_US.UTF-8';
 

Thanks

On 10/30/2018 05:16 PM, David G. Johnston wrote:

On Tue, Oct 30, 2018 at 3:09 PM Ron <ronljohnsonjr@gmail.com> wrote:

Hi,

v9.6.9

Why is pg_restore trying to drop my production database, when I (think I) am telling it to create the new database "Molson"?

 

Straight from the pg_restore documentation:

 

-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."

So I've got to explicitly CREATE DATABASE "Molson" and then
pg_restore -d Molson Molson

Also

" pg_restore -vcC --if-exists --jobs=8 -d postgres Molson "

pg_restore [connection-option...] [option...] [filename] 

 

"Molson" is a file name; pg_restore doesn't use the file name aside from finding where the data you want to restore is located.

David J.

On 10/30/18 3:25 PM, Ron wrote:
> On 10/30/2018 05:16 PM, David G. Johnston wrote:
>> On Tue, Oct 30, 2018 at 3:09 PM Ron <ronljohnsonjr@gmail.com 
>> <mailto:ronljohnsonjr@gmail.com>> wrote:
>>
>>     Hi,
>>
>>     v9.6.9
>>
>>     Why is pg_restore trying to drop my production database, when I
>>     (think I) am telling it to create the new database "Molson"?
>>
>> Straight from the pg_restore documentation:
>> -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."
> 
> So I've got to explicitly CREATE DATABASE "Molson" and then
> pg_restore -d Molson Molson

No. The docs have an example that demonstrates:

https://www.postgresql.org/docs/9.6/static/app-pgrestore.html

To reload the dump into a new database called newdb:

$ createdb -T template0 newdb
$ pg_restore -d newdb db.dump

So(you will probably need to add appropriate -U to below):

createdb -T template0 "Molson"
pg_restore -d "Molson" proddb

> 
>>
>> Also
>> " pg_restore -vcC --if-exists --jobs=8 -d postgres Molson "
>>
>> pg_restore [connection-option...] [option...] [filename]
>>
>> "Molson" is a file name; pg_restore doesn't use the file name aside 
>> from finding where the data you want to restore is located.
>>
>> David J.
>>
> 
> -- 
> Angular momentum makes the world go 'round.


-- 
Adrian Klaver
adrian.klaver@aklaver.com