[cc:ing the hackers mailing list, as this exposes an issue I hadn't
thought about]
[for the benefit of the hackers list, Dale Lovelace is testing the rpm
upgrading for potential inclusion in RedHat 6.1.  Read his transcript
carefully.  I comment below it on what the problem is.]
[version 6.0 had postgresql-6.4.2; version 6.1 potentially 6.5.1]

Dale Lovelace wrote:
>   Here is a test of upgrading a database with your method:
> 
> [root@test144 i386]# cd /mnt/redhat/comps/dist/6.0/i386/
> [root@test144 i386]# rpm -Uvh postgresql-*
> postgresql                  ##################################################
> postgresql-clients          ##################################################
> postgresql-devel            ##################################################
> [root@test144 i386]# su postgres
> [postgres@test144 i386]$ initdb --pglib=/usr/lib/pgsql/
> --pgdata=/var/lib/pgsql/
> We are initializing the database system with username postgres (uid=215).
> This user will own all the files and must also own the server process.
> 
> Creating Postgres database system directory /var/lib/pgsql//base
> 
> Creating template database in /var/lib/pgsql//base/template1
> 
> Creating global classes in /var/lib/pgsql//base
> 
> Adding template1 database to pg_database...
> 
> Vacuuming template1
> Creating public pg_user view
> Creating view pg_rules
> Creating view pg_views
> Creating view pg_tables
> Creating view pg_indexes
> Loading pg_description
> [postgres@test144 i386]$ exit
> 
> [root@test144 i386]# /etc/rc.d/init.d/postgresql start
> Starting postgresql service: postmaster [1481]
> [root@test144 i386]# su postgres
> [postgres@test144 i386]$ psql -d template1
> Welcome to the POSTGRESQL interactive sql monitor:
>   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> 
>    type \? for help on slash commands
>    type \q to quit
>    type \g or terminate with semicolon to execute query
>  You are currently connected to the database: template1

-----NOTE THIS SESSION!  WHAT'S MISSING?-------
> 
> template1=> create table dale (row1 text, row2 text, row3 text);
> CREATE
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17515 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17516 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17517 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17518 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17519 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17520 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17521 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17522 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17523 1
> template1=> insert into dale (row1, row2, row3) values ('this', 'is', 'text');
> INSERT 17524 1
> template1=> select * from dale;
> row1|row2|row3
> ----+----+----
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> this|is  |text
> (10 rows)
> 
> template1=> \q
> [postgres@test144 i386]$ exit
> [root@test144 i386]# /etc/rc.d/init.d/postgresql stop
> Stopping postgresql service:                               [  OK  ]
> [root@test144 i386]# cd /mnt/redhat/comps/dist/6.1/i386/
> [root@test144 i386]# rpm -Uvh postgresql-*
> postgresql                  ##################################################
> cannot remove /var/lib/pgsql - directory not empty
> cannot remove /usr/lib/pgsql - directory not empty
> postgresql-devel            ##################################################
> postgresql-jdbc             ##################################################
> postgresql-odbc             ##################################################
> postgresql-perl             ##################################################
> postgresql-python           ##################################################
> postgresql-server           ##################################################
> postgresql-tcl              ##################################################
> postgresql-test             ##################################################
> [root@test144 i386]# /etc/rc.d/init.d/postgresql start
> Checking postgresql installation: old version. Need to Upgrade.
> See /usr/doc/postgresql-6.5.2/README.rpm for more information.
> [root@test144 i386]# su postgres
> [postgres@test144 i386]$ postgresql-dump -t /usr/lib/pgsql/backup/db.bak -p
> /usr/lib/pgsql/backup/old -d
> /usr/bin/postgresql-dump: [: /usr/lib/pgsql/backup/db.bak: unary operator
> expected
> /usr/bin/postgresql-dump: [: /usr/lib/pgsql/backup: unary operator expected
> /usr/bin/postgresql-dump: [: /usr/lib/pgsql: unary operator expected
> /usr/bin/postgresql-dump: [: /usr/lib: unary operator expected
> /usr/bin/postgresql-dump: [: /usr: unary operator expected
> This is the ASCII output of the dump for you to check:
> 
> -- postgresql-dump  on Sat Sep 25 19:36:51 EDT 1999 from version 6.4
> \connect template1
> select datdba into table tmp_pg_shadow       from pg_database where datname =
> 'template1';
> delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
> drop table tmp_pg_shadow;
> copy pg_shadow from stdin;
> \.
> -- postgresql-dump  completed on Sat Sep 25 19:36:51 EDT 1999
> On the basis of this dump, is it OK to delete the old database? [y/n] y
> Destroying old database...
> [postgres@test144 i386]$ exit
> [root@test144 i386]# /etc/rc.d/init.d/postgresql start
> Checking postgresql installation: no database files found.
> 
> We are initializing the database system with username postgres (uid=215).
> This user will own all the files and must also own the server process.
> 
> Creating Postgres database system directory /var/lib/pgsql/base
> 
> Creating template database in /var/lib/pgsql/base/template1
> 
> Creating global classes in /var/lib/pgsql/base
> 
> Adding template1 database to pg_database...
> 
> Vacuuming template1
> Creating public pg_user view
> Creating view pg_rules
> Creating view pg_views
> Creating view pg_tables
> Creating view pg_indexes
> Loading pg_description
> Starting postgresql service: postmaster [1828]
> [root@test144 i386]# su postgres
> [postgres@test144 i386]$ psql -e template1 </usr/lib/pgsql/backup/db.bak
> -- postgresql-dump  on Sat Sep 25 19:36:51 EDT 1999 from version 6.4
> \connect template1
> connecting to new database: template1
> select datdba into table tmp_pg_shadow       from pg_database where datname =
> 'template1';
> QUERY: select datdba into table tmp_pg_shadow       from pg_database where
> datname = 'template1';
> SELECT
> delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
> QUERY: delete from pg_shadow where usesysid <> tmp_pg_shadow.datdba;
> DELETE 0
> drop table tmp_pg_shadow;
> QUERY: drop table tmp_pg_shadow;
> DROP
> copy pg_shadow from stdin;
> QUERY: copy pg_shadow from stdin;
> -- postgresql-dump  completed on Sat Sep 25 19:36:51 EDT 1999
> EOF
> [postgres@test144 i386]$ psql -d template1
> Welcome to the POSTGRESQL interactive sql monitor:
>   Please read the file COPYRIGHT for copyright terms of POSTGRESQL
> [PostgreSQL 6.5.2 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
> 
>    type \? for help on slash commands
>    type \q to quit
>    type \g or terminate with semicolon to execute query
>  You are currently connected to the database: template1
> 
> template1=> \d
> Couldn't find any tables, sequences or indices!
> template1=> select * from dale;
> ERROR:  dale: Table does not exist.
> template1=>


> 
>   I'm not really sure what is going on. I really don't have time to delve into
> it :-) If you could point me in the right direction I would sure appreciate
> it! I am wondering if those unary operator errors while running
> postgresql-dump are the root of this?

The unary operator errors are red herrings.  The core issue is an
undocumented assumption of pg_dumpall (a modified version of which is
use by postgresql-dump) -- template1 is assumed to always be empty.
[hackers -- is this an ACCURATE ASSUMPTION???]

To test the upgrading with this assumption in place, do this:

1.)	Downgrade to 6.4.2
2.)	Initdb
3.)	Su to postgres, and type the following command:  createdb dale
4.)	Perform the same psql session as you did above.
5.)	Upgrade just as you did above.
6.)	When checking for the existance of you data, issue a psql -d dale
instead of psql -d template1
7.)	The data should be there.

