最近新出 PostgreSQL 9.6 版，把練習環境更新一下
以下紀錄升級的流水帳，適用於 9.x 版之間的升版。
使用的環境是 CentOS 7.2 64-bit，使用 PostgreSQL 維護的 RPM 發行版。
如果使用 EnterpriseDB 公司發行的 PostgreSQL 安裝套件（可以用圖形界面安裝的），步驟是相似的。差別只在安裝程式的位置不在 /usr/pgsql-9.x/ 而是在 /opt/PostgreSQL/9.x/ 底下。對 EnterpriseDB 的企業版，則安裝位置在 /opt/PostgresPlus/9.xAS/。
PostgreSQL 升級工具 pg_upgrade 會把實體資料檔「複製」到新的資料夾，以及作相關的變動調整。
升級需求：PostgreSQL 升級程式成功升級之後，舊資料會保留著，標記成 Old，所以需要兩倍的空間；當然有需要也有沿用舊的 Cluster DIR 的方式。若要初估時間，可以用 I/O 讀寫速率來除資料大小，因為基本上資料升版大部分是以複製作業進行。
例如，升級工具提供下列額外功能：
- Dry-Run 模式：檢查有沒有升級問題。常發生的是就資料庫裡面安裝的套件（例如 postgresql96-contrib modules 或自己編譯的套件），在新版資料庫上面忘記裝上去，在移轉中便會出現沒升級成功。於是可以先用此模式去檢查。
- Parallel Jobs 模式：指定執行緒加速移轉時間
- 使用 Hard Link 模式：
- 保留資料庫 Log 選項
就可以在手冊頁面找到使用方式。
以下開始流水帳紀錄：
先列一下我裝了哪些套件
bash-4.2$ whoami postgres bash-4.2$ rpm -qa | grep postgresql postgresql95-devel-9.5.4-2PGDG.rhel7.x86_64 postgresql95-libs-9.5.4-2PGDG.rhel7.x86_64 postgresql95-server-9.5.4-2PGDG.rhel7.x86_64 postgresql95-contrib-9.5.4-2PGDG.rhel7.x86_64 postgresql95-9.5.4-2PGDG.rhel7.x86_64
很多套件沒有裝來練習，不小心漏餡惹。。。
把舊資料庫停下來
bash-4.2$ sudo service postgresql-9.5 stop
安裝新版套件
bash-4.2$ sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm Loaded plugins: fastestmirror pgdg-centos96-9.6-3.noarch.rpm | 4.7 kB 00:00:00 Examining /var/tmp/yum-root-Hds1GB/pgdg-centos96-9.6-3.noarch.rpm: pgdg-centos96-9.6-3.noarch Marking /var/tmp/yum-root-Hds1GB/pgdg-centos96-9.6-3.noarch.rpm to be installed Resolving Dependencies --> Running transaction check ---> Package pgdg-centos96.noarch 0:9.6-3 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================= Package Arch Version Repository Size ========================================================================================= Installing: pgdg-centos96 noarch 9.6-3 /pgdg-centos96-9.6-3.noarch 2.7 k Transaction Summary ========================================================================================= Install 1 Package Total size: 2.7 k Installed size: 2.7 k Is this ok [y/d/N]: y Downloading packages: Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : pgdg-centos96-9.6-3.noarch 1/1 Verifying : pgdg-centos96-9.6-3.noarch 1/1 Installed: pgdg-centos96.noarch 0:9.6-3 Complete! bash-4.2$ sudo yum install postgresql96 Loaded plugins: fastestmirror pgdg96 | 4.1 kB 00:00:00 (1/2): pgdg96/7/x86_64/group_gz | 333 B 00:00:01 (2/2): pgdg96/7/x86_64/primary_db | 98 kB 00:00:01 Loading mirror speeds from cached hostfile * base: centos.mbni.med.umich.edu * epel: mirrors.kernel.org * extras: centos.mirrors.tds.net * updates: centos.eecs.wsu.edu No package postgresql-96 available. Error: Nothing to do [brandon_hsu@pgsql pgaudit-1.0.3]$ sudo yum install postgresql96 Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: centos-distro.1gservers.com * epel: mirrors.kernel.org * extras: centos.mirrors.tds.net * updates: centos.sonn.com Resolving Dependencies --> Running transaction check ---> Package postgresql96.x86_64 0:9.6.0-1PGDG.rhel7 will be installed --> Processing Dependency: postgresql96-libs(x86-64) = 9.6.0-1PGDG.rhel7 for package: postgresql96-9.6.0-1PGDG.rhel7.x86_64 --> Running transaction check ---> Package postgresql96-libs.x86_64 0:9.6.0-1PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================= Package Arch Version Repository Size ========================================================================================= Installing: postgresql96 x86_64 9.6.0-1PGDG.rhel7 pgdg96 1.3 M Installing for dependencies: postgresql96-libs x86_64 9.6.0-1PGDG.rhel7 pgdg96 308 k Transaction Summary ========================================================================================= Install 1 Package (+1 Dependent package) Total download size: 1.6 M Installed size: 7.9 M Is this ok [y/d/N]: y Downloading packages: (1/2): postgresql96-libs-9.6.0-1PGDG.rhel7.x86_64.rpm | 308 kB 00:00:01 (2/2): postgresql96-9.6.0-1PGDG.rhel7.x86_64.rpm | 1.3 MB 00:00:02 ----------------------------------------------------------------------------------------- Total 673 kB/s | 1.6 MB 00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql96-libs-9.6.0-1PGDG.rhel7.x86_64 1/2 Installing : postgresql96-9.6.0-1PGDG.rhel7.x86_64 2/2 Verifying : postgresql96-9.6.0-1PGDG.rhel7.x86_64 1/2 Verifying : postgresql96-libs-9.6.0-1PGDG.rhel7.x86_64 2/2 Installed: postgresql96.x86_64 0:9.6.0-1PGDG.rhel7 Dependency Installed: postgresql96-libs.x86_64 0:9.6.0-1PGDG.rhel7 Complete! bash-4.2$ sudo yum install postgresql96-server Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: centos-distro.1gservers.com * epel: mirrors.kernel.org * extras: centos.mirrors.tds.net * updates: centos.eecs.wsu.edu Resolving Dependencies --> Running transaction check ---> Package postgresql96-server.x86_64 0:9.6.0-1PGDG.rhel7 will be installed --> Finished Dependency Resolution --> Finished Dependency Resolution Dependencies Resolved ========================================================================================= Package Arch Version Repository Size ========================================================================================= Installing: postgresql96-server x86_64 9.6.0-1PGDG.rhel7 pgdg96 4.2 M Transaction Summary ========================================================================================= Install 1 Package Total download size: 4.2 M Installed size: 17 M Is this ok [y/d/N]: y Downloading packages: postgresql96-server-9.6.0-1PGDG.rhel7.x86_64.rpm | 4.2 MB 00:00:02 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql96-server-9.6.0-1PGDG.rhel7.x86_64 1/1 Verifying : postgresql96-server-9.6.0-1PGDG.rhel7.x86_64 1/1 Installed: postgresql96-server.x86_64 0:9.6.0-1PGDG.rhel7 Complete! bash-4.2$ sudo yum install postgresql96-contrib Loaded plugins: fastestmirror Loading mirror speeds from cached hostfile * base: centos-distro.1gservers.com * epel: mirrors.kernel.org * extras: centos.mirrors.tds.net * updates: centos.eecs.wsu.edu Resolving Dependencies --> Running transaction check ---> Package postgresql96-contrib.x86_64 0:9.6.0-1PGDG.rhel7 will be installed --> Finished Dependency Resolution Dependencies Resolved ========================================================================================= Package Arch Version Repository Size ========================================================================================= Installing: postgresql96-contrib x86_64 9.6.0-1PGDG.rhel7 pgdg96 560 k Transaction Summary ========================================================================================= Install 1 Package Total download size: 560 k Installed size: 2.0 M Is this ok [y/d/N]: y Downloading packages: postgresql96-contrib-9.6.0-1PGDG.rhel7.x86_64.rpm | 560 kB 00:00:01 Running transaction check Running transaction test Transaction test succeeded Running transaction Installing : postgresql96-contrib-9.6.0-1PGDG.rhel7.x86_64 1/1 Verifying : postgresql96-contrib-9.6.0-1PGDG.rhel7.x86_64 1/1 Installed: postgresql96-contrib.x86_64 0:9.6.0-1PGDG.rhel7 Complete!
上面佔據了很大篇幅，Good。
接著，先起一個新資料庫，然後開始升級：注意 pg_upgrade 工具需要指定「新」「舊」PostgreSQL 程式位置和「新」「舊」資料目錄位置
bash-4.2$ sudo /usr/pgsql-9.6/bin/postgresql96-setup initdb
Initializing database ... OK
bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade -b /usr/pgsql-9.5/bin/ -B /usr/pgsql-9.6/bin/ -d ~/9.5/data/ -D ~/9.6/data/
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is the install user ok
Checking database connection settings ok
Checking for prepared transactions ok
Checking for reg* system OID user data types ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for roles starting with 'pg_' ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Analyzing all rows in the new cluster ok
Freezing all rows on the new cluster ok
Deleting files from new pg_clog ok
Copying old pg_clog to new server ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
ok
Copying user relation files
ok
Setting next OID for new cluster ok
Sync data directory to disk ok
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.2$
最後依需求，照工具建議執行 analyze_new_cluster.sh 作資料庫 Analyze，和 delete_old_cluster.sh 清掉舊資料庫資料。建議在確認新資料庫無虞之後，再執行清掉舊資料庫資料指令以防萬一。
最後啟動資料庫
Upgrading PostgreSQL from 9.3 to 9.4 on Ubuntu 14.04 LTS
下面這是一篇完整的「大型」資料庫升版的紀錄（9 TB）～升版程式運作只花 3 分鐘！
History of Upgrading 9 Tb PostgreSQL database - Blog dbi services
bash-4.2$ chkconfig postgresql-9.6 on
bash-4.2$ sudo service postgresql-9.6 start
Redirecting to /bin/systemctl start postgresql-9.6.service
bash-4.2$ psql -d postgres
psql (9.6.0)
Type "help" for help.
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit
(1 row)
在此提醒的是，在 CentOS7 這種 SystemD 下，使用 sysyemctl 系統工具啟動服務比自己用 pg_ctl -D $PGDATA start 保險一些，以免在未知狀況下被 SystemD 砍掉服務。。。
上面沒問題之後，我要清掉舊版的安裝擋了
bash-4.2$ ./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.6/bin/vacuumdb" --all --analyze-only
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "srcdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "trgdb": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "srcdb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "trgdb": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "srcdb": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
vacuumdb: processing database "trgdb": Generating default (full) optimizer statistics
Done
bash-4.2$ ./delete_old_cluster.sh
bash-4.2$ sudo yum remove pgdg-centos95-9.5-2
Loaded plugins: fastestmirror
Resolving Dependencies
--> Running transaction check
---> Package pgdg-centos95.noarch 0:9.5-2 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
=========================================================================================
Package Arch Version Repository Size
=========================================================================================
Removing:
pgdg-centos95 noarch 9.5-2 @/pgdg-centos95-9.5-2.noarch 2.2 k
Transaction Summary
=========================================================================================
Remove 1 Package
Installed size: 2.2 k
Is this ok [y/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : pgdg-centos95-9.5-2.noarch 1/1
Verifying : pgdg-centos95-9.5-2.noarch 1/1
Removed:
pgdg-centos95.noarch 0:9.5-2
Complete!
bash-4.2$ sudo yum -y remove postgresql95*
Loaded plugins: fastestmirror
Resolving Dependencies
--> Running transaction check
---> Package postgresql95.x86_64 0:9.5.4-2PGDG.rhel7 will be erased
---> Package postgresql95-contrib.x86_64 0:9.5.4-2PGDG.rhel7 will be erased
---> Package postgresql95-devel.x86_64 0:9.5.4-2PGDG.rhel7 will be erased
---> Package postgresql95-libs.x86_64 0:9.5.4-2PGDG.rhel7 will be erased
---> Package postgresql95-server.x86_64 0:9.5.4-2PGDG.rhel7 will be erased
--> Finished Dependency Resolution
Dependencies Resolved
=========================================================================================
Package Arch Version Repository Size
=========================================================================================
Removing:
postgresql95 x86_64 9.5.4-2PGDG.rhel7 @pgdg95 6.6 M
postgresql95-contrib x86_64 9.5.4-2PGDG.rhel7 @pgdg95 1.8 M
postgresql95-devel x86_64 9.5.4-2PGDG.rhel7 @pgdg95 8.1 M
postgresql95-libs x86_64 9.5.4-2PGDG.rhel7 @pgdg95 688 k
postgresql95-server x86_64 9.5.4-2PGDG.rhel7 @pgdg95 17 M
Transaction Summary
=========================================================================================
Remove 5 Packages
Installed size: 34 M
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Erasing : postgresql95-contrib-9.5.4-2PGDG.rhel7.x86_64 1/5
Erasing : postgresql95-devel-9.5.4-2PGDG.rhel7.x86_64 2/5
Erasing : postgresql95-server-9.5.4-2PGDG.rhel7.x86_64 3/5
warning: file /var/lib/pgsql/9.5/data: remove failed: No such file or directory
Erasing : postgresql95-9.5.4-2PGDG.rhel7.x86_64 4/5
Erasing : postgresql95-libs-9.5.4-2PGDG.rhel7.x86_64 5/5
Verifying : postgresql95-server-9.5.4-2PGDG.rhel7.x86_64 1/5
Verifying : postgresql95-devel-9.5.4-2PGDG.rhel7.x86_64 2/5
Verifying : postgresql95-contrib-9.5.4-2PGDG.rhel7.x86_64 3/5
Verifying : postgresql95-9.5.4-2PGDG.rhel7.x86_64 4/5
Verifying : postgresql95-libs-9.5.4-2PGDG.rhel7.x86_64 5/5
Removed:
postgresql95.x86_64 0:9.5.4-2PGDG.rhel7
postgresql95-contrib.x86_64 0:9.5.4-2PGDG.rhel7
postgresql95-devel.x86_64 0:9.5.4-2PGDG.rhel7
postgresql95-libs.x86_64 0:9.5.4-2PGDG.rhel7
postgresql95-server.x86_64 0:9.5.4-2PGDG.rhel7
Complete!
完成！
從上面的步驟，可見 PostgreSQL 資料庫要升級，可不是把資料庫 instance 資料夾換名字就 ok 的。再每次的大改版中（例如，9.4.x 到 9.5.x），資料庫的檔案組織模式都會改進與變動，所以跨了一個大版本，之間的當然就互相不認得了。所以要進行升級，需要使用 pg_dump 取出資料庫內容再匯入新版本，或是使用 pg_upgrade 才行。
pg_upgrade 也接受輸入連線字串，因此也可以作跨主機升級，不過本機升級可以避免網路速度的問題，要依照實際需要選擇。
另外，有時候可能有不直接全部升級的需求（可能有些資料不要了），可以用 pg_dump 加上 pg_restore 進行。不過所需的硬碟容量就需要準備一下了。從上面的步驟，可見 PostgreSQL 資料庫要升級，可不是把資料庫 instance 資料夾換名字就 ok 的。再每次的大改版中（例如，9.4.x 到 9.5.x），資料庫的檔案組織模式都會改進與變動，所以跨了一個大版本，之間的當然就互相不認得了。所以要進行升級，需要使用 pg_dump 取出資料庫內容再匯入新版本，或是使用 pg_upgrade 才行。
pg_upgrade 也接受輸入連線字串，因此也可以作跨主機升級，不過本機升級可以避免網路速度的問題，要依照實際需要選擇。
參考：
資料很多，可以自行上網查找
How fast is pg_upgrade anyway? | End Point BlogUpgrading PostgreSQL from 9.3 to 9.4 on Ubuntu 14.04 LTS
下面這是一篇完整的「大型」資料庫升版的紀錄（9 TB）～升版程式運作只花 3 分鐘！
History of Upgrading 9 Tb PostgreSQL database - Blog dbi services
沒有留言:
張貼留言