Thread: Postgresql 9.0b2 : pg_upgrade not passing username to pgdumpall ?
Hi all,
it’s my first trial with pg_upgrade so I’m surely missing something....
I’m trying to launch pg_upgrade passing my super user name but pg_dumpall fails with a permission denied error....
I don’t see the username in the pgdumpall call....could it be this the problem ?
Thank you (I hope this is the right list to post this)
Marco
The output :
------------------------------
-bash-3.2$ /usr/local/pgsql/bin/pg_upgrade -d /dbms/pgdata-8.4 -D /dbms/pgdata -b /usr/local/pgsql-8.4/bin -B /usr/local/pgsql/bin -l /tmp/pg_upgrade.log -v --user=mysuperuser
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking old data directory (/dbms/pgdata-8.4) ok
Checking new data directory (/dbms/pgdata) ok
"/usr/local/pgsql-8.4/bin/pg_ctl" -l "/tmp/pg_upgrade.log" -D "/dbms/pgdata-8.4" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/tmp/pg_upgrade.log" 2>&1
Checking for /contrib/isn with bigint-passing mismatch ok
Checking for large objects ok
Creating catalog dump "/usr/local/pgsql/bin/pg_dumpall" --port 5432 --schema-only --binary-upgrade > "/home/postgres/pg_upgrade_output/pg_upgrade_dump_all.sql"
pg_dumpall: query failed: ERROR: permission denied for relation pg_authid
pg_dumpall: query was: SELECT rolname, rolsuper, rolinherit, rolcreaterole, rolcreatedb, rolcatupdate, rolcanlogin, rolconnlimit, rolpassword, rolvaliduntil, pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment FROM pg_authid ORDER BY 1
Cassiano, Marco wrote: > Hi all, > > > > it's my first trial with pg_upgrade so I'm surely missing something.... > > I'm trying to launch pg_upgrade passing my super user name but > pg_dumpall fails with a permission denied error.... > > I don't see the username in the pgdumpall call....could it be this the > problem ? > > Thank you (I hope this is the right list to post this) Turns out I was missing the user name designation. I am attaching the patch that fixes this, and a pg_ctl issue on Win32. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: contrib/pg_upgrade/dump.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/dump.c,v retrieving revision 1.2 diff -c -c -r1.2 dump.c *** contrib/pg_upgrade/dump.c 12 Jun 2010 17:05:29 -0000 1.2 --- contrib/pg_upgrade/dump.c 22 Jun 2010 16:44:06 -0000 *************** *** 19,27 **** * restores the frozenid's for databases and relations. */ exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/pg_dumpall\" --port %d --schema-only " ! "--binary-upgrade > \"%s/" ALL_DUMP_FILE "\"" SYSTEMQUOTE, ! ctx->new.bindir, ctx->old.port, ctx->cwd); check_ok(ctx); } --- 19,27 ---- * restores the frozenid's for databases and relations. */ exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/pg_dumpall\" --port %d --username \"%s\" " ! "--schema-only --binary-upgrade > \"%s/" ALL_DUMP_FILE "\"" ! SYSTEMQUOTE, ctx->new.bindir, ctx->old.port, ctx->user, ctx->cwd); check_ok(ctx); } Index: contrib/pg_upgrade/option.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/option.c,v retrieving revision 1.7 diff -c -c -r1.7 option.c *** contrib/pg_upgrade/option.c 15 Jun 2010 23:25:01 -0000 1.7 --- contrib/pg_upgrade/option.c 22 Jun 2010 16:44:06 -0000 *************** *** 174,185 **** * start. */ /* truncate */ ! ctx->log_fd = fopen(ctx->logfile, "w"); ! if (!ctx->log_fd) pg_log(ctx, PG_FATAL, "Cannot write to log file %s\n", ctx->logfile); fclose(ctx->log_fd); ! ctx->log_fd = fopen(ctx->logfile, "a"); ! if (!ctx->log_fd) pg_log(ctx, PG_FATAL, "Cannot write to log file %s\n", ctx->logfile); } else --- 174,183 ---- * start. */ /* truncate */ ! if ((ctx->log_fd = fopen(ctx->logfile, "w")) == NULL) pg_log(ctx, PG_FATAL, "Cannot write to log file %s\n", ctx->logfile); fclose(ctx->log_fd); ! if ((ctx->log_fd = fopen(ctx->logfile, "a")) == NULL) pg_log(ctx, PG_FATAL, "Cannot write to log file %s\n", ctx->logfile); } else Index: contrib/pg_upgrade/pg_upgrade.h =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/pg_upgrade.h,v retrieving revision 1.8 diff -c -c -r1.8 pg_upgrade.h *** contrib/pg_upgrade/pg_upgrade.h 15 Jun 2010 02:08:01 -0000 1.8 --- contrib/pg_upgrade/pg_upgrade.h 22 Jun 2010 16:44:06 -0000 *************** *** 48,54 **** #define pg_link_file win32_pghardlink #define EXE_EXT ".exe" #define sleep(x) Sleep(x * 1000) ! #define DEVNULL "nul" /* "con" does not work from the Msys 1.0.10 console (part of MinGW). */ #define DEVTTY "con" /* from pgport */ --- 48,54 ---- #define pg_link_file win32_pghardlink #define EXE_EXT ".exe" #define sleep(x) Sleep(x * 1000) ! #define DEVNULL "nul" /* "con" does not work from the Msys 1.0.10 console (part of MinGW). */ #define DEVTTY "con" /* from pgport */ Index: contrib/pg_upgrade/server.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/server.c,v retrieving revision 1.2 diff -c -c -r1.2 server.c *** contrib/pg_upgrade/server.c 14 May 2010 00:32:21 -0000 1.2 --- contrib/pg_upgrade/server.c 22 Jun 2010 16:44:06 -0000 *************** *** 177,188 **** port = ctx->new.port; } ! /* use -l for Win32 */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" " "-o \"-p %d -c autovacuum=off -c autovacuum_freeze_max_age=2000000000\" " "start >> \"%s\" 2>&1" SYSTEMQUOTE, ! bindir, ctx->logfile, datadir, port, ctx->logfile); exec_prog(ctx, true, "%s", cmd); /* wait for the server to start properly */ --- 177,198 ---- port = ctx->new.port; } ! /* ! * On Win32, we can't send both server output and pg_ctl output ! * to the same file because we get the error: ! * "The process cannot access the file because it is being used by another process." ! * so we have to send pg_ctl output to 'nul'. ! */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" " "-o \"-p %d -c autovacuum=off -c autovacuum_freeze_max_age=2000000000\" " "start >> \"%s\" 2>&1" SYSTEMQUOTE, ! bindir, ctx->logfile, datadir, port, ! #ifndef WIN32 ! ctx->logfile); ! #else ! DEVNULL); ! #endif exec_prog(ctx, true, "%s", cmd); /* wait for the server to start properly */ *************** *** 200,205 **** --- 210,216 ---- void stop_postmaster(migratorContext *ctx, bool fast, bool quiet) { + char cmd[MAXPGPATH]; const char *bindir; const char *datadir; *************** *** 216,225 **** else return; /* no cluster running */ ! /* use -l for Win32 */ ! exec_prog(ctx, fast ? false : true, SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" %s stop >> \"%s\" 2>&1" SYSTEMQUOTE, ! bindir, ctx->logfile, datadir, fast ? "-m fast" : "", ctx->logfile); ctx->postmasterPID = 0; ctx->running_cluster = NONE; --- 227,242 ---- else return; /* no cluster running */ ! /* See comment in start_postmaster() about why win32 output is ignored. */ ! snprintf(cmd, sizeof(cmd), SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" %s stop >> \"%s\" 2>&1" SYSTEMQUOTE, ! bindir, ctx->logfile, datadir, fast ? "-m fast" : "", ! #ifndef WIN32 ! ctx->logfile); ! #else ! DEVNULL); ! #endif ! exec_prog(ctx, fast ? false : true, "%s", cmd); ctx->postmasterPID = 0; ctx->running_cluster = NONE;
Thank you Bruce, I applied the patch and found two things you might want to take in consideration : 1) dump.c Line 25 should be : SYSTEMQUOTE, ctx->new.bindir, ctx->old.port, ctx->user, ctx->output_dir); And not SYSTEMQUOTE, ctx->new.bindir, ctx->old.port, ctx->user, ctx->cwd); (sorry, I'm not confident at producing patch files....) 2) I then recompiled pg_upgrade and reinstalled it. Now it goes a little bit further but I think there's another point (vacuumdb of the new cluster) in which the username ismissing. You might want to take a look at this output (/tmp/pg_upgrade.log) .... Checking for presence of required libraries ok | If pg_upgrade fails after this point, you must | re-initdb the new cluster before continuing. | You will also need to remove the ".old" suffix | from /dbms/pgdata-8.4/global/pg_control.old. Performing Migration -------------------- Adding ".old" suffix to old global/pg_control ok Analyzing all rows in the new cluster "/usr/local/pgsql/bin/vacuumdb" --port 5432 --all --analyze >>/tmp/pg_upgrade.log 2>&1 FATAL: role "postgres" does not exist vacuumdb: could not connect to database postgres: FATAL: role "postgres" does not exist .............. If I launch it manually adding the -U mysuperuser option it works... Hope this is somehow useful Regards Marco -----Messaggio originale----- Da: Bruce Momjian [mailto:bruce@momjian.us] Inviato: martedì 22 giugno 2010 18:48 A: Cassiano, Marco Cc: pgsql-admin@postgresql.org Oggetto: Re: [ADMIN] Postgresql 9.0b2 : pg_upgrade not passing username to pgdumpall ? Cassiano, Marco wrote: > Hi all, > > > > it's my first trial with pg_upgrade so I'm surely missing something.... > > I'm trying to launch pg_upgrade passing my super user name but > pg_dumpall fails with a permission denied error.... > > I don't see the username in the pgdumpall call....could it be this the > problem ? > > Thank you (I hope this is the right list to post this) Turns out I was missing the user name designation. I am attaching the patch that fixes this, and a pg_ctl issue on Win32. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. +
Re: [TESTERS] R: Postgresql 9.0b2 : pg_upgrade not passing username to pgdumpall ?
Cassiano, Marco wrote: > Thank you Bruce, > > I applied the patch and found two things you might want to take in consideration : > > 1) dump.c Line 25 should be : > > SYSTEMQUOTE, ctx->new.bindir, ctx->old.port, ctx->user, ctx->output_dir); > > And not > > SYSTEMQUOTE, ctx->new.bindir, ctx->old.port, ctx->user, ctx->cwd); > > (sorry, I'm not confident at producing patch files....) OK, the problem here is that I changed output_dir to cwd since beta2; your change is fine for your version of pg_upgrade. > 2) I then recompiled pg_upgrade and reinstalled it. Now it goes > a little bit further but I think there's another point (vacuumdb of > the new cluster) in which the username is missing. You might want >to take a look at this output (/tmp/pg_upgrade.log) Ah, you are right --- I seem to have missed a bunch of username designations. I went through and looked all all the exec calls, and added the username to each one that supported it; applied patch attached. -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + None of us is going to be here forever. + Index: contrib/pg_upgrade/check.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/check.c,v retrieving revision 1.6 diff -c -c -r1.6 check.c *** contrib/pg_upgrade/check.c 12 Jun 2010 17:05:29 -0000 1.6 --- contrib/pg_upgrade/check.c 23 Jun 2010 20:03:07 -0000 *************** *** 149,158 **** { prep_status(ctx, "Adjusting sequences"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/psql\" --set ON_ERROR_STOP=on --port %d " ! "-f \"%s\" --dbname template1 >> \"%s\"" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, sequence_script_file_name, ! ctx->logfile); unlink(sequence_script_file_name); check_ok(ctx); } --- 149,159 ---- { prep_status(ctx, "Adjusting sequences"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/psql\" --set ON_ERROR_STOP=on --port %d " ! "--username \"%s\" -f \"%s\" --dbname template1 >> \"%s\"" ! SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->user, ! sequence_script_file_name, ctx->logfile); unlink(sequence_script_file_name); check_ok(ctx); } Index: contrib/pg_upgrade/pg_upgrade.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/pg_upgrade.c,v retrieving revision 1.5 diff -c -c -r1.5 pg_upgrade.c *** contrib/pg_upgrade/pg_upgrade.c 12 Jun 2010 17:05:29 -0000 1.5 --- contrib/pg_upgrade/pg_upgrade.c 23 Jun 2010 20:03:07 -0000 *************** *** 78,84 **** * because there is no need to have the schema load use new oids. */ prep_status(&ctx, "Setting next oid for new cluster"); ! exec_prog(&ctx, true, SYSTEMQUOTE "\"%s/pg_resetxlog\" -o %u \"%s\" > " DEVNULL SYSTEMQUOTE, ctx.new.bindir, ctx.old.controldata.chkpnt_nxtoid, ctx.new.pgdata); check_ok(&ctx); --- 78,85 ---- * because there is no need to have the schema load use new oids. */ prep_status(&ctx, "Setting next oid for new cluster"); ! exec_prog(&ctx, true, SYSTEMQUOTE "\"%s/pg_resetxlog\" -o %u \"%s\" > " ! DEVNULL SYSTEMQUOTE, ctx.new.bindir, ctx.old.controldata.chkpnt_nxtoid, ctx.new.pgdata); check_ok(&ctx); *************** *** 156,163 **** */ prep_status(ctx, "Analyzing all rows in the new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --all --analyze >> %s 2>&1" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->logfile); check_ok(ctx); /* --- 157,165 ---- */ prep_status(ctx, "Analyzing all rows in the new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --username \"%s\" " ! "--all --analyze >> %s 2>&1" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->user, ctx->logfile); check_ok(ctx); /* *************** *** 168,175 **** */ prep_status(ctx, "Freezing all rows on the new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --all --freeze >> %s 2>&1" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->logfile); check_ok(ctx); get_pg_database_relfilenode(ctx, CLUSTER_NEW); --- 170,178 ---- */ prep_status(ctx, "Freezing all rows on the new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/vacuumdb\" --port %d --username \"%s\" " ! "--all --freeze >> %s 2>&1" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->user, ctx->logfile); check_ok(ctx); get_pg_database_relfilenode(ctx, CLUSTER_NEW); *************** *** 196,204 **** */ prep_status(ctx, "Creating databases in the new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/psql\" --set ON_ERROR_STOP=on --port %d " ! "-f \"%s/%s\" --dbname template1 >> \"%s\"" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->cwd, GLOBALS_DUMP_FILE, ctx->logfile); check_ok(ctx); --- 199,208 ---- */ prep_status(ctx, "Creating databases in the new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/psql\" --port %d --username \"%s\" " ! "--set ON_ERROR_STOP=on -f \"%s/%s\" --dbname template1 >> \"%s\"" ! SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->user, ctx->cwd, GLOBALS_DUMP_FILE, ctx->logfile); check_ok(ctx); *************** *** 218,226 **** prep_status(ctx, "Restoring database schema to new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/psql\" --set ON_ERROR_STOP=on --port %d " ! "-f \"%s/%s\" --dbname template1 >> \"%s\"" SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->cwd, DB_DUMP_FILE, ctx->logfile); check_ok(ctx); --- 222,231 ---- prep_status(ctx, "Restoring database schema to new cluster"); exec_prog(ctx, true, ! SYSTEMQUOTE "\"%s/psql\" --port %d --username \"%s\" " ! "--set ON_ERROR_STOP=on -f \"%s/%s\" --dbname template1 >> \"%s\"" ! SYSTEMQUOTE, ! ctx->new.bindir, ctx->new.port, ctx->user, ctx->cwd, DB_DUMP_FILE, ctx->logfile); check_ok(ctx); Index: contrib/pg_upgrade/server.c =================================================================== RCS file: /cvsroot/pgsql/contrib/pg_upgrade/server.c,v retrieving revision 1.3 diff -c -c -r1.3 server.c *** contrib/pg_upgrade/server.c 22 Jun 2010 16:45:10 -0000 1.3 --- contrib/pg_upgrade/server.c 23 Jun 2010 20:03:07 -0000 *************** *** 185,191 **** */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" " ! "-o \"-p %d -c autovacuum=off -c autovacuum_freeze_max_age=2000000000\" " "start >> \"%s\" 2>&1" SYSTEMQUOTE, bindir, ctx->logfile, datadir, port, #ifndef WIN32 --- 185,192 ---- */ snprintf(cmd, sizeof(cmd), SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" " ! "-o \"-p %d -c autovacuum=off " ! "-c autovacuum_freeze_max_age=2000000000\" " "start >> \"%s\" 2>&1" SYSTEMQUOTE, bindir, ctx->logfile, datadir, port, #ifndef WIN32 *************** *** 229,235 **** /* See comment in start_postmaster() about why win32 output is ignored. */ snprintf(cmd, sizeof(cmd), ! SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" %s stop >> \"%s\" 2>&1" SYSTEMQUOTE, bindir, ctx->logfile, datadir, fast ? "-m fast" : "", #ifndef WIN32 ctx->logfile); --- 230,237 ---- /* See comment in start_postmaster() about why win32 output is ignored. */ snprintf(cmd, sizeof(cmd), ! SYSTEMQUOTE "\"%s/pg_ctl\" -l \"%s\" -D \"%s\" %s stop >> " ! "\"%s\" 2>&1" SYSTEMQUOTE, bindir, ctx->logfile, datadir, fast ? "-m fast" : "", #ifndef WIN32 ctx->logfile);