Wow, Dale, you are exposing some serious assumptions made in PostgreSQL.

Also, unless you guys are releasing 6.5.2, then you'll need to replace
all the '6.5.2's in postgresql.init with '6.5.1'.  Of course, if you're
shipping 6.5.2, ignore that... ;-)

Hackers:  should pg_dumpall dump template1??  If not, why not?  What
EXACTLY does template1 do in the larger scheme of things? If dumping
template1 is desired -- it can be arranged in the upgrade by modifying
pg_dumpall.

As it stands now, any data the user might, whether mistakenly or not,
place in tables in template1 will not get dumped by pg_dumpall.  Dale,
I'll get back to you ASAIC with a patch to pg_dumpall_new that will
address this, if I don't hear otherwise from the hackers list. 
Ordinarily, template1 is not used for user data storage and is normally
empty.

>   Thanks for your help with this! I am totally braindead, going home. Will be
> working on this tommorrow. If you get a chance to look at it, wouldbe great!

Glad to be of help....

Mon, 27 Sep 1999 11:30:12 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: New init script and upgrade attempt: failed
Lamar Owen <lamar.owen@wgcr.org> writes:
> Hackers:  should pg_dumpall dump template1??  If not, why not?  What
> EXACTLY does template1 do in the larger scheme of things? If dumping
> template1 is desired -- it can be arranged in the upgrade by modifying
> pg_dumpall.

template1 is copied verbatim by CREATE DATABASE to produce the initial
state of any new database.  So, people might reasonably put stuff in
it that they want copied to new DB's.  The most common example is
doing a createlang to create non-default PLs (plpgsql etc); you can do
it just once in template1 instead of every time you make a DB, assuming
that you want plpgsql in all your DBs.  I guess there could be reasons
for making a user table in template1 to be copied to each new DB.

If pg_dumpall doesn't dump the (user-added) contents of template1,
I think that's an oversight...

			regards, tom lane

Mon, 27 Sep 1999 19:35:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: [HACKERS] Re: New init script and upgrade attempt: failed 
On Mon, 27 Sep 1999, Tom Lane wrote:

> If pg_dumpall doesn't dump the (user-added) contents of template1,
> I think that's an oversight...

Thanks, Tom -- that's what I thought.  I'll tackle making and verifying changes
to pg_dumpall to do just that.  It's not a showstopper -- but it is a nuisance.

-----------------------------------------------------------------------------
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Mon, 27 Sep 1999 19:40:54 -0400
Lamar Owen <lamar.owen@wgcr.org>
Re: [HACKERS] Re: New init script and upgrade attempt: failed