Shulou(Shulou.com)06/01 Report--

For upgrades in PostgreSQL, if you upgrade from a small version, such as 9.6.1 to 9.6.2 (the current latest version), you only need to replace the 9.6.1 version with the 9.6.2 version without additional operation, because the whole major version is compatible with each other and the internal storage form is also compatible. However, when it comes to cross-version upgrades such as 9.4.11 to 9.6.2, this direct replacement software is not good because the internal storage form of the cross-version has changed.

Officials have given three ways to solve cross-version upgrades:

Pg_dumpall

Pg_upgrade

By copying

Pg_dumpall is a method of logically exporting data from an old version and then importing it into a new version, which is a process of export and import.

The way to copy is to create a high version of the slave library, and so on after the data synchronization is completed, the master transformer, backup master, to achieve the purpose of upgrade.

The other is through the pg_upgrade command, which is a quick upgrade method by creating a new system table and using the old user table. It is divided into two ways: in-situ upgrade and non-in-situ upgrade, in-place upgrade needs to specify-- link parameter.

Here are the general steps to upgrade using pg_upgrade:

The example is an upgrade from 9.4.11 to 9.6.2.

1. Install the new version of software

The new version of the software needs to be compatible with the old version in configuration, and pg_upgrade checks the pg_controldata before upgrading to make sure all settings are compatible.

2. Initialize a new database with a new version

[postgres@rhel7 ~] $/ 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" default database encoding has accordingly been set to "UTF8" default text search configuration will be set to "english" .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 up pg_hba.conf to ensure that pg_upgrade connects the old and new libraries

4. Stop the old library

# create a test table [postgres@rhel7 ~] $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) # stop the old library [postgres@rhel7 ~] $/ opt/pgsql-9.4/bin/pg_ctl stop-D / usr/local/pgsql/data/waiting for server to shut down.... Doneserver stopped

5. Use pg_upgrade to perform upgrade

[postgres@rhel7] $/ 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 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 thenew cluster okFreezing all rows on thenew 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 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 parameter-b used under the introduction specifies the bin directory of the old version of the software-B specifies the bin directory of 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 the new version of the database and check it

[postgres@rhel7 ~] $/ opt/pgsql-9.6.2/bin/pg_ctl start-D / pgdata-new/-l logfile server starting [postgres@rhel7 ~] $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

Since the statistics will not be transferred to the new library system table during the upgrade process, the statistics need to be re-collected. The script for collecting statistics is most given by pg_upgrade:

[postgres@rhel7] $. / 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 the old version of software and data after the upgrade is successful.

Official document: https://www.postgresql.org/docs/9.6/static/pgupgrade.html

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.