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(dot)klaver(at)aklaver(dot)com