Go to Qiita Advent Calendar 2024 Top
LoginSignup
10
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

エムスリーAdvent Calendar 2016

Day 5
@yteraoka(Yoshinori Teraoka)

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

Last updated at Posted at 2016-12-05

きっかけ

先日、pgrepup – upgrade PostgreSQL using logical replication という記事で 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章ロジカルデコーディング (Chapter 46. Logical Decoding)

Qiita にも記事があります PostgreSQLのLogical Decoding機能についての紹介 。また、PostgreSQL Advent Calendar 2016 の1日目でも紹介されています「Logical Decodingを使ったCDC（Change Data Capture）の実現方法を考えてみる」

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 や Pgpool-II を使うという方法はある)

（ちょっと脱線）これは Oracle Database も似た感じで redo log と WAL が同じような役割で archive log 転送で replica を作成するというのがお金をかけない replication で SharePlex や GoldenGate が Logical Decoding っぽい有償 replication ツールです。異なるバージョン間でも使えるためバージョンアップの停止時間短縮に使えます。LogMinor という redo log の decode ツールもあります、これは無償。

pglogical

pgrepup では論理レプリケーションに 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 への追記

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
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 列が追加されました。次のようなクエリが実行されます。

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) では次のような処理が

        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) では次のような処理が行われます

        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
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
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) で遅延が確認できるんだとか。

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

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=#
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

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のまま。

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 が更新されたか確認してみます

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
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


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にアップグレードした話 という例を見ると 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/

10
3
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
10
3

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?