Thread: BUG #7507: pg_restore silently fails when restoring a db with the --create flag and no user.

The following bug has been logged on the website:

Bug reference:      7507
Logged by:          Ian Nobile
Email address:      iann@fuelforce.com
PostgreSQL version: 9.1.4
Operating system:   Mac OS X Version 10.8
Description:        =


Attempting to restore a db using pg_restore to a new database server using
the --create and --dbname flags fails silently if the db owner username does
not exist in the new db or the -O flag is not used.
Command used:
pg_restore --create --verbose --dbname=3Dtestserver
/Volumes/USBLINKS1/hostname.testserver.net_postgres_database =
iann@fuelforce.com writes:
> Attempting to restore a db using pg_restore to a new database server using
> the --create and --dbname flags fails silently if the db owner username does
> not exist in the new db or the -O flag is not used.
> Command used:
> pg_restore --create --verbose --dbname=testserver
> /Volumes/USBLINKS1/hostname.testserver.net_postgres_database

It'll fail, yes, because pg_dump by itself doesn't dump CREATE USER
commands (since users are external to any individual database).
However, it hardly seems "silent" to me.  I see complaints like these:

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1851; 1262 116736 DATABASE mydb dbowner
pg_restore: [archiver (db)] could not execute query: ERROR:  role "dbowner" does not exist
    Command was: ALTER DATABASE mydb OWNER TO dbowner;

pg_restore: [archiver (db)] Error from TOC entry 161; 1259 116737 TABLE mytable dbowner
pg_restore: [archiver (db)] could not execute query: ERROR:  role "dbowner" does not exist
    Command was: ALTER TABLE public.mytable OWNER TO dbowner;

WARNING: errors ignored on restore: 2

Possibly you want to use the --exit-on-error option?

            regards, tom lane
On Mon, Aug 27, 2012 at 03:34:10PM +0000, iann@fuelforce.com wrote:
> The following bug has been logged on the website:
>
> Bug reference:      7507
> Logged by:          Ian Nobile
> Email address:      iann@fuelforce.com
> PostgreSQL version: 9.1.4
> Operating system:   Mac OS X Version 10.8
> Description:
>
> Attempting to restore a db using pg_restore to a new database server using
> the --create and --dbname flags fails silently if the db owner username does
> not exist in the new db or the -O flag is not used.
> Command used:
> pg_restore --create --verbose --dbname=testserver
> /Volumes/USBLINKS1/hostname.testserver.net_postgres_database

Well, I tried this and it showed a proper error:

    $ pg_restore --create --verbose -U yy --dbname=testserver /rtmp/xx
    pg_restore: connecting to database for restore
    pg_restore: [archiver (db)] connection to database "testserver" failed:
    FATAL:  role "yy" does not exist

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +