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

升級PostgreSQL 9.2 到PostgreSQL 9.3

本文是以CentOS 6.5 x86_64上面原安裝PostgreSQL 9.2作為說明，預設執行身分為root，如需切換為postgres，會以su - postgres開始

1.備份舊的資料

su - postgres

mkdir /tmp/pgsql

pg_dumpall > /tmp/pgsql/db.out

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

exit

2.安裝新版的PostgreSQL
2.1 安裝新版的PostgreSQL repository
到http://yum.postgresql.org/ 先點要升級的版本，如9.3，然後找到目前使用的作業系統版本，例如CentOS 6 - x86_64

cd /root/setup/database

wget http://yum.postgresql.org/9.3/redhat/rhel-6-x86_64/pgdg-centos93-9.3-1.noarch.rpm

rpm -ivh ./pgdg-centos93-9.3-1.noarch.rpm

2.2 安裝PostgreSQL新版的套件
可以先找出之前安裝了那些套件: rpm -qa | grep postgresql92

[root@www database]# rpm -qa | grep postgresql92

postgresql92-libs-9.2.9-1PGDG.rhel6.x86_64

postgresql92-9.2.9-1PGDG.rhel6.x86_64

postgresql92-server-9.2.9-1PGDG.rhel6.x86_64

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

yum install postgresql93-server postgresql93-libs postgresql93 postgresql93-contrib

3.升級前置動作
3.1修改新版PostgreSQL啟動檔的Port
因為舊版的還在run，新版PostgreSQL預設的啟動port先改為5433
編輯/etc/init.d/postgresql-9.3檔案，將

PGPORT=5432

改為

PGPORT=5433

3.2初始化新版PostgreSQL資料庫

/etc/init.d/postgresql-9.3 initdb

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

ls /var/lib/pgsql/9.3/

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

3.4停止網頁服務

service tomcat stop

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

4.開始升級資料庫
4.1 關閉資料庫

service postgresql-9.2 stop

4.2 執行pg_upgrade
切換身分為postgres(再多備份一次檔案系統)

su - postgres

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

執行pg_upgrade(注意要使用新版的PostgreSQL目錄下的pg_upgrade)

/usr/pgsql-9.3/bin/pg_upgrade -v -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.3/bin/ -d /var/lib/pgsql/9.2/data/ -D /var/lib/pgsql/9.3/data/

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

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

-bash-4.1$

4.3 調整設定檔: pg_hba.conf, postgresql.conf
pg_hba.conf通常可以直接複製過去就好:

diff /var/lib/pgsql/9.2/data/pg_hba.conf /var/lib/pgsql/9.3/data/pg_hba.conf

cp -p /var/lib/pgsql/9.2/data/pg_hba.conf /var/lib/pgsql/9.3/data/pg_hba.conf

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

diff /var/lib/pgsql/9.2/data/postgresql.conf /var/lib/pgsql/9.3/data/postgresql.conf

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

4.4 修改/etc/init.d/postgresql-9.3
退出postgres

exit

將

PGPORT=5433

改回

PGPORT=5432

5.啟動新版的PostgreSQL服務

service postgresql-9.3 start

6.升級後的優化作業

su - postgres

./analyze_new_cluster.sh

-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-9.3/bin/vacuumdb" --all --analyze-only



Generating minimal optimizer statistics (1 target)

--------------------------------------------------

vacuumdb: vacuuming database "jforum"

vacuumdb: vacuuming database "postgres"

vacuumdb: vacuuming database "template1"



The server is now available with minimal optimizer statistics.

Query performance will be optimal once this script completes.



Generating medium optimizer statistics (3 targets)

---------------------------------------------------

vacuumdb: vacuuming database "jforum"

vacuumdb: vacuuming database "postgres"

vacuumdb: vacuuming database "template1"





Generating default (full) optimizer statistics (100 targets?)

-------------------------------------------------------------

vacuumdb: vacuuming database "jforum"

vacuumdb: vacuuming database "postgres"

vacuumdb: vacuuming database "template1"





Done

-bash-4.1$

7.啟動網頁服務

service tomcat start

7.1 測試網頁服務是否正常

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

su - postgres

./delete_old_cluster.sh

8.2 移除舊版的PostgreSQL套件

yum remove postgresql92-server postgresql92-libs postgresql92 pgdg-centos92

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

rm -rf /tmp/pgsql

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

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

參考資料:
http://www.uptimemadeeasy.com/databases/upgrade-postgresql/