Thread: Postgres12 - Confusion with pg_restore

According to the all-mighty manual (https://www.postgresql.org/docs/current/app-pgrestore.html), life is supposed to be
assimple as: 

"To drop the database and recreate it from the dump:
$ dropdb mydb
$ pg_restore -C -d postgres db.dump"

The reality seems to be somewhat different ?

sudo -u postgres pg_restore -v -C -d foobar 4_foobar_pgdump_Fc
pg_restore: connecting to database for restore
pg_restore: error: connection to database "foobar" failed: FATAL:  database "foobar" does not exist

So I thought I would try to create the database manually first (CREATE DATABSE ....).  That made pg_restore even more
angry:

sudo -u postgres pg_restore -C -d foobar 4_foobar_pgdump_Fc
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 3088; 1262 43395 DATABASE foobar postgres
pg_restore: error: could not execute query: ERROR:  database "foobar" already exists
Command was: CREATE DATABASE foobar WITH TEMPLATE = template0 ENCODING = 'UTF8' LC_COLLATE = 'en_GB.UTF-8' LC_CTYPE =
'en_GB.UTF-8';
                                                                                                pg_restore: warning:
errorsignored on restore: 1 

> On Jun 5, 2020, at 11:20, Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> wrote:
> sudo -u postgres pg_restore -v -C -d foobar 4_foobar_pgdump_Fc

You need to connect to a database that already exists (such as "postgres"); it then creates the database you are
restoringand switches to it.  The relevant manual line is: 

    "When (-C / --create) is used, the database named with -d is used only to issue the initial DROP DATABASE and
CREATEDATABASE commands. All data is restored into the database name that appears in the archive." 

--
-- Christophe Pettus
   xof@thebuild.com

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 5 June 2020 19:23, Christophe Pettus <xof@thebuild.com> wrote:

> > On Jun 5, 2020, at 11:20, Laura Smith n5d9xq3ti233xiyif2vp@protonmail.ch wrote:
> > sudo -u postgres pg_restore -v -C -d foobar 4_foobar_pgdump_Fc
>
> You need to connect to a database that already exists (such as "postgres"); it then creates the database you are
restoringand switches to it. The relevant manual line is: 
>
> "When (-C / --create) is used, the database named with -d is used only to issue the initial DROP DATABASE and CREATE
DATABASEcommands. All data is restored into the database name that appears in the archive." 
>
>

But doesn't the second half of my original post demonstrate that I tried that very thing  ?  I did try creating the
databasefirst, but pg_restore just complained even more ? 

Laura Smith <n5d9xq3ti233xiyif2vp@protonmail.ch> writes:
> But doesn't the second half of my original post demonstrate that I tried that very thing  ?  I did try creating the
databasefirst, but pg_restore just complained even more ? 

There are two ways you can do this:

1. Create the new database by hand (with CREATE DATABASE) and tell
pg_restore to restore into it.  In this case you *don't* say -C
to pg_restore, and your -d switch points at the DB to restore into.

2. Have pg_restore issue CREATE DATABASE.  In this case you *do* use
the -C switch, and your -d switch has to point at some pre-existing
database that pg_restore can connect to for long enough to issue the
CREATE DATABASE.

You can't mix-and-match these approaches.

            regards, tom lane

Sent with ProtonMail Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Friday, 5 June 2020 19:35, Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Laura Smith n5d9xq3ti233xiyif2vp@protonmail.ch writes:
>
> > But doesn't the second half of my original post demonstrate that I tried that very thing ? I did try creating the
databasefirst, but pg_restore just complained even more ? 
>
> There are two ways you can do this:
>
> 1.  Create the new database by hand (with CREATE DATABASE) and tell
>     pg_restore to restore into it. In this case you don't say -C
>     to pg_restore, and your -d switch points at the DB to restore into.
>
> 2.  Have pg_restore issue CREATE DATABASE. In this case you do use
>     the -C switch, and your -d switch has to point at some pre-existing
>     database that pg_restore can connect to for long enough to issue the
>     CREATE DATABASE.
>
>     You can't mix-and-match these approaches.
>
>     regards, tom lane
>

Thanks for the clarificaiton Tom.  All working now !