PG Database Upgrade step description (Pg_dumpall and Pg_upgrade)

One, database upgrade 9.4 to 9.5

1.pg_dump Import and export (used when data volume is small)

Postgres User Login

$ pg_dumpall > Db_export.out

$ env|grep LANG

Lang=en_us. UTF-8

If the database character set is en_US. UTF-8, the system character set is ZH_CN. UTF-8

The import step is

Postgres User Login

$export Lang=en_us. UTF-8

$psql-F Db_export.out postgres

2.pg_upgrade (Pg_upgrade mode)

Upgrade version: 9.4--"9.5

Upgrade Prerequisites: 9.4 of the database has complete data, 9.5 after the installation is complete only need initdb can

1) Pre-upgrade consistency check

Postgres User Login Execute the following command:

[Email protected] pgdata]# Su-postgres

[Email protected] ~]$ pwd

/home/postgres

Checks are performed using the Pg_upgrade of the 9.5 new version bin path.

[Email protected] ~]$/opt/pg/9.5/bin/pg_upgrade-c-d/pgdata94-d/pgdata-b/opt/pg/9.4/bin-b/opt/pg/9.5/bin

Performing consistency Checks

-----------------------------

Checking Cluster versions OK

Checking database user is the install user OK

Checking Database connection Settings OK

Checking for prepared transactions OK

Checking for reg* system OID user data types OK

Checking for contrib/isn with bigint-passing mismatch OK

Checking for presence of required libraries OK

Checking database user is the install user OK

Checking for prepared transactions OK

*clusters is compatible*

[Email protected] ~]$

2). Perform the upgrade

Upgrade method: Copy data file mode, 9.4 old version Pgdata data copied to 9.5 new version pgdata, slow execution, two data directory independent, save two copies of data;

Hard link, upgrade command line with the--link parameter, do not copy data only establish a connection, execute fast, save only one copy of the data.

(Note: All two databases are in a stopped state when this step is performed)

[Email protected] ~]$/opt/pg/9.5/bin/pg_upgrade-d/pgdata94-d/pgdata-b/opt/pg/9.4/bin-b/opt/pg/9.5/bin

Performing consistency Checks

-----------------------------

Checking Cluster versions OK

Checking database user is the install user OK

Checking Database connection Settings OK

Checking for prepared transactions OK

Checking for reg* system OID user data types OK

Checking for contrib/isn with bigint-passing mismatch OK

Creating dump of global objects OK

Creating Dump of database schemas

Ok

Checking for presence of required libraries OK

Checking database user is the install user OK

Checking for prepared transactions OK

If Pg_upgrade fails after this point, you must re-initdb the

New cluster before continuing.

Performing Upgrade

------------------

Analyzing all rows in the new cluster OK

Freezing all rows on the new cluster OK

Deleting files from new Pg_clog OK

Copying old Pg_clog to new server ok

Setting Next transaction ID and epoch for new cluster OK

Deleting files from new pg_multixact/offsets OK

Copying old pg_multixact/offsets to new server ok

Deleting files from new pg_multixact/members OK

Copying old pg_multixact/members to new server ok

Setting next multixact ID and offset for new cluster OK

Resetting WAL Archives OK

Setting Frozenxid and Minmxid counters in new cluster OK

Restoring global objects in the new cluster OK

Restoring database schemas in the new cluster

Ok

Creating newly-required TOAST Tables OK

Copying User relation files

Ok

Setting next OID for new cluster OK

Sync Data directory to disk OK

Creating script to analyze new cluster OK

Creating script to delete old cluster ok

Upgrade Complete

----------------

Optimizer statistics is not transferred by Pg_upgrade so,

Once you start the new server, consider running:

./analyze_new_cluster.sh

Running This script would delete the old cluster ' s data files:

./delete_old_cluster.sh

[Email protected] ~]$

[email protected] ~]$ LL

Total 1208

-RWX------. 1 Postgres postgres 749 APR 9 18:52 analyze_new_cluster.sh

