PostgreSQL Upgrade Pg_upgrade Upgrade

The upgrade in PostgreSQL, if for a minor version of the upgrade, such as 9.6.1 upgrade to 9.6.2 (the current latest version), Only 9.6.2 version of the software to replace the 9.6.1 version of the software can be, do not need to do extra work, because the entire large version is compatible with the internal storage format is also compatible. However, if a cross-version upgrade is involved, such as 9.4.11 upgrade to 9.6.2, this direct replacement software is not, because the cross-version of the internal storage format has changed.

The official has given three upgrade methods to solve the cross-version upgrade:

• Pg_dumpall
  

• Pg_upgrade

• by copying

Pg_dumpall is a way to export data from the old version logically, and then import the new version, which is an export import process.

By copying the way is to create a high version from the library, and so the data synchronization after the main to prepare, to change the master, to achieve the purpose of upgrading.

Another is the upgrade of the Pg_upgrade command, which is a quick way to upgrade by creating a new system table and using the old user table. It is divided into two ways: in-place upgrade and non-in-place upgrade, in-place upgrade needs to specify the--link parameter.

Here are the general steps for upgrading using Pg_upgrade:

An example is an upgrade from 9.4.11 to 9.6.2.

1. Install the new version of the software

The new version of the software needs to be guaranteed to be compatible with older versions of the software, and Pg_upgrade will check pg_controldata before the upgrade to ensure that all settings are compatible.

2. Initialize a new database with a new version

[[Email protected] ~]$ /opt/pgsql-9.6.2/bin/initdb -d /pgdata-new/the files  belonging to this database system will be owned by user   "Postgres". This user must also own the server process. the database cluster will be initialized with locale  "En_US. UTF-8 ". the default database encoding has accordingly been set to  "UTF8". the default text search configuration will be set to  "中文版". data page checksums are disabled.fixing permissions on existing  Directory /pgdata-new ... okcreating subdirectories ... okselecting default  max_connections ... 100selecting default shared_buffers ... 128mbselecting  dynamic shared memory imPlementation ... posixcreating configuration files ... okrunning bootstrap  script ... okperforming post-bootstrap initialization ... oksyncing  data to disk ... okwarning: enabling  "Trust"  authentication for  local connectionsyou can change this by editing pg_hba.conf or  using the option -a, or--auth-local and --auth-host, the next  Time you run initdb. success. you can now start the database server using:     /opt/pgsql-9.6.2/bin/pg_ctl -d /pgdata-new/ -l logfile start

3, set pg_hba.conf, ensure pg_upgrade by connecting new and old two libraries

4. Stop the Old Library

#创建测试表 [[email protected] ~]$ psqlpsql  (9.4.11) type  "Help"  for help.                              ^postgres=# create table zx  (ID  int); create tablepostgres=# \d         list of  relations schema | name | type  |  owner   ------- -+------+-------+---------- public | zx   | table | postgres (1  row) postgres=# insert into zx values (1); insert 0 1postgres=# select  * from zx; id ----  1 (1 row) #停止旧库 [[email protected] ~]$  /opt/pgsql-9.4/bin/pg_ctl stop -d /usr/local/pgsql/data/waiting for server  to shut down.... doneserver stopped 

5. Perform an upgrade using Pg_upgrade

[[email protected] ~]$ /opt/pgsql-9.6.2/bin/pg_upgrade -d /usr/local/pgsql/data/  -d /pgdata-new/ -b /opt/pgsql-9.4/bin/ -b /opt/pgsql-9.6.2/bin/ performing  consistency checks-----------------------------checking cluster versions                                     okChecking  database user is the install user                   okChecking database connection  settings                        okChecking for prepared transactions          &nbSp;                okchecking  for reg* system OID user data types                 okchecking for contrib/isn with  bigint-passing mismatch       okchecking for roles  starting with  ' Pg_ '                        okCreating dump of global  objects                              okCreating dump of  database schemas                                                               okChecking for presence of required libraries                  okChecking  database user is the install user                   okChecking for prepared  transactions                           okIf pg_upgrade fails after  This point, you must re-initdb thenew cluster before continuing. Performing upgrade------------------Analyzing all rows in the new cluster                        okfreezing all  rows on the new cluster                         okdeleting files  from new pg_clog                              okcopying  old pg_clog to new server                             oksetting next transaction id and epoch for new cluster        okdeleting files from new pg_multixact/offsets                 okcopying old pg_multixact/offsets to new  server              okdeleting  files from new pg_multixact/members                 okCopying old pg_multixact/members to new  Server              oksetting next  multixact ID and offset for new cluster         okResetting WAL archives                                        okSetting frozenxid and minmxid counters in  new cluster       okrestoring global objects in  the new cluster                  okRestoring database schemas in the new cluster                                                                okCopying user relation files                                                               oksetting next oid  for new cluster                             oksync data  directory to disk                                   okCreating script to analyze new cluster                        okcreating script to delete old cluster              &Nbsp;         okupgrade complete----------------Optimizer  statistics are not transferred by pg_upgrade so,once you start  the new server, consider running:    ./analyze_new_ Cluster.shrunning this script will delete the old cluster ' s data  files:    ./delete_old_cluster.sh

The parameters used under the description-B specify the bin directory for the old version of the software----Specify the bin directory for the new version of the software,-d specifies the data directory for the old version, and-d specifies the data directory for the new version.

6. Start a new version of the database and check

[[email protected] ~]$/opt/pgsql-9.6.2/bin/pg_ctl start-d/pgdata-new/-L logfile server Starting[[email protected] ~]$ Psql psql (9.6.2) Type ' help ' for help.postgres=# \d List of relations Schema | Name |  Type | Owner--------+------+-------+----------Public | ZX | Table | Postgres (1 row) postgres=# select * from ZX; ID----1 (1 row)

7. Restore configuration files such as pg_hba.conf, postgresql.conf, etc.

8. Collect statistical information

Because statistics are not transmitted to the new library system tables during the upgrade process, statistics are collected again. Pg_upgrade is the most informative script to collect statistics:

[[Email protected] ~]$ ./analyze_new_cluster.sh this script will generate  minimal optimizer statistics rapidlyso your system is usable,  and then gather statistics twice morewith increasing accuracy.   When it is done, your system willhave the default level of  optimizer statistics. If you have used alter table to modify the statistics target  forany tables, you might want to remove them and restore  them afterrunning this script because they will delay fast  Statistics generation. If you would like default statistics as quickly as possible,  cancelthis script and run:     "/opt/pgsql-9.6.2/bin/vacuumdb " --all --analyze-onlyvacuumdb: processing database " Postgres ": generating minimal optimizer statistics  (1 target) vacuumdb:  processing database  "Template1": generating minimal optimizer statistics  (1  target) vacuumdb: processing database  "Postgres": generating medium  optimizer statistics  (10 targets) vacuumdb: processing database  "template1":  Generating medium optimizer statistics  (10 targets) vacuumdb: processing  database  "Postgres": generating default  (full)  optimizer  statisticsvacuumdb: processing database  "Template1": generating default  (full)  optimizer statisticsdone

9. Delete old version software and data after successful upgrade.

Official Document: Https://www.postgresql.org/docs/9.6/static/pgupgrade.html

Https://www.postgresql.org/docs/9.6/static/upgrading.html

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1910475

PostgreSQL Upgrade Pg_upgrade Upgrade