Postgres Pro
Downloads
Home   >   mailing lists

Thread: pg_restore --clean --create reference documentation incongruity

pg_restore --clean --create reference documentation incongruity

From
Matteo Fabbri
Date:

Hi,

I'm trying to restore a backup (of TEST2 db) into TEST1 db with the pg_restore options --clean –create.

This is the command launched:

 

pg_restore -v -h localhost -p 54321 -U postgres -d TEST1 -c -C -F c /Users/matt/Desktop/TEST2.backup

 

and this is the output:

 

pg_restore: connecting to database for restore

pg_restore: dropping DATABASE TEST2

pg_restore: creating DATABASE TEST2

pg_restore: connecting to new database "TEST2"

pg_restore: connecting to database "TEST2" as user "postgres"

pg_restore: creating SCHEMA public

pg_restore: [archiver (db)] Error while PROCESSING TOC:

pg_restore: [archiver (db)] Error from TOC entry 7; 2615 2200 SCHEMA public postgres

pg_restore: [archiver (db)] could not execute query: ERROR:  schema "public" already exists

    Command was: CREATE SCHEMA public;

 

 

 

pg_restore: creating COMMENT SCHEMA public

pg_restore: creating SCHEMA schemax

pg_restore: creating EXTENSION plpgsql

pg_restore: creating COMMENT EXTENSION plpgsql

pg_restore: creating TABLE table_1

pg_restore: processing data for table "table_1"

pg_restore: creating CONSTRAINT table_1_pkey

pg_restore: setting owner and privileges for DATABASE TEST2

pg_restore: setting owner and privileges for SCHEMA public

pg_restore: setting owner and privileges for COMMENT SCHEMA public

pg_restore: setting owner and privileges for ACL public

pg_restore: setting owner and privileges for SCHEMA schemax

pg_restore: setting owner and privileges for EXTENSION plpgsql

pg_restore: setting owner and privileges for COMMENT EXTENSION plpgsql

pg_restore: setting owner and privileges for TABLE table_1

pg_restore: setting owner and privileges for TABLE DATA table_1

pg_restore: setting owner and privileges for CONSTRAINT table_1_pkey

WARNING: errors ignored on restore: 1

 

Now, now as described in the reference documentation

 

-C
--create

Create the database before restoring into it. If --clean is also specified, drop and recreate the target database before connecting to it.

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.

I expected the specified db (-d TEST1) was dropped and created, not the TEST2 db.

In essence it seems that the –d parameter is ignored and TEST1 db remains untouched.

What is the correct behavior? Is a pg_restore error? Is a reference documentation error? Or simply I have not interpreted correctly it?

Thanks in advance,

matt

 

Re: pg_restore --clean --create reference documentation incongruity

From
"Hunley, Douglas"
Date:

On Fri, Oct 7, 2016 at 5:32 AM, Matteo Fabbri <ma.fabbri@teamsystem.com> wrote:

I expected the specified db (-d TEST1) was dropped and created, not the TEST2 db.

In essence it seems that the –d parameter is ignored and TEST1 db remains untouched.

What is the correct behavior? Is a pg_restore error? Is a reference documentation error? Or simply I have not interpreted correctly it?


You're misunderstanding the documentation slightly. The '-d' parameter is what database to connect to. The '-c' and '-C' parameters define how pg_restore handles the database(s) defined inside the dump. 

Think of it this way, if you have a completely new empty PostgreSQL install, and you have a dump of 'testdb' you want to restore, you can't use '-d testdb' because you can't connect to a db that doesn't exist yet. 


--
{
  "name" : "douglas j hunley",
  "email" : "douglas.hunley@openscg.com",
  "title" : "database engineer",
  "phone" : "+1 732 339 3419 x163"
}

Re: pg_restore --clean --create reference documentation incongruity

From
Matteo Fabbri
Date:

Ok, -d intended as the database to connect to, makes sense.

Not the words of the documentation (-C --create): “When this option is used, the database named with

-d is used only to issue the initial DROP DATABASE and CREATE DATABASE commands.”!

Thanks for the clarification,

matt

 

On Fri, Oct 7, 2016 at 5:32 AM, Matteo Fabbri <ma.fabbri@teamsystem.com> wrote:

I expected the specified db (-d TEST1) was dropped and created, not the TEST2 db.

In essence it seems that the –d parameter is ignored and TEST1 db remains untouched.

What is the correct behavior? Is a pg_restore error? Is a reference documentation error? Or simply I have not interpreted correctly it?


You're misunderstanding the documentation slightly. The '-d' parameter is what database to connect to. The '-c' and '-C' parameters define how pg_restore handles the database(s) defined inside the dump. 

 

Think of it this way, if you have a completely new empty PostgreSQL install, and you have a dump of 'testdb' you want to restore, you can't use '-d testdb' because you can't connect to a db that doesn't exist yet. 


 

--

{
  "name" : "douglas j hunley",
  "email" : "douglas.hunley@openscg.com",
  "title" : "database engineer",

  "phone" : "+1 732 339 3419 x163"

}