PostgreSQL – Move Database to New Server and Update with Minimum Downtime

postgresqlpostgresql-9.3postgresql-9.6recovery

I have a PostgreSQL 9.3 database, which I have to move to a new server and update to 9.6 with minimum downtime.
I thought I could :

on the old Postgres, activate archive_command to copy WAL to a NFS directory mounted on both the old and new server
pg_dumpall the old Postgres
install a Postgres 9.6 on the new server
import the SQL dump
restart the new server in recovery mode, pointing to the NFS directory to get the WAL from the old server
So when the old and new Postgres are in sync, I can stop the old, verify that the new is up-to-date, change applications configuration to the new Postgres, and restart the applications.

I tested this with some test databases, and it does not work: on the new postgres the latest checkpoint's redo wal file has a number which is higher than the WAL number on the old. When I import the sql dump some WAL are generated, so the WAL number get incremented. So when I restart the new in recovery mode it does not read the old Postgres WAL.

I'm not sure this method is possible at all.

Could someone advise me another method if this one is not possible?

Best Answer

You can't do this, because WAL archive can only work with physical copies, while pg_dumpall and restore generates a logical copy. Also, WAL format is not compatible across major versions, so you couldn't re-play WAL from one version to another anyway. (You can do something like that with logical replication, but that doesn't exist in version 9.3, and is a rather advanced topic requiring extensive design and testing in the newer versions.)

Is it really necessary to move versions and servers at the same time? If something goes wrong during such a double migration, you've just created a big diagnostic mess by changing two major things at the same time.

The lowest down-time way to move versions (other than logical replication) is to use pg_upgrade -k. This requires you to have both old and new versions installed on the same machine simultaneously.

The lowest down-time way to move machines is to set up streaming replication; and wait for it catch up (as much as it can get while the master is still active), then shutdown all user connections to the master, wait for all WAL to replay to the replica, and promote the replica.

Related Solutions

Postgresql – Trying to set up new data_directory on Postgres 9.3

Ubuntu and Debian use pg_wrapper to manage PostgreSQL. It's in the postgresql-common package.

(As a result, this answer doesn't apply to RHEL/CentOS/Fedora).

If there's no data of value, you can just use pg_dropcluster (part of pg_wrapper) to drop (delete) your old datadir. This will destroy all data in it. Then use pg_createcluster to make a new one in /data. That's simplest, but of course it's destructive.

If you want to retain the data, you must:

Ensure that the file system on the new storage is compatible with PostgreSQL. Do not attempt to put PostgreSQL on FAT32 / vfat. I wouldn't attempt to use NTFS on Linux either.
Create every directory up to the location you want for the new datadir.
Ensure that the postgres user has at least x rights on all directories from the data dir down to the / directory, especially including the mount point. You may want to chmod a+x each directory along the path, or chgrp postgres and chmod g+x it.
stop PostgreSQL
mv the data directory, as shown by pg_lsclusters, to the desired new location. If you copy it instead, use cp -aRv to make sure permissions are preserved correctly. Don't just move all contents, move the directory its self.
Edit /etc/postgresql/mypgversion/postgresql.conf and change data_directory to point to the new data directory location.
Start PostgreSQL

I think the mistake you made was, in bold above, that you failed to stop PostgreSQL before moving the datadir. It won't stop the server because it isn't sure it'd be killing the right process, so it refuses to act. You must:

sudo pkill -u postgres

then make sure that all PostgreSQL processes have exited (very important) by making sure that this returns no results:

ps -u postgres

and only then attempt to start the server again.

Never delete postmaster.pid.

PostgreSQL – WAL Files Not Deleting During PITR

You probably have checkpoint_timeout set very high on the production system, and archive switches are being driven by checkpoint_segments, probably in conjunction with a low setting of archive_timeout.

But, checkpoint_segments has no effect during recovery. Restartpoints during recovery are driven exclusively by checkpoint_timeout. Since recovery can replay files far faster than it took production to create them, that means you have a huge accumulation of old xlog files waiting for a restartpoint to allow them to be cleared out.

One solution is to lower checkpoint_timeout by quite a bit for the system undergoing recovery, and then set it back once recovery has finished. This will cause restartpoints to happen more often, allowing the log files to be removed sooner.

Another option is to simply have a script delete the files from pg_xlog during recovery once a newer file has been restored and renamed. If the system crashes during recovery, it may then have to go refetch some of those deleted files from the archive.

The system was designed to tolerate the case where files are removed from the archive once they are fetched and so cannot be re-fetched. That is why it stores all the fetched files in pg_xlog until a restartpoint; so it has all the data it needs to restart the recovery if the recovery crashes. As long as you don't remove files from the archive, you can remove them from pg_xlog.

This situation will thankfully be improved in 9.5.

Best Answer

Related Solutions

Postgresql – Trying to set up new data_directory on Postgres 9.3

PostgreSQL – WAL Files Not Deleting During PITR

Related Question