Re: [HACKERS] pg_upgrade fails for non-postgres user
On Tue, Feb 1, 2011 at 02:25, Bruce Momjian br...@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 rconnect 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/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails for non-postgres user
Magnus Hagander wrote: On Tue, Feb 1, 2011 at 02:25, Bruce Momjian br...@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 rconnect to either - because people will create object there by mistake, and then get it duplicated out to all new databases. OK. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails for non-postgres user
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c index 294f58b..d3e1fef 100644 *** a/contrib/pg_upgrade/pg_upgrade.c --- b/contrib/pg_upgrade/pg_upgrade.c *** static void set_frozenxids(void); *** 50,55 --- 50,58 static void setup(char *argv0, bool live_check); static void cleanup(void); + /* This is the database used by pg_dumpall to restore global tables */ + #define GLOBAL_DUMP_DB postgres + ClusterInfo old_cluster, new_cluster; OSInfo os_info; *** prepare_new_databases(void) *** 226,235 prep_status(Creating databases in the new cluster); /* ! * Install support functions in the database accessed by ! * GLOBALS_DUMP_FILE because it can preserve pg_authid.oid. */ ! install_support_functions_in_new_db(os_info.user); /* * We have to create the databases first so we can install support --- 229,238 prep_status(Creating databases in the new cluster); /* ! * Install support functions in the global-restore database ! * to preserve pg_authid.oid. */ ! install_support_functions_in_new_db(GLOBAL_DUMP_DB); /* * We have to create the databases first so we can install support *** create_new_objects(void) *** 266,272 DbInfo *new_db = new_cluster.dbarr.dbs[dbnum]; /* skip db we already installed */ ! if (strcmp(new_db-db_name, os_info.user) != 0) install_support_functions_in_new_db(new_db-db_name); } check_ok(); --- 269,275 DbInfo *new_db = new_cluster.dbarr.dbs[dbnum]; /* skip db we already installed */ ! if (strcmp(new_db-db_name, GLOBAL_DUMP_DB) != 0) install_support_functions_in_new_db(new_db-db_name); } check_ok(); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails for non-postgres user
--On 28. Januar 2011 14:49:21 -0800 Josh Berkus j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails for non-postgres user
Bernd Helmle maili...@oopsware.de writes: --On 28. Januar 2011 14:49:21 -0800 Josh Berkus j...@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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade fails for non-postgres user
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/ diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c index 294f58b..be9519c 100644 --- a/contrib/pg_upgrade/pg_upgrade.c +++ b/contrib/pg_upgrade/pg_upgrade.c @@ -229,7 +229,7 @@ prepare_new_databases(void) * Install support functions in the database accessed by * GLOBALS_DUMP_FILE because it can preserve pg_authid.oid. */ - install_support_functions_in_new_db(os_info.user); + install_support_functions_in_new_db(postgres); /* * We have to create the databases first so we can install support @@ -239,7 +239,7 @@ prepare_new_databases(void) SYSTEMQUOTE \%s/psql\ --set ON_ERROR_STOP=on /* --no-psqlrc prevents AUTOCOMMIT=off */ --no-psqlrc --port %d --username \%s\ - -f \%s/%s\ --dbname template1 \%s\ SYSTEMQUOTE, + -f \%s/%s\ --dbname postgres \%s\ SYSTEMQUOTE, new_cluster.bindir, new_cluster.port, os_info.user, os_info.cwd, GLOBALS_DUMP_FILE, log_opts.filename); check_ok(); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails for non-postgres user
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. H ... 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade fails for non-postgres user
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 br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
