Re: How to restore to empty database

On 1/30/20 2:23 PM, Andrus wrote:
> Hi!
> 
> I want to restore to new empty database using pg_restore .
> pg_restore should create new empty database and restore into it.
> If database exists, pg_restore should clean all data from it or drop and 
> create new empty database before restore.
> 
> According to pg_restore doc, switches --clean --create --if-exists  
> should do this.
> 
> I tried
> 
>     PG_COLOR=auto
>     PGHOST=localhost
>     PGPASSWORD=mypass
>     PGUSER=postgres
>     export PGHOST  PGPASSWORD PG_COLOR PGUSER
>     pg_restore --clean --create --if-exists --dbname=mydb  --jobs=4 
> --verbose "mydb.backup"
> 
> but got error
> 
> pg_restore: connecting to database for restore
> pg_restore: error: connection to database "mydb" failed: FATAL:  
> database "mydb" does not exist
> 
> I also tried without --dbname=mydb but then got error
> 
> pg_restore: error: one of -d/--dbname and -f/--file must be specified

You need to connect to a database that exists with --dbname, for 
instance --dbname=postgres. Postgres will then use that connection to 
create the new database, in your case mydb.

> 
> How to restore to database which does not exist to drop existing 
> database before restore if it exists ?
> Should I invoke
> 
> drop database if exists
> 
> and
> 
> create database
> 
> commands before calling pg_restore or can pg_restore do it itself ?
> 
> Using Postgres 12 on Debian 10
> 
> Andrus.
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com