pg_upgrade大版本升级

pg_upgrade

（1）PostgreSQL提供大版本升级的一个工具，比如说从9.1到9.2，也可以一次跨多个大版本，直接从9.1到9.5等，它的优点是不需要把数据导入导出，这在数据量比较大的时候，非常方便。

（2）不适合小版本升级，比如说从9.0.1到9.0.4

（3）PostgreSQL8.4.x之后才能使用pg_upgrade

（4）如果数据量不大，可以使用pg_dump/pg_restore升级数据库

环境

Old:postgresql-9.5.9

New:postgresql-9.6.5

安装postgresql-9.5.9

[root@Darren2 ~]# mkdir -p /usr/local/pgsql/pgsql9.5.9/{data,arch}

[root@Darren2 pgsql]# groupadd dba

[root@Darren2 pgsql]# useradd -g dba -G root postgres -d /usr/local/pgsql

[root@Darren2 pgsql9.5.9]# tar xf postgresql-9.5.9.tar.gz

[root@Darren2 pgsql]# chmod -R 755 /usr/local/pgsql

[root@Darren2 pgsql]# chown -R postgres:dba /usr/local/pgsql

[root@Darren2 pgsql]# chmod -R 700 /usr/local/pgsql/pgsql9.5.9/data/

Darren2:postgres:/usr/local/pgsql:>cat .bash_profile

# .bash_profile

# Get the aliases and functions

if [ -f ~/.bashrc ]; then

. ~/.bashrc

fi

# User specific environment and startup programs

PATH=$PATH:$HOME/bin

export PGHOME=/usr/local/pgsql/pgsql9.5.9

export PGDATA=$PGHOME/data

export PATH=$PATH:$PGHOME/bin

#stty erase

set umask to 022

umask 022

PS1=`uname -n`":"'$USER'":"'$PWD'":>"; export PS1

[root@Darren2 postgresql-9.5.9]# ./configure --prefix=/usr/local/pgsql/pgsql9.5.9/

[root@Darren2 postgresql-9.5.9]# make world && make install-world

Darren2:postgres:/usr/local/pgsql:>initdb -D $PGDATA -U postgres -E UTF8 -W

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>vim pg_hba.conf

host all all 0.0.0.0/0 md5

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>vim postgresql.conf

listen_addresses = '*'

wal_level = archive

port = 5432

max_connections = 300

shared_buffers = 128MB

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

archive_mode = on

archive_command = 'test ! -f /usr/local/pgsql/pgsql9.5.9/arch/%f && cp %p /usr/local/pgsql/pgsql9.5.9/arch/%f'

#启动数据库

Darren2:postgres:/usr/local/pgsql:>pg_ctl start

#创建测试数据

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9:>cd /usr/local/pgsql/pgsql9.5.9

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9:>mkdir tbs1

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>psql

postgres=# create role cdhu1 login encrypted password '147258';

postgres=# create tablespace tbs1 location '/usr/local/pgsql/pgsql9.5.9/tbs1';

postgres=# create database testdb1 template template0 encoding 'UTF8' tablespace tbs1;

postgres=# grant all on database testdb1 to cdhu1;

postgres=# grant all on tablespace tbs1 to cdhu1;

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>psql -d testdb1 -U cdhu1

testdb1=> create table t1(id int primary key, info text);

testdb1=> insert into t1 select generate_series(1,2000000),'helloWorld';

安装新版本postgresql-9.6.5

[root@Darren2 ~]# mkdir -p /usr/local/pgsql/pgsql9.6.5/{data,arch}

[root@Darren2 pgsql9.6.5]# tar xf postgresql-9.6.5.tar.gz

[root@Darren2 pgsql]# chmod -R 755 /usr/local/pgsql

[root@Darren2 pgsql]# chown -R postgres:dba /usr/local/pgsql

[root@Darren2 pgsql]# chmod -R 700 /usr/local/pgsql/pgsql9.6.5/data/

[root@Darren2 postgresql-9.5.9]# ./configure --prefix=/usr/local/pgsql/pgsql9.6.5/

[root@Darren2 postgresql-9.5.9]# make world && make install-world

Darren2:postgres:/usr/local/pgsql/pgsql9.6.5/bin:>cd /usr/local/pgsql/pgsql9.6.5/bin

Darren2:postgres:/usr/local/pgsql:>./initdb -D /usr/local/pgsql/pgsql9.6.5/data -U postgres -E UTF8 -W

Darren2:postgres:/usr/local/pgsql/pgsql9.5.9/data:>vim pg_hba.conf

host all all 0.0.0.0/0 md5

Darren2:postgres:/usr/local/pgsql/pgsql9.6.5/data:>vim postgresql.conf

listen_addresses = '*'

wal_level = replica

port = 5431 #必须和上面端口号的不一样

max_connections = 300

shared_buffers = 128MB

logging_collector = on

log_directory = 'pg_log'

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

archive_mode = on

archive_command = 'test ! -f /usr/local/pgsql/pgsql9.6.5/arch/%f && cp %p /usr/local/pgsql/pgsql9.6.5/arch/%f'

#多实例的启动数据库服务

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.5.9/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.5.9/data/

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data

