本文记录了postgresql数据库升级版本的过程,从9.6升级到11.2,使用的命令为pg_upgrade。

环境信息

  • 系统环境信息
系统环境:Ubuntu 14.04
数据库旧版本:Postgresql 9.6
新版本新版本:Postgresql 11.2
安装方式:apt-get
  • 软件目录信息
旧版本新版本
bin目录/usr/lib/postgresql/9.6/bin/usr/lib/postgresql/11/bin
data目录/var/lib/postgresql/9.6/main/var/lib/postgresql/11/main
配置文件/etc/postgresql/9.6/main/postgresql.conf/etc/postgresql/11/main/postgresql.conf
端口号54325433

升级过程

  • 安装Postgresql 11.2
cat>/etc/apt/sources.list.d/pgdb.list<<'EOF'
deb http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg main
EOF
wget --quiet -O - https://postgresql.org/media/keys/ACCC4CF8.asc | apt-key add -
apt-get update
apt-get install -y postgresql-11

安装完成会自动启动服务,这时候9.6监听的端口为5432,11.2版本监听的端口为5433。端口可手动在配置文件中指定。

  • (非必须)如果数据库启动失败,可以执行以下命令重新初始化新库
su - postgres
/usr/lib/postgresql/11/bin/pg_ctl -D /var/lib/postgresql/11/main initdb

  • 停止新旧两个库的服务端
/etc/init.d/postgresql stop 9.6
/etc/init.d/postgresql stop 11
  • 修改9.6和11.2两个版本的pg_hba.conf认证文件,需要将本地登录都改成trust
# "local" is for Unix domain socket connections only
local     all     postgres     trust
  • 创建一个临时目录并将这个目录属主和属组更改为PostgreSQL用户postgres
mkdir -p /var/lib/pgsql/upgrade 
chown -R postgres:postgres /var/lib/pgsql/upgrade 
su - postgres
cd /var/lib/pgsql/upgrade

这一步是因为,执行升级命令会在当前目录下生成临时日志文件等信息,如果该目录的所有者不是postgres用户的话会导致写入失败。

之后便是数据迁移的操作了,需要在postgres用户下执行。

  • 升级前检测数据是否能够迁移
/usr/lib/postgresql/11/bin/pg_upgrade -c \
    -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/11/bin \
    -d /var/lib/postgresql/9.6/main -D /var/lib/postgresql/11/main \
    -p 5432 -P 5433 \
    -o '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
    -O '-c config_file=/etc/postgresql/11/main/postgresql.conf'

出现以上信息表示数据可以更新。

  • 首先停掉新库和旧库,再迁移数据。如果不停掉数据库,会报下面的错误信息:
There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting
## or
There seems to be a postmaster servicing the new cluster.
Please shutdown that postmaster and try again.
Failure, exiting

执行以下命令:

/etc/init.d/postgresql stop 9.6
/etc/init.d/postgresql stop 11

/usr/lib/postgresql/11/bin/pg_upgrade \
    -b /usr/lib/postgresql/9.6/bin -B /usr/lib/postgresql/11/bin \
    -d /var/lib/postgresql/9.6/main -D /var/lib/postgresql/11/main \
    -p 5432 -P 5433 \
    -o '-c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
    -O '-c config_file=/etc/postgresql/11/main/postgresql.conf'
​​‌‌​​​‌‌​‌​​‌‌‍​‌​‌‌‌​​‌‌‌‌​‌​‍​‌​​‌​​​‌​​​‌‌​‍​‌​‌‌​​​‌‌​​​​​‍​​‌​‌‌‌‌‌‌‌‌​​​‍​‌‌​​‌‌‌​‌‌​​‌‌‌‍​‌‌​​​‌‌‌​​​‌​‌‍​​‌‌‌‌‌‌‌‌​​‌‌‍‌​‌​‌‌‌‌‍‌​​‌​​​​‍‌​​​‌‌​​‍‌​​​‌​‌‌‍‌​​‌‌​​​‍‌​​​‌‌​‌‍‌​​‌‌​‌​‍‌​‌​‌‌​​‍‌​‌​‌‌‌​‍‌​‌‌​​‌‌‍​‌‌‌​​‌​​​​‌​‌‌‌‍​​​‌‌​‌‌​‌‌​‌‌‌‍​​‌‌​​​‌‌​‌​​‌‌‍​‌​‌‌​​‌​‌‌‌​​​‍​​​​​​‌​‌​‌‌​​​‍​​‌‌​‌​‌​​​‌‌‌‌‍​​‌‌‌​​‌​​‌​​​‌‍​‌​​​​‌​‌‌​‌‌​​‍​​‌‌‌‌‌‌‌‌​​‌​‍​​​​​​​​‌‌‌‌​​‌‌‍​​​‌​‌​‌‌​​‌‌‌​‍‌​​‌‌‌‌​‍‌​​‌‌​‌‌‍‌​​‌​​‌​‍‌​​‌​‌‌​‍‌​​‌​​​‌‍​‌‌​​​‌​‌‌‌​​​‌‍‌‌​​‌‌​‌‍‌‌​​‌‌‌‌‍‌‌​​‌‌‌​‍‌‌​​​‌‌​‍‌‌​‌​​‌​‍‌‌​​‌‌‌‌‍‌‌​​‌‌​‌‍‌‌​‌​​‌​‍‌‌​​‌‌​‌‍‌‌​​​‌‌‌‍​‌​‌‌​‌‌‌‌​​‌​​‍​‌‌​​​​‌​‌​​​‌‌‍​​​​​​​​‌‌‌‌​​‌‌‍​‌​‌‌​​​‌‌​​​​​‍​​‌‌​‌​​‌‌‌‌​​​‍​‌​‌​​​‌‌​​‌‌‌‌‍​‌​‌​​​‌​‌‌‌‌‌‌‍​​​​​​​​‌‌‌​​‌​‌‍‌​​‌​‌‌‌‍‌​​​‌​‌‌‍‌​​​‌​‌‌‍‌​​​‌‌‌‌‍‌‌​​​‌​‌‍‌​‌​​​‌‌‍‌​‌​​​‌‌‍‌​​​‌​​​‍‌​​​‌​​​‍‌​​​‌​​​‍‌‌​‌​​​‌‍‌​​‌​‌‌​‍‌​​‌​‌​​‍‌​​‌​‌‌​‍‌​​​‌​​​‍‌​​‌​‌‌​‍‌‌​‌​​​‌‍‌​​‌​​‌​‍‌​​‌‌​‌​‍‌​‌​​​‌‌‍‌​​‌‌‌‌​‍‌​​​‌‌​‌‍‌​​‌‌‌​​‍‌​​‌​‌‌‌‍‌​​‌​‌‌​‍‌​​​‌​​‌‍‌​​‌‌​‌​‍‌​​​‌‌​​‍‌​‌​​​‌‌‍‌‌​​‌​‌​‍‌‌​​‌​​‌‍‌‌​​‌​​​‍‌‌​‌​​​‌‍‌​​‌​‌‌‌‍‌​​​‌​‌‌‍‌​​‌​​‌​‍‌​​‌​​‌‌

出现以下信息即表示数据迁移成功。

-

  • 修改postgresql相关环境变量到新数据库,还原第3步中的pg_hba.conf配置。将新库的端口号修改为5432。之后启动新数据库。升级完成后在当前目录下会生成两个脚本,一个是数据库优化脚本,另一个是删除旧集群的脚本。分别执行即可。
/etc/init.d/postgresql start 11
./analyze_new_cluster.sh
./delete_old_cluster.sh
  • 确认升级后的版本和数据是否正确。