https://forum.andowson.com/posts/list/12.page JForum - http://www.jforum.net
1.安裝新版的PostgreSQL
1.1 安裝新版的PostgreSQL repository
先開啟網頁到[url]http://yum.postgresql.org/[/url]，在Available PostgreSQL Releases點選我們要升級的版本的連結，這裡應該是9.4，然後找到目前使用的作業系統版本，例如CentOS 6 - x86_64，在這個連結上按滑鼠右鍵複製連結，取得URL，回到PuTTY視窗
[code]cd /root/setup/database
yum install -y http://yum.postgresql.org/9.4/redhat/rhel-6-x86_64/pgdg-centos94-9.4-1.noarch.rpm[/code]

1.2 安裝PostgreSQL新版的套件
先找出之前安裝了那些套件: rpm -qa | grep postgresql93
[code][root@www database]# rpm -qa | grep postgresql93
postgresql93-9.3.6-1PGDG.rhel6.x86_64
postgresql93-libs-9.3.6-1PGDG.rhel6.x86_64
postgresql93-server-9.3.6-1PGDG.rhel6.x86_64
postgresql93-contrib-9.3.6-1PGDG.rhel6.x86_64[/code]

安裝新版的套件(必須安裝postgresql94-contrib套件才會有pg_upgrade)
[code]yum install postgresql94-server postgresql94-libs postgresql94 postgresql94-contrib[/code]


2.升級前置動作
2.1初始化新版PostgreSQL資料庫
[code]/etc/init.d/postgresql-9.4 initdb[/code]


2.2檢查是否成功建立資料庫所需目錄
[code]ls /var/lib/pgsql/9.4/
cat /var/lib/pgsql/9.4/pgstartup.log[/code]


3.停止網頁服務
[code]service tomcat stop[/code]

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

4.開始升級資料庫
4.1.備份舊的資料
[code]su - postgres
mkdir /tmp/pgsql
pg_dumpall > /tmp/pgsql/db.out
exit [/code]
4.2 關閉資料庫
[code]service postgresql-9.3 stop[/code]

4.3 備份檔案系統
切換身分為postgres(再多備份一次檔案系統)
[code]su - postgres
cp -pr /var/lib/pgsql/9.3 /tmp/pgsql/[/code]

4.4 執行升版前的檢查pg_upgrade --check
執行pg_upgrade(注意要使用新版的PostgreSQL目錄下的pg_upgrade)
[code]/usr/pgsql-9.4/bin/pg_upgrade -b /usr/pgsql-9.3/bin -B /usr/pgsql-9.4/bin -d /var/lib/pgsql/9.3/data -D /var/lib/pgsql/9.4/data --check[/code]

4.5 進行真正的升級動作，執行pg_upgrade
[code]/usr/pgsql-9.4/bin/pg_upgrade -v -b /usr/pgsql-9.3/bin/ -B /usr/pgsql-9.4/bin/ -d /var/lib/pgsql/9.3/data -D /var/lib/pgsql/9.4/data[/code]

最後看到下面這些畫面就完成了
[code]reating 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$
[/code]

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

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

例如，我修改了:
[code]effective_cache_size = 256MB [/code]

退出postgres
[code]exit[/code]

5.啟動新版的PostgreSQL服務
[code]service postgresql-9.4 start[/code]
[root@www database]# service postgresql-9.4 start
正在啟動 postgresql-9.4 服務： [ 確定 ]

6.升級後的優化作業
[code]su - postgres
./analyze_new_cluster.sh

[root@www database]# 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-9.4/bin/vacuumdb" --all --analyze-only

vacuumdb: vacuuming database "jforum"
Generating minimal optimizer statistics (1 target)
vacuumdb: vacuuming database "postgres"
Generating minimal optimizer statistics (1 target)
vacuumdb: vacuuming database "template1"
Generating minimal optimizer statistics (1 target)
vacuumdb: vacuuming database "jforum"
Generating medium optimizer statistics (10 targets)
vacuumdb: vacuuming database "postgres"
Generating medium optimizer statistics (10 targets)
vacuumdb: vacuuming database "template1"
Generating default (full) optimizer statistics
vacuumdb: vacuuming database "jforum"
Generating default (full) optimizer statistics
vacuumdb: vacuuming database "postgres"
Generating default (full) optimizer statistics
vacuumdb: vacuuming database "template1"

Done
-bash-4.1$
[/code]

7.啟動網頁服務
[code]service tomcat start[/code]

7.1 測試網頁服務是否正常

8. 升級後的清理作業
8.1 刪除舊的資料庫存放區
[code]su - postgres
./delete_old_cluster.sh[/code]


8.2 移除舊版的PostgreSQL套件
[code]yum remove postgresql93* pgdg-centos93[/code]


8.3 刪除/tmp/pgsql目錄下的備份檔
[code]rm -rf /tmp/pgsql[/code]


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

至此就完成了PostgreSQL 9.4的升版作業]]> https://forum.andowson.com/posts/preList/636/1329.page https://forum.andowson.com/posts/preList/636/1329.page GMT