Skip site navigation (1) Skip section navigation (2)

[patch] pg_upgrade script for 8.3->8.4

From: Zdenek Kotala <Zdenek(dot)Kotala(at)Sun(dot)COM>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Cc: Greg Smith <gsmith(at)gregsmith(dot)com>, Gregory Stark <stark(at)enterprisedb(dot)com>
Subject: [patch] pg_upgrade script for 8.3->8.4
Date: 2008-12-04 16:57:01
Message-ID: 49380BDD.1000903@sun.com (view raw or flat)
Thread:
Lists: 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








Attachment: pgu.patch.gz
Description: application/x-gzip (8.0 KB)

Responses

pgsql-hackers by date

Next:From: Gregory StarkDate: 2008-12-04 17:04:42
Subject: Re: Simple postgresql.conf wizard
Previous:From: Kevin GrittnerDate: 2008-12-04 16:11:40
Subject: Re: Simple postgresql.conf wizard

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group