PostgreSQL upgrade

以升级 PostgreSQL 9.1 至 PostgreSQL 11 (跨越 9.2、9.3、9.4、9.5、9.6、10 六个大版本) 为例，本文将分享一下过去一年升级数十套 PostgreSQL 生产集群的实际经验。

此步骤同样适用于 PostgreSQL 9.1 之后的大版本升级。

数据库升级周知

提前通过邮件或 IM 周知升级信息和相关注意事项，以便相关同学能够提前安排工作并在升级期间进行上线支持。尤其是需要停服务的应用，需要提前周知终端用户停服时间窗口。

检查已有日志有无报错

有没有遇到过这样的情景？

数据库升级后，开发同学发现应用有报错，比如访问某个表没有权限，甚至是某些应用访问不了数据库，抱怨都是数据库升级的问题。此时，把问题 fix 就完事了么？当然不是，还要查明原因，到底是哪个步骤出问题了。查到最后，竟发现升级操作没有问题。这时候可能会想起来查一下之前的数据库日志，如果你还没有删除的话。最后才知道，升级前就存在此问题。

或者数据库升级后，你查看数据库日志，一看没有某些表的访问权限。此时你可能就抓瞎了，一顿操作，终于把问题 fix 了，时间也已经早已过了之前周知的时间窗口。事后再查日志，才知道这是已有问题，与数据库升级无关，白白浪费那么多宝贵的升级时间。

所以有些报错并非是数据库升级造成的，而是升级之前就已经存在问题。此步骤就是尽早发现错误，提前排除与升级无关的错误。

可以通过如下命令检查 PostgreSQL 日志：

grep -i -E 'error:|fatal:|warning:' postgresql-*|less

如有报错，查看报错的上下文：

grep -A 2 -i -E 'error:|fatal:|warning:' postgresql-*|less

Merge ACL

如果集群没有做配置管理(如 Ansible)，或者没有机制保证集群各实例 pg_hba.conf 完全一致或符合一定规则，就需要人工检查对比，避免后续出现主从切换后由于 ACL 不一致而访问不了数据库的情况。

pg_hba.conf 等配置文件建议做配置管理。人工对比的话，那么多行，还集群的各个实例都要对比。写个脚本对比合并吧，不直观且脚本有 bug 不易发现，应用后续受到影响就为时已晚。有些实例还打开了所有子网的访问权限(如 10.0.0.0/8)，你不得不整个集群都打开所有访问权限，然而 ACL 放开了，数据库安全性就降低了。

高版本集群初始化

集群初始化

此处以配置管理自动化为例。

Ansible:

ansible-playbook playbooks/cluster.yml -i inv.ini -e 'server_group=cluster1' -D

Salt:

salt -E 'db[1-2].az1|db3.az2' state.sls cluster

postgres 数据库

若在 postgres 数据库存储了信息，如一些元数据、procedure、view 等，可以选择在初始化集群时导入或后续单独导入。

如能集成在上述配置管理中最好。

Archive

需要注意的是，由于数据迁移过程中会产生大量 WAL log，搭建新集群时需要设置一下 archive_command 命令以避免产生不必要的 IO、备份等或避免 archiver 进程堵塞。在数据迁移完成后恢复 archive_command 为原有命令。

如设置为：

archive_command = 'cd .'

archive_command = '/bin/true'

如果是在本机进行数据库升级，在升级完成前，新集群需要使用临时端口。

port = 6432

PostgreSQL extensions

有些早期的 PostgreSQL extension 不是通过 CREATE EXTENSION 创建的，通过 \dx 是看不到的，pg_dump 产生的 SQL 中也没有 CREATE EXTENSION，此时要额外执行 CREATE EXTENSION 语句。

新版本的对应的 PostgreSQL extensions 相关软件已通过上述 ansible playbook 或 salt states 安装。

此处假设上述配置管理中未包含 CREATE DATABASE 及 CREATE EXTENSION 。如已包含在配置管理自动化中，可跳过此步骤。

以 CentOS 为例，通过以下命令查看旧版本数据库已安装的扩展，如

rpm -qa|grep pg

通过以下命令查看各数据库实例中通过 CREATE EXTENSION 安装的扩展，如

\dx

通过对比上述两个结果，找出未通过 CREATE EXTENSION 创建的扩展。

假设早期版本的 postgis extension 未通过 CREATE EXTENSION 创建。在新版本中通过如下方式手动创建。

