Re: New init script and upgrade attempt: failed

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