PostgreSQL uses Pg_upgrade upgrade version

Reference: http://www.postgresql.org/docs/current/static/pgupgrade.html

• Overview

There are several options to upgrade from a lower version to a new version. One is the use of Pg_dump/pg_restore, the method is more time-consuming, that is, the downtime is relatively long, especially the large data volume, but stable and easy to operate. The second is the use of replication, like MySQL, with short downtime, but relatively complex operations. The third is the use of Pg_upgrade, rebuild the system table, relative downtime is relatively short.

• Pg_upgrade

1. Syntax

Pg_upgrade-b oldbindir-b newbindir-d olddatadir-d newdatadir [option ...]

2. Principle

According to the official documentation, PostgreSQL each upgrade is mainly reflected in the system table changes, the format of the data file basically do not change, so use this feature, as long as the reconstruction of all system tables, reuse the original data file, you can complete the upgrade. Applicable for every major version upgrade after 8.4.X. It is important to note that, even so, the method has some limitations and is detailed in the note section of the official documentation.

• Upgrade steps

1. Edit the postgresql.conf, modify the old database port to 5433

2. Install the new PostgreSQL

3. Stop the old and new database service

sudo service PostgreSQL stop

4. Verifying compatibility

/usr/lib/postgresql/9.4/bin/pg_upgrade--old-datadir=/var/lib/postgresql/9.3/main/-- new-datadir=/var/lib/postgresql/9.4/main/  --old-bindir=/usr/lib/postgresql/9.3/bin/  --new-bindir=/usr/lib/postgresql/9.4/bin-p5433-p5432-o"-C config_file=/etc/postgresql/9.3 /main/postgresql.conf" -o"-C config_file=/etc/postgresql/9.4/main/ postgresql.conf" -C

5. Upgrade

/usr/lib/postgresql/9.4/bin/pg_upgrade--old-datadir=/var/lib/postgresql/9.3/main/-- new-datadir=/var/lib/postgresql/9.4/main/  --old-bindir=/usr/lib/postgresql/9.3/bin/  --new-bindir=/usr/lib/postgresql/9.4/bin-p5433-p5432-o"-C config_file=/etc/postgresql/9.3 /main/postgresql.conf" -o"-C config_file=/etc/postgresql/9.4/main/postgresql.conf "

6. Execute script rebuild statistics, delete old database (optional)

./analyze_new_cluster. SH . /delete_old_cluster. SH

PostgreSQL uses Pg_upgrade upgrade version