-RWX------. 1 postgres postgres 9 18:52 delete_old_cluster.sh

[Email protected] ~]$

3). Start the database with the new version startup script

[[Email protected] ~]$ exit

Logout

[[email protected] pgdata]#/etc/init.d/postgresql start

Starting Postgresql:ok

[Email protected] pgdata]# Su-postgres

[Email protected] ~]$ Psql

Psql (9.5.2)

Type ' help ' for help.

postgres=# \l

List of databases

Name | Owner |   Encoding |    Collate |   Ctype | Access Privileges

--------------------+----------+----------+-------------+-------------+-----------------------

a_authentication | Vincent | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

a_resources | Vincent | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

Postgres | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 |

A_server | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | postgres=ctc/postgres+

|          |             |             | | =tc/postgres +

|          |             |             | | Acent=ctc/postgres

Template0 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | =c/postgres +

|          |             |             | | Postgres=ctc/postgres

template1 | Postgres | UTF8 | en_US. UTF-8 | en_US. UTF-8 | postgres=ctc/postgres+

|          |             |             | | =c/postgres

a_security | UCCC | UTF8 | en_US. UTF-8 | en_US. UTF-8 | A=CTC/UCCC +

|          |             |             | | =TC/UCCC +

|          |             |             | | A=ctc/uccc

(7 rows)

postgres=# \q

4). Production of new statistical data

[Email protected] ~]$./analyze_new_cluster.sh

This script would generate minimal optimizer statistics rapidly

So your system was usable, and then gather statistics twice more

With increasing accuracy. When it was done, your system would

The default level of optimizer statistics.

If you have used ALTER TABLE to modify the statistics target for

Any tables, might want to remove them and restore them after

Running this script because they would delay fast statistics generation.

If you would like default statistics as quickly as possible, cancel

This script and run:

"/opt/pg/9.5/bin/vacuumdb"--all--analyze-only

vacuumdb:processing database "a_authentication": Generating minimal Optimizer statistics (1 target)

vacuumdb:processing database "a_resources": Generating minimal Optimizer statistics (1 target)

vacuumdb:processing database "Postgres": Generating minimal Optimizer statistics (1 target)

vacuumdb:processing database "A_server": Generating minimal Optimizer statistics (1 target)

vacuumdb:processing database "template1": Generating minimal Optimizer statistics (1 target)

vacuumdb:processing database "a_security": Generating minimal Optimizer statistics (1 target)

vacuumdb:processing database "a_authentication": Generating medium Optimizer statistics (targets)

vacuumdb:processing database "a_resources": Generating medium Optimizer statistics (targets)

vacuumdb:processing database "Postgres": Generating medium Optimizer statistics (targets)

vacuumdb:processing database "A_server": Generating medium Optimizer statistics (targets)

vacuumdb:processing database "template1": Generating medium Optimizer statistics (targets)

vacuumdb:processing database "a_security": Generating medium Optimizer statistics (targets)

vacuumdb:processing database "a_authentication": Generating Default (FULL) Optimizer statistics

vacuumdb:processing database "a_resources": Generating Default (FULL) Optimizer statistics

vacuumdb:processing database "Postgres": Generating Default (FULL) Optimizer statistics

vacuumdb:processing database "A_server": Generating Default (FULL) Optimizer statistics

vacuumdb:processing database "template1": Generating Default (FULL) Optimizer statistics

vacuumdb:processing database "a_security": Generating Default (FULL) Optimizer statistics

Done

[Email protected] ~]$

5). old version Data cleanup

You can execute the delete_old_cluster.sh script after the new database has been running for a period of time, deleting the old version Pgdata directory

[Email protected] ~]$ more delete_old_cluster.sh

#!/bin/sh

Rm-rf '/pgdata94 '

[Email protected] ~]$

This article is from the "Yiyi" blog, make sure to keep this source http://heyiyi.blog.51cto.com/205455/1762076

PG Database Upgrade step description (Pg_dumpall and Pg_upgrade)