13518219792
我们知道pg_upgrade和pg_dump/pg_restore可以实现大版本升级数据库，那么小版本如何升级，比如从9.6.3到9.6.5？
创新互联拥有十载成都网站建设工作经验,为各大企业提供成都网站制作、成都做网站、外贸营销网站建设服务，对于网页设计、PC网站建设（电脑版网站建设）、重庆App定制开发、wap网站建设（手机版网站建设）、程序开发、网站优化（SEO优化）、微网站、空间域名等，凭借多年来在互联网的打拼，我们在互联网网站建设行业积累了很多网站制作、网站设计、网络营销经验，集策划、开发、设计、营销、管理等网站化运作于一体，具备承接各种规模类型的网站建设项目的能力。
原理：用新版本的软件程序启动，指定老的的数据目录
安装pgsql9.6.3
mkdir -p /usr/local/pgsql/pgsql9.6.3/{data,arch}
groupadd dba
useradd -g dba -G root postgres -d /usr/local/pgsql
tar xf postgresql-9.6.3.tar.gz
chmod -R 755 /usr/local/pgsql
chown -R postgres:dba /usr/local/pgsql
chmod -R 700 /usr/local/pgsql/pgsql9.6.3/data/
./configure --prefix=/usr/local/pgsql/pgsql9.6.3/
make world && make install-world
cd /usr/local/pgsql/pgsql9.6.3/bin
./initdb -D /usr/local/pgsql/pgsql9.6.3/data -U postgres -E UTF8 -W
vim pg_hba.conf
host all all 0.0.0.0/0 md5
vim postgresql.conf
listen_addresses = '*'
wal_level = replica
port = 5432
max_connections = 300
shared_buffers = 128MB
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/pgsql9.6.5/arch/%f && cp %p /usr/local/pgsql/pgsql9.6.5/arch/%f'
/usr/local/pgsql/pgsql9.6.3/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.3/data
#生成测试数据
create database testdb1;
\c testdb1;
create table t1(id int primary key, info text);
insert into t1 select generate_series(1,1000000),'helloWorld';
安装pgsql9.6.5
mkdir -p /usr/local/pgsql/pgsql9.6.5/{data,arch}
groupadd dba
useradd -g dba -G root postgres -d /usr/local/pgsql
tar xf postgresql-9.6.5.tar.gz
chmod -R 755 /usr/local/pgsql
chown -R postgres:dba /usr/local/pgsql
chmod -R 700 /usr/local/pgsql/pgsql9.6.5/data/
./configure --prefix=/usr/local/pgsql/pgsql9.6.5/
make world && make install-world
cd /usr/local/pgsql/pgsql9.6.5/bin
/usr/local/pgsql/pgsql9.6.5/bin/initdb -D /usr/local/pgsql/pgsql9.6.5/data -U postgres -E UTF8 -W
vim pg_hba.conf
host all all 0.0.0.0/0 md5
vim postgresql.conf
listen_addresses = '*'
wal_level = replica
port = 5431
max_connections = 300
shared_buffers = 128MB
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
archive_mode = on
archive_command = 'test ! -f /usr/local/pgsql/pgsql9.6.5/arch/%f && cp %p /usr/local/pgsql/pgsql9.6.5/arch/%f'
/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.5/data
/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl stop -m fast -D /usr/local/pgsql/pgsql9.6.5/data
#停止老的数据库9.6.3
/usr/local/pgsql/pgsql9.6.3/bin/pg_ctl stop -m fast -D /usr/local/pgsql/pgsql9.6.3/data
#用9.6.5的软件程序启动，指定9.6.3的数据目录
/usr/local/pgsql/pgsql9.6.5/bin/pg_ctl start -D /usr/local/pgsql/pgsql9.6.3/data
postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 9.6.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-4), 64-bit
testdb1=# select count(*) from t1;
count
---------
1000000
