PostgreSQL中的升级，如果针对小版本的升级，比如9.6.1升级到9.6.2(当前的最新版本)，只需要用9.6.2版本的软件替换9.6.1版本的软件即可，不需要做额外的操作，因为整个大版本是相互兼容的，内部存储形式也是兼容的。但如果涉及到跨大版本升级比如9.4.11升级到9.6.2，这种直接替换软件就不行了，因为跨版本的内部存储形式发生了变化。
官方给了三种升级的方式来解决跨版本升级：
-
pg_dumpall
-
pg_upgrade
-
通过复制
pg_dumpall是一种把数据从旧版本逻辑导出，再导入新版本的方法，就是一个导出导入的过程。
通过复制的方式是创建一个高版本的从库，等数据同步完后主变备，备变主，达到升级的目的。
再一种是通过pg_upgrade命令的升级方式，它是一种快速升级的方法，通过创建新的系统表并使用旧的用户表的方式进行升级。它又分为两种方式：原地升级和非原地升级，原地升级需要指定--link参数。
下面介绍一下使用pg_upgrade做升级的大体步骤：
示例是从9.4.11升级到9.6.2。
1、安装新版本软件
新版本的软件需要保证与旧版本的软件在配置上兼容，pg_upgrade会在升级前检查pg_controldata，确保所有的设置是兼容的。
2、用新版本初始化一个新的数据库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
[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"
.
The
default
database
encoding has accordingly been
set
to
"UTF8"
.
The
default
text search configuration will be
set
to
"english"
.
Data page checksums are disabled.
fixing permissions
on
existing directory /pgdata-new ... ok
creating subdirectories ... ok
selecting
default
max_connections ... 100
selecting
default
shared_buffers ... 128MB
selecting
dynamic
shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data
to
disk ... ok
WARNING: enabling
"trust"
authentication
for
local
connections
You 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、设置pg_hba.conf，保证pg_upgrade通过连接新旧两个库
4、停止旧库
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
#创建测试表
[postgres@rhel7 ~]$ psql
psql (9.4.11)
Type
"help"
for
help.
^
postgres=#
create
table
zx (id
int
);
CREATE
TABLE
postgres=# \d
List
of
relations
Schema
|
Name
| Type | Owner
--------+------+-------+----------
public
| zx |
table
| postgres
(1 row)
postgres=#
insert
into
zx
values
(1);
INSERT
0 1
postgres=#
select
*
from
zx;
id
----
1
(1 row)
#停止旧库
[postgres@rhel7 ~]$ /opt/pgsql-9.4/bin/pg_ctl stop -D /usr/
local
/pgsql/data/
waiting
for
server
to
shut down.... done
server stopped
|
5、使用pg_upgrade执行升级
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
|
[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 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
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
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
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
|
介绍下使用的参数-b指定旧版本软件的bin目录-B指定新版本软件的bin目录，-d指定旧版本对应的数据目录，-D指定新版本对应的数据目录。
6、启动新版本数据库并做检查
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
|
[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、恢复配置文件如pg_hba.conf、postgresql.conf等
8、收集统计信息
由于升级过程中不会把统计信息传到新库系统表中，需要重新收集统计信息。pg_upgrade的最给出了收集统计信息的脚本：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
[postgres@rhel7 ~]$ ./analyze_new_cluster.sh
This script will generate minimal optimizer
statistics
rapidly
so your system
is
usable,
and
then
gather
statistics
twice more
with
increasing accuracy.
When
it
is
done, your system will
have the
default
level
of
optimizer
statistics
.
If you have used
ALTER
TABLE
to
modify
the
statistics
target
for
any
tables, you might want
to
remove them
and
restore them
after
running this script because they will delay fast
statistics
generation.
If you would
like
default
statistics
as
quickly
as
possible, cancel
this script
and
run:
"/opt/pgsql-9.6.2/bin/vacuumdb"
--all --analyze-only
vacuumdb: 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
statistics
vacuumdb: processing
database
"template1"
: Generating
default
(
full
) optimizer
statistics
Done
|
9、升级成功后删除旧版本软件和数据。
官方文档：https://www.postgresql.org/docs/9.6/static/pgupgrade.html
https://www.postgresql.org/docs/9.6/static/upgrading.html
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1910475，如需转载请自行联系原作者
