Horton
Menu

Thread

  1. What is this...?

    Marc Fournier <scrappy@hub.org> — 1998-03-08T22:50:20Z

    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...

  2. Re: [HACKERS] What is this...?

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-03-08T23:34:27Z

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

  3. Re: [HACKERS] What is this...?

    Marc Fournier <scrappy@hub.org> — 1998-03-09T01:05:59Z

    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

  4. Re: [HACKERS] What is this...?

    Jan Wieck <jwieck@debis.com> — 1998-03-09T08:27:47Z

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

  5. Re: [HACKERS] What is this...?

    Bruce Momjian <maillist@candle.pha.pa.us> — 1998-03-09T14:48:50Z

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