安道生練功房 - 分享經驗，累積智慧

andowson

升級PostgreSQL 9.4到PostgreSQL 12

0.前言
因CentOS 6.10已經EOL，故我已將其透過script轉換成Oracle Linux 6.10

目前yum.postgresql.org網站上面對RHEL/CentOS/Oracle Linux 6只有支援到PostgreSQL 12，沒有到PostgreSQL 13，因此以升級到PostgreSQL 12為目標

本文是以Oracle Linux 6.10 x86_64上面原安裝PostgreSQL 9.4作為說明，預設執行身分為root，如需切換帳號為postgres，會以su - postgres開始

警語: 升級資料庫有其風險，本文為作者自行實測心得，照作不保證一定可以成功或沒有其他未知風險，讀者應先於測試環境驗證新版資料庫對應用程式的相容性，並備妥資料庫備份還原步驟，以防萬一失敗時可以回到原先狀態。

1.安裝新版的PostgreSQL
1.1 安裝新版的PostgreSQL yum repository
先開啟網頁到https://www.postgresql.org/download/linux/redhat/，在Select platform找到目前使用的作業系統版本，例如Red Hat Enterprise, CentOS, Scientific or Oracle version 6，有個Copy script

cd /root/setup/database

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-6-x86_64/pgdg-redhat-repo-latest.noarch.rpm

1.2 安裝PostgreSQL新版的套件
安裝新版的套件(必須安裝postgresql12-contrib套件才會有pg_upgrade)

sudo yum install -y postgresql12-server postgresql12-contrib

2.升級前置動作
2.1初始化新版PostgreSQL資料庫

sudo service postgresql-12 initdb

2.2檢查是否成功建立資料庫所需目錄

ls /var/lib/pgsql/12/

cat /var/lib/pgsql/12/pgstartup.log

3.停止網頁服務

sudo service tomcat stop

注意/etc/crontab中有無設定定期檢查Tomcat是否有在跑的tomcat_monitor，有的話先註解掉該排程作業，以免系統又自己將Tomcat服務帶起來

4.開始升級資料庫
4.1.備份舊的資料
執行pg_dumpall(建議使用新版的PostgreSQL目錄下的pg_dumpall)

su - postgres

mkdir /tmp/pgsql

/usr/pgsql-12/bin/pg_dumpall > /tmp/pgsql/db.out

exit

4.2 關閉資料庫

sudo service postgresql-9.4 stop

4.3 備份檔案系統
切換身分為postgres(再多備份一次檔案系統)

su - postgres

cp -pr /var/lib/pgsql/9.4 /tmp/pgsql/

4.4 執行升版前的檢查pg_upgrade --check
執行pg_upgrade(注意要使用新版的PostgreSQL目錄下的pg_upgrade)

/usr/pgsql-12/bin/pg_upgrade -b /usr/pgsql-9.4/bin -B /usr/pgsql-12/bin -d /var/lib/pgsql/9.4/data -D /var/lib/pgsql/12/data --check

4.5 進行真正的升級動作，執行pg_upgrade

/usr/pgsql-12/bin/pg_upgrade -v -b /usr/pgsql-9.4/bin/ -B /usr/pgsql-12/bin/ -d /var/lib/pgsql/9.4/data -D /var/lib/pgsql/12/data

最後看到下面這些畫面就完成了

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

-bash-4.1$

4.6 調整設定檔: pg_hba.conf, postgresql.conf
可能新版會有新增一些參數，我們用diff比較一下差異，再把舊版修改的地方依據需要反映到新的版本去

diff /var/lib/pgsql/9.4/data/pg_hba.conf /var/lib/pgsql/12/data/pg_hba.conf

vi /var/lib/pgsql/12/data/pg_hba.conf

例如，我把local的peer改成trust，host的ident改成md5，有關replication的部分都先mark掉:


# TYPE  DATABASE        USER            ADDRESS                 METHOD



# "local" is for Unix domain socket connections only

local   all             all                                     trust

# IPv4 local connections:

host    all             all             127.0.0.1/32            md5

# IPv6 local connections:

host    all             all             ::1/128                 md5

# Allow replication connections from localhost, by a user with the

# replication privilege.

#local   replication     all                                     peer

#host    replication     all             127.0.0.1/32            ident

#host    replication     all             ::1/128                 ident

postgresql.conf可能新版會有新增一些參數，我們用diff比較一下差異，再把舊版修改的地方依據需要反映到新的版本去

diff /var/lib/pgsql/9.4/data/postgresql.conf /var/lib/pgsql/12/data/postgresql.conf

例如，我修改了:
effective_cache_size = 256MB

退出postgres

exit

5.啟動新版的PostgreSQL服務

sudo chkconfig postgresql-12 on

sudo service postgresql-12 start

[root@www ~]# sudo service postgresql-12 start

正在啟動 postgresql-12 服務：                              [  確定  ]

6.升級後的優化作業

su - postgres

./analyze_new_cluster.sh

[root@www ~]# su - postgres

-bash-4.1$ ./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:

    "/usr/pgsql-12/bin/vacuumdb" --all --analyze-only



....



Done

-bash-4.1$

退出postgres

exit

7.啟動網頁服務

sudo service tomcat start

7.1 測試網頁服務是否正常

8. 升級後的清理作業
8.1 刪除舊的資料庫存放區

su - postgres

./delete_old_cluster.sh

8.2 移除舊版的PostgreSQL套件

yum remove postgresql94*

8.3 刪除/tmp/pgsql目錄下的備份檔

rm -rf /tmp/pgsql

[選擇性作業]8.4 重新啟用排程/etc/crontab中的tomcat_monitor

至此就完成了PostgreSQL 12的升版作業

安道生練功房 - 分享經驗，累積智慧

練功房推薦書單