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/