pg_upgrade 升级数据库方式二 链接

环境如下:os: centos6.8
postgresql: old 9.1
postgresql: new 9.6

pg_upgrade 是postgresql 大版本升级的得力工具。主要有两种用法:

  1. 使用pg_upgrade拷贝升级。
  2. 使用pg_upgrade链接升级。

网上搜索说 “拷贝升级虽然耗时长,但是原库还可以用。链接升级虽然耗时短,但是原库不可以用。”但是实践发现都需要事先把旧的postgrsql集群停掉。后面如果发现描述有错误,再修正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>.

升级

新库 pg_upgrade 检测兼容性
链接方式 是添加 –link 参数

# su - postgres
$ /usr/pgsql-9.6/bin/pg_upgrade -c --link -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 --link -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

如果看到的信息如下,则说明旧库没有关闭

*failure*
There were problems executing ""/usr/pgsql-9.1/bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "/var/lib/pgsql/9.1/main" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000  -c listen_addresses='' -c unix_socket_permissions=0700" start >> "pg_upgrade_server.log" 2>&1"
Consult the last few lines of "pg_upgrade_server.log" for
the probable cause of the failure.

There seems to be a postmaster servicing the old cluster.
Please shutdown that postmaster and try again.
Failure, exiting

查看新库、旧库数据目录大小。

# su - postgres
$ cd /var/lib/pgsql
$ du -sh *
298M    9.1
48M 9.6

可以看出新库的目录大小并没有发生什么变化。
对比下

$ cd /var/lib/pgsql/9.6
$ du -sh
159M    

可以看到 48M 与 159M 的差异。找到 tmp_t0 对应的文件

$ cd /var/lib/pgsql/9.6/main/base/16400
$ ls -lS|more
total 123088
-rw------- 2 postgres postgres 118538240 Jun 23 19:28 16431

貌似是个正常的文件,又仔细看了pg_upgrade的文档,原来是硬链接。
那就是判断是否有相同 inode

$ ls -li /var/lib/pgsql/9.6/main/base/16400/16431
102593505 -rw------- 2 postgres postgres 118538240 Jun 23 19:28 /var/lib/pgsql/9.6/main/base/16400/16431
$ ls -li /var/lib/pgsql/9.1/main/base/16385/16431
102593505 -rw------- 2 postgres postgres 118538240 Jun 23 19:28 /var/lib/pgsql/9.1/main/base/16385/16431

执行 analyze_new_cluster.sh和delete_old_cluster.sh

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

同样,下面是使用 –link 方式的日志信息,要仔细分析分析。

-bash-4.2$ /usr/pgsql-9.6/bin/pg_upgrade --link -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/1896
Latest checkpoint's NextOID:          16438
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:           6570284288719362492
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 1896 "/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 1896,1896 "/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 = '1896'
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 = '1896' 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 = '1896' 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 = '1896' 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
Adding ".old" suffix to old global/pg_control               ok

If you want to start the old cluster, you will need to remove
the ".old" suffix from /var/lib/pgsql/9.1/main/global/pg_control.old.
Because "link" mode was used, the old cluster cannot be safely
started once the new cluster has been started.

Linking 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


linking "/var/lib/pgsql/9.1/main/base/16385/12707" to "/var/lib/pgsql/9.6/main/base/16400/2613"
linking "/var/lib/pgsql/9.1/main/base/16385/12709" to "/var/lib/pgsql/9.6/main/base/16400/2683"
linking "/var/lib/pgsql/9.1/main/base/16385/12658" to "/var/lib/pgsql/9.6/main/base/16400/2995"
linking "/var/lib/pgsql/9.1/main/base/16385/12660" to "/var/lib/pgsql/9.6/main/base/16400/2996"
linking "/var/lib/pgsql/9.1/main/base/16385/16431" to "/var/lib/pgsql/9.6/main/base/16400/16431"
linking "/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


linking "/var/lib/pgsql/9.1/main/base/12835/12707" to "/var/lib/pgsql/9.6/main/base/13323/2613"
linking "/var/lib/pgsql/9.1/main/base/12835/12709" to "/var/lib/pgsql/9.6/main/base/13323/2683"
linking "/var/lib/pgsql/9.1/main/base/12835/12658" to "/var/lib/pgsql/9.6/main/base/13323/2995"
linking "/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


linking "/var/lib/pgsql/9.1/main/base/1/12707" to "/var/lib/pgsql/9.6/main/base/1/2613"
linking "/var/lib/pgsql/9.1/main/base/1/12709" to "/var/lib/pgsql/9.6/main/base/1/2683"
linking "/var/lib/pgsql/9.1/main/base/1/12658" to "/var/lib/pgsql/9.6/main/base/1/2995"
linking "/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 16438 "/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
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: 代码科技 设计师:Amelia_0503 返回首页