Skip site navigation (1) Skip section navigation (2)

Re: pg_upgrade fails for non-postgres user

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: pg_upgrade fails for non-postgres user
Date: 2011-02-01 01:25:59
Message-ID: 201102010125.p111PxU22660@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
Magnus Hagander wrote:
> I just tried doing pg_upgrade on a database when logged in as user
> "mha" rather than "postgres" on my system. And it failed. Even though
> the db was initialized with superuser "mha". The reason for this was
> that pg_upgrade tried to connect to the database "mha" (hardcoded to
> be the db username), and that certainly didn't exist.
> 
> When that was fixed, I realized the psql command to create the
> datanbases connect to database "template1" only to immediately switch
> to database "postgres", which also seems rather pointless.
> 
> Attach patch makes it connect to the "postgres" database instead of
> $USER, and then also changes the psql command to actually use it.
> 
> I know way too little about pg_upgrade to tell if this is fully safe,
> but it does fix the problem in my installation.

I have found that this problem only affects PG 9.1 and is not part of
released PG 9.0 because we don't restore pg_authid in 9.0 (we don't need
to because we have no pg_largeobject_metadata table in PG 8.4).

I have applied a modified version of your patch to always retore into
the 'postgres' database rather than the OS user.  I thought we created
an os-user-named database, but it seems that database is always called
'postgres' but is owned by the OS user.  That seems kind of
inconsistent, but no matter.

I did not modify what we use for psql because everything else in
pg_upgrade connects to template1.  I am surprised that we recommend
restoring pg_dump to the 'postgres' database rather than template1, and
have no idea why we do that.  pg_dumpall also favors the 'postgres'
database:

       -l dbname, --database=dbname
           Specifies the name of the database to connect to to
           dump global objects and discover what other databases
           should be dumped. If not specified, the postgres
           database will be used, and if that does not exist,
           template1 will be used.

Anyway, it seems good to keep consistent and I defined a macro to record
what pg_dumpall uses as a hard-coded database for the restore. 
pg_dumpall always assumes the 'postgres' database exists, so we are OK
there.

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

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

Attachment: /rtmp/pg_upgrade.diff
Description: text/x-diff (1.7 KB)

In response to

Responses

pgsql-hackers by date

Next:From: Robert HaasDate: 2011-02-01 01:27:09
Subject: Re: Error code for "terminating connection due to conflict with recovery"
Previous:From: Tom LaneDate: 2011-02-01 01:05:25
Subject: Re: Issues with generate_series using integer boundaries

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group