os: centos 7.4
postgresql: old 9.1
postgresql: new 9.6
pg_upgrade 是postgresql 大版本升级的得力工具。主要有两种用法：
1、使用pg_upgrade**拷贝**升级。
2、使用pg_upgrade**链接**升级。
网上搜索说 “拷贝升级虽然耗时长，但是原库还可以用。链接升级虽然耗时短，但是原库不可以用。”
但是实践发现都需要事先把旧的postgrsql集群停掉。后面如果发现描述有错误，再修正blog。
这篇blog是采用第一种方法升级，另外一篇blog会采用第二种方式升级：
编译安装9.1
# su - postgres
$ cd /tmp
$ wget https://ftp.postgresql.org/pub/source/v9.1.24/postgresql-9.1.24.tar.gz
$ tar -zxvf postgresql-9.1.24.tar.gz
$ cd postgresql-9.1.24
$ ./configure --prefix=/usr/pgsql-9.1 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt
$ gmake world
$ gmake install-world初始化集群
# su - postgres
$ /usr/pgsql-9.1/bin/initdb -E UTF8 -D /var/lib/pgsql/9.1/main --locale=zh_CN.UTF-8 -U postgres -W修改pg_hba.conf,postgresql.conf
初始化用户，用户库，用户表
postgres=# create user peiyb;
CREATE ROLE
postgres=# alter user peiyb with password 'peiybpeiyb';
ALTER ROLE
postgres=# create database peiybdb with owner=peiyb;
CREATE DATABASE
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
peiybdb | peiyb | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
postgres=# c peiybdb
You are now connected to database "peiybdb" as user "postgres".
peiybdb=# create extension dblink;
CREATE EXTENSION
peiybdb=#
peiybdb=# set role peiyb;
SET
peiybdb=> create table tmp_t0(c1 varchar(100),c2 varchar(100),c3 varchar(100));
CREATE TABLE
peiybdb=> insert into tmp_t0 select a,md5(a::varchar),md5(a::varchar) from generate_series(1,1000000) as a;
INSERT 0 1000000
peiybdb=# d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+--------+-------------
public | tmp_t0 | table | peiyb | 103 MB | 编译安装9.6
# su - postgres
$ cd /tmp
$ wget https://ftp.postgresql.org/pub/source/v9.6.9/postgresql-9.6.9.tar.gz
$ tar -zxvf postgresql-9.6.9.tar.gz
$ cd postgresql-9.6.9
$ ./configure --prefix=/usr/pgsql-9.6 --with-perl --with-python --with-openssl --with-pam --without-ldap --with-libxml --with-libxslt
$ gmake world
$ gmake install-world初始化集群
# su - postgres
$ /usr/pgsql-9.6/bin/initdb -E UTF8 -D /var/lib/pgsql/9.6/main --locale=zh_CN.UTF-8 -U postgres -W修改pg_hba.conf,postgresql.conf，确保端口和旧库端口不一致，本次修改为5433，升级嘛。
不要启动新库集群！！！
不要启动新库集群！！！
不要启动新库集群！！！
新库 pg_upgrade 帮助信息
$ /usr/pgsql-9.6/bin/pg_upgrade --help
pg_upgrade upgrades a PostgreSQL cluster to a different major version.
Usage:
pg_upgrade [OPTION]...
Options:
-b, --old-bindir=BINDIR old cluster executable directory
-B, --new-bindir=BINDIR new cluster executable directory
-c, --check check clusters only, don't change any data
-d, --old-datadir=DATADIR old cluster data directory
-D, --new-datadir=DATADIR new cluster data directory
-j, --jobs number of simultaneous processes or threads to use
-k, --link link instead of copying files to new cluster
-o, --old-options=OPTIONS old cluster options to pass to the server
-O, --new-options=OPTIONS new cluster options to pass to the server
-p, --old-port=PORT old cluster port number (default 50432)
-P, --new-port=PORT new cluster port number (default 50432)
-r, --retain retain SQL and log files after success
-U, --username=NAME cluster superuser (default "postgres")
-v, --verbose enable verbose internal logging
-V, --version display version information, then exit
-?, --help show this help, then exit
Before running pg_upgrade you must:
create a new database cluster (using the new version of initdb)
shutdown the postmaster servicing the old cluster
shutdown the postmaster servicing the new cluster
When you run pg_upgrade, you must provide the following information:
the data directory for the old cluster (-d DATADIR)
the data directory for the new cluster (-D DATADIR)
the "bin" directory for the old version (-b BINDIR)
the "bin" directory for the new version (-B BINDIR)
For example:
pg_upgrade -d oldCluster/data -D newCluster/data -b oldCluster/bin -B newCluster/bin
or
$ export PGDATAOLD=oldCluster/data
$ export PGDATANEW=newCluster/data
$ export PGBINOLD=oldCluster/bin
$ export PGBINNEW=newCluster/bin
$ pg_upgrade
Report bugs to <pgsql-bugs@postgresql.org>.
停止旧库
$ /usr/pgsql-9.1/bin/pg_ctl stop -m fast -D /var/lib/pgsql/9.1/main新库 pg_upgrade 检测兼容性
对数据文件的拷贝，如果数据量多，还是用链接(添加 –link)吧，能够减少业务中断时间。
# su - postgres
$ /usr/pgsql-9.6/bin/pg_upgrade -c -j 2 -U postgres -v -b /usr/pgsql-9.1/bin -B /usr/pgsql-9.6/bin -d /var/lib/pgsql/9.1/main -D /var/lib/pgsql/9.6/main -p 5432 -P 5433
如果看到如下的输出，说明检查ok，可以继续下一步了。
*Clusters are compatible*
"/usr/pgsql-9.6/bin/pg_ctl" -w -D "/var/lib/pgsql/9.6/main" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1新库 pg_upgrade 升级
# su - postgres
$ /usr/pgsql-9.6/bin/pg_upgrade -j 2 -U postgres -v -b /usr/pgsql-9.1/bin -B /usr/pgsql-9.6/bin -d /var/lib/pgsql/9.1/main -D /var/lib/pgsql/9.6/main -p 5432 -P 5433
看到如下输出，则说明升级成功
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查看新库、旧库数据目录大小。
# su - postgres
$ cd /var/lib/pgsql
$ du -sh *
313M 9.1
161M 9.6差异为旧库wal日志的大小
下面为旧库
$ cd /var/lib/pgsql/9.1/main/pg_xlog
$ ls -l
total 163840
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000001
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000002
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000003
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000004
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000005
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000006
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000007
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000008
-rw------- 1 postgres postgres 16777216 Jun 23 11:06 000000010000000000000009
-rw------- 1 postgres postgres 16777216 Jun 23 11:08 00000001000000000000000A
drwx------ 2 postgres postgres 6 Jun 23 09:50 archive_status
$ du -sh
161M .下面为新库
$ cd /var/lib/pgsql/9.6/main/pg_xlog/
$ ls -l
total 16384
-rw------- 1 postgres postgres 16777216 Jun 23 11:08 00000001000000000000000C
drwx------ 2 postgres postgres 6 Jun 23 10:43 archive_status
$ du -sh
16M .pg_upgrade analyze_new_cluster.sh
$ cat analyze_new_cluster.sh
#!/bin/sh
echo 'This script will generate minimal optimizer statistics rapidly'
echo 'so your system is usable, and then gather statistics twice more'
echo 'with increasing accuracy. When it is done, your system will'
echo 'have the default level of optimizer statistics.'
echo
echo 'If you have used ALTER TABLE to modify the statistics target for'
echo 'any tables, you might want to remove them and restore them after'
echo 'running this script because they will delay fast statistics generation.'
echo
echo 'If you would like default statistics as quickly as possible, cancel'
echo 'this script and run:'
echo ' "/usr/pgsql-9.6/bin/vacuumdb" -U 'postgres' --all --analyze-only'
echo
"/usr/pgsql-9.6/bin/vacuumdb" -U 'postgres' --all --analyze-in-stages
echo
echo 'Done'pg_upgrade delete_old_cluster.sh
$ cat delete_old_cluster.sh
#!/bin/sh
rm -rf '/var/lib/pgsql/9.1/main'启动新库
如果需要将新库的5433端口改为5432，则修改新库对应的 postgresql.conf
$ /usr/pgsql-9.6/bin/pg_ctl start -D /var/lib/pgsql/9.6/main/
$ ps -ef|grep -i postgres
postgres 5605 1 0 11:24 pts/1 00:00:00 /usr/pgsql-9.6/bin/postgres -D /var/lib/pgsql/9.6/main
postgres 5606 5605 0 11:24 ? 00:00:00 postgres: logger process
postgres 5608 5605 0 11:24 ? 00:00:00 postgres: checkpointer process
postgres 5609 5605 0 11:24 ? 00:00:00 postgres: writer process
postgres 5610 5605 0 11:24 ? 00:00:00 postgres: wal writer process
postgres 5611 5605 0 11:24 ? 00:00:00 postgres: autovacuum launcher process
postgres 5612 5605 0 11:24 ? 00:00:00 postgres: stats collector process
postgres 5626 25814 0 11:25 pts/1 00:00:00 ps -ef
postgres 5627 25814 0 11:25 pts/1 00:00:00 grep --color=auto -i postgres
登录新库
$ cd /usr/pgsql-9.6/bin/
$ ./psql -p 5433
psql (9.6.9)
Type "help" for help.
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
peiybdb | peiyb | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | postgres=CTc/postgres+
| | | | | =c/postgres
(4 rows)
postgres=# c peiybdb
You are now connected to database "peiybdb" as user "postgres".
peiybdb=# d+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------+-------+-------+--------+-------------
public | tmp_t0 | table | peiyb | 103 MB |
(1 row)
peiybdb=# select count(1) from tmp_t0;
count
---------
1000000
(1 row)至此，pg_upgrade的第一种升级完毕。
参考：
https://www.postgresql.org/docs/9.6/static/pgupgrade.html
http://postgres.cn/docs/9.6/pgupgrade.html
下面是pg_upgrade的日志输出，有兴趣的哥们可以看下，对于深入了解postgresql绝对有帮助。
$ /usr/pgsql-9.6/bin/pg_upgrade -j 2 -U postgres -v -b /usr/pgsql-9.1/bin -B /usr/pgsql-9.6/bin -d /var/lib/pgsql/9.1/main -D /var/lib/pgsql/9.6/main -p 5432 -P 5433
Running in verbose mode
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
pg_control values:
First log file ID after reset: 0
First log file segment after reset: 11
pg_control version number: 903
Catalog version number: 201105231
Database system identifier: 6570091506641514495
Latest checkpoint's TimeLineID: 1
Latest checkpoint's NextXID: 0/1895
Latest checkpoint's NextOID: 16434
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1877
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Current pg_control values:
pg_control version number: 960
Catalog version number: 201608131
Database system identifier: 6570105243309453745
Latest checkpoint's TimeLineID: 1
Latest checkpoint's full_page_writes: off
Latest checkpoint's NextXID: 0:1758
Latest checkpoint's NextOID: 13324
Latest checkpoint's NextMultiXactId: 1
Latest checkpoint's NextMultiOffset: 0
Latest checkpoint's oldestXID: 1751
Latest checkpoint's oldestXID's DB: 1
Latest checkpoint's oldestActiveXID: 0
Latest checkpoint's oldestMultiXid: 1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Maximum data alignment: 8
Database block size: 8192
Blocks per segment of large relation: 131072
WAL block size: 8192
Bytes per WAL segment: 16777216
Maximum length of identifiers: 64
Maximum columns in an index: 32
Maximum size of a TOAST chunk: 1996
Size of a large-object chunk: 2048
Date/time type storage: 64-bit integers
Float4 argument passing: by value
Float8 argument passing: by value
Data page checksum version: 0
Values to be changed:
First log segment after reset: 000000010000000000000002
"/usr/pgsql-9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.1/main" -o "-p 5432 -b -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directory='/var/lib/pgsql/9.6/main'" start >> "pg_upgrade_server.log" 2>&1
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, t.spclocation AS spclocation FROM pg_catalog.pg_database d LEFT OUTER JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, t.spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, t.spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, t.spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
old databases:
Database: peiybdb
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
relname: public.tmp_t0: reloid: 16431 reltblspace:
Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT spclocation FROM pg_catalog.pg_tablespace WHERE spcname != 'pg_default' AND spcname != 'pg_global'
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT DISTINCT probin FROM pg_catalog.pg_proc WHERE prolang = 13 AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT DISTINCT probin FROM pg_catalog.pg_proc WHERE prolang = 13 AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT DISTINCT probin FROM pg_catalog.pg_proc WHERE prolang = 13 AND probin IS NOT NULL AND oid >= 16384;
executing: SELECT pg_catalog.set_config('search_path', '', false)
Checking database user is the install user executing: SELECT rolsuper, oid FROM pg_catalog.pg_roles WHERE rolname = current_user AND rolname !~ '^pg_'
executing: SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'
ok
Checking database connection settings executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT datname, datallowconn FROM pg_catalog.pg_database
ok
executing: SELECT pg_catalog.set_config('search_path', '', false)
Checking for prepared transactions executing: SELECT * FROM pg_catalog.pg_prepared_xacts
ok
Checking for reg* system OID user data types executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ( 'pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype,'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ( 'pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype,'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid IN ( 'pg_catalog.regproc'::pg_catalog.regtype, 'pg_catalog.regprocedure'::pg_catalog.regtype, 'pg_catalog.regoper'::pg_catalog.regtype,'pg_catalog.regoperator'::pg_catalog.regtype, 'pg_catalog.regconfig'::pg_catalog.regtype, 'pg_catalog.regdictionary'::pg_catalog.regtype) AND c.relnamespace = n.oid AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ok
Checking for contrib/isn with bigint-passing mismatch ok
executing: SELECT pg_catalog.set_config('search_path', '', false)
Checking for roles starting with 'pg_' executing: SELECT * FROM pg_catalog.pg_roles WHERE rolname ~ '^pg_'
ok
Checking for invalid "line" user columns executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid = 'pg_catalog.line'::pg_catalog.regtype AND c.relnamespace = n.oid AND n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid = 'pg_catalog.line'::pg_catalog.regtype AND c.relnamespace = n.oid AND n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT n.nspname, c.relname, a.attname FROM pg_catalog.pg_class c, pg_catalog.pg_namespace n, pg_catalog.pg_attribute a WHERE c.oid = a.attrelid AND NOT a.attisdropped AND a.atttypid = 'pg_catalog.line'::pg_catalog.regtype AND c.relnamespace = n.oid AND n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ok
Creating dump of global objects "/usr/pgsql-9.6/bin/pg_dumpall" --host '/var/lib/pgsql/9.6/main' --port 5432 --username 'postgres' --globals-only --quote-all-identifiers --binary-upgrade --verbose -f pg_upgrade_dump_globals.sql >> "pg_upgrade_utility.log" 2>&1
ok
Creating dump of database schemas
"/usr/pgsql-9.6/bin/pg_dump" --host '/var/lib/pgsql/9.6/main' --port 5432 --username 'postgres' --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_16385.custom" 'dbname=peiybdb' >> "pg_upgrade_dump_16385.log" 2>&1
"/usr/pgsql-9.6/bin/pg_dump" --host '/var/lib/pgsql/9.6/main' --port 5432 --username 'postgres' --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_12835.custom" 'dbname=postgres' >> "pg_upgrade_dump_12835.log" 2>&1
"/usr/pgsql-9.6/bin/pg_dump" --host '/var/lib/pgsql/9.6/main' --port 5432 --username 'postgres' --schema-only --quote-all-identifiers --binary-upgrade --format=custom --verbose --file="pg_upgrade_dump_1.custom" 'dbname=template1' >> "pg_upgrade_dump_1.log" 2>&1
ok
"/usr/pgsql-9.1/bin/pg_ctl" -w -D "/var/lib/pgsql/9.1/main" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1
"/usr/pgsql-9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.6/main" -o "-p 5433 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql/9.6/main'" start >> "pg_upgrade_server.log" 2>&1
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM pg_catalog.pg_database d LEFT OUTER JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
new databases:
Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
executing: SELECT pg_catalog.set_config('search_path', '', false)
Checking for presence of required libraries ok
executing: SELECT pg_catalog.set_config('search_path', '', false)
Checking database user is the install user executing: SELECT rolsuper, oid FROM pg_catalog.pg_roles WHERE rolname = current_user AND rolname !~ '^pg_'
executing: SELECT COUNT(*) FROM pg_catalog.pg_roles WHERE rolname !~ '^pg_'
ok
executing: SELECT pg_catalog.set_config('search_path', '', false)
Checking for prepared transactions executing: SELECT * FROM pg_catalog.pg_prepared_xacts
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 "/usr/pgsql-9.6/bin/vacuumdb" --host '/var/lib/pgsql/9.6/main' --port 5433 --username 'postgres' --all --analyze --verbose >> "pg_upgrade_utility.log" 2>&1
ok
Freezing all rows on the new cluster "/usr/pgsql-9.6/bin/vacuumdb" --host '/var/lib/pgsql/9.6/main' --port 5433 --username 'postgres' --all --freeze --verbose >> "pg_upgrade_utility.log" 2>&1
ok
"/usr/pgsql-9.6/bin/pg_ctl" -w -D "/var/lib/pgsql/9.6/main" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1
Deleting files from new pg_clog ok
Copying old pg_clog to new server cp -Rf "/var/lib/pgsql/9.1/main/pg_clog" "/var/lib/pgsql/9.6/main/pg_clog" >> "pg_upgrade_utility.log" 2>&1
ok
Setting next transaction ID and epoch for new cluster "/usr/pgsql-9.6/bin/pg_resetxlog" -f -x 1895 "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
"/usr/pgsql-9.6/bin/pg_resetxlog" -f -e 0 "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
"/usr/pgsql-9.6/bin/pg_resetxlog" -f -c 1895,1895 "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
ok
Deleting files from new pg_multixact/offsets ok
Setting oldest multixact ID on new cluster "/usr/pgsql-9.6/bin/pg_resetxlog" -m 2,1 "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
ok
Resetting WAL archives "/usr/pgsql-9.6/bin/pg_resetxlog" -l 00000001000000000000000B "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
ok
"/usr/pgsql-9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.6/main" -o "-p 5433 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql/9.6/main'" start >> "pg_upgrade_server.log" 2>&1
Setting frozenxid and minmxid counters in new cluster executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_database SET datfrozenxid = '1895'
executing: UPDATE pg_catalog.pg_database SET datminmxid = '1'
executing: SELECT datname, datallowconn FROM pg_catalog.pg_database
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relfrozenxid = '1895' WHERE relkind IN ('r', 'm', 't')
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relfrozenxid = '1895' WHERE relkind IN ('r', 'm', 't')
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
executing: ALTER DATABASE "template0" ALLOW_CONNECTIONS = true
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relfrozenxid = '1895' WHERE relkind IN ('r', 'm', 't')
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
executing: ALTER DATABASE "template0" ALLOW_CONNECTIONS = false
ok
Restoring global objects in the new cluster "/usr/pgsql-9.6/bin/psql" --echo-queries --set ON_ERROR_STOP=on --no-psqlrc --dbname=template1 --host '/var/lib/pgsql/9.6/main' --port 5433 --username 'postgres' -f "pg_upgrade_dump_globals.sql" >> "pg_upgrade_utility.log" 2>&1
ok
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM pg_catalog.pg_database d LEFT OUTER JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
new databases:
Database: peiybdb
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
Restoring database schemas in the new cluster
"/usr/pgsql-9.6/bin/pg_restore" --host '/var/lib/pgsql/9.6/main' --port 5433 --username 'postgres' --exit-on-error --verbose --dbname 'dbname=peiybdb' "pg_upgrade_dump_16385.custom" >> "pg_upgrade_dump_16385.log" 2>&1
"/usr/pgsql-9.6/bin/pg_restore" --host '/var/lib/pgsql/9.6/main' --port 5433 --username 'postgres' --exit-on-error --verbose --dbname 'dbname=postgres' "pg_upgrade_dump_12835.custom" >> "pg_upgrade_dump_12835.log" 2>&1
"/usr/pgsql-9.6/bin/pg_restore" --host '/var/lib/pgsql/9.6/main' --port 5433 --username 'postgres' --exit-on-error --verbose --dbname 'dbname=template1' "pg_upgrade_dump_1.custom" >> "pg_upgrade_dump_1.log" 2>&1
ok
Setting minmxid counter in new cluster executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_database SET datminmxid = '1'
executing: SELECT datname, datallowconn FROM pg_catalog.pg_database
executing: ALTER DATABASE "template0" ALLOW_CONNECTIONS = true
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
executing: ALTER DATABASE "template0" ALLOW_CONNECTIONS = false
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: UPDATE pg_catalog.pg_class SET relminmxid = '1' WHERE relkind IN ('r', 'm', 't')
ok
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: SELECT d.oid, d.datname, d.encoding, d.datcollate, d.datctype, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM pg_catalog.pg_database d LEFT OUTER JOIN pg_catalog.pg_tablespace t ON d.dattablespace = t.oid WHERE d.datallowconn = true ORDER BY 2
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
executing: SELECT pg_catalog.set_config('search_path', '', false)
executing: WITH regular_heap (reloid, indtable, toastheap) AS ( SELECT c.oid, 0::oid, 0::oid FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid WHERE relkind IN ('r', 'm', 'S') AND ((n.nspname !~ '^pg_temp_' AND n.nspname !~ '^pg_toast_temp_' AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade', 'pg_toast') AND c.oid >= 16384::pg_catalog.oid) OR (n.nspname = 'pg_catalog' AND relname IN ('pg_largeobject', 'pg_largeobject_metadata') ))), toast_heap (reloid, indtable, toastheap) AS ( SELECT c.reltoastrelid, 0::oid, c.oid FROM regular_heap JOIN pg_catalog.pg_class c ON regular_heap.reloid = c.oid WHERE c.reltoastrelid != 0), all_index (reloid, indtable, toastheap) AS ( SELECT indexrelid, indrelid, 0::oid FROM pg_catalog.pg_index WHERE indisvalid AND indisready AND indrelid IN (SELECT reloid FROM regular_heap UNION ALL SELECT reloid FROM toast_heap)) SELECT all_rels.*, n.nspname, c.relname, c.relfilenode, c.reltablespace, pg_catalog.pg_tablespace_location(t.oid) AS spclocation FROM (SELECT * FROM regular_heap UNION ALL SELECT * FROM toast_heap UNION ALL SELECT * FROM all_index) all_rels JOIN pg_catalog.pg_class c ON all_rels.reloid = c.oid JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid LEFT OUTER JOIN pg_catalog.pg_tablespace t ON c.reltablespace = t.oid ORDER BY 1;
new databases:
Database: peiybdb
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
relname: public.tmp_t0: reloid: 16431 reltblspace:
Database: postgres
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
Database: template1
relname: pg_catalog.pg_largeobject: reloid: 2613 reltblspace:
relname: pg_catalog.pg_largeobject_loid_pn_index: reloid: 2683 reltblspace:
relname: pg_catalog.pg_largeobject_metadata: reloid: 2995 reltblspace:
relname: pg_catalog.pg_largeobject_metadata_oid_index: reloid: 2996 reltblspace:
"/usr/pgsql-9.6/bin/pg_ctl" -w -D "/var/lib/pgsql/9.6/main" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1
Copying user relation files
mappings for database "peiybdb":
pg_catalog.pg_largeobject: 12707 to 2613
pg_catalog.pg_largeobject_loid_pn_index: 12709 to 2683
pg_catalog.pg_largeobject_metadata: 12658 to 2995
pg_catalog.pg_largeobject_metadata_oid_index: 12660 to 2996
public.tmp_t0: 16431 to 16431
copying "/var/lib/pgsql/9.1/main/base/16385/12707" to "/var/lib/pgsql/9.6/main/base/16400/2613"
copying "/var/lib/pgsql/9.1/main/base/16385/12709" to "/var/lib/pgsql/9.6/main/base/16400/2683"
copying "/var/lib/pgsql/9.1/main/base/16385/12658" to "/var/lib/pgsql/9.6/main/base/16400/2995"
copying "/var/lib/pgsql/9.1/main/base/16385/12660" to "/var/lib/pgsql/9.6/main/base/16400/2996"
copying "/var/lib/pgsql/9.1/main/base/16385/16431" to "/var/lib/pgsql/9.6/main/base/16400/16431"
copying "/var/lib/pgsql/9.1/main/base/16385/16431_fsm" to "/var/lib/pgsql/9.6/main/base/16400/16431_fsm"
mappings for database "postgres":
pg_catalog.pg_largeobject: 12707 to 2613
pg_catalog.pg_largeobject_loid_pn_index: 12709 to 2683
pg_catalog.pg_largeobject_metadata: 12658 to 2995
pg_catalog.pg_largeobject_metadata_oid_index: 12660 to 2996
copying "/var/lib/pgsql/9.1/main/base/12835/12707" to "/var/lib/pgsql/9.6/main/base/13323/2613"
copying "/var/lib/pgsql/9.1/main/base/12835/12709" to "/var/lib/pgsql/9.6/main/base/13323/2683"
copying "/var/lib/pgsql/9.1/main/base/12835/12658" to "/var/lib/pgsql/9.6/main/base/13323/2995"
copying "/var/lib/pgsql/9.1/main/base/12835/12660" to "/var/lib/pgsql/9.6/main/base/13323/2996"
mappings for database "template1":
pg_catalog.pg_largeobject: 12707 to 2613
pg_catalog.pg_largeobject_loid_pn_index: 12709 to 2683
pg_catalog.pg_largeobject_metadata: 12658 to 2995
pg_catalog.pg_largeobject_metadata_oid_index: 12660 to 2996
copying "/var/lib/pgsql/9.1/main/base/1/12707" to "/var/lib/pgsql/9.6/main/base/1/2613"
copying "/var/lib/pgsql/9.1/main/base/1/12709" to "/var/lib/pgsql/9.6/main/base/1/2683"
copying "/var/lib/pgsql/9.1/main/base/1/12658" to "/var/lib/pgsql/9.6/main/base/1/2995"
copying "/var/lib/pgsql/9.1/main/base/1/12660" to "/var/lib/pgsql/9.6/main/base/1/2996"
ok
Setting next OID for new cluster "/usr/pgsql-9.6/bin/pg_resetxlog" -o 16434 "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
ok
Sync data directory to disk "/usr/pgsql-9.6/bin/initdb" --sync-only "/var/lib/pgsql/9.6/main" >> "pg_upgrade_utility.log" 2>&1
ok
Creating script to analyze new cluster ok
Creating script to delete old cluster ok
"/usr/pgsql-9.6/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.6/main" -o "-p 5433 -b -c synchronous_commit=off -c fsync=off -c full_page_writes=off -c listen_addresses='' -c unix_socket_permissions=0700 -c unix_socket_directories='/var/lib/pgsql/9.6/main'" start >> "pg_upgrade_server.log" 2>&1
"/usr/pgsql-9.6/bin/pg_ctl" -w -D "/var/lib/pgsql/9.6/main" -o "" -m smart stop >> "pg_upgrade_server.log" 2>&1
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