#多实例的停止数据服务（升级前需要停止数据库服务）

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.5.9/bin/pg_ctl stop -m fast -D /usr/local/pgsql/pgsql9.5.9/data

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl stop -m fast -D /usr/local/pgsql/pgsql9.6.5/data

pg_upgrade参数解析

-b, --old-bindir=BINDIR old cluster executable directory

-B, --new-bindir=BINDIR new cluster executable directory

-d, --old-datadir=DATADIR old cluster data directory

-D, --new-datadir=DATADIR new cluster data directory

-p, --old-port=PORT old cluster port number (default 50432)

-P, --new-port=PORT new cluster port number (default 50432)

-c, --check check clusters only, don't change any data

-j, --jobs number of simultaneous processes or threads to use

-k, --link link instead of copying files to new cluster

-r, --retain retain SQL and log files after success

-U, --username=NAME cluster superuser (default "postgres")

-v, --verbose enable verbose internal logging

pg_upgrade有两种升级方式，一个是缺省的通过拷贝数据文件到新的data目录下，一个是创建硬链接。拷贝的方式升级较慢，但是原库还可用；硬链接的方式升级较快，但是原库不可用。

使用9.6.5的pg_upgrade检测兼容性

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_upgrade -b /usr/local/pgsql/pgsql9.5.9/bin -B /usr/local/pgsql/pgsql9.6.5/bin -d /usr/local/pgsql/pgsql9.5.9/data -D /usr/local/pgsql/pgsql9.6.5/data -p 5432 -P 5431 -U postgres -j 8 -k -c

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 roles starting with 'pg_' ok

Checking for presence of required libraries ok

Checking database user is the install user ok

Checking for prepared transactions ok

正式升级

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_upgrade -b /usr/local/pgsql/pgsql9.5.9/bin -B /usr/local/pgsql/pgsql9.6.5/bin -d /usr/local/pgsql/pgsql9.5.9/data -D /usr/local/pgsql/pgsql9.6.5/data -p 5432 -P 5431 -U postgres -j 8 -k -r -v

......

Upgrade Complete

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

Optimizer statistics are not transferred by pg_upgrade so,

once you start the new server, consider running:

./analyze_new_cluster.sh

Running this script will delete the old cluster's data files:

./delete_old_cluster.sh

#生成2个脚本, 用于收集统计信息和删除老集群，和一些有关升级过程的日志信息

Darren2:postgres:/usr/local/pgsql:>ls -ltr

-rw-r--r--. 1 postgres dba 2215 Oct 18 01:37 pg_upgrade_dump_globals.sql

-rw-------. 1 postgres dba 2237 Oct 18 01:37 pg_upgrade_dump_13241.custom

-rw-------. 1 postgres dba 2228 Oct 18 01:37 pg_upgrade_dump_1.custom

-rw-------. 1 postgres dba 3733 Oct 18 01:37 pg_upgrade_dump_16386.custom

-rw-------. 1 postgres dba 2422 Oct 18 01:37 pg_upgrade_dump_13241.log

-rw-------. 1 postgres dba 2409 Oct 18 01:37 pg_upgrade_dump_1.log

-rw-------. 1 postgres dba 2686 Oct 18 01:37 pg_upgrade_dump_16386.log

-rw-------. 1 postgres dba 240810 Oct 18 01:37 pg_upgrade_utility.log

-rwx------. 1 postgres dba 112 Oct 18 01:37 delete_old_cluster.sh

-rwx------. 1 postgres dba 809 Oct 18 01:37 analyze_new_cluster.sh

-rw-------. 1 postgres dba 2956 Oct 18 01:37 pg_upgrade_server.log

-rw-------. 1 postgres dba 42227 Oct 18 01:37 pg_upgrade_internal.log

#启动新的数据库集群

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data

#根据脚本analyze_new_cluster.sh执行统计信息收集

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/vacuumdb -U 'postgres' --all --analyze-in-stages -p 5431

vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)

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

vacuumdb: processing database "testdb1": 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 "testdb1": Generating medium optimizer statistics (10 targets)

vacuumdb: processing database "postgres": Generating default (full) optimizer statistics

vacuumdb: processing database "template1": Generating default (full) optimizer statistics

vacuumdb: processing database "testdb1": Generating default (full) optimizer statistics

#查看数据是否存在

Darren2:postgres:/usr/local/pgsql:>psql -p 5431

postgres=# select version();

version

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

PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit

testdb1=# \dt+

List of relations

Schema | Name | Type | Owner | Size | Description

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

public | t1 | table | cdhu1 | 85 MB |

#通过上述脚本删除老数据

Darren2:postgres:/usr/local/pgsql:>cat delete_old_cluster.sh

#!/bin/sh

rm -rf '/usr/local/pgsql/pgsql9.5.9/data'

rm -rf '/usr/local/pgsql/pgsql9.5.9/tbs1/PG_9.5_201510051'

Darren2:postgres:/usr/local/pgsql:>bash delete_old_cluster.sh

最后再修改环境变量和端口号等和原来版本保持一致。

Error

Darren2:postgres:/usr/local/pgsql:>/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data

pg_ctl: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory

解决方法:

[root@Darren2 postgresql-9.6.5]# ln -s /usr/local/pgsql/pgsql9.6.5/lib/libpq.so.5 /usr/lib64/