pgrepup で論理レプリケーションによる PostgreSQL の Upgrade

## きっかけ

先日、[pgrepup – upgrade PostgreSQL using logical replication](http://gasparin.net/2016/11/pgrepup-upgrade-postgresql-using-logical-replication/) という記事で [pgrepup](https://github.com/rtshome/pgrepup) というツールの存在を知りました。

これを試してみます。環境は次の通り。

CentOS 7.2.1511
PostgreSQL 9.4.10
PostgreSQL 9.6.1
pgrepup 0.3.7

## Logical Decoding

PostgreSQL では 9.4 から Logical Decoding という機能が追加されています。
[第46章ロジカルデコーディング](https://www.postgresql.jp/document/pg940doc/html/logicaldecoding.html) ([Chapter 46. Logical Decoding](https://www.postgresql.org/docs/9.4/static/logicaldecoding.html))

Qiita にも記事があります [PostgreSQLのLogical Decoding機能についての紹介](http://qiita.com/bwtakacy/items/d8461518a1770524e0d6) 。また、[PostgreSQL Advent Calendar 2016](http://qiita.com/advent-calendar/2016/postgresql) の1日目でも紹介されています「[Logical Decodingを使ったCDC（Change Data Capture）の実現方法を考えてみる](http://pgsqldeepdive.blogspot.jp/2016/12/logical-decoding-change-data-capture.html)」

pgrepup はこの Logical Decoding で論理レプリケーションを行いメジャーバージョンアップを簡単に行うためのツールです。

Github repository の説明には次のように書かれています

> Pgrepup is a tool for upgrading through PostgreSQL major versions using logical replication and pglogical extension

PostgreSQL での replication といえば WAL (Write Ahead Logging) ファイルの転送に始まり、Streaming Replication がこれまでの標準構成でこれからもそうだと思いますが、これでは異なるバージョン間でのレプリケーションが行えないために MySQL のようなバージョンアップ方法が使えませんでした。([Slony-I](http://www.slony.info/) や [Pgpool-II](http://www.pgpool.net/) を使うという方法はある)

（ちょっと脱線）これは Oracle Database も似た感じで redo log と WAL が同じような役割で archive log 転送で replica を作成するというのがお金をかけない replication で [SharePlex](https://www.quest.com/jp-ja/products/shareplex/) や [GoldenGate](http://www.oracle.com/technetwork/jp/middleware/goldengate/overview/index.html) が Logical Decoding っぽい有償 replication ツールです。異なるバージョン間でも使えるためバージョンアップの停止時間短縮に使えます。LogMinor という redo log の decode ツールもあります、これは無償。

## pglogical

pgrepup では論理レプリケーションに [pglogical](https://2ndquadrant.com/it/resources/pglogical/) ([pglogical Docs](https://2ndquadrant.com/en/resources/pglogical/pglogical-docs/)) を使います。


pglogical は Logical Decoding 機能を使うため PostgreSQL 9.4 以上が必須で、origin filtering と conflict detection のためには 9.5 以上が必要となります。

## 試してみる構成

PostgreSQL 9.4 から 9.6 への更新を模してみます。
pgrepup は PostgreSQL サーバーのホストにインストールする必要はなく、ネットワーク越しにレプリケーション元、先の2台の PostgreSQL にアクセスできれば良いので3台のサーバーを用意します。

| Hostname |  用途            | PostgreSQL Version | IP Address      |
|----------|-----------------|--------------------|------------------|
| db1      | Source DB       | 9.4.10             | 10.130.5.106/16  |
| db2      | Destination DB  | 9.6.1              | 10.130.11.130/16 |
| repup    | pgrepup         | (9.6)              | 10.130.21.132/16 |

DigitalOcean の仮想サーバーを使いました。（DigitalOcean は初期状態では root でログインすることになるのでそのまま root で作業していますが権限として root が必要な作業ばかりではありません）
pgrepup は Python 2.7 以上が必要ですが、CentOS 7 の Python は 2.7.5 なのでこれをそのまま使います。

## 準備

### db1 (Source)

レプリケーション元の DB サーバーを PostgreSQL 9.4 でセットアップします。テスト用のユーザー (scott) と DB (testdb) を作成し　`animal` と `fish` という table を作っておきます。後で理由がわかりますが `animal` は PRIMARY KEY 有り、`fish` は無しです。
pgrepup から postgres ユーザーでアクセスするためパスワードを設定します。
pgrepup がどんな Query を発行しているのか確認できるように `log_statement = 'all'` としています。`/var/lib/pgsql/9.4/data/pg_log/` にログ出力されます。

```
yum -y install https://download.postgresql.org/pub/repos/yum/9.4/redhat/rhel-7-x86_64/pgdg-centos94-9.4-3.noarch.rpm
yum -y install postgresql94-server postgresql94-contrib
yum -y install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-2.noarch.rpm
yum -y install postgresql94-pglogical
/usr/pgsql-9.4/bin/postgresql94-setup initdb
sed -i -e "s/^#\?listen_addresses =.*/listen_addresses = '*'/" \
       -e "s/^#\?wal_level =.*/wal_level = logical/" \
       -e "s/^#\?max_worker_processes =.*/max_worker_processes = 10/" \
       -e "s/^#\?max_replication_slots =.*/max_replication_slots = 10/" \
       -e "s/^#\?max_wal_senders =.*/max_wal_senders = 10/" \
       -e "s/^#\?shared_preload_libraries =.*/shared_preload_libraries = 'pglogical'/" \
       -e "s/^#\?log_line_prefix =.*/log_line_prefix = '%t user=%u, db=%d, remote=%r, pid=%p, xid=%x '/" \
       -e "s/^#\?log_statement =.*/log_statement = 'all'" \
       /var/lib/pgsql/9.4/data/postgresql.conf
echo "host all all 10.130.0.0/16 md5" >> /var/lib/pgsql/9.4/data/pg_hba.conf
systemctl start postgresql-9.4
su -l postgres -c psql
postgres=# ALTER USER postgres PASSWORD 'secret';
postgres=# CREATE USER scott PASSWORD 'tiger';
postgres=# CREATE DATABASE testdb OWNER scott ENCODING 'UTF-8';
postgres=# \c testdb
testdb=# set role scott;
testdb=> create table animal (id serial primary key, name text, created_at timestamp default current_timestamp);
testdb=> create table fish (id serial, name text, created_at timestamp default current_timestamp);
testdb=> INSERT INTO animal (name) VALUES ('cat');
testdb=> INSERT INTO animal (name) VALUES ('dog');
testdb=> INSERT INTO fish (name) VALUES ('saba');
testdb=> INSERT INTO fish (name) VALUES ('maguro');
```

### db2 (Destination)

レプリケーション先の DB を PostgreSQL 9.6 でセットアップします。
pgrepup から postgres ユーザーでアクセスするためパスワードを設定します。
こちらには DB は追加しません。

```
yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum -y install postgresql96-server postgresql96-contrib
yum -y install http://packages.2ndquadrant.com/pglogical/yum-repo-rpms/pglogical-rhel-1.0-2.noarch.rpm
yum -y install postgresql96-pglogical
/usr/pgsql-9.6/bin/postgresql96-setup initdb
sed -i -e "s/^#\?listen_addresses =.*/listen_addresses = '*'/" \
       -e "s/^#\?wal_level =.*/wal_level = logical/" \
       -e "s/^#\?max_worker_processes =.*/max_worker_processes = 10/" \
       -e "s/^#\?max_replication_slots =.*/max_replication_slots = 10/" \
       -e "s/^#\?max_wal_senders =.*/max_wal_senders = 10/" \
       -e "s/^#\?shared_preload_libraries =.*/shared_preload_libraries = 'pglogical'/" \
       -e "s/^#\?log_line_prefix =.*/log_line_prefix = '%t user=%u, db=%d, remote=%r, pid=%p, xid=%x '/" \
       -e "s/^#\?log_statement =.*/log_statement = 'all'" \
       /var/lib/pgsql/9.6/data/postgresql.conf
echo "host all all 10.130.0.0/16 md5" >> /var/lib/pgsql/9.6/data/pg_hba.conf
systemctl start postgresql-9.6
su -l postgres -c psql
postgres=# ALTER USER postgres PASSWORD 'secret';
```

### repup (pgrepup)

```
yum -y install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum -y install gcc libffi-devel python-devel openssl-devel postgresql96-devel
yum -y install epel-release
yum -y install python-pip
PATH=/usr/pgsql-9.6/bin:$PATH pip install pgrepup
```

```
[root@repup ~]# pgrepup --help
Pgrepup 0.3.7
Pgrepup - PostGreSQL REplicate and UPgrade
A tool for upgrading a PostgreSQL cluster to a new major version using logical replication.

Usage:
  pgrepup [-c config] config
  pgrepup [-c config] check [source|destination|all]
  pgrepup [-c config] fix
  pgrepup [-c config] setup
  pgrepup [-c config] start
  pgrepup [-c config] status
  pgrepup [-c config] stop
  pgrepup [-c config] uninstall
  pgrepup -h | --help
  pgrepup --version

Options:
  -c config     Optional config file. [default: ~/.pgrepup]
  -h --help     Show this screen
  --version     Show version

Quick start:
    1) Configure pgrepup using the config command
    pgrepup config

    2) Check source and destination clusters with the check command
    pgrepup check

    3) Apply all the hints/fixes suggested by the check command

    4) Prepare both clusters for replication using pglogical
    pgrepup setup

    5) Launch the replication process using the start command
    pgrepup start
```

`config` -> `check` -> `setup` -> `start` というステップを踏むようです。

## pgrepup の設定

```
[root@repup ~]# pgrepup config
Pgrepup 0.3.7
Create a new pgrepup config
Configuration filename [~/.pgrepup]
Security
Do you want to encrypt database credentials using a password? [Y/n] Y
Password:
You'll be prompted for password every time pgrepup needs to connect to database
Folder where pgrepup store temporary dumps and pgpass file [/tmp]
Source Database configuration
Ip address or Dns name: 10.130.5.106
Port: 5432
Connect Database: [template1] postgres
Username: postgres
Password:
Destination Database configuration
Ip address or Dns name: 10.130.11.130
Port: 5432
Connect Database: [template1] postgres
Username: postgres
Password:
Configuration saved to /root/.pgrepup.
You can now use the check command to verify setup of source and destination databases
```

## pgrepup check でレプリケーションできるかどうかの確認

`pgrepup check` で pgrepup の config が正しいかどうか、PostgreSQL の設定がレプリケーションを行うための条件を満たしているかをチェックします。

```
[root@repup ~]# pgrepup check
Pgrepup 0.3.7
Password:
Global checkings...
 >  Folder /tmp exists and is writable ...........................................OK
Checking Source...
 >  Connection PostgreSQL connection to 10.130.5.106:5432 with user postgres .....OK
 >  pglogical installation Traceback (most recent call last):
  File "/usr/bin/pgrepup", line 25, in <module>
    main()
  File "/usr/lib/python2.7/site-packages/pgrepup/cli.py", line 65, in main
    dispatch(__doc__)
  File "/usr/lib/python2.7/site-packages/pgrepup/helpers/docopt_dispatch.py", line 39, in __call__
    function(**self._kwargify(arguments))
  File "/usr/lib/python2.7/site-packages/pgrepup/commands/check.py", line 62, in check
    c = checks(t, 'pglogical_installed', db_conn=conn)
  File "/usr/lib/python2.7/site-packages/pgrepup/commands/check.py", line 168, in checks
    checks_result[c] = create_extension(db_conn, 'pglogical', test=True)
  File "/usr/lib/python2.7/site-packages/pgrepup/helpers/database.py", line 99, in create_extension
    cur.execute("CREATE EXTENSION IF NOT EXISTS %s" % extension_name)
psycopg2.ProgrammingError: required extension "pglogical_origin" is not installed
```

エラーですね、PostgreSQL 9.4 で pglogical を使うためには `pglogical_origin` extension が必要だったようです。
すべての DB に対して
```CREATE EXTENSION IF NOT EXISTS pglogical_origin;```
を実行します。


```
[root@db1 ~]# su -l postgres -c psql
psql (9.4.10)
Type "help" for help.

postgres=# CREATE EXTENSION IF NOT EXISTS pglogical_origin;
CREATE EXTENSION
postgres=# \c testdb
You are now connected to database "testdb" as user "postgres".
testdb=# CREATE EXTENSION IF NOT EXISTS pglogical_origin;
CREATE EXTENSION
testdb=# \c template1
You are now connected to database "template1" as user "postgres".
template1=# CREATE EXTENSION IF NOT EXISTS pglogical_origin;
CREATE EXTENSION
template1=# \q
[root@db1 ~]#
```

再度 `pgrepup check` を実行してみます。

```
[root@repup ~]# pgrepup check
Pgrepup 0.3.7
Password:
Global checkings...
 >  Folder /tmp exists and is writable ...........................................OK
Checking Source...
 >  Connection PostgreSQL connection to 10.130.5.106:5432 with user postgres .....OK
 >  pglogical installation .......................................................OK
 >  Needed wal_level setting .....................................................OK
 >  Needed max_worker_processes setting ..........................................OK
 >  Needed max_replication_slots setting .........................................OK
 >  Needed max_wal_senders setting ...............................................OK
 >  pg_hba.conf settings .........................................................KO
    Hint: Add the following lines to /var/lib/pgsql/9.4/data/pg_hba.conf:
        host replication pgrepup_replication 10.130.11.130/32 md5
        host all pgrepup_replication 10.130.11.130/32 md5
    After adding the lines, remember to reload postgreSQL
 >  Local pg_dumpall version .....................................................OK
 >  Source cluster tables without primary keys
 >      template1
 >          pglogical_origin.replication_origin ..................................KO
    Hint: Add a primary key or unique index or use the pgrepup fix command
 >      testdb
 >          public.animal ........................................................OK
 >          public.fish ..........................................................KO
    Hint: Add a primary key or unique index or use the pgrepup fix command
 >          pglogical_origin.replication_origin ..................................KO
    Hint: Add a primary key or unique index or use the pgrepup fix command
 >      postgres
 >          pglogical_origin.replication_origin ..................................KO
    Hint: Add a primary key or unique index or use the pgrepup fix command
Checking Destination...
 >  Connection PostgreSQL connection to 10.130.11.130:5432 with user postgres ....OK
 >  pglogical installation .......................................................OK
 >  Needed wal_level setting .....................................................OK
 >  Needed max_worker_processes setting ..........................................OK
 >  Needed max_replication_slots setting .........................................OK
 >  Needed max_wal_senders setting ...............................................OK
 >  pg_hba.conf settings .........................................................KO
    Hint: Add the following lines to /var/lib/pgsql/9.6/data/pg_hba.conf:
        host replication pgrepup_replication 10.130.11.130/32 md5
        host all pgrepup_replication 10.130.11.130/32 md5
    After adding the lines, remember to reload postgreSQL
 >  Local pg_dumpall version .....................................................OK
```

実際には色付きで表示されるのでもっと見やすいですが

* primary key が全ての table に必要
* pg_hba.conf にレプリケーション先からの接続許可設定の追加が必要

ということのようです。primary key については `pgrepup fix` コマンドを実行すれば pgreup がやってくれます。

### pg_hba.conf への追記

```text:db1
echo "host replication pgrepup_replication 10.130.11.130/32 md5
host all pgrepup_replication 10.130.11.130/32 md5" >> /var/lib/pgsql/9.4/data/pg_hba.conf
systemctl reload postgresql-9.4
```

```text:db2
echo "host replication pgrepup_replication 10.130.11.130/32 md5
host all pgrepup_replication 10.130.11.130/32 md5" >> /var/lib/pgsql/9.6/data/pg_hba.conf
systemctl reload postgresql-9.6
```

ところで、pgrepup は PostgreSQL への接続しか許可されていないのにどうして pg_hba.conf の中身が確認できたのでしょう？ソースコードを確認したら temporary table に `COPY` で読み込んでました。確かにこうすれば出来ますね。

### pgrepup fix

```
[root@repup ~]# pgrepup fix
Pgrepup 0.3.7
Password:
Find Source cluster's databases with tables without primary key/unique index...
 >  template1
 >      Found pglogical_origin.replication_origin without primary key Added __pgrepup_id field
 >  postgres
 >      Found pglogical_origin.replication_origin without primary key Added __pgrepup_id field
 >  testdb
 >      Found public.fish without primary key ..............Added __pgrepup_id field

 >      Found pglogical_origin.replication_origin without primary key Added __pgrepup_id field
```

それぞれの table に `__pgrepup_id` 列が追加されました。次のようなクエリが実行されます。

```python
c.execute("ALTER TABLE %s.%s ADD COLUMN %s BIGSERIAL NOT NULL PRIMARY KEY" % (schema, table, get_unique_field_name()))
```

全行更新なので行数の多い table が対象の場合は結構辛そうです。unique な column がある場合は手動で primary key 設定を行う方が良さそうです。

### 再度 pgrepup check

問題点を修正したので再度 `pgrepup check` を実行します。

```
[root@repup ~]# pgrepup check
Pgrepup 0.3.7
Password:
Global checkings...
 >  Folder /tmp exists and is writable ...........................................OK
Checking Source...
 >  Connection PostgreSQL connection to 10.130.5.106:5432 with user postgres .....OK
 >  pglogical installation .......................................................OK
 >  Needed wal_level setting .....................................................OK
 >  Needed max_worker_processes setting ..........................................OK
 >  Needed max_replication_slots setting .........................................OK
 >  Needed max_wal_senders setting ...............................................OK
 >  pg_hba.conf settings .........................................................OK
 >  Local pg_dumpall version .....................................................OK
 >  Source cluster tables without primary keys
 >      template1
 >          pglogical_origin.replication_origin ..................................OK
 >      testdb
 >          public.animal ........................................................OK
 >          public.fish ..........................................................OK
 >          pglogical_origin.replication_origin ..................................OK
 >      postgres
 >          pglogical_origin.replication_origin ..................................OK
Checking Destination...
 >  Connection PostgreSQL connection to 10.130.11.130:5432 with user postgres ....OK
 >  pglogical installation .......................................................OK
 >  Needed wal_level setting .....................................................OK
 >  Needed max_worker_processes setting ..........................................OK
 >  Needed max_replication_slots setting .........................................OK
 >  Needed max_wal_senders setting ...............................................OK
 >  pg_hba.conf settings .........................................................OK
 >  Local pg_dumpall version .....................................................OK
```

全部 OK となりました。

## pgrepup setup

続いて `pgrepup setup` コマンドです。

```
[root@repup ~]# pgrepup setup
Pgrepup 0.3.7
Password:
Check if there are active subscriptions in Destination nodes .....................OK
Global tasks
 >  Remove nodes from Destination cluster
 >      postgres .................................................................OK
 >      template1 ................................................................OK
 >  Create temp pgpass file ......................................................OK
 >  Drop pg_logical extension in all databases of Source cluster
 >      template1 ................................................................OK
 >      postgres .................................................................OK
 >      testdb ...................................................................OK
 >  Drop pg_logical extension in all databases of Destination cluster
 >      postgres .................................................................OK
 >      template1 ................................................................OK
Setup Source
 >  Create user for replication ..................................................OK
 >  Dump globals and schema of all databases .....................................OK
 >  Setup pglogical replication sets on Source node name
 >      template1 ................................................................OK
 >      postgres .................................................................OK
 >      testdb ...................................................................OK
Setup Destination
 >  Create and import source globals and schema ..................................OK
 >  Setup pglogical Destination node name
 >      postgres .................................................................OK
 >      template1 ................................................................OK
 >      testdb ...................................................................OK
Cleaning up
 >  Remove temporary pgpass file .................................................OK
 >  Remove other temporary files .................................................OK
```

`pg_dumpall -s --if-exists -c` で Source DB から schema を dump して Destination DB へ適用されます。
その後

Source DB (db1) では次のような処理が

https://github.com/rtshome/pgrepup/blob/33853ca5a601b7ae42cbc3fa8f884a4729af9d74/pgrepup/helpers/replication.py#L160

```python
        c = db_conn.cursor()
        c.execute("CREATE EXTENSION pglogical")
        c.execute("SELECT pglogical.drop_node(node_name := %s, ifexists := false)", ['Source'])
        c.execute("SELECT pglogical.create_node(node_name := %s, dsn := %s );",
                  ['Source', get_dsn_for_pglogical('Source', db_name=db)])
        c.execute("SELECT pglogical.replication_set_add_all_tables('default', '{%s}'::text[]);" % ','.join(db_schemas))
        c.execute("SELECT pglogical.replication_set_add_all_sequences( set_name := 'default', schema_names := %s)",
                  [db_schemas])
        db_conn.commit()
```

Destination DB (db2) では次のような処理が行われます

https://github.com/rtshome/pgrepup/blob/33853ca5a601b7ae42cbc3fa8f884a4729af9d74/pgrepup/helpers/replication.py#L197

```python
        c = db_conn.cursor()
        drop_extension(db_conn, "pglogical")
        c.execute("DROP SCHEMA IF EXISTS pglogical CASCADE")
        c.execute("CREATE EXTENSION pglogical")
        c.execute("SELECT pglogical.drop_node(node_name := %s, ifexists := false)", ['Destination'])
        c.execute("SELECT pglogical.create_node( node_name := %s, dsn := %s );", [
            'Destination', get_dsn_for_pglogical('Destination', db)
        ])
        db_conn.commit()
```

## pgrepup start でレプリケーション開始

`setup` で準備が完了したので `start` でレプリケーションを開始します。

```
[root@repup ~]# pgrepup start
Pgrepup 0.3.7
Password:
Start replication and upgrade
 >  postgres .................................................................OK
 >  template1 ................................................................OK
 >  testdb ...................................................................OK
```

```text:source
2016-12-05 11:28:46 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(53378), pid=30759, xid=0 LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ ONLY;
        SET DATESTYLE = ISO;
        SET INTERVALSTYLE = POSTGRES;
        SET extra_float_digits TO 3;
        SET statement_timeout = 0;
        SET lock_timeout = 0;
        SET TRANSACTION SNAPSHOT '00000785-1';

2016-12-05 11:28:46 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(53378), pid=30759, xid=0 LOG:  statement: SELECT nspname, relname FROM pglogical.tables WHERE set_name = ANY(ARRAY['default'])
2016-12-05 11:28:46 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(53378), pid=30759, xid=0 LOG:  statement: COPY "public"."animal" TO stdout
2016-12-05 11:28:46 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(53378), pid=30759, xid=0 LOG:  statement: COPY "public"."fish" TO stdout
2016-12-05 11:28:46 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(53378), pid=30759, xid=0 LOG:  statement: ROLLBACK
```

```text:destination
2016-12-05 11:28:45 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(52284), pid=30330, xid=0 LOG:  statement: BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
        SET session_replication_role = 'replica';
        SET DATESTYLE = ISO;
        SET INTERVALSTYLE = POSTGRES;
        SET extra_float_digits TO 3;
        SET statement_timeout = 0;
        SET lock_timeout = 0;

2016-12-05 11:28:45 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(52284), pid=30330, xid=0 LOG:  statement: COPY "public"."animal" FROM stdin
2016-12-05 11:28:45 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(52284), pid=30330, xid=1843 LOG:  statement: COPY "public"."fish" FROM stdin
2016-12-05 11:28:45 JST user=pgrepup_replication, db=testdb, remote=10.130.11.130(52284), pid=30330, xid=1843 LOG:  statement: COMMIT
```

ここで COPY コマンドでデータがコピーされるようです。はて？これは INDEX やら制約やらがついた状態で行われるのだろうか？FK とかついたテーブルも用意するべきだったか。


## pgrepup status で状況確認

```
[root@repup ~]# pgrepup status
Pgrepup 0.3.7
Password:
Configuration
 >  Source database cluster ......................................................OK
 >  Destination database cluster .................................................OK
Pglogical setup
 >  Source database cluster
 >      template1 ................................................................OK
 >      postgres .................................................................OK
 >      testdb ...................................................................OK
 >  Destination database cluster
 >      postgres .................................................................OK
 >      template1 ................................................................OK
 >      testdb ...................................................................OK
Replication status
 >  Database postgres
 >      Replication status ..............................................replicating
 >  Database template1
 >      Replication status ..............................................replicating
 >  Database testdb
 >      Replication status ..............................................replicating
 >  Xlog difference (bytes) ..................................................107080
```

`Xlog difference (bytes)` で遅延が確認できるんだとか。

## レプリケーション中（アップグレード中）にレコードを追加してみる

```text:source
testdb=# select * from animal;
 id | name |         created_at
----+------+----------------------------
  1 | cat  | 2016-12-05 09:59:40.456885
  2 | dog  | 2016-12-05 09:59:43.048854
(2 rows)

testdb=# insert into animal (name) values ('mouse');
INSERT 0 1
testdb=# select * from animal;
 id | name  |         created_at
----+-------+----------------------------
  1 | cat   | 2016-12-05 09:59:40.456885
  2 | dog   | 2016-12-05 09:59:43.048854
  3 | mouse | 2016-12-05 11:45:32.51085
(3 rows)

testdb=#
```

```text:destination
testdb=# select * from animal;
 id | name  |         created_at
----+-------+----------------------------
  1 | cat   | 2016-12-05 09:59:40.456885
  2 | dog   | 2016-12-05 09:59:43.048854
  3 | mouse | 2016-12-05 11:45:32.51085
(3 rows)

testdb=#
```

Destination DB にレコード追加が反映されました。

### Sequence

レプリケーション中に Sequence は更新されませんが、`pgrepup stop` を実行すると、そこで反映されます。ですからアップグレードの完了には stop 処理が必要です。
https://github.com/rtshome/pgrepup/#sequences

```text:source
testdb=# select * from animal_id_seq;
-[ RECORD 1 ]-+--------------------
sequence_name | animal_id_seq
last_value    | 3
start_value   | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 1
log_cnt       | 32
is_cycled     | f
is_called     | t
```

destination 側は1のまま。

```text:destination
testdb=# select * from animal_id_seq;
-[ RECORD 1 ]-+--------------------
sequence_name | animal_id_seq
last_value    | 1
start_value   | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 1
log_cnt       | 0
is_cycled     | f
is_called     | f
```

### DDL

DDL もレプリケートされません
https://github.com/rtshome/pgrepup/#ddl-commands
stop, setup, start でやり直せとあります
長期間使うためのツールではないので、これはこれで問題なさそうです。

コードはコメントアウトされている
https://github.com/rtshome/pgrepup/blob/141cca9e03a2d595e90f8282b7808cf5fabb1bf5/pgrepup/commands/setup.py#L138


## pgrepup stop

```
[root@repup ~]# pgrepup stop
Pgrepup 0.3.7
Password:
Check active subscriptions in Destination nodes
 >  template1 ................................................................Active
 >      Launch stop command ..................................................OK
 >  testdb ...................................................................Active
 >      Launch stop command ..................................................OK
 >  postgres .................................................................Active
 >      Launch stop command ..................................................OK
```

sequence が更新されたか確認してみます

```text:source
testdb=# select * from animal_id_seq;
-[ RECORD 1 ]-+--------------------
sequence_name | animal_id_seq
last_value    | 4
start_value   | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 1
log_cnt       | 32
is_cycled     | f
is_called     | t
```

```text:destination
testdb=# select * from animal_id_seq;
-[ RECORD 1 ]-+--------------------
sequence_name | animal_id_seq
last_value    | 1004
start_value   | 1
increment_by  | 1
max_value     | 9223372036854775807
min_value     | 1
cache_value   | 1
log_cnt       | 0
is_cycled     | f
is_called     | t
```

https://github.com/rtshome/pgrepup/blob/33853ca5a601b7ae42cbc3fa8f884a4729af9d74/pgrepup/helpers/replication.py#L109

```sql
SELECT pglogical.synchronize_sequence( seqoid ) FROM pglogical.sequence_state
```

で Sequence の同期が行われるわけですが、+1000 されるようです。

## pgrepup uninstall で掃除

```
[root@repup ~]# pgrepup uninstall
Pgrepup 0.3.7
Password:
Check active subscriptions in Destination nodes
 >  template1 ...............................................................Stopped
 >  testdb ..................................................................Stopped
 >  postgres ................................................................Stopped
Uninstall operations
 >  Remove nodes from Destination cluster
 >      postgres .................................................................OK
 >      template1 ................................................................OK
 >      testdb ...................................................................OK
 >  Drop pg_logical extension in all databases
 >      Source
 >          template1 ............................................................OK
 >          postgres .............................................................OK
 >          testdb ...............................................................OK
 >      Destination
 >          postgres .............................................................OK
 >          template1 ............................................................OK
 >          testdb ...............................................................OK
 >  Drop user for replication ....................................................OK
 >  Drop unique fields added by fix command
 >          template1
 >              pglogical_origin.replication_origin ..............................KO
 >          postgres
 >              pglogical_origin.replication_origin ..............................KO
 >          testdb
 >              public.fish ......................................................OK
 >              pglogical_origin.replication_origin ..............................KO
 >              public.animal ....................................................OK
```

`pglogical` extension の削除と `pgrepup fix` で追加された `__pgrepup_id` column の削除が行われました。pglogical_origin の KO は無視して良さそうです（pglogical schema は除外リストに入ってるけど _origin の方は入ってないんですよね）。

## まとめ

PRIMARY KEY がすべてのテーブルに必須ということで、もともと PRIMARY KEY 相当の列がすべてのテーブルに存在すれば良いですが、そうではない大きなテーブルがあるといきなりこのツールでやってしまうのは怖い感じです。

[3TB超のCacooのPostgreSQL 9.3を9.5にアップグレードした話](https://nulab-inc.com/ja/blog/cacoo/postgresql-from-9-3-to-9-5/) という例を見ると pg_upgrade で良いのかなという気もする。

これをきっかけに Logical Decoding を調査して分析用のDBへ必要なテーブルだけ同期させるとか、複数のアプリのテーブルを1つのDBに集めるとかができると便利かなという感じです。
FDW と Materialized View の組み合わせの方が良かったりもしそうだけど。

## おまけ

Alibaba 製の同期ツールでも Logical Decoding が使われているようです。
https://github.com/aliyun/rds_dbsync
PostgreSQL -> PostgreSQL
PostgreSQL -> Greenplum
MySQL -> PostgreSQL
MySQL -> Greenplum
っていう同期（コピー？）が出来るみたい

PGConf. Asia 2016 行けば良かった
http://www.pgconf.asia/JP/material/