环境:当前版本: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总结
以上是内存溢出为你收集整理的postgresql大版本升级全部内容，希望文章能够帮你解决postgresql大版本升级所遇到的程序开发问题。
如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）