[HACKERS] [patch] pg_upgrade script for 8.3->8.4

[Zdenek Kotala]

Hi all,




There are two changes from previous 8.1->8.2.

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





#!/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.





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



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



Suggested sugar syntax is:

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



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


                Comments, thoughts?

                        Thanks Zdenek

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. 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: 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) 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: 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. 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?

pgu.patch.gz
Description: GNU Zip compressed data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers