Thread: What is this...?
I might have missed somethign with all the dump/reload discussions that went on, but what is: postgres@zeus> psql -e -f db.dump template1 \connect template1 connecting to new database: template1 select datdba into table tmp_pguser from pg_database where datname = 'template1'; QUERY: select datdba into table tmp_pguser from pg_database where datname = 'template1'; SELECT delete from pg_user where usesysid <> tmp_pguser.datdba; QUERY: delete from pg_user where usesysid <> tmp_pguser.datdba; DELETE 0 drop table tmp_pguser; QUERY: drop table tmp_pguser; DROP copy pg_user from stdin; QUERY: copy pg_user from stdin; Enter info followed by a newline End with a backslash and a period on a line by itself. >> And then it just stops...? This is using pg_dump/pg_dumpall from 6.2.1, before shutting down v6.2.1, to create the db.dump file...
> drop table tmp_pguser; > QUERY: drop table tmp_pguser; > DROP > copy pg_user from stdin; > QUERY: copy pg_user from stdin; > Enter info followed by a newline > End with a backslash and a period on a line by itself. > >> > [Redirected to appropriate group. Sorry, couldn't resist. :-)] > > And then it just stops...? > > This is using pg_dump/pg_dumpall from 6.2.1, before shutting down v6.2.1, > to create the db.dump file... Sure you are not running 6.3 pg_dumpall, which has a bug? -- Bruce Momjian | 830 Blythe Avenue maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026 + If your life is a hard drive, | (610) 353-9879(w) + Christ can be your backup. | (610) 853-3000(h)
On Sun, 8 Mar 1998, Bruce Momjian wrote:
> > drop table tmp_pguser;
> > QUERY: drop table tmp_pguser;
> > DROP
> > copy pg_user from stdin;
> > QUERY: copy pg_user from stdin;
> > Enter info followed by a newline
> > End with a backslash and a period on a line by itself.
> > >>
> >
>
> [Redirected to appropriate group. Sorry, couldn't resist. :-)]
>
> >
> > And then it just stops...?
> >
> > This is using pg_dump/pg_dumpall from 6.2.1, before shutting down v6.2.1,
> > to create the db.dump file...
>
> Sure you are not running 6.3 pg_dumpall, which has a bug?
Positive...hadn't even installed v6.3 when I did my pg_dumpall :(
Marc G. Fournier
Systems Administrator @ hub.org
primary: scrappy@hub.org secondary: scrappy@{freebsd|postgresql}.org
>
>
> I might have missed somethign with all the dump/reload discussions that
> went on, but what is:
>
> postgres@zeus> psql -e -f db.dump template1
> \connect template1
> connecting to new database: template1
> select datdba into table tmp_pguser from pg_database where datname =
> 'template1';
> QUERY: select datdba into table tmp_pguser from pg_database where
> datname = 'template1';
> SELECT
> delete from pg_user where usesysid <> tmp_pguser.datdba;
> QUERY: delete from pg_user where usesysid <> tmp_pguser.datdba;
> DELETE 0
> drop table tmp_pguser;
> QUERY: drop table tmp_pguser;
> DROP
> copy pg_user from stdin;
> QUERY: copy pg_user from stdin;
^^^^^^^ COPY into a view? Cool!
> Enter info followed by a newline
> End with a backslash and a period on a line by itself.
> >>
>
>
> And then it just stops...?
>
> This is using pg_dump/pg_dumpall from 6.2.1, before shutting down v6.2.1,
> to create the db.dump file...
>
>
>
>
>
So we missed something when renaming pg_user into pg_shadow.
Damn.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#======================================== jwieck@debis.com (Jan Wieck) #
> > QUERY: copy pg_user from stdin;
>
> ^^^^^^^ COPY into a view? Cool!
>
> > Enter info followed by a newline
> > End with a backslash and a period on a line by itself.
>
> So we missed something when renaming pg_user into pg_shadow.
> Damn.
Here is a patch. Haven't tested it yet, but patch has been applied to
source tree.
---------------------------------------------------------------------------
*** ./bin/pg_dump/pg_dumpall.orig Fri Mar 6 12:17:36 1998
--- ./bin/pg_dump/pg_dumpall Fri Mar 6 12:18:26 1998
***************
*** 2,8 ****
#
# pg_dumpall [pg_dump parameters]
# dumps all databases to standard output
! # It also dumps the pg_user table
#
# to adapt to System V vs. BSD 'echo'
#set -x
--- 2,8 ----
#
# pg_dumpall [pg_dump parameters]
# dumps all databases to standard output
! # It also dumps the pg_shadow table
#
# to adapt to System V vs. BSD 'echo'
#set -x
***************
*** 30,50 ****
# we don't use POSTGRES_SUPER_USER_ID because the postgres super user id
# could be different on the two installations
#
! echo "select datdba into table tmp_pguser \
from pg_database where datname = 'template1';"
! echo "delete from pg_user where usesysid <> tmp_pguser.datdba;"
! echo "drop table tmp_pguser;"
#
# load all the non-postgres users
#
! echo "copy pg_user from stdin;"
psql -q template1 <<END
! select pg_user.*
! into table tmp_pg_user
! from pg_user
where usesysid <> $POSTGRES_SUPER_USER_ID;
! copy tmp_pg_user to stdout;
! drop table tmp_pg_user;
END
echo "${BS}."
psql -l -A -q -t| tr '|' ' ' | grep -v '^template1 ' | \
--- 30,50 ----
# we don't use POSTGRES_SUPER_USER_ID because the postgres super user id
# could be different on the two installations
#
! echo "select datdba into table tmp_pg_shadow \
from pg_database where datname = 'template1';"
! echo "delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;"
! echo "drop table tmp_pg_shadow;"
#
# load all the non-postgres users
#
! echo "copy pg_shadow from stdin;"
psql -q template1 <<END
! select pg_shadow.*
! into table tmp_pg_shadow
! from pg_shadow
where usesysid <> $POSTGRES_SUPER_USER_ID;
! copy tmp_pg_shadow to stdout;
! drop table tmp_pg_shadow;
END
echo "${BS}."
psql -l -A -q -t| tr '|' ' ' | grep -v '^template1 ' | \
***************
*** 52,58 ****
do
POSTGRES_USER="`echo \" \
select usename \
! from pg_user \
where usesysid = $DBUSERID; \" | \
psql -A -q -t template1`"
echo "${BS}connect template1 $POSTGRES_USER"
--- 52,58 ----
do
POSTGRES_USER="`echo \" \
select usename \
! from pg_shadow \
where usesysid = $DBUSERID; \" | \
psql -A -q -t template1`"
echo "${BS}connect template1 $POSTGRES_USER"
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)