- 原文地址:http://blog.csdn.net/yangzhawen/article/details/51006625
- 环境:
- 当前版本:pg 9.4.6 安装路劲为:/data/pg946/
- 目标版本:pg 9.5.1 安装路劲为:/data/pg951/
- *****************************************************
- 1.安装新版本
- *****************************************************
- /*******
- make --version
- gcc --version
- perl --version
- python --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 ... ok
- creating subdirectories ... ok
- selecting default max_connections ... 100
- selecting default shared_buffers ... 128MB
- selecting dynamic shared memory implementation ... posix
- creating configuration files ... ok
- creating template1 database in /data/pg951/data/base/1 ... ok
- initializing pg_authid ... ok
- initializing dependencies ... ok
- creating system views ... ok
- loading system objects' descriptions ... ok
- creating collations ... ok
- creating conversions ... ok
- creating dictionaries ... ok
- setting privileges on built-in objects ... ok
- creating information schema ... ok
- loading PL/pgSQL server-side language ... ok
- vacuuming database template1 ... ok
- copying template1 to template0 ... ok
- copying template1 to postgres ... ok
- syncing data to disk ... ok
- WARNING: enabling "trust" authentication for local connections
- You 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 postgres
- tcp 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 status
- pg_ctl: no server running
- $ /data/pg951/bin/pg_ctl -D /data/pg951/data status
- pg_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 5435
- 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 presence of required libraries ok
- Checking database user is the install user ok
- Checking 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 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
- 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
- Creating newly-required TOAST tables ok
- Copying 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
- ---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/data
- export 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_PATH
- export 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 prefix
- prefix=/data/pg951
- # Data directory
- PGDATA="/data/pg951/data"
- # Who to run the postmaster as, usually "postgres". (NOT "root")
- PGUSER=postgres
- # Where to keep a log file
- PGLOG="$PGDATA/serverlog"
- ----6.启动新版本
- /data/pg951/bin/pg_ctl -D /data/pg951/data start
- -----7.验证
- $ /data/pg951/bin/psql --version
- psql (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 wind
- You 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 ms
- postgres@127.0.0.1 wind=#
- ---8.删除老版本软件
- $ cat delete_old_cluster.sh
- #!/bin/sh
- rm -rf '/data/pg941/data'
- $ ./delete_old_cluster.sh
postgresql大版本升级
最新推荐文章于 2024-07-22 06:02:52 发布