Postgresql inplace database upgrade

Question

I'm trying to find a fastest way to move postgresql database v9.1.20 from one server to another server with postgres v9.3.10.

Scenario as follows:

Production server running Ubuntu 12.04 postgresql 9.1.20, database size appox 250g

Target server we are trying to relocate on is Ubuntu 14.04 postgresq 9.3.10.

The very first attempt we are tried to experiment with was to dump database (pg_dump) from old server and restore it on the new server (pg_restore).

It has worked just fine but the time we spent to relocate is about 4 hours (pg_dump takes 3 hours and pg_restore takes 1 hour (network link 1g, SSD disks on both servers).

Total downtime in 4 hours is not acceptable.

The next attempt was to use pg_basebackup instead of pg_dump. The method has reduced backup time up to 40 mins instead of 3 hours which is acceptable.

However we cannot use dump provided by the pg_basebackup due to version incompatibility.

I had read many articles on how to provide inplace database upgrade but it seems they are all referring to upgrade on the SAME server.

So my question - how I can upgrade the database backup produced by the pg_basebackup on the server without having previous postgresql serve binaries installed?

Thanks.

Did you try to pipe the output of pg_dump directly into a psql? Something like pg_dump -h host1 dbname | psql -h host2 dbname Btw: why don't you upgrade to a more recent version like 9.4 or 9.5? — user330315
– user330315, Commented Feb 28, 2016 at 12:14
You could do a (binary) copy of the old version (binaries+data) to the new machine (compile and) install the new version there and perform a --link ("in-place") upgrade on the new machine. postgresql.org/docs/9.5/static/pgupgrade.html [start with a dry-run, of course. with the old installation still running on the old machine] — wildplasser
– wildplasser, Commented Feb 28, 2016 at 12:41

Tombart · Accepted Answer · 2017-03-06 10:56:18Z

You can perform upgrade using repmgr and pg_upgrade with minimal downtime (several minutes).

On master (postgresql 9.1) enable streaming replication. DB restart is required

hot_standby = on
wal_keep_segments = 4000 # must be hight enough for standby to catch up

On standby server install PostgreSQL 9.1 and 9.3. On both servers install repmgr 2.0. Because repmgr 3.x versions work with PostgreSQL 9.3 or higher.
Synchronize master and standby server:
```
repmgr -D /var/lib/postgresql/9.1/main -p 5432 -d repmgr -U repmgr --verbose standby clone -h psql01a.example.net --fast-checkpoint
```
Underneath pg_basebackup should be used. So this method is pretty much the same as the one you've described.
Once cloning is finished you can start register standby:
```
repmgr -f /etc/repmgr.conf standby register
service postgresql start
```
This would allow standby to catch up changes committed on the master during replication.

With both databases running (in sync) check if upgrade is possible:

/usr/lib/postgresql/9.3/bin/pg_upgrade --check \
  --old-datadir=/var/lib/postgresql/9.1/main \
  --new-datadir=/var/lib/postgresql/9.3/main \
  --old-bindir=/usr/lib/postgresql/9.1/bin \
  --new-bindir=/usr/lib/postgresql/9.3/bin -v

If you pass upgrade checks you have to stop the master. Now the downtime period comes. Promote standby to master.
```
postgres#: repmgr standby promote
service postgresql start
# if everything looks ok, just stop the server
service postgresql stop 
```
The fastest upgrade method is using links:
```
/usr/lib/postgresql/9.3/bin/pg_upgrade --link \
  --old-datadir=/var/lib/postgresql/9.1/main \
  --new-datadir=/var/lib/postgresql/9.3/main \
  --old-bindir=/usr/lib/postgresql/9.1/bin \
  --new-bindir=/usr/lib/postgresql/9.3/bin
```
Even with 200GB data the upgrade shouldn't take longer than few minutes (typically less than one minute). When things go south, it's hard to revert changes (but we'd still have functional master server). Once upgrade is finished, start the new server. Verify everything is ok and then you can safely delete the old cluster:
```
./delete_old_cluster.sh
```

Collectives™ on Stack Overflow

Postgresql inplace database upgrade

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related