Thread

1. pg_upgrade fails for non-postgres user

   Magnus Hagander <magnus@hagander.net> — 2011-01-28T22:08:22Z

   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.
   
   -- 
    Magnus Hagander
    Me: http://www.hagander.net/
    Work: http://www.redpill-linpro.com/
   

2. Re: pg_upgrade fails for non-postgres user

   Josh Berkus <josh@agliodbs.com> — 2011-01-28T22:49:21Z

   > 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.
   
   Hmmmm ... shouldn't we connect to template1?  The database "postgres" is
   reasonably likely to be dropped, whereas template1 doesn't get touched
   usually.
   
   -- 
                                     -- Josh Berkus
                                        PostgreSQL Experts Inc.
                                        http://www.pgexperts.com
   
   

3. Re: pg_upgrade fails for non-postgres user

   Bruce Momjian <bruce@momjian.us> — 2011-01-29T04:07:00Z

   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 will run tests and report back -- thanks.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +
   
   

4. Re: pg_upgrade fails for non-postgres user

   Bernd Helmle <mailings@oopsware.de> — 2011-01-29T16:09:23Z

   
   --On 28. Januar 2011 14:49:21 -0800 Josh Berkus <josh@agliodbs.com> wrote:
   
   > The database "postgres" is
   > reasonably likely to be dropped, whereas template1 doesn't get touched
   > usually.
   
   This is true for a bunch of installations i know. Maybe it's worth to make 
   it a command line switch to override the default behavior, too.
   
   -- 
   Thanks
   
   	Bernd
   
   

5. Re: pg_upgrade fails for non-postgres user

   Tom Lane <tgl@sss.pgh.pa.us> — 2011-01-29T17:11:40Z

   Bernd Helmle <mailings@oopsware.de> writes:
   > --On 28. Januar 2011 14:49:21 -0800 Josh Berkus <josh@agliodbs.com> wrote:
   >> The database "postgres" is
   >> reasonably likely to be dropped, whereas template1 doesn't get touched
   >> usually.
   
   > This is true for a bunch of installations i know. Maybe it's worth to make 
   > it a command line switch to override the default behavior, too.
   
   You're both forgetting that the new DB is freshly initdb'd.  It is
   certain to contain a postgres database.
   
   			regards, tom lane
   
   

6. Re: pg_upgrade fails for non-postgres user

   Bruce Momjian <bruce@momjian.us> — 2011-02-01T01:25:59Z

   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@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +
   

7. Re: pg_upgrade fails for non-postgres user

   Magnus Hagander <magnus@hagander.net> — 2011-02-01T10:01:46Z

   On Tue, Feb 1, 2011 at 02:25, Bruce Momjian <bruce@momjian.us> wrote:
   > 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).
   
   Ah, that explains why we haven't seen reports on this before.
   
   
   > 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.
   
   The whole reason for the postgres database is to provide a
   *predictable* name for people and tools to connect to, and possibly
   store things in. template1 works reasonably well for "connect to", but
   not for "store in" - because it gets duplicated out to all new
   databases after that.
   
   Which is also why it's a good reason to have it the default fo
   r"connect to" either - because people will create object there by
   mistake, and then get it duplicated out to all new databases.
   
   
   
   -- 
    Magnus Hagander
    Me: http://www.hagander.net/
    Work: http://www.redpill-linpro.com/
   
   

8. Re: pg_upgrade fails for non-postgres user

   Bruce Momjian <bruce@momjian.us> — 2011-02-01T18:10:35Z

   Magnus Hagander wrote:
   > On Tue, Feb 1, 2011 at 02:25, Bruce Momjian <bruce@momjian.us> wrote:
   > > 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).
   > 
   > Ah, that explains why we haven't seen reports on this before.
   
   Yes.  I wisely did not backpatch this:
   
   	http://archives.postgresql.org/pgsql-hackers/2011-01/msg00531.php
   
   If I had, we might not have found the bug until we released a minor
   version, and then it might have taken months for another minor release
   to fix it, which would have cause pg_upgrade users months of problems.
   
   > > 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.
   > 
   > The whole reason for the postgres database is to provide a
   > *predictable* name for people and tools to connect to, and possibly
   > store things in. template1 works reasonably well for "connect to", but
   > not for "store in" - because it gets duplicated out to all new
   > databases after that.
   
   OK, that makes sense.  pg_upgrade _mostly_ just issues queries, both in
   the new and old cluster, and because the old cluster might not have a
   'postgres' database (deleted), it seems best to do connections to
   template1 unless I need to create something.
   
   > Which is also why it's a good reason to have it the default fo
   > r"connect to" either - because people will create object there by
   > mistake, and then get it duplicated out to all new databases.
   
   OK.
   
   -- 
     Bruce Momjian  <bruce@momjian.us>        http://momjian.us
     EnterpriseDB                             http://enterprisedb.com
   
     + It's impossible for everything to be true. +