Postgres 升級有數個方案可以使用
1.使用 pg_dump 跟pg_restore 做匯出匯入,但資料量大的話匯入時間就會拉長
2.使用 pg_upgrade
3.使用同步 舊版本向新版本資料同步 , 但我這邊 9.2 - 9.6 版本差異太大,會出現以下錯誤
pg_basebackup: incompatible server version 9.2.15; client does not support streaming from server versions older than 9.3
所以我這邊使用 pg_upgrade 升級
Postgres 升級 9.2 > 9.6
使用 pg_upgrade 指令
環境說明
9.2
PGDATA : /datafile/9.2
bin : /usr/pgsql-9.2/bin
9.6
PGDATA : /datafile/9.6
bin : /usr/pgsql-9.6/bin
1.先安裝 Postgres 9.6
使用initdb 建立一個新的 9.6 資料庫
-bash-4.2$ /usr/pgsql-9.6/bin/initdb -D /datafile/9.6
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.
The database cluster will be initialized with locale "zh_TW.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_TW.UTF-8"
The default text search configuration will be set to "simple".
Data page checksums are disabled.
fixing permissions on existing directory /datafile/9.6 ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.
Success. You can now start the database server using:
/usr/pgsql-9.6/bin/pg_ctl -D /datafile/9.6 -l logfile start
2.停止 9.2 資料庫
-bash-4.2$ pg_ctl stop -m i
正在等候伺服器關閉.... 完成
伺服器已停止
-bash-4.2$
-bash-4.2$
3.使用 pg_upgrade 指令升級
/usr/pgsql-9.6/bin/pg_upgrade -d /datafile/9.2 -D /datafile/9.6 -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.6/bin/
-d 指定舊版本的資料夾 -D 指定新版本資料夾
-bash-4.2$
-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade -d /datafile/9.2 -D /datafile/9.6 -b /usr/pgsql-9.2/bin/ -B /usr/pgsql-9.6/bin/
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
Checking for invalid "line" user columns 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
Setting oldest multixact ID on 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
Setting minmxid counter in 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$
完成!!!!!
可以看到最後的訊息, Postgres 正在檢查及複製資料檔案
4.啟動 9.6 版本資料庫
-bash-4.2$ /usr/pgsql-9.6/bin/pg_ctl -D /datafile/9.6 -l logfile start
server starting
-bash-4.2$
-bash-4.2$
-bash-4.2$ ps -ef|grep postgres
root 3048 3009 0 15:05 pts/1 00:00:00 su - postgres
postgres 3049 3048 0 15:05 pts/1 00:00:00 -bash
root 4301 2666 0 15:56 pts/0 00:00:00 su - postgres
postgres 4302 4301 0 15:56 pts/0 00:00:00 -bash
postgres 4708 1 0 16:09 pts/0 00:00:00 /usr/pgsql-9.6/bin/postgres -D /datafile/9.6
postgres 4709 4708 0 16:09 ? 00:00:00 postgres: logger process
postgres 4711 4708 0 16:09 ? 00:00:00 postgres: checkpointer process
postgres 4712 4708 0 16:09 ? 00:00:00 postgres: writer process
postgres 4713 4708 0 16:09 ? 00:00:00 postgres: wal writer process
postgres 4714 4708 0 16:09 ? 00:00:00 postgres: autovacuum launcher process
postgres 4715 4708 0 16:09 ? 00:00:00 postgres: stats collector process
postgres 4716 4302 0 16:09 pts/0 00:00:00 ps -ef
postgres 4717 4302 0 16:09 pts/0 00:00:00 grep --color=auto postgres
-bash-4.2$
-bash-4.2$ /usr/pgsql-9.6/bin/psql
psql (9.6.3)
輸入 "help" 顯示說明。
postgres=#
postgres=# \l
資料庫清單
名稱 | 擁有者 | 字元編碼 | Collate | 轉換型別 | 存取權限
-----------+----------+----------+-------------+-------------+-----------------------
TEST | TEST | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 |
postgres | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 |
template0 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | postgres=CTc/postgres+
| | | | | =c/postgres
(4 筆資料列)
postgres=#
postgres=#
5.配置 9.6 postgres.conf、pg_hba.conf 文件及修改.bash_profile
6.重新收集統計資訊
這時9.6 資料夾下有一個 analyze_new_cluster.sh , 是postgres 自動生成的檔案
-bash-4.2$ cd /datafile/9.6/
-bash-4.2$
-bash-4.2$ ls -al
總計 144
drwx------. 20 postgres postgres 4096 6月 20 16:14 .
drwxr-xr-x 5 root root 4096 6月 20 15:22 ..
-rwx------ 1 postgres postgres 755 6月 20 16:07 analyze_new_cluster.sh
drwx------ 6 postgres postgres 4096 6月 20 16:03 base
-rwx------ 1 postgres postgres 34 6月 20 16:07 delete_old_cluster.sh
drwx------ 2 postgres postgres 4096 6月 20 16:15 global
-rw------- 1 postgres postgres 364 6月 20 16:14 logfile
drwx------ 2 postgres postgres 4096 6月 20 16:03 pg_clog
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_commit_ts
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_dynshmem
-rw------- 1 postgres postgres 4502 6月 20 15:55 pg_hba.conf
-rw------- 1 postgres postgres 1636 6月 20 15:54 pg_ident.conf
drwx------ 2 postgres postgres 4096 6月 20 16:03 pg_log
drwx------ 4 postgres postgres 4096 6月 20 15:54 pg_logical
drwx------ 4 postgres postgres 4096 6月 20 15:54 pg_multixact
drwx------ 2 postgres postgres 4096 6月 20 16:14 pg_notify
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_replslot
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_serial
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_snapshots
drwx------ 2 postgres postgres 4096 6月 20 16:14 pg_stat
drwx------ 2 postgres postgres 4096 6月 20 16:16 pg_stat_tmp
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_subtrans
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_tblspc
drwx------ 2 postgres postgres 4096 6月 20 15:54 pg_twophase
-rw------- 1 postgres postgres 4 6月 20 15:54 PG_VERSION
drwx------ 3 postgres postgres 4096 6月 20 16:07 pg_xlog
-rw------- 1 postgres postgres 88 6月 20 15:54 postgresql.auto.conf
-rw------- 1 postgres postgres 22233 6月 20 16:11 postgresql.conf
-rw------- 1 postgres postgres 49 6月 20 16:14 postmaster.opts
-rw------- 1 postgres postgres 77 6月 20 16:14 postmaster.pid
-bash-4.2$
-bash-4.2$ ./analyze_new_cluster.sh
-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 "TEST": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "postgres": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "template1": Generating minimal optimizer statistics (1 target)
vacuumdb: processing database "TEST": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "postgres": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "template1": Generating medium optimizer statistics (10 targets)
vacuumdb: processing database "TEST": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
Done
-bash-4.2$
-bash-4.2$
/usr/pgsql-9.6/bin/vacuumdb" --all --analyze-only
這個指令而已
7.刪除舊版本軟體及資料
執行 delete_old_cluster.sh , 這個也是剛剛執行 pg_upgrade 後產生的檔案
[root@localhost 9.6]# ./delete_old_cluster.sh
[root@localhost 9.6]#
[root@localhost 9.6]#
查看一下 delete_old_cluster.sh 其實就是 rm 指令而已
-bash-4.2$ cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/datafile/9.2'
-bash-4.2$
-bash-4.2$
8.確認
-bash-4.2$
-bash-4.2$
-bash-4.2$ psql
psql (9.6.3)
輸入 "help" 顯示說明。
postgres=#
postgres=#
postgres=# \l
資料庫清單
名稱 | 擁有者 | 字元編碼 | Collate | 轉換型別 | 存取權限
-----------+----------+----------+-------------+-------------+-----------------------
TEST | TEST | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 |
postgres | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 |
template0 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_TW.UTF-8 | zh_TW.UTF-8 | postgres=CTc/postgres+
| | | | | =c/postgres
(4 筆資料列)
postgres=#
postgres=#
9.6.3 確認無誤, 升級完成!!!!
(7.6G資料我大概15分內轉換完成)