psql -p 6432 -U postgres -c "CREATE ROLE alvin;"psql -p 6432 -U postgres -c "CREATE DATABASE alvindb WITH OWNER = alvin;"psql -p 6432 -d alvindb -U postgres -c "CREATE EXTENSION postgis;"

注意事项

1. 如遇到 EXTENSION 不同版本所依赖软件的兼容问题，在不影响原数据库的情况下，可能需要卸载或升级。

2. 使用源码安装的扩展或扩展相关的依赖，可以通过在其安装时源码目录执行 make uninstall 进行卸载。

   相关文章：PostGIS 扩展创建失败原因调查。

停掉受影响的定时任务

有些集群会部署 VACUUM 的定时任务，备份定时任务，或其他任务。

数据库升级期间，需要停掉受影响的定时任务，避免不必要的失败或影响数据库升级。

以 postgres 下定时任务为例。

可以手动一个一个实例查看，

su - postgrescrontab -l

也可以通过配置管理工具查看。

Ansible:

ansible -i inv.ini -m shell -a 'sudo -iu postgres crontab -l' cluster1

Salt:

salt -E 'db[1-2].az1|db3.az2' cmd.run 'sudo -iu postgres crontab -l'

持续观察数据库日志

老集群和新集群每个机器单独开一个窗口，通过如下命令持续观察日志。

此命令会自动取最新日志。

cd logls -lth|head -2|grep post && tail -f $(ls -lth|head -2|grep post|awk '{print $NF}')|grep -i -E 'error:|fatal:|warning:'

同时观察是否有写操作和错误日志

ls -lth|head -2|grep post && tail -f $(ls -lth|head -2|grep post|awk '{print $NF}')|grep -i -E 'error:|fatal:|warning:|insert |update |delete |copy '

关闭报警监控

关闭数据库相关监控，避免不必要的报警。此处包括老集群和新集群的报警。

如果报警机制设置粒度比较细的话，尽量保留原集群必要的报警，防止升级过程中对原集群产生不良影响或在升级过程中原集群有报警。

通知开发同学作数据迁移准备

数据库写操作

如应用可接受停止写操作，则需要开发同学将写操作相关任务停掉或封掉写操作相关接口。

如数据需要不断写入，则需要定制增量数据同步方案或选择合适的数据库升级方案。

可通过如下命令检查日志是否有新的写入：

grep log_statement postgresql.conflog_statement = 'mod'

cd pg_logls -lth|head -2|grep post && tail -f $(ls -lth|head -2|grep post|awk '{print $NF}')|grep -i -E 'insert |update |delete '

如仍有计划外的写入，可通过如下命令查看仍有写入的 ip，然后根据 ip 查询相应的 server group 反馈给开发同学进行确认。

grep -i -E 'insert |update |delete |copy ' postgresql-*.log|grep -Eo '[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}\.[0-9]{1,3}'|sort|uniq

数据库读操作

对于读操作，原库在数据迁移期间可提供只读服务，但在新旧库切换瞬间等时刻读操作会有秒级闪断。如读操作也不能受影响且又没有 load balancer 的话，可以考虑更优方案。

以使用 vip 为例，在整个实例升级期间：主库读操作闪断三次，不可写；各从库读操作将闪断一次。

具体如下：

1. 为保证主库绝对的只读，升级开始前将主库 vip 漂移至从库。主库读操作将出现闪断，写操作将失败。升级期间所有库可读，不可写。
2. 新旧库切换瞬间会有秒级闪断。
3. 升级完成后，将主库 vip 漂移回主库机器，主库读操作将出现闪断。写操作 vip 漂移完后将正常可写。

观察应用是否有报警或报错。如有数据库升级相关引起的报错，需要及时反馈。

检查已有数据库连接

升级前检查原数据库集群每个实例实时连接情况，升级后观察新集群实例中有无相应新连接。

PostgreSQL 9.2 and later versions

SELECT datname, usename, application_name, client_addr, client_hostname, state, COUNT(1) connectionsFROM pg_stat_activityWHERE pid <> pg_backend_pid()GROUP BY datname, usename, application_name, client_addr, client_hostname, stateORDER BY connections DESC, datname, usename, application_name, client_addr, client_hostname, state;

PostgreSQL 9.1

SELECT datname, usename, application_name, client_addr, client_hostname, COUNT(1) connectionsFROM pg_stat_activityWHERE procpid <> pg_backend_pid()GROUP BY datname, usename, application_name, client_addr, client_hostnameORDER BY connections DESC, datname, usename, application_name, client_addr, client_hostname;

主库设置为只读

实例级别只读

如果需要升级整个实例，则可以将整个实例设置为只读。

修改配置文件：

vi postgresql.conf

将 default_transaction_read_only 设置为 on :

default_transaction_read_only = on

reload 生效。已有连接不需要 terminate，即时生效。

psql -U postgres -d postgres -p 5432 -c 'SHOW default_transaction_read_only'psql -U postgres -d postgres -p 5432 -c 'SELECT pg_reload_conf()'psql -U postgres -d postgres -p 5432 -c 'SHOW default_transaction_read_only'

数据库级别只读

当需要将单个数据库或多个数据库从实例中迁移出来，需要在数据库级别设置只读。如一个实例中有多个数据库，并且有数据库比较大，如超过 1T，从性能、备份任务、磁盘空间等因素考虑，需要将数据库迁移出来；或将不同部门或业务线的数据库从共用实例中迁移出来。

执行如下 SQL 可以设置数据库级别只读：

ALTER DATABASE alvindb SET default_transaction_read_only = on;

但需要注意，只对新的连接生效，也就是迁移数据前需要 terminate 已有的连接。

PostgreSQL 9.2 and later versions

查看连接：

SELECT * FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle in transaction';SELECT * FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle';SELECT * FROM pg_stat_activity WHERE datname = 'alvindb';

terminate 连接：

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle in transaction';SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle';SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'alvindb';

PostgreSQL 9.1

查看连接：

SELECT * FROM pg_stat_activity WHERE datname = 'alvindb' AND current_query = '<IDLE> in transaction';SELECT * FROM pg_stat_activity WHERE datname = 'alvindb' AND current_query = '<IDLE>';SELECT * FROM pg_stat_activity WHERE datname = 'alvindb';

terminate 连接：

SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'alvindb' AND current_query = '<IDLE> in transaction';SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'alvindb' AND current_query = '<IDLE>';SELECT pg_terminate_backend(procpid) FROM pg_stat_activity WHERE datname = 'alvindb';

主库 vip 漂移至从库(实例级别)

升级整个实例时，为保证主库绝对的只读，应用使用 vip 连接的可以将 vip 漂移至从库。

vip 漂移完毕可通过如下命令，分别在主库和从库上查询 vip 漂移后连接状态：

netstat -tnp|grep 5432|grep 10.20.20.10netstat -tnp|grep 5432|grep 10.20.20.10|wc -l

以下步骤在 screen 中执行。

此处数据迁移采用 Easy Dump shell 脚本工具，封装了 pg_dump 的 16 种 case，设置好相关参数后，一行命令即可。

同时给出对应的 pg_dump 命令以供参考。

以下列出几种常用 case (引自 Easy Dump 文档原文)。

Dump all schema and data

If you need to dump all the databases and users in one of the following cases, just use this easiest way to dump a PostgreSQL instance.

1. The instance size is quite small
2. You have got enough time to wait for the hours long dump

Easy Dump command

bash pg_dump.sh -v -M ALL

PostgreSQL pg_dump command

time "${PGBIN}"/pg_dumpall -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -s 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -e &>>"${lv_restore_log}"

Dump all tables of a database

In some cases you need to dump the users separately and then dump the database.

1. The database size is quite small
2. You've got enough time to wait for the hours long dump
3. You are separating one database from a huge instance on which there are multiple databases or you just don't need other databases

Easy Dump command

bash pg_dump.sh -v -M DB -d alvindb

PostgreSQL pg_dump command

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Dump all tables, specified tables are dumped in parallel

In some cases you need to dump a database and dump some of the tables in parallel.

1. PostgreSQL database to be dumped contains one or more huge tables or time consuming tables
2. You need to minimize the dump time to reduce the affect on the application

Easy Dump command

bash pg_dump.sh -v -M DB -d alvindb -T "public.tb_vacuum alvin.tb_alvindb_vacuum" -L -t 3

PostgreSQL pg_dump command

Firstly dump the database with exclusion.

You can use one -T option to specify table pattern. Please note that the table pattern is not regular expression and in rare cases like same table name exists in in various schemas it might not work as expected.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public|alvin.tb_vacuum|tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

You can also use multiple -T options to specify all tables to be excluded.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${lv_dbname}" -T "public.tb_vacuum" -T "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${lv_dbname}" -e &>>"${lv_restore_log}"

Then dump specified tables in parallel.

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "public.tb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

time "${PGBIN}"/pg_dump -v -U "${DBUSER}" -h "${DBHOST}" -p "${DBPORT}" -d "${DBNAME}" -t "alvin.tb_alvindb_vacuum" 2>>"${lv_dump_log}" | "${PGBIN}"/psql -U postgres -p "${DBPORT_TARGET}" -d "${DBNAME}" -e &>>"${lv_restore_log}" &

数据迁移过程中检查

查看 CPU、load、IO 及网络流量等。

查看 dump 进程

date && ps -ef|grep -E 'dump|psql'date && ps -ef|grep 'dump'date && ps -ef|grep 'psql'

查看数据库实例大小

date && psql -p 6432 -U postgres -c '\l+'

通过脚本日志查看数据迁移进度

tail -f *.log

检查数据迁移中是否有错误

grep -i -E 'error:|fatal:|warning:' *.log

查看正在执行的 SQL

PostgreSQL 9.2 and later versions

psql -p 6432 -U postgres -c "SELECT * FROM pg_stat_activity WHERE application_name = 'psql' and pid <> pg_backend_pid() ORDER BY backend_start" -x

PostgreSQL 9.1

目前一般没有需要升级到 PostgreSQL 9.1 的，除非要迁移到 PostgreSQL 9.1 的库。

psql -p 6432 -U postgres -c "SELECT * FROM pg_stat_activity WHERE application_name = 'psql' and procpid <> pg_backend_pid() ORDER BY backend_start" -x

查看主从延迟

PostgreSQL 10 and later versions

SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS diffFROM pg_stat_replication;

PostgreSQL 9.6 and earlier versions

目前一般没有需要升级到 PostgreSQL 9.x 的。

SELECT application_name, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::bigint) as diff FROM pg_stat_replication;

查看有无锁 block 数据迁移

psql -p 6432 -U postgres -c "SELECT * FROM pg_locks WHERE not granted;" -x

查看有无 AUTOVACUUM

PostgreSQL 9.2 and later versions

psql -p 6432 -U postgres -c "SELECT * FROM pg_stat_activity WHERE query ~ 'auto' AND pid <> pg_backend_pid() ORDER BY backend_start" -x

PostgreSQL 9.1

目前一般没有需要升级到 PostgreSQL 9.1 的，除非要迁移到 PostgreSQL 9.1 的库。

psql -p 6432 -U postgres -c "SELECT * FROM pg_stat_activity WHERE current_query ~ 'auto' AND procpid <> pg_backend_pid() ORDER BY backend_start" -x

ANALYZE

为防止数据迁移后，由于统计信息等原因对查询性能产生影响，需要进行 ANALYZE。

ANALYZE TABLES

如果数据迁移中，有多个表并行迁移的话，迁移完成的表可以先进行 ANALYZE。

time psql -U postgres -d alvindb -p 6432 -U postgres -c 'ANALYZE VERBOSE alvin.tb_test' && echo Done|mail -s "ANALYZE alvin.tb_test completed" "[email protected]" &

ANALYZE DATABASE

先 ANALYZE database postgres:

time psql -U postgres -d postgres -p 6432 -U postgres -c 'ANALYZE VERBOSE' && echo Done|mail -s "ANALYZE postgres completed" "[email protected]" &

整个数据库数据迁移完成后，对整个数据库进行 ANALYZE。

time psql -U postgres -d alvindb -p 6432 -U postgres -c 'ANALYZE VERBOSE' && echo Done|mail -s "ANALYZE alvindb completed" "[email protected]" &

新老集群切换

在主从无延迟后进行新老集群切换。

修改配置文件

修改如下配置文件

vi postgresql.conf

恢复如下参数

port = 5432archive_command = 'xxx'

同时，也将如下配置文件中的 port 修改为相应的值。

recovery.conf

查看主从延迟

PostgreSQL 10 and later versions

SELECT application_name, pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) AS diffFROM pg_stat_replication;

PostgreSQL 9.6 and earlier versions

目前一般没有需要升级到 PostgreSQL 9.x 的。

SELECT application_name, pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_location(), replay_location)::bigint) as diff FROM pg_stat_replication;

老集群减少 wal 日志

无主从延迟后进行如下操作。

为了节省空间，减少老集群不必要的 wal 日志，修改老集群如下配置文件

vi postgresql.conf

修改如下参数，如

wal_keep_segments = 1000

reload 生效，并最晚在下一步执行 CHECKPOINT 后自动删除多余的 wal 日志。

psql -U postgres -d postgres -p 5432 -c 'select pg_reload_conf()'

执行 CHECKPOINT

为保证老集群能够尽快停止和新集群能够尽快启动以提供服务，在切换前执行 CHECKPOINT ：

date && time psql -p 5432 -U postgres -c 'CHECKPOINT'date && time psql -p 6432 -U postgres -c 'CHECKPOINT'

停旧实例并启动新实例

运行如下命令，在停掉原实例后紧接着启动新实例。

/usr/pg91/bin/pg_ctl stop -D /data/pg91 -mi && /usr/pg11/bin/pg_ctl stop -D /data/pg11 -mf && /usr/pg11/bin/pg_ctl start -D /data/pg11

如果只是迁移一个数据库而非整个实例，则原实例不需要停掉，只把原数据库名字改了即可。

检查 archive log

确认 archiver 进程正常运行且无 archive 滞后，

cd pg_walps -ef|grep postgres|grep 'archiver'|grep -v grep && ls -lt $(ps -ef|grep postgres|grep 'archiver'|grep -v grep|awk '{print $NF}')

可以手动在主库执行如下命令，并在 archive 目录中检查 archiver 进程是否正常工作。

SELECT pg_switch_wal();

升级其他软件

如果集群中使用的与数据库版本相关的软件，也需要相应升级。如 archive_command 中涉及的软件和备份相关的软件等。

主库 vip 漂移回主库

如升级开始时进行了 vip 漂移，此时需要将 vip 漂移回主库。

vip 漂移完毕可通过如下命令，分别在主库和从库上查询 vip 漂移后连接状态：

netstat -tnp|grep 5432|grep 10.20.20.10netstat -tnp|grep 5432|grep 10.20.20.10|wc -l

从库可以 terminate 主库 vip 的连接了：

查看连接：

SELECT * FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle in transaction';SELECT * FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle';SELECT * FROM pg_stat_activity WHERE datname = 'alvindb';

terminate 连接：

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle in transaction';SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'alvindb' AND state = 'idle';SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = 'alvindb';

检查数据库日志

通过如下命令持续观察日志，确保升级后不出现新的报错。

cd logls -lth|head -2|grep post && tail -f $(ls -lth|head -2|grep post|awk '{print $NF}')|grep -i -E 'error:|fatal:|warning:'

检查数据库连接

确保有新的数据库连接到新的集群。如应用未自动连接到数据库，则与开发同学确认是否需要重启应用。

检查并对比升级前后各种监控，如 qps、wal size per second 等确保业务恢复正常。

如果应用较多，对于其中访问 qps 较低的应用，此方法不明显，最好由业务方查看应用日志或相关业务监控。

通知业务同学验证

开发同学或测试同学进行验证，确保应用正常运行。并观察升级前后业务方面监控，如订单量的监控等。

恢复定时任务

如定时任务脚本中有使用数据库软件的绝对路径，则需要改成新版本的路径，以免定时任务报错。

脚本确认无问题后，可恢复定时任务或重新跑未完成的任务。

如使用配置管理工具，如 Ansible，修改相关配置后直接使用 Ansible 更新配置即可。

尤其是多个脚本 (vacuum、备份、定时清理数据等任务) 使用数据库软件的绝对路径时，如 /usr/pg91/bin/，使用配置管理会避免遗漏，减少人工操作，质量更有保证。

打开报警监控

检查原集群或新集群临时端口是否有监控项需要清理。无问题后，恢复监控。

数据库升级完成周知

通过邮件或 IM 周知。

检查是否有文档或系统记录与数据库版本有关并需要手动更新的信息。或是否需要关闭相关的 ticket。

后续观察监控及日志

如有异常，及时调查并解决。

如在升级过程中遇到问题，详细记录并总结在以后的数据库升级中如何优化或解决。

如已有配置管理，升级过程中如发现未考虑到的 case 也可以优化一下，或者将更多手工操作步骤配置管理化。

生活需要仪式感，工作也是。好好犒劳一下小伙伴们吧！

原文链接：
https://www.cnblogs.com/dbadaily/p/postgresql-upgrade.html
您浏览的网址与此链接不一致的话，则为未授权的转载，为了更好的阅读体验，建议阅读原文。

关注 DBA Daily 公众号，第一时间收到文章的更新。
通过一线 DBA 的日常工作，学习实用数据库技术干货！

公众号优质文章推荐

PostgreSQL VACUUM 之深入浅出

pg_dump 的十六般变化

写了一个简单易用的 shell 框架

华山论剑之 PostgreSQL sequence

GitLab supports only PostgreSQL now

MySQL or PostgreSQL?

PostgreSQL hstore Insight