实际操作：
已有环境和pg
192.168.12.231 pg9.1.1
端口 5432
安装目录 /pgtina
数据目录 /pgtina/data
1、在本机器上安装9.4.5
端口 1922 --随意指定，但不可与之前的一样
安装目录 /pgnew
数据目录 /pgnew/data
./configure --prefix=/pgnew --with-pgport=1922 && gmake world && gmake install-world
安装时候记得带world，内外模块都要，不然找不到pg_upgrade
2、修改属主
[root@datanode1 /]# chown -R postgres:postgres pgnew
[root@datanode1 /]# chmod 700 /pgnew
[postgres@datanode1 pgtina]$ chmod 700 data
3、初始化新库
[postgres@datanode1 pgnew]$ mkdir data
[postgres@datanode1 pgnew]$ /pgnew/bin/initdb -E UTF8 -D /pgnew/data --locale=C -U postgres -W ---初始化locale指定错误，按照下面的来
4、修改pg_hba.cof
因为升级需要多次连接新老集群数据库实例, 所以修改为使用本地trust认证.
postgres@localhost-> vi /pgdata01/pg_root/pg_hba.conf
postgres@localhost-> vi /pgdata01/pg_root_9.4/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
5、修改postgresql.conf
注意使用不同的监听端口. (PostgreSQL 9.4新增)
6、停止旧库
pg_ctl -D /pgtina/data stop
7、9.4的pg_upgrade检测兼容性
[postgres@datanode1 data]$ /pgnew/bin/pg_upgrade -c --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -p 5432 -P 1922 -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/pgnew/data/.s.PGSQL.5432"? ---之前/data目录的权限不是700，修改后就好了
could not connect to old postmaster started with the command:
"/pgtina/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/pgtina/data" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/pgnew/data'" start
Failure, exiting
----检测失败
重新初始化：
[postgres@datanode1 data]$ /pgnew/bin/pg_upgrade -c --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser 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 invalid "line" user columns ok
lc_collate cluster values do not match: old "en_US.UTF-8", new "C"
Failure, exiting
初始化的时候出了问题：
删除data目录，重新做一次初始化
/pgnew/bin/initdb -E UTF8 -D /pgnew/data --locale=en_US.UTF-8 -U postgres -W
[postgres@datanode1 data]$ /pgnew/bin/pg_upgrade -c --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser 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 invalid "line" user columns ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
*Clusters are compatible*
8、升级
/pgnew/bin/pg_upgrade --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -U postgres -j 8 -k -r -v 去掉了-c
升级成功后，日志文件被自动删除，可以在命令行中用-r参数指定保留。
9、升级完成：
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
10、启动新库
/pgnew/bin/pg_ctl -D /pgnew/data start
11、登录查看
[postgres@datanode1 data]$ psql -h 127.0.0.1 -U postgres -p 1922 ---不能直接使用psql，要指定位置才行。
psql (9.1.1, server 9.4.5)
WARNING: psql version 9.1, server version 9.4.
Some psql features might not work.
Type "help" for help.
postgres=#
[postgres@datanode1 data]$ /pgnew/bin/psql -p 1922 -U postgres
psql (9.4.5)
Type "help" for help.
postgres=#
12、分析全库
[postgres@datanode1 data]$ "/pgnew/bin/vacuumdb" -U "postgres" --all --analyze-only -p 1922
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "tina"
13、连接查看--正常
[postgres@datanode1 data]$ /pgnew/bin/pg_ctl reload -D /pgnew/data
server signaled
已有环境和pg
192.168.12.231 pg9.1.1
端口 5432
安装目录 /pgtina
数据目录 /pgtina/data
1、在本机器上安装9.4.5
端口 1922 --随意指定，但不可与之前的一样
安装目录 /pgnew
数据目录 /pgnew/data
./configure --prefix=/pgnew --with-pgport=1922 && gmake world && gmake install-world
安装时候记得带world，内外模块都要，不然找不到pg_upgrade
2、修改属主
[root@datanode1 /]# chown -R postgres:postgres pgnew
[root@datanode1 /]# chmod 700 /pgnew
[postgres@datanode1 pgtina]$ chmod 700 data
3、初始化新库
[postgres@datanode1 pgnew]$ mkdir data
[postgres@datanode1 pgnew]$ /pgnew/bin/initdb -E UTF8 -D /pgnew/data --locale=C -U postgres -W ---初始化locale指定错误，按照下面的来
4、修改pg_hba.cof
因为升级需要多次连接新老集群数据库实例, 所以修改为使用本地trust认证.
postgres@localhost-> vi /pgdata01/pg_root/pg_hba.conf
postgres@localhost-> vi /pgdata01/pg_root_9.4/pg_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
5、修改postgresql.conf
注意使用不同的监听端口. (PostgreSQL 9.4新增)
6、停止旧库
pg_ctl -D /pgtina/data stop
7、9.4的pg_upgrade检测兼容性
[postgres@datanode1 data]$ /pgnew/bin/pg_upgrade -c --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -p 5432 -P 1922 -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
*failure*
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.
connection to database failed: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/pgnew/data/.s.PGSQL.5432"? ---之前/data目录的权限不是700，修改后就好了
could not connect to old postmaster started with the command:
"/pgtina/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/pgtina/data" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/pgnew/data'" start
Failure, exiting
----检测失败
重新初始化：
[postgres@datanode1 data]$ /pgnew/bin/pg_upgrade -c --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser 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 invalid "line" user columns ok
lc_collate cluster values do not match: old "en_US.UTF-8", new "C"
Failure, exiting
初始化的时候出了问题：
删除data目录，重新做一次初始化
/pgnew/bin/initdb -E UTF8 -D /pgnew/data --locale=en_US.UTF-8 -U postgres -W
[postgres@datanode1 data]$ /pgnew/bin/pg_upgrade -c --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -U postgres
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database user is a superuser 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 invalid "line" user columns ok
Checking for presence of required libraries ok
Checking database user is a superuser ok
Checking for prepared transactions ok
*Clusters are compatible*
8、升级
/pgnew/bin/pg_upgrade --link -b /pgtina/bin -B /pgnew/bin -d /pgtina/data -D /pgnew/data -U postgres -j 8 -k -r -v 去掉了-c
升级成功后，日志文件被自动删除，可以在命令行中用-r参数指定保留。
9、升级完成：
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
10、启动新库
/pgnew/bin/pg_ctl -D /pgnew/data start
11、登录查看
[postgres@datanode1 data]$ psql -h 127.0.0.1 -U postgres -p 1922 ---不能直接使用psql，要指定位置才行。
psql (9.1.1, server 9.4.5)
WARNING: psql version 9.1, server version 9.4.
Some psql features might not work.
Type "help" for help.
postgres=#
[postgres@datanode1 data]$ /pgnew/bin/psql -p 1922 -U postgres
psql (9.4.5)
Type "help" for help.
postgres=#
12、分析全库
[postgres@datanode1 data]$ "/pgnew/bin/vacuumdb" -U "postgres" --all --analyze-only -p 1922
vacuumdb: vacuuming database "postgres"
vacuumdb: vacuuming database "template1"
vacuumdb: vacuuming database "tina"
13、连接查看--正常
[postgres@datanode1 data]$ /pgnew/bin/pg_ctl reload -D /pgnew/data
server signaled
