前些天發現了一個巨牛的人工智能學習網站，通俗易懂，風趣幽默，忍不住分享一下給大家。點擊跳轉到教程。
進行升級版本之前請一定做好備份！
查看當前版本：
[postgres@node1 ~]$ psql
psql (9.4.4)
Type "help" for help.
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
(1 row)
$PGDATA：
/opt/pg9.4.4//data
/opt/pg9.6.2//data
升級至9.6.2
安裝新版本：
[root@node1 upload]# mkdir -p /opt/pg9.6.2//data
[root@node1 upload]# tar -zxvf postgresql-9.6.2.tar.gz
./configure --prefix=/opt/pg9.6.2/
- --with-pgport=5433??\??【建議在此處直接更改了端口】
- --with-perl?--with-python?--with-tcl??\??
- --with-openssl??--without-ldap??\??
- ?--with-libxml??--with-libxslt????\??
- --enable-thread-safety????\??
- --with-wal-blocksize=64???\??
- --with-blocksize=32?\??
- --with-wal-segsize=64?\??
- -enable-dtrace??\??
- --enable-debug??
make
make install
[postgres@node1 ~]$ /opt/pg9.6.2/bin/initdb -D /opt/pg9.6.2/data/
--encoding=utf8???-U?postgres??【可以指定字符集和用戶】
如果安裝的時候沒有更改端口的話則此時應該：
vim postgresql.conf
port = 5433
log_destination = 'stderr'
logging_collector = on
log_directory = 'pg_log'
[postgres@node1 data]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ start
[postgres@node1 data]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ status
pg_ctl: server is running (PID: 12720)
/opt/pg9.6.2/bin/postgres "-D" "/opt/pg9.6.2/data"
[postgres@node1 data]$ exit
logout
[root@node1 postgresql-9.6.2]# netstat -lntp | grep postgres
tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 2271/postgres
tcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 12720/postgres
tcp 0 0 :::5432 :::* LISTEN 2271/postgres
tcp 0 0 ::1:5433 :::* LISTEN 12720/postgres
至此，新庫安裝啟動成功
關閉兩個庫：
[postgres@node1 ~]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ stop
[postgres@node1 ~]$ /opt/pg9.4.4/bin/pg_ctl -D /opt/pg9.4.4/data/ stop
升級前的檢測：
[postgres@node1 bin]$ /opt/pg9.6.2/bin/pg_upgrade -c --link -b /opt/pg9.4.4/bin -B /opt/pg9.6.2/bin -d /opt/pg9.4.4/data -D /opt/pg9.6.2/data/ -p 5432 -P 5433
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 presence of required libraries fatal
Your installation references loadable libraries that are missing from the
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
Failure, exiting
備注： -b, -B 分別表示老版本 PG bin 目錄，新版本 PG bin目錄， -d, -D 分別表示老版本PG 數據目錄，新版本 PG 數據目錄， -c 表示僅檢查，并不會做任何更改， 根據提示查看文件 loadable_libraries.txt 。
cat /opt/pg9.4.4/bin/loadable_libraries.txt
could not load library "$libdir/dblink":
ERROR: could not access file "$libdir/dblink": No such file or directory
could not load library "$libdir/postgres_fdw":
ERROR: could not access file "$libdir/postgres_fdw": No such file or directory
could not load library "$libdir/slony1_funcs":
ERROR: could not access file "$libdir/slony1_funcs": No such file or directory
檢測發現：新庫缺少一些舊庫已經安裝的工具和軟件
解決方案：
cd /upload/postgresql-9.6.2/contrib/
make
make install postgres_fdw
make install dblink
【這兩個插件9.6.2也有，且是常用的小型插件，可以直接在9.6.2環境中安裝】
此時node1和node2的test01和test02是可以同步的，即slony-i是可以正常使用的。
思路：可以卸載slony-i 來實現升級postgresql或者同時升級slony-i來升級postgresql。
本博客使用卸載升級：（博主認為像slony-i這種工具由于對不同版本的postgres數據庫有著不同的版本要求，升級slony-i更容易出問題，不如直接刪除然后在新的數據庫重新安裝同步）
① 關閉守護進程：
slon_kill 1
slon_kill 2
② 卸載節點：
slonik_uninstall_nodes | slonik
③查看安裝模式：
master=# \dn
List of schemas
Name | Owner
--------------+----------
_replication | slony
public | postgres
(2 rows)
④刪除Slony安裝的模式：【此處應確認slon運行守護程序已經關閉】
master=# DROP SCHEMA _replication CASCADE;
slave=# DROP SCHEMA _replication CASCADE;
[postgres@node1 ~]$ slonslony_show_configuration
-bash: slonslony_show_configuration: command not found
【檢測一下配置，發現已經完全卸載干凈了，需要注意的是slon-i并不是刪除所有有關目錄和文件即可刪除！】
重新檢測：
[postgres@node1 ~]$ /opt/pg9.6.2/bin/pg_upgrade -c --link -b /opt/pg9.4.4/bin -B /opt/pg9.6.2/bin -d /opt/pg9.4.4/data -D /opt/pg9.6.2/data/ -p 5432 -P 5433
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 presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
*Clusters are compatible*
兩種升級方式:
1).缺省的通過拷貝數據文件到新的data目錄下，拷貝的方式升級較慢，但是原庫還可用；
2).硬鏈接的方式升級較快，但是原庫不可用.
一般來說是建議使用硬鏈接的方式來升級的，這樣更符合實際生產
但是需要注意無論是哪種方式，升級要停庫且升級時間不可控，這也是升級的弊端
升級：
[postgres@node1 ~]$ /opt/pg9.6.2/bin/pg_upgrade --link -b /opt/pg9.4.4/bin -B /opt/pg9.6.2/bin -d /opt/pg9.4.4/data -D /opt/pg9.6.2/data/ -p 5432 -P 5433
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
*failure*
Consult the last few lines of "pg_upgrade_utility.log" for
the probable cause of the failure.
Failure, exiting
提示：恢復新集群中的全局變量失敗，到該日志下查看并沒有發現有用的信息。猜測是因為心集群無法正常啟動。
[postgres@node1 pg9.6.2]$ /opt/pg9.6.2/bin/initdb -D /opt/pg9.6.2/data/
根據前面的提示重新初始化了新的數據庫，但是還是沒有任何改變。
[postgres@node1 ~]$ /opt/pg9.6.2/bin/psql -p5433
/opt/pg9.6.2/bin/psql: symbol lookup error: /opt/pg9.6.2/bin/psql: undefined symbol: PQsetErrorContextVisibility
果然，問題出現在了新集群的啟動上，出現該問題的原因是目前狀態的lib仍是指定到了舊的數據庫，臨時定義LD_LIBRARY_PATH到新數據庫
[postgres@node1 pg9.6.2]$ export LD_LIBRARY_PATH=/opt/pg9.6.2/lib:$HOME/lib
[postgres@node1 pg9.6.2]$ /opt/pg9.6.2/bin/psql -p5433
psql (9.6.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 rows)
postgres=# \q
[postgres@node1 pg9.6.2]$ pg_ctl start
server starting
[postgres@node1 pg9.6.2]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
[postgres@node1 pg9.6.2]$ psql
psql (9.4.4)
Type "help" for help.
postgres=#
postgres=# \q
可以發現此時新舊數據庫都可以登入
關閉服務：
[postgres@node1 pg9.6.2]$ pg_ctl stop
[postgres@node1 pg9.6.2]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ stop
重新升級：
[postgres@node1 pg9.6.2]$ /opt/pg9.6.2/bin/pg_upgrade --link -b /opt/pg9.4.4/bin -B /opt/pg9.6.2/bin -d /opt/pg9.4.4/data -D /opt/pg9.6.2/data/ -p 5432 -P 5433
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
Adding ".old" suffix to old global/pg_control ok
If you want to start the old cluster, you will need to remove
the ".old" suffix from /opt/pg9.4.4/data/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.
Linking 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
查看新數據庫端口：
[postgres@node1 pg9.6.2]$ grep -i "^port" /opt/pg9.6.2/data/postgresql.conf
port = 5433 # (change requires restart)
修改端口號為5432
【sed -i直接修改讀取的文件內容，而不是輸出到終端。詳情請參考博主另一篇博客http://blog.csdn.net/oraclesand/article/details/68923042】
[postgres@node1 pg9.6.2]$ sed -i "s/5433/5432/1" /opt/pg9.6.2/data/postgresql.conf
[postgres@node1 pg9.6.2]$ grep -i "^port" /opt/pg9.6.2/data/postgresql.conf
port = 5432 # (change requires restart)
修改環境變量：
# .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=/opt/pg9.4.4/
export PGHOME=/opt/pg9.6.2/
export PGDATA=$PGHOME/data
export PATH=$PGHOME/bin:$PATH
export LD_LIBRARY_PATH=$PGHOME/lib
#默認端口
#export PGPORT=5432
#默認密碼
#export PGPASSWORD=''
#默認字符集
#export LANG=en_US.utf8
#PostgreSQL默認主機地址
#export PGHOST=127.0.0.1
#默認的數據庫名
#export PGDATABASE=postgres
#PostgreSQL的 man 手冊
#export MANPATH=$PGHOME/share/man:$MANPATH
#PostgreSQL 連接庫文件
#export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH
#export DATE=`date +"%Y%m%d%H%M"`
source ~/.bash_profile
檢測升級效果，查看新庫是否有原有數據：
[postgres@node1 pg9.6.2]$ pg_ctl start
server starting
[postgres@node1 pg9.6.2]$ LOG: redirecting log output to logging collector process
HINT: Future log output will appear in directory "pg_log".
[postgres@node1 pg9.6.2]$ psql
psql (9.6.2)
Type "help" for help.
postgres=# \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
master | slony | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres=CTc/postgres+
| | | | | =c/postgres
(4 rows)
postgres=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------+---------------+----------
public | dblink_test00_view | view | postgres
public | qqq | foreign table | postgres
public | test01 | table | postgres
(3 rows)
一切正常
執行腳本
[postgres@node1 pg9.6.2]$ ./analyze_new_cluster.sh
這個腳本其實就一條 vacuumdb 命令，收集新庫統計信息
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:
"/opt/pg9.6.2/bin/vacuumdb" --all --analyze-only
vacuumdb: processing database "master": 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 "master": 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 "master": Generating default (full) optimizer statistics
vacuumdb: processing database "postgres": Generating default (full) optimizer statistics
vacuumdb: processing database "template1": Generating default (full) optimizer statistics
刪除舊數據庫data：
[postgres@node1 pg9.6.2]$ cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/opt/pg9.4.4/data'
[postgres@node1 pg9.6.2]$ ./delete_old_cluster.sh
[postgres@node1 opt]$ ls pg9.4.4/ -l
total 20
drwxr-xr-x. 2 postgres postgres 4096 Mar 30 17:59 bin
drwxr-xr-x. 4 postgres postgres 4096 Mar 13 12:56 include
drwxr-xr-x. 4 postgres postgres 4096 Mar 30 14:22 lib
drwxr-xr-x. 4 postgres postgres 4096 Mar 28 16:54 share
drwxrwxr-x. 3 postgres postgres 4096 Mar 30 14:46 slonylog
[postgres@node1 opt]$ postgres --version
postgres (PostgreSQL) 9.6.2
至此升級完成！
根據需求實際修改postgresql.conf, pg_hba.conf等文件。
官方參考文檔：
http://slony.info/documentation/1.2/dropthings.html
一個有趣的相同問題：
http://www.postgresql-archive.org/Slony-error-please-help-td5908267.html#a5908317
?
?
?
轉自：https://blog.csdn.net/Oraclesand/article/details/68923078
)
)
)
![[云框架]KONG API Gateway v1.5 -框架說明、快速部署、插件開發](http://pic.xiahunao.cn/[云框架]KONG API Gateway v1.5 -框架說明、快速部署、插件開發)
)
)
