Postgresql – “Zero” downtime and minimal space requirements for upgrade to PostgreSQL 9.2

migrationpostgresqlupgrade

Need to migrate from PostgreSQL 8.3.x to 9.2

Constraint 1: 8.4 and above have made datetime format to be integer by default. Wasn't so in 8.3.x. As a result pg_upgrade can't be used. Unless server is rebuilt with –disable_integer_datetime. This implies all future migrations would also have to retain this setting. This isn't acceptable in our case.

Constraint 2: The application using the 8.3.x database can't go down (there isn't any allowed downtime for at least a portion of its function). We could, however, hold off all the db updates for a short while. This gives us a window of about an hour at most.

Proposed solution (or actually – can the experts out there validate if this idea will work or if it makes any sense):
The idea is to move tables over in parallel (processes or threads) using the copy command from the old to the new version. Assuming that both old and new servers are running (on different ports) …
Question is whether the copy command on the old version can be sent "directly" into the new version? Optimally, if it can be done without an intermediate file.
Has anyone tried anything like this before? Is it practical? Does it even make sense?

Thanks in advance for your replies.

Best Answer

The two constraints that you have specified:

Very low downtime
Limited disk space use

conflict rather seriously.

You have (wisely, IMO) excluded the option of using pg_upgrade with a custom rebuilt Pg, which is the only option I see that'd satisfy both those constraints.

I suspect you'll have to drop the disk space constraint. The only way out of this that I see is to configure Slony-I to replicate from your 8.3 database to a new 9.2 instance deployed either on new storage on the same machine, or on a separate machine. Allow Slony-I to bring the replica up to date, then shut the old server down, disable replication and cut over to the new server. "New server" here can be a new 9.2 cluster running on a different port on the existing server hardware, it doesn't have to be a new physical or virtual machine.

At this point you can use streaming replication - or Slony-I again - to replicate the data back to a 9.2 instance on the original server and fail back to it. The temporary server used for migration can be retired, or (preferably, if it's real hardware) configured to operate as a streaming replication slave and WAL archiving repository for PITR and failover purposes.

Slony-I is a bit of an experience to work with and places some constraints on how you can use the DB (mainly: All DDL must go through Slony, not direct DDL commands), so I recommend taking a dump of the 8.3 server, restoring it to a test workstation, and testing the proposed replication setup there before attempting it in production. You will need to test your application on 9.2 anyway, so this is a good chance to do both. Test it on 8.3-with-slony, and on 9.2.

This might be a good opportunity to see professional support from people who have experience dealing with these issues.

Using pg_upgrade

On The New Server (pg_upgrade)

Install PostgreSQL 8.4 dependencies and hope that your copied binaries work. If they don't, then you may need to just remove these copied binaries and install 8.4 from apt.
Attempt to start the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl start -D /tmp/postgres.old/main/
If that works, stop the service /tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl stop -D /tmp/postgres.old/main/
Now try your pg_upgrade script

Without Using pg_upgrade

Create a VM or use an old, decommed server and install postgresql 8.4
Initialize your server using initdb (or pg_createcluster)
Copy your backed up data over that which was just created
Start postgresql
Backup the data using pg_dump
Copy the dump file to the "new server"
Restore to a new database on the new server

Postgresql – How to Restore Postgresql Cluster from data directory

Here's what I'd do:

Download postgresql-8.1_8.1.8-1.diff.gz, postgresql-8.1_8.1.8-1.dsc and postgresql-8.1_8.1.8.orig.tar.gz from http://archive.debian.org/debian/pool/main/p/postgresql-8.1/ on a machine running the same OS as what you intend to install on.
dpkg-source -x postgresql-8.1_8.1.8-1.dsc
Examine debian/rules for configure options.

This shows:

DEB_CONFIGURE_EXTRA_FLAGS := --mandir=\$${prefix}/share/postgresql/8.1/man \
                    --with-docdir=\$${prefix}/share/doc/postgresql-doc-8.1 \
                    --datadir=\$${prefix}/share/postgresql/8.1 \
                    --bindir=\$${prefix}/lib/postgresql/8.1/bin \
                    --includedir=\$${prefix}/include/postgresql/ \
                    --enable-nls \
                    --enable-integer-datetimes \
                    --enable-thread-safety \
                    --enable-debug \
                    --disable-rpath \
                    --with-tcl \
                    --with-perl \
                    --with-python \
                    --with-pam \
                    --with-krb5 \
                    --with-openssl \
                    --with-gnu-ld \
                    --with-tclconfig=/usr/lib/tcl$(TCL_VER) \
                    --with-tkconfig=/usr/lib/tk$(TCL_VER) \
                    --with-includes=/usr/include/tcl$(TCL_VER) \
                    --with-pgport=5432 \
                    $(ARCH_OPTS) \
                    CFLAGS='$(CFLAGS)' \
                    LDFLAGS='$(LDFLAGS)'

This should help you do your own source build. In particular I think you probably need --enable-integer-datetimes.

Alternately, you could dpkg-buildpackage -rfakeroot -uc -b to build the package, then copy it to the target server and install it.

Best Answer

Related Solutions

Postgresql – How to copy databases from Postgres 8.4 to Postgres 9.1

Using pg_upgrade

Without Using pg_upgrade

Postgresql – How to Restore Postgresql Cluster from data directory

Related Question