postgresql大版本升级

环境:当前版本:pg 9.4.6 安装路劲为:/data/pg946/目标版本:pg 9.5.1 安装路劲为:/data/pg951/*****************************************************1.安装新版本*****************************************************/*******make --versiongcc --versionperl --versionpython --version----1.安装环境包yum -y install wget gcc gcc-c++ readline-devel zlib-devel make   systemtap systemtap-sdt-devel \perl perl-devel python python-devel tcl  tcl-devel    perl-ExtUtils-Embed \sgml-common docbook stylesheets openjade  sgml-tools  xsltproc libxslt libxslt-devel \libxml2 libxml2-devel zlib zlib-devel openssl openssl-devel   pam pam-devel bison flex libreadline6-devel ******/----2.编译安装#useradd postgres && echo 'password' |passwd --stdin  postgres#mkdir -p /data/pg951/data   && chown -R postgres /data/pg951/data# wget https://ftp.postgresql.org/pub/source/v9.5.1/#mkdir -p /soft && cd /soft#rz# tar xf postgresql-9.5.1.tar.gz && cd postgresql-9.5.1#./configure./configure --prefix=/data/pg951 \--with-pgport=5435  \--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-----3.执行数据库初始化脚本(指定字符集)$/data/pg951/bin/initdb  -D /data/pg951/data  --encoding=utf8   -U postgres---结果如下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 "C".The default database encoding has accordingly been set to "SQL_ASCII".The default text search configuration will be set to "english".Data page checksums are disabled.fixing permissions on existing directory /data/pg951/data ... okcreating subdirectories ... okselecting default max_connections ... 100selecting default shared_buffers ... 128MBselecting dynamic shared memory implementation ... posixcreating configuration files ... okcreating template1 database in /data/pg951/data/base/1 ... okinitializing pg_authid ... okinitializing dependencies ... okcreating system views ... okloading system objects' descriptions ... okcreating collations ... okcreating conversions ... okcreating dictionaries ... oksetting privileges on built-in objects ... okcreating information schema ... okloading PL/pgSQL server-side language ... okvacuuming database template1 ... okcopying template1 to template0 ... okcopying template1 to postgres ... oksyncing data to disk ... okWARNING: enabling "trust" authentication for local connectionsYou 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:    /data/pg951/bin/pg_ctl -D /data/pg951/data -l logfile start----4.启动pg新版本$cp postgresql.conf  /data/pg951/data/$/data/pg951/bin/pg_ctl  -D /data/pg951/data  status$/data/pg951/bin/pg_ctl  -D /data/pg951/data  start $退出变更登录exit*****************************************************---2.升级*****************************************************-----1.将两个库都停止服务$ netstat -lntp | grep postgrestcp        0      0 0.0.0.0:5432                0.0.0.0:*                   LISTEN      6287/postgres       tcp        0      0 127.0.0.1:5435              0.0.0.0:*                   LISTEN      6303/postgres   $/data/pg946/bin/pg_ctl   -D /data/pg946/data  stop $/data/pg951/bin/pg_ctl  -D /data/pg951/data  stop  $ /data/pg946/bin/pg_ctl  -D /data/pg946/data  statuspg_ctl: no server running$ /data/pg951/bin/pg_ctl  -D /data/pg951/data  statuspg_ctl: no server running------2.执行pg_upgrade#mkdir -p /data/upgrade   && chown -R postgres /data/upgrade  ----2.1 进行pg_upgrade检查$cd /data/upgrade/$/data/pg951/bin/pg_upgrade   -c    -b /data/pg946/bin   \-B /data/pg951/bin/ -d /data/pg946/data -D /data/pg951/data -p 5432 -P 5435Performing 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 presence of required libraries                 okChecking database user is the install user                  okChecking for prepared transactions                          ok*Clusters are compatible*----2.2进行pg_upgrade升级两种升级方式:1).缺省的通过拷贝数据文件到新的data目录下，拷贝的方式升级较慢，但是原库还可用；2).硬链接的方式升级较快，但是原库不可用.$/data/pg951/bin/pg_upgrade   -b /data/pg946/bin   \-B /data/pg951/bin/ -d /data/pg946/data -D /data/pg951/data -p 5432 -P 5435----执行结果Performing 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       okCreating dump of global objects                             okCreating dump of database schemas                                                            okChecking 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 cluster                                                            okCreating newly-required TOAST tables                        okCopying user relation files                                                            okSetting 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 cluster's data files:    ./delete_old_cluster.sh---3. 修改新版本为正常端口号$ grep -i "^port" /data/pg951/data/postgresql.conf port = 5435# (change requires restart)$ sed  -i "s/5435/5432/1" /data/pg951/data/postgresql.conf port = 5432# (change requires restart)----4.修改环境变量# su - postgres$  vi  ~/.bash_profile # postgres#PostgreSQL端口PGPORT=5432#PostgreSQL数据目录PGDATA=/data/pg951/dataexport PGPORT PGDATA #所使用的语言export LANG=en_US.utf8#PostgreSQL 安装目录export PGHOME=/data/pg951#PostgreSQL 连接库文件export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATHexport DATE=`date +"%Y%m%d%H%M"`#将PostgreSQL的命令行添加到 PATH 环境变量export PATH=$PGHOME/bin:$PATH#PostgreSQL的 man 手册export MANPATH=$PGHOME/share/man:$MANPATH#PostgreSQL的默认用户export PGUSER=postgres#PostgreSQL默认主机地址export PGHOST=127.0.0.1#默认的数据库名export PGDATABASE=postgres#source ~/.bash_profile ----5.PostgreSQL执行脚本#复制PostgreSQL执行脚本cp /soft/postgresql-9.5.1/contrib/start-scripts/linux  /etc/init.d/postgresql#增加执行权限chmod +x /etc/init.d/postgresql#编辑PostgreSQL执行脚本,确定以下参数或修改#vi /etc/init.d/postgresql# Installation prefixprefix=/data/pg951# Data directoryPGDATA="/data/pg951/data"# Who to run the postmaster as, usually "postgres".  (NOT "root")PGUSER=postgres# Where to keep a log filePGLOG="$PGDATA/serverlog"----6.启动新版本/data/pg951/bin/pg_ctl  -D /data/pg951/data  start-----7.验证$ /data/pg951/bin/psql  --versionpsql (PostgreSQL) 9.5.1$ /data/pg951/bin/psql psql (9.5.1)Type "help" for help.postgres@127.0.0.1 ~=# \l                                 List of databases   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges   -----------+----------+----------+------------+------------+----------------------- postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |  template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +           |          |          |            |            | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | postgres=CTc/postgres+           |          |          |            |            | =c/postgres wind      | postgres | UTF8     | en_US.utf8 | en_US.utf8 | (4 rows)postgres@127.0.0.1 ~=# \c windYou are now connected to database "wind" as user "postgres".postgres@127.0.0.1 wind=# \d        List of relations Schema | Name | Type  |  Owner   --------+------+-------+---------- public | t00  | table | postgres(1 row)postgres@127.0.0.1 wind=# select count(*) from t00; count -------  1000(1 row)Time: 2.308 mspostgres@127.0.0.1 wind=# ---8.删除老版本软件$ cat delete_old_cluster.sh #!/bin/shrm -rf '/data/pg941/data'$ ./delete_old_cluster.sh