前些天發(fā)現(xiàn)了一個(gè)巨牛的人工智能學(xué)習(xí)網(wǎng)站，通俗易懂，風(fēng)趣幽默，忍不住分享一下給大家。點(diǎn)擊跳轉(zhuǎn)到教程。 進(jìn)行升級(jí)版本之前請(qǐng)一定做好備份！ 查看當(dāng)前版本： [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 升級(jí)至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??【可以指定字符集和用戶(hù)】 如果安裝的時(shí)候沒(méi)有更改端口的話(huà)則此時(shí)應(yīng)該： 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 至此，新庫(kù)安裝啟動(dòng)成功 關(guān)閉兩個(gè)庫(kù)： [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 升級(jí)前的檢測(cè)： [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 數(shù)據(jù)目錄，新版本 PG 數(shù)據(jù)目錄， -c 表示僅檢查，并不會(huì)做任何更改， 根據(jù)提示查看文件 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 檢測(cè)發(fā)現(xiàn)：新庫(kù)缺少一些舊庫(kù)已經(jīng)安裝的工具和軟件 解決方案： cd /upload/postgresql-9.6.2/contrib/ make make install postgres_fdw make install dblink 【這兩個(gè)插件9.6.2也有，且是常用的小型插件，可以直接在9.6.2環(huán)境中安裝】 此時(shí)node1和node2的test01和test02是可以同步的，即slony-i是可以正常使用的。 思路：可以卸載slony-i 來(lái)實(shí)現(xiàn)升級(jí)postgresql或者同時(shí)升級(jí)slony-i來(lái)升級(jí)postgresql。 本博客使用卸載升級(jí)：（博主認(rèn)為像slony-i這種工具由于對(duì)不同版本的postgres數(shù)據(jù)庫(kù)有著不同的版本要求，升級(jí)slony-i更容易出問(wèn)題，不如直接刪除然后在新的數(shù)據(jù)庫(kù)重新安裝同步） ① 關(guān)閉守護(hù)進(jìn)程： slon_kill 1 slon_kill 2 ② 卸載節(jié)點(diǎn)： slonik_uninstall_nodes | slonik ③查看安裝模式： master=# \dn List of schemas Name | Owner --------------+---------- _replication | slony public | postgres (2 rows) ④刪除Slony安裝的模式：【此處應(yīng)確認(rèn)slon運(yùn)行守護(hù)程序已經(jīng)關(guān)閉】 master=# DROP SCHEMA _replication CASCADE; slave=# DROP SCHEMA _replication CASCADE; [postgres@node1 ~]$ slonslony_show_configuration -bash: slonslony_show_configuration: command not found 【檢測(cè)一下配置，發(fā)現(xiàn)已經(jīng)完全卸載干凈了，需要注意的是slon-i并不是刪除所有有關(guān)目錄和文件即可刪除！】 重新檢測(cè)： [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* 兩種升級(jí)方式: 1).缺省的通過(guò)拷貝數(shù)據(jù)文件到新的data目錄下，拷貝的方式升級(jí)較慢，但是原庫(kù)還可用； 2).硬鏈接的方式升級(jí)較快，但是原庫(kù)不可用. 一般來(lái)說(shuō)是建議使用硬鏈接的方式來(lái)升級(jí)的，這樣更符合實(shí)際生產(chǎn) 但是需要注意無(wú)論是哪種方式，升級(jí)要停庫(kù)且升級(jí)時(shí)間不可控，這也是升級(jí)的弊端 升級(jí)： [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 提示：恢復(fù)新集群中的全局變量失敗，到該日志下查看并沒(méi)有發(fā)現(xiàn)有用的信息。猜測(cè)是因?yàn)樾募簾o(wú)法正常啟動(dòng)。 [postgres@node1 pg9.6.2]$ /opt/pg9.6.2/bin/initdb -D /opt/pg9.6.2/data/ 根據(jù)前面的提示重新初始化了新的數(shù)據(jù)庫(kù)，但是還是沒(méi)有任何改變。 [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 果然，問(wèn)題出現(xiàn)在了新集群的啟動(dòng)上，出現(xiàn)該問(wèn)題的原因是目前狀態(tài)的lib仍是指定到了舊的數(shù)據(jù)庫(kù)，臨時(shí)定義LD_LIBRARY_PATH到新數(shù)據(jù)庫(kù) [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 可以發(fā)現(xiàn)此時(shí)新舊數(shù)據(jù)庫(kù)都可以登入 關(guān)閉服務(wù)： [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 重新升級(jí)： [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 查看新數(shù)據(jù)庫(kù)端口： [postgres@node1 pg9.6.2]$ grep -i "^port" /opt/pg9.6.2/data/postgresql.conf port = 5433 # (change requires restart) 修改端口號(hào)為5432 【sed -i直接修改讀取的文件內(nèi)容，而不是輸出到終端。詳情請(qǐng)參考博主另一篇博客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) 修改環(huán)境變量： # .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 #默認(rèn)端口 #export PGPORT=5432 #默認(rèn)密碼 #export PGPASSWORD='' #默認(rèn)字符集 #export LANG=en_US.utf8 #PostgreSQL默認(rèn)主機(jī)地址 #export PGHOST=127.0.0.1 #默認(rèn)的數(shù)據(jù)庫(kù)名 #export PGDATABASE=postgres #PostgreSQL的 man 手冊(cè) #export MANPATH=$PGHOME/share/man:$MANPATH #PostgreSQL 連接庫(kù)文件 #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 檢測(cè)升級(jí)效果，查看新庫(kù)是否有原有數(shù)據(jù)： [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) 一切正常 執(zhí)行腳本 [postgres@node1 pg9.6.2]$ ./analyze_new_cluster.sh 這個(gè)腳本其實(shí)就一條 vacuumdb 命令，收集新庫(kù)統(tǒng)計(jì)信息 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 刪除舊數(shù)據(jù)庫(kù)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 至此升級(jí)完成！ 根據(jù)需求實(shí)際修改postgresql.conf, pg_hba.conf等文件。 官方參考文檔： http://slony.info/documentation/1.2/dropthings.html 一個(gè)有趣的相同問(wèn)題： http://www.postgresql-archive.org/Slony-error-please-help-td5908267.html#a5908317

?

?

?

轉(zhuǎn)自：https://blog.csdn.net/Oraclesand/article/details/68923078

總結(jié)

以上是生活随笔為你收集整理的postgres大版本升级的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。