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 as simple 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: errors ignored 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 restoring and 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 DATABASE 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_FcYou need to connect to a database that already exists (such as "postgres"); it then creates the database you are restoring and 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 DATABASE commands. 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 database first, 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 database first, 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 database first, 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 !