[patch] pg_upgrade script for 8.3->8.4 - Mailing list pgsql-hackers

Hi all,

I attached pg_upgrade.sh script patch which works now for 8.3->8.4. It is
contrib module in contrib/pg_upgrade directory. Just make/make install and it works.

There are two changes from previous 8.1->8.2.

1) pg_largeobject is also upgraded
2) added check for dropped column

And now how to run a test. At first DO NOT test it on production database :-).
Please, do binary copy and start test on binary copy. Binary copy is important
because it contains a lot of updated, dead tuples and other interesting things.

Script is easy to use. You need only setup access to old and new binaries and
old and new data directory. I use following script:

#!/bin/bash

export PG_OLD_DATADIR=/zfs_test/postgres_83/data_83
export PG_OLD_BINDIR=/usr/postgres/8.3/bin
export PG_NEW_DATADIR=/zfs_test/postgres_83/data_84_upg
export PG_NEW_BASEDIR=/var/tmp/pg84_upg/
ksh ${PG_NEW_BASEDIR}/bin/pg_upgrade.sh -m

you can use also switches - try pg_upgrade.sh --help

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

The script contains some magic to handle following issues.

1) Keep relfileid of toast file same. It is important because toast pointer
contains relfileid. Currently script creates fake files with same number to
protect postgresql to create new relation with this refileid. It works but by my
opinion it is not much robust. I suggest to use following syntax:

create table foo (id int) with (relfileid=16544, reltoastid=11655,
reltoastidx=16543)

pg_dump(all) will be extended to dump this information on a request.

2) problem with dropped columns. PostgreSQL do not remove column physically from
the disk. It only marks that column as deleted and the column is ignored in the
future. But pg_dump dumps only valid column. There is idea from greg&greg to
extend create table syntax with padding column:

CREATE TABLE foo (
   col1 integer,
   NULL COLUMN(2,0),
   col2 integer
);

3) tablespace and database oid mapping. It is similar with relations. Another
problem with tablespace location is that CREATE TABLESPACE checks if directory
is empty and it fails when it contains any file/directory. Unfortunately, it is
no much good for upgrade because usually tablespace is mountpoint and any
copy/move outside a mountpoint is not wanted.

Suggested sugar syntax is:

CREATE DATABASE foobar WITH ID=17012;
CREATE TABLESPACE bar LOCATION '/dev/null/' ID=15543 IGNORECONTENT;

4) script is written in ksh. It has several problems. First is that it does not
work on win, second is that it needs extra magic to escape any stupid object
names. Bruce has suggested to rewrite it to PERL. It is maybe good idea but I'm
not really PERL guru - any volunteers?

By the way why we accept whole ASCII in name datatype (including 0-31)?


        Comments, thoughts?

            Thanks Zdenek