1SELECT table_name
2  FROM information_schema.tables
3 WHERE (table_catalog, table_schema, table_name) NOT IN
4          (SELECT table_catalog, table_schema, table_name
5             FROM information_schema.table_constraints
6            WHERE constraint_type = 'PRIMARY KEY')
7   AND table_schema IN ('myschema')

1# prodvider 上配置允许 super user 从 subscriber 上访问 replication。
2# 假如 subscriber IP 是 10.1.10.15，则 provider 的 pg_hba.conf 中应包含以下配置
3host    all             postgres       10.1.10.15/32               trust
4host    replication     postgres       10.1.10.15/32                 trust

1# subscriber 上允许 super user 访问本地的 postgresql，
2# 假如 subscriber IP 是 10.1.10.6，pg_hba.conf 中应包含以下配置
3host    all             postgres             10.1.10.6/32            trust

1sudo -iu postgres pg_dumpall --schema-only -f dump.sql

1sudo -iu postgres psql -f dump.sql

1apt install postgresql-9.5-pglogical/xenial-pgdg

1-- host 填的是provider的IP
2mydb=# SELECT pglogical.create_node(node_name := 'provider', dsn := 'host=10.1.10.6 port=5432 dbname=mydb');

1mydb=# SELECT pglogical.replication_set_add_all_tables('default', ARRAY['public', 'mydb', 'monitoring', 'authenticator']);

1postgres@provider:~$ for i in `psql mydb -c '\d' | grep table | cut -d '|' -f 2`; do psql mydb -c "select pglogical.replication_set_add_table('default', '$i')"; done

1apt install postgresql-10-pglogical/xenial-pgdg

1shared_preload_libraries = 'pglogical'

1mydb=# CREATE EXTENSION pglogical;

1-- host 填的是subscriber的IP
2mydb=# SELECT pglogical.create_node(node_name := 'subscriber', dsn := 'host=10.1.10.15 port=5432 dbname=mydb');

1-- host 填的是provider的IP
2mydb=# SELECT pglogical.create_subscription( subscription_name := 'subscriber', provider_dsn := 'host=10.1.10.6 port=5432 dbname=mydb');

1mydb=# SELECT pglogical.replication_set_add_all_sequences('default', ARRAY['public', 'mydb', 'monitoring', 'authenticator'], true);

1mydb=# select * from pglogical.drop_subscription('subscriber');

1mydb=# select * from pglogical.drop_node('subscriber');

1mydb=# select * from pglogical.drop_node('provider');

 1export LC_CTYPE=en_US.UTF-8
 2export LC_ALL=en_US.UTF-8
 3
 4apt update
 5
 6# 安装pg 10
 7apt install postgresql-10
 8
 9# 安装索引检查插件
10apt install postgresql-10-amcheck

1service postgresql@9.5-main stop
2service postgresql@10-main stop

1user@ubuntu:~# pg_lsclusters
2
3Ver Cluster Port Status Owner    Data directory               Log file
49.5 main    5432 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-%d.csv
510  main    5433 down   postgres /var/lib/postgresql/10/main  /var/log/postgresql/postgresql-10-main.log

1user@ubuntu:~# pg_dropcluster --stop 10 main

 1user@ubuntu:~# pg_upgradecluster --method upgrade --link 9.5 main
 2
 3Finding the real data directory for the source cluster      ok
 4Finding the real data directory for the target cluster      ok
 5Performing Consistency Checks
 6-----------------------------
 7Checking cluster versions                                   ok
 8Checking database user is the install user                  ok
 9Checking database connection settings                       ok
10Checking for prepared transactions                          ok
11Checking for reg* data types in user tables                 ok
12Checking for contrib/isn with bigint-passing mismatch       ok
13Checking for invalid "unknown" user columns                 ok
14Checking for roles starting with "pg_"                      ok
15Creating dump of global objects                             ok
16Creating dump of database schemas
17                                                            ok
18Checking for presence of required libraries                 ok
19Checking database user is the install user                  ok
20Checking for prepared transactions                          ok
21 
22If pg_upgrade fails after this point, you must re-initdb the
23new cluster before continuing.
24 
25Performing Upgrade
26------------------
27Analyzing all rows in the new cluster                       ok
28Freezing all rows in the new cluster                        ok
29Deleting files from new pg_xact                             ok
30Copying old pg_clog to new server                           ok
31Setting next transaction ID and epoch for new cluster       ok
32Deleting files from new pg_multixact/offsets                ok
33Copying old pg_multixact/offsets to new server              ok
34Deleting files from new pg_multixact/members                ok
35Copying old pg_multixact/members to new server              ok
36Setting next multixact ID and offset for new cluster        ok
37Resetting WAL archives                                      ok
38Setting frozenxid and minmxid counters in new cluster       ok
39Restoring global objects in the new cluster                 ok
40Restoring database schemas in the new cluster
41                                                            ok
42Adding ".old" suffix to old global/pg_control               ok
43 
44If you want to start the old cluster, you will need to remove
45the ".old" suffix from /var/lib/postgresql/9.5/main/global/pg_control.old.
46Because "link" mode was used, the old cluster cannot be safely
47started once the new cluster has been started.
48 
49Linking user relation files
50                                                            ok
51Setting next OID for new cluster                            ok
52Sync data directory to disk                                 ok
53Creating script to analyze new cluster                      ok
54Creating script to delete old cluster                       ok
55Checking for hash indexes                                   ok
56 
57Upgrade Complete
58----------------
59Optimizer statistics are not transferred by pg_upgrade so,
60once you start the new server, consider running:
61    ./analyze_new_cluster.sh
62 
63Running this script will delete the old cluster's data files:
64    ./delete_old_cluster.sh
65pg_upgrade output scripts are in /var/log/postgresql/pg_upgradecluster-9.5-10-main.yo8D
66Re-enabling connections to the old cluster...
67Copying old configuration files...
68Copying old start.conf...
69Copying old pg_ctl.conf...
70Disabling automatic startup of old cluster...
71Configuring old cluster to use a different port (5433)...
72Success. Please check that the upgraded cluster works. If it does,
73you can remove the old cluster with
74    pg_dropcluster 9.5 main
75 
76Ver Cluster Port Status Owner    Data directory               Log file
779.5 main    5433 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-%d.csv
78Ver Cluster Port Status Owner    Data directory              Log file
7910  main    5432 down   postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-%d.csv

1cp postgresql.conf /etc/postgresql/10/main/postgresql.conf

1service postgresql@10-main start

1pg_dropcluster --stop 9.5 main

1pg_lsclusters
2
3Ver Cluster Port Status Owner    Data directory              Log file
410  main    5432 online postgres /var/lib/postgresql/10/main /var/log/postgresql/postgresql-%d.csv

1sudo -iu postgres psql -c 'vacuum analyze'