pg_upgrade有普通模式和Link模式兩種更新模式。 在普通模式下， 會把舊版本的資料拷貝到新版本中， 是以如果使用普通模式更新， 要確定有足夠的磁盤空間存儲新舊兩份資料；link模式下， 隻是在新版本的資料目錄中建立了舊版本資料檔案的硬連結， 可以有效減少磁盤占用的空間在更新之前應該運作pg_upgrade并用-c參數檢查新舊版本的相容性， 把每一項不相容的問題都解決了才可以順利更新。使用pg_upgrade時加上-c參數隻會檢查新舊版本的相容性， 不會運作真正的更新程式， 不會修改資料檔案， 并且在指令結束時， 會輸出一份檢查結果的報告， 還會對需要手動調整的項做出簡要的描述
1、安裝新版本PostgreSQL并初始化資料目錄
使用源碼安裝，configure配置和原庫使用一緻
檢視舊的安裝時的參數
[[email protected] postgresql-10.3]$ cat config.log | more
This file contains any messages produced by compilers while
running configure, to aid debugging if configure makes a mistake.
It was created by PostgreSQL configure 10.3, which was
generated by GNU Autoconf 2.69. Invocation command line was
$ ./configure --prefix=/home/postgres/pg10.3/ --without-readline --without-zlib
2、安裝并初始化
上傳
postgresql-12.2.tar.gz
解壓
tar -xvf postgresql-12.2.tar.gz
安裝
cd postgresql-12.2
./configure --prefix=/home/postgres/pg12/ --without-readline --without-zlib
make
make install
初始化資料目錄
[[email protected] ~]$ /home/postgres/pg12/bin/initdb -E utf8 -D /home/postgres/pg12/data
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 "en_US.utf8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
fixing permissions on existing directory /home/postgres/pg12/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_COLLATE to default locale: No such file or directory
2020-11-19 13:51:10.978 CST [15921] WARNING: no usable system locales were found
ok
syncing data to disk ... ok
initdb: 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:
/home/postgres/pg12/bin/pg_ctl -D /home/postgres/pg12/data -l logfile start
[[email protected] ~]$
3、檢查版本相容性
[[email protected] ~]$ /home/postgres/pg12/bin/pg_upgrade -b /home/postgres/pg10.3/bin -B /home/postgres/pg12/bin -d /home/postgres/pg10/data -D /home/postgres/pg12/data -k -c
Performing Consistency Checks on Old Live Server
------------------------------------------------
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* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" user columns ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
*Clusters are compatible*
[[email protected] ~]$
最後一行輸出“Clusters are compatible”說明已經通過相容性測試， 如果最後一行輸出“Failure， exiting”， 說明新舊版本不相容， 這時應該檢視輸出中給的提示， 例如
Your installation references loadable libraries that are missing from th
new installation. You can add these libraries to the new installation,
or remove the functions using them from the old installation. A list of
problem libraries is in the file:
loadable_libraries.txt
4、停止舊版本資料庫
[[email protected] ~]$ pg_ctl stop
waiting for server to shut down.... done
server stopped
[[email protected] ~]$
5、使用pg_upgrade普通模式更新
[[email protected] ~]$ /home/postgres/pg12/bin/pg_upgrade -b /home/postgres/pg10.3/bin -B /home/postgres/pg12/bin -d /home/postgres/pg10/data -D /home/postgres/pg12/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* data types in user tables ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking for tables WITH OIDS ok
Checking for invalid "sql_identifier" 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 in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact 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
[[email protected] ~]$
-b源庫的bin目錄
-B新庫的bin目錄
-d源庫的資料目錄
-D為新版本的資料目錄
更新完成之後，會生成兩個腳本，一個是analyze_new_cluster.sh，該腳本主要是分析收集新庫的統計資訊等，另一個腳本delete_old_cluster.sh 是删除舊庫的資料
[[email protected] ~]$ cat ./analyze_new_cluster.sh
#!/bin/sh
echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy. When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo
echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo
echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo ' "/home/postgres/pg12/bin/vacuumdb" --all --analyze-only'
echo
"/home/postgres/pg12/bin/vacuumdb" --all --analyze-in-stages
echo
echo 'Done'
[[email protected] ~]$
[[email protected] ~]$ cat ./delete_old_cluster.sh
#!/bin/sh
rm -rf '/home/postgres/pg10/data'
rm -rf '/home/postgres/pgtbs/PG_10_201707211'
rm -rf '/home/postgres/tbs/PG_10_201707211'
[[email protected] ~]$
6、更新之後環境驗證
--修改環境變量
[[email protected] ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
export PGHOME=/home/postgres/pg12
export PGDATA=/home/postgres/pg12/data
export PATH=$PGHOME/bin:/opt/python2.7.3/bin:$PATH
export MANPATH=/opt/python2.7.3/share/man:$MANPATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=en_US.utf8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PYTHONPATH=/opt/python2.7.3/lib:$PYTHONPATH
stty erase ^H
export TERM=xterm
alias rm='rm -i'
alias ll='ls -lh'
[[email protected] ~]$ . .bash_profile
[[email protected] ~]$
[[email protected] ~]$
[[email protected] ~]$ psql
psql (12.2)
Type "help" for help.
[email protected][local]:5432=#
[email protected][local]:5432=#
[email protected][local]:5432=#select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 12.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit
(1 row)
[email protected][local]:5432=#
[email protected][local]:5432=#\dt
List of relations
Schema | Name | Type | Owner
--------+--------------+-------------------+----------
public | history | partitioned table | zabbix
public | history_log | partitioned table | zabbix
public | history_str | partitioned table | zabbix
public | history_text | partitioned table | zabbix
public | history_uint | partitioned table | zabbix
public | t | table | postgres
public | trends | partitioned table | zabbix
public | trends_uint | partitioned table | zabbix
(8 rows)
[email protected][local]:5432=#select * from t;
id
----
10
(1 row)
![pgAgent編譯總結[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)