Postgresql 9.0b2 : pg_upgrade not passing username to pgdumpall ?

This is a multi-part message in MIME format.

------_=_NextPart_001_01CB115F.6F119660
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

Hi all,

=20

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)

=20

Marco=20

=20

The output :

------------------------------

=20

=20

-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=3Dmysuperuser

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=3Doff -c
autovacuum_freeze_max_age=3D2000000000" 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

=20





=20


------_=_NextPart_001_01CB115F.6F119660
Content-Type: text/html;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml" =
xmlns=3D"http://www.w3.org/TR/REC-html40">


charset=3Dus-ascii">

Re: Postgresql 9.0b2 : pg_upgrade not passingusername to pgdumpall ?

--ELM1277225292-3537-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

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 http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

--ELM1277225292-3537-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/diff"

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;

--ELM1277225292-3537-1_
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--ELM1277225292-3537-1_--

R: Postgresql 9.0b2 : pg_upgrade not passing username to pgdumpall ?

Thank you Bruce,

I applied the patch and found two things you might want to take in conside=
ration :

1) dump.c Line 25 should be :=20

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 (vacuumd=
b of the new cluster) in which the username is missing.
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/pgs=
ql/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" d=
oes not exist

...............

If I launch it manually adding the -U mysuperuser option it works...


Hope this is somehow useful

Regards=20

Marco



-----Messaggio originale-----
Da: Bruce Momjian [mailto:bruce@momjian.us]=20
Inviato: marted=EC 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,
>=20
>
>=20
> it's my first trial with pg_upgrade so I'm surely missing something....
>=20
> I'm trying to launch pg_upgrade passing my super user name but=20
> pg_dumpall fails with a permission denied error....
>=20
> I don't see the username in the pgdumpall call....could it be this the=20
> problem ?
>=20
> 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.

--=20
Bruce Momjian http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

Re: [TESTERS] R: Postgresql 9.0b2 : pg_upgrade notpassing username to pgdumpall ?

--ELM1277323461-740-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset="US-ASCII"

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 http://momjian.us
EnterpriseDB http://enterprisedb.com

+ None of us is going to be here forever. +

--ELM1277323461-740-1_
Content-Transfer-Encoding: 7bit
Content-Type: text/x-diff
Content-Disposition: inline; filename="/rtmp/diff"

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);

--ELM1277323461-740-1_
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable


--=20
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin

--ELM1277323461-740-1_--