友情链接购买网站,欧派整装大家居装修公司加盟,开贴纸网站要怎么做的,网站建设业务员话术前些天发现了一个巨牛的人工智能学习网站#xff0c;通俗易懂#xff0c;风趣幽默#xff0c;忍不住分享一下给大家。点击跳转到教程。进行升级版本之前请一定做好备份#xff01;查看当前版本#xff1a;[postgresnode1 ~]$ psqlpsql (9.4.4)Type help for h…前些天发现了一个巨牛的人工智能学习网站通俗易懂风趣幽默忍不住分享一下给大家。点击跳转到教程。进行升级版本之前请一定做好备份查看当前版本[postgresnode1 ~]$ psqlpsql (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安装新版本[rootnode1 upload]# mkdir -p /opt/pg9.6.2//data[rootnode1 upload]# tar -zxvf postgresql-9.6.2.tar.gz./configure --prefix/opt/pg9.6.2/--with-pgport5433 \ 【建议在此处直接更改了端口】--with-perl --with-python --with-tcl \ --with-openssl --without-ldap \ --with-libxml --with-libxslt \ --enable-thread-safety \ --with-wal-blocksize64 \ --with-blocksize32 \ --with-wal-segsize64 \ -enable-dtrace \ --enable-debug
makemake install[postgresnode1 ~]$ /opt/pg9.6.2/bin/initdb -D /opt/pg9.6.2/data/--encodingutf8 -U postgres 【可以指定字符集和用户】如果安装的时候没有更改端口的话则此时应该vim postgresql.confport 5433log_destination stderrlogging_collector onlog_directory pg_log[postgresnode1 data]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ start[postgresnode1 data]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ statuspg_ctl: server is running (PID: 12720)/opt/pg9.6.2/bin/postgres -D /opt/pg9.6.2/data[postgresnode1 data]$ exitlogout[rootnode1 postgresql-9.6.2]# netstat -lntp | grep postgrestcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 2271/postgrestcp 0 0 127.0.0.1:5433 0.0.0.0:* LISTEN 12720/postgrestcp 0 0 :::5432 :::* LISTEN 2271/postgrestcp 0 0 ::1:5433 :::* LISTEN 12720/postgres至此新库安装启动成功关闭两个库[postgresnode1 ~]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ stop[postgresnode1 ~]$ /opt/pg9.4.4/bin/pg_ctl -D /opt/pg9.4.4/data/ stop升级前的检测[postgresnode1 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 5433Performing Consistency Checks-----------------------------Checking cluster versions okChecking database user is the install user okChecking database connection settings okChecking for prepared transactions okChecking for reg* system OID user data types okChecking for contrib/isn with bigint-passing mismatch okChecking for roles starting with pg_ okChecking for presence of required libraries fatalYour installation references loadable libraries that are missing from thenew installation. You can add these libraries to the new installation,or remove the functions using them from the old installation. A list ofproblem libraries is in the file:loadable_libraries.txtFailure, exiting备注 -b, -B 分别表示老版本 PG bin 目录新版本 PG bin目录 -d, -D 分别表示老版本PG 数据目录新版本 PG 数据目录 -c 表示仅检查并不会做任何更改 根据提示查看文件 loadable_libraries.txt 。cat /opt/pg9.4.4/bin/loadable_libraries.txtcould not load library $libdir/dblink:ERROR: could not access file $libdir/dblink: No such file or directorycould not load library $libdir/postgres_fdw:ERROR: could not access file $libdir/postgres_fdw: No such file or directorycould 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/makemake install postgres_fdwmake 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 1slon_kill 2② 卸载节点slonik_uninstall_nodes | slonik③查看安装模式master# \dnList of schemasName | Owner------------------------_replication | slonypublic | postgres(2 rows)④删除Slony安装的模式【此处应确认slon运行守护程序已经关闭】master# DROP SCHEMA _replication CASCADE;slave# DROP SCHEMA _replication CASCADE;[postgresnode1 ~]$ slonslony_show_configuration-bash: slonslony_show_configuration: command not found【检测一下配置发现已经完全卸载干净了需要注意的是slon-i并不是删除所有有关目录和文件即可删除】重新检测[postgresnode1 ~]$ /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 5433Performing Consistency Checks-----------------------------Checking cluster versions okChecking database user is the install user okChecking database connection settings okChecking for prepared transactions okChecking for reg* system OID user data types okChecking for contrib/isn with bigint-passing mismatch okChecking for roles starting with pg_ okChecking for presence of required libraries okChecking database user is the install user okChecking for prepared transactions ok*Clusters are compatible*两种升级方式:1).缺省的通过拷贝数据文件到新的data目录下拷贝的方式升级较慢但是原库还可用2).硬链接的方式升级较快但是原库不可用.一般来说是建议使用硬链接的方式来升级的这样更符合实际生产但是需要注意无论是哪种方式升级要停库且升级时间不可控这也是升级的弊端升级[postgresnode1 ~]$ /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 5433Performing Consistency Checks-----------------------------Checking cluster versions okChecking database user is the install user okChecking database connection settings okChecking for prepared transactions okChecking for reg* system OID user data types okChecking for contrib/isn with bigint-passing mismatch okChecking for roles starting with pg_ okCreating dump of global objects okCreating dump of database schemasokChecking for presence of required libraries okChecking database user is the install user okChecking for prepared transactions okIf pg_upgrade fails after this point, you must re-initdb thenew cluster before continuing.Performing Upgrade------------------Analyzing all rows in the new cluster okFreezing all rows on the new cluster okDeleting files from new pg_clog okCopying old pg_clog to new server okSetting next transaction ID and epoch for new cluster okDeleting files from new pg_multixact/offsets okCopying old pg_multixact/offsets to new server okDeleting files from new pg_multixact/members okCopying old pg_multixact/members to new server okSetting next multixact ID and offset for new cluster okResetting WAL archives okSetting frozenxid and minmxid counters in new cluster okRestoring global objects in the new cluster*failure*Consult the last few lines of pg_upgrade_utility.log forthe probable cause of the failure.Failure, exiting提示恢复新集群中的全局变量失败到该日志下查看并没有发现有用的信息。猜测是因为心集群无法正常启动。[postgresnode1 pg9.6.2]$ /opt/pg9.6.2/bin/initdb -D /opt/pg9.6.2/data/根据前面的提示重新初始化了新的数据库但是还是没有任何改变。[postgresnode1 ~]$ /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到新数据库[postgresnode1 pg9.6.2]$ export LD_LIBRARY_PATH/opt/pg9.6.2/lib:$HOME/lib[postgresnode1 pg9.6.2]$ /opt/pg9.6.2/bin/psql -p5433psql (9.6.2)Type help for help.postgres# \lList of databasesName | 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 | | | | | postgresCTc/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | c/postgres | | | | | postgresCTc/postgres(3 rows)postgres# \q[postgresnode1 pg9.6.2]$ pg_ctl startserver starting[postgresnode1 pg9.6.2]$ LOG: redirecting log output to logging collector processHINT: Future log output will appear in directory pg_log.[postgresnode1 pg9.6.2]$ psqlpsql (9.4.4)Type help for help.postgres#postgres# \q可以发现此时新旧数据库都可以登入关闭服务[postgresnode1 pg9.6.2]$ pg_ctl stop[postgresnode1 pg9.6.2]$ /opt/pg9.6.2/bin/pg_ctl -D /opt/pg9.6.2/data/ stop重新升级[postgresnode1 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 5433Performing Consistency Checks-----------------------------Checking cluster versions okChecking database user is the install user okChecking database connection settings okChecking for prepared transactions okChecking for reg* system OID user data types okChecking for contrib/isn with bigint-passing mismatch okChecking for roles starting with pg_ okCreating dump of global objects okCreating dump of database schemasokChecking for presence of required libraries okChecking database user is the install user okChecking for prepared transactions okIf pg_upgrade fails after this point, you must re-initdb thenew cluster before continuing.Performing Upgrade------------------Analyzing all rows in the new cluster okFreezing all rows on the new cluster okDeleting files from new pg_clog okCopying old pg_clog to new server okSetting next transaction ID and epoch for new cluster okDeleting files from new pg_multixact/offsets okCopying old pg_multixact/offsets to new server okDeleting files from new pg_multixact/members okCopying old pg_multixact/members to new server okSetting next multixact ID and offset for new cluster okResetting WAL archives okSetting frozenxid and minmxid counters in new cluster okRestoring global objects in the new cluster okRestoring database schemas in the new clusterokAdding .old suffix to old global/pg_control okIf you want to start the old cluster, you will need to removethe .old suffix from /opt/pg9.4.4/data/global/pg_control.old.Because link mode was used, the old cluster cannot be safelystarted once the new cluster has been started.Linking user relation filesokSetting next OID for new cluster okSync data directory to disk okCreating script to analyze new cluster okCreating script to delete old cluster okUpgrade Complete----------------Optimizer statistics are not transferred by pg_upgrade so,once you start the new server, consider running:./analyze_new_cluster.shRunning this script will delete the old clusters data files:./delete_old_cluster.sh查看新数据库端口[postgresnode1 pg9.6.2]$ grep -i ^port /opt/pg9.6.2/data/postgresql.confport 5433 # (change requires restart)修改端口号为5432【sed -i直接修改读取的文件内容而不是输出到终端。详情请参考博主另一篇博客http://blog.csdn.net/oraclesand/article/details/68923042】[postgresnode1 pg9.6.2]$ sed -i s/5433/5432/1 /opt/pg9.6.2/data/postgresql.conf[postgresnode1 pg9.6.2]$ grep -i ^port /opt/pg9.6.2/data/postgresql.confport 5432 # (change requires restart)修改环境变量# .bash_profile# Get the aliases and functionsif [ -f ~/.bashrc ]; then. ~/.bashrcfi# User specific environment and startup programsPATH$PATH:$HOME/binexport PATH#export PGHOME/opt/pg9.4.4/export PGHOME/opt/pg9.6.2/export PGDATA$PGHOME/dataexport PATH$PGHOME/bin:$PATHexport LD_LIBRARY_PATH$PGHOME/lib#默认端口#export PGPORT5432#默认密码#export PGPASSWORD#默认字符集#export LANGen_US.utf8#PostgreSQL默认主机地址#export PGHOST127.0.0.1#默认的数据库名#export PGDATABASEpostgres#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 DATEdate %Y%m%d%H%Msource ~/.bash_profile检测升级效果查看新库是否有原有数据[postgresnode1 pg9.6.2]$ pg_ctl startserver starting[postgresnode1 pg9.6.2]$ LOG: redirecting log output to logging collector processHINT: Future log output will appear in directory pg_log.[postgresnode1 pg9.6.2]$ psqlpsql (9.6.2)Type help for help.postgres# \lList of databasesName | 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 | | | | | postgresCTc/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgresCTc/postgres| | | | | c/postgres(4 rows)postgres# \dList of relationsSchema | Name | Type | Owner-----------------------------------------------------public | dblink_test00_view | view | postgrespublic | qqq | foreign table | postgrespublic | test01 | table | postgres(3 rows)一切正常执行脚本[postgresnode1 pg9.6.2]$ ./analyze_new_cluster.sh这个脚本其实就一条 vacuumdb 命令收集新库统计信息This script will generate minimal optimizer statistics rapidlyso your system is usable, and then gather statistics twice morewith increasing accuracy. When it is done, your system willhave the default level of optimizer statistics.If you have used ALTER TABLE to modify the statistics target forany tables, you might want to remove them and restore them afterrunning this script because they will delay fast statistics generation.If you would like default statistics as quickly as possible, cancelthis script and run:/opt/pg9.6.2/bin/vacuumdb --all --analyze-onlyvacuumdb: 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 statisticsvacuumdb: processing database postgres: Generating default (full) optimizer statisticsvacuumdb: processing database template1: Generating default (full) optimizer statistics删除旧数据库data[postgresnode1 pg9.6.2]$ cat delete_old_cluster.sh#!/bin/shrm -rf /opt/pg9.4.4/data[postgresnode1 pg9.6.2]$ ./delete_old_cluster.sh[postgresnode1 opt]$ ls pg9.4.4/ -ltotal 20drwxr-xr-x. 2 postgres postgres 4096 Mar 30 17:59 bindrwxr-xr-x. 4 postgres postgres 4096 Mar 13 12:56 includedrwxr-xr-x. 4 postgres postgres 4096 Mar 30 14:22 libdrwxr-xr-x. 4 postgres postgres 4096 Mar 28 16:54 sharedrwxrwxr-x. 3 postgres postgres 4096 Mar 30 14:46 slonylog[postgresnode1 opt]$ postgres --versionpostgres (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
