Quick upgrade of PostgreSQL version

Quick upgrade of PostgreSQL version

Write in front

The PostgreSQL9.5 version supports the capability of data sharding, and for future distributed considerations, prepare to upgrade the 9.1 version of the production environment to 9.5. Data migration is required in the middle.

In the migration operation, in order to ensure the data integrity, generally need to be in the case of the database to stop the backup recovery operation, in the case of small amount of data, the backup and recovery by Pg_dumpall is very fast, but if the data volume is large, then use this method will consume a lot of time, In a production environment, it is very deadly to stop using a database for a long time.

PostgreSQL provides pg_upgrade commands for database version upgrades, and the link mode allows you to quickly migrate data.

Operation Steps:

1. Install a new version of the database

　　　　/path/to/postgresql-9.5.1-1-linux-x64.run, follow the prompts for database installation operations.

2. Stop the old version of the database

　　　　/etc/init.d/postgresql-9.1 Stop, the database operation is stopped according to the actual situation

3. Check the regional support format for old data

Zone support is specified when the database is initialized and cannot be changed, so you need to change the zone support for the new database to be the same as the old database, or the Pg_upgrade operation will error.

A, to view the old database of regional support, mainly lc-collate,lc-ctype two parameters, into the database, using \l, you can view

B. Use Initdb to specify the zone to support initializing the new database.

　　　/path/to/9.5/bin/initdb--lc-collate=zh_cn.utf8--lc-ctype=zh_cn.utf8--lc-messages=zh_cn.utf8--lc-monetary=zh_ Cn.utf8--lc-numeric=zh_cn.utf8--lc-time=zh_cn.utf8-d/path/newdata/

4, through the link mode for a rapid database upgrade, to use the link model to ensure that the old and new data directory under the same file system

Introduce some common parameters of the next Pg_upgrade: Use pg_upgrade--help To view detailed parameter configuration

　　　　-D,--old-datadir=datadir specifies the directory where the old data file resides

　　　　-D,--new-datadir=datadir specify the directory where the new data files are located

　　　　-B,--old-bindir=bindir specify an older version of the executable command directory

　　　　-B,--new-bindir=bindir specify a new version of the executable command directory

　　　　-K,--link is updated with link mode (that is, using a hard link to point the new data file to the actual location of the old data file, minus the copy time)

　　　　/path/to/9.5/bin/pg_upgrade-d/path/to/olddata-d/path/to/newdata-b/path/to/9.1/bin-b/PATH/TO/9.5/bin--link

If there is no error, complete.

5. Start a new database

　　　　

Reference:

Http://www.postgresql.org/docs/9.5/static/pgupgrade.html

Quick upgrade of PostgreSQL version