Clever Geek Handbook

PostgreSQL 10の論理レプリケーション

PG Day'17は、著作権で保護された記事であなたを喜ばせ続けています。 今日、私たちの古くからの友人であり、Web開発に関する挑発的な記事の長年の著者であるvaranioが論理複製について話します。







最初に、私は記事を「ハリー・ポッターと賢者の石」と呼びたかった。なぜなら、PostgreSQLとMySQLを比較するとき、長年にわたって誰かが常に現れ、Pgresには論理的な複製がないことに気づいた（データベース全体のみが複製可能で、読み取り専用の複製） MySQLには、ステートメントベースと行ベースの2種類があります。







ステートメントベースが脚にレーザー照準器を備えた時限爆弾である場合、PGでは行ベースが本当に見逃されました。 つまり 複製の問題は、ベース愛好家にとっての哲学者の石のようなものです。







より正確には、進行中に、たとえば1つまたは2つの必要なテーブルのみを複製するために、 slonyを使用することが常に可能でした。 しかし、スローはトリガーのトリッキーな部分であり、原則として機能します。動作する-触れないでください。 つまり たとえば、ALTER TABLE ADD COLUMNを取得して実行することはできません。これは特別なメカニズムを使用して行う必要があります。 それにもかかわらず、誰かが誤ってこれを行い、さらに悪いことに、しばらくパニックで戻った後、黒魔術師80lvlだけがこの状況をすばやく解決できます。 slonyに加えて、9.4からwalを介して論理複製の拡張機能を作成できるようになりました。そのような拡張機能の例はpglogicalです。







しかし、それは違います！







コミットが、拡張機能やプラグインなしで、個々のテーブルをそのまま論理的に複製できるPostgreSQL 10 devブランチに落ちたことがわかったとき、そこでの動作を確認することにしました。







ソースからPostgreSQLをubuntuに置きます



それはまったく難しくないことが判明しました。 アセンブリに必要なあらゆる種類のあいまいな単語を配置します。







sudo apt-get avada kedavra expelliarmusのインストール

sudo apt-get install -y build-essential libreadline-dev zlib1g-dev flex bison libxml2-dev libxslt-dev libssl-dev









ソースをダウンロード：







git clone git://git.postgresql.org/git/postgresql.git









この全体をまとめると： 







 cd postgresql ./configure make make install


make installを作成しました。これは/ usr / localに修復不可能なダメージを与えるため、ウイルスまたはドッカーコンテナーで行うか、実際の溶接工に慎重に行う方法をお勧めします。 私はまったく管理者ではないので、すみません。







テストデーモンを実行する



ユーザーpostgresになります。 まだ持っていない場合は作成します。 存在するが、その下にログインしない場合、おそらくパスワードが設定されていないだけなので、sudo passwd postgresを実行する必要があります。 それでは、postgresユーザーの下に行きましょう。







su - postgres









マスターフォルダーとスレーブフォルダーを作成し、そこでデータベースを更新します。 







 /usr/local/pgsql/bin/initdb -D ~/master /usr/local/pgsql/bin/initdb -D ~/slave


つまり 別々のテーブルを互いに複製する2つのローカルpgデーモンがあります。 1つをポート5433で、もう1つを5434で動作させます。







これを行うには、 ~/master/postgresql.conf



~/slave/postgresql.conf



line port = 5433



を、 ~/slave/postgresql.conf



にline port = 5434



をそれぞれ入力します。







両方のpostgresql.conf



構成で、次を指定する必要があります。 







 wal_level = logical


さらに、レプリケーションが機能するには、 pg_hba.conf



の行のコメントを解除する必要があります。 







 local replication postgres trust


両方の悪魔を開始します。 







 /usr/local/pgsql/bin/pg_ctl start -D ~/master -l ~/master.log /usr/local/pgsql/bin/pg_ctl start -D ~/slave -l ~/slave.log


レプリケーションを構成する



残念ながら、まだドキュメントは（まったく）ありません。 そのため、このタイプのレプリケーションを構成して使用する方法を少なくとも学ぶために、ソースコードのテストを少しテストする必要がありました。







詳細が乱雑にならないように、組み込みのpostgresデータベースですべてを正しく行います。 マスターに移動します。 







 /usr/local/pgsql/bin/psql -p 5433


テーブルと「パブリケーション」を作成しましょう： 







 CREATE TABLE repl ( id int, name text, primary key(id) ); CREATE PUBLICATION testpub;


パブリケーションに必要なすべてのテーブル（この場合は1つ）を追加します。 







 ALTER PUBLICATION testpub ADD TABLE repl;


スレーブ側で： 







 /usr/local/pgsql/bin/psql -p 5434


また、テーブルを作成します。 







 CREATE TABLE repl ( id int, name text, primary key(id) );


次に、パブリケーションへのサブスクリプションを作成する必要があります。このサブスクリプションでは、別のデータベースへの接続文字列と名前PUBLICATIONを示します。 







 CREATE SUBSCRIPTION testsub CONNECTION 'port=5433 dbname=postgres' PUBLICATION testpub;


確認する



ウィザードに挿入します。 







 INSERT INTO repl (id, name) VALUES (1, '');


レプリカで読み取ります。 







 postgres=# select * from repl; id | name ----+------ 1 |  (1 row)


うまくいく！







レプリカを停止します。 







 /usr/local/pgsql/bin/pg_ctl stop -D ~/slave


マスターで行います： 







 delete from repl; insert into repl (id, name) values (10, 'test');


スレーブを実行して確認します。 







 /usr/local/pgsql/bin/pg_ctl start -D ~/slave -l ~/slave.log
      
      

        
        
        
      

    
        
        
        
      
      

        
        
        
      

    
     


 postgres=# select * from repl; id | name ----+------ 10 | test (1 row)


すべてがうまくいきました。







さらに、マスターとスレーブに新しい列を追加し、レコードを挿入しましたが、それも機能しました。







さらに使用例を学びたい場合は、ソースファイルsrc/test/subscription/t/001_rep_changes.pl



。 彼は真珠の上にいますが、すべてがはっきりしています。







src / test / subscription / t / 001_rep_changes.pl 
 # Basic logical replication test use strict; use warnings; use PostgresNode; use TestLib; use Test::More tests => 11; # Initialize publisher node my $node_publisher = get_new_node('publisher'); $node_publisher->init(allows_streaming => 'logical'); $node_publisher->start; # Create subscriber node my $node_subscriber = get_new_node('subscriber'); $node_subscriber->init(allows_streaming => 'logical'); $node_subscriber->start; # Create some preexisting content on publisher $node_publisher->safe_psql('postgres', "CREATE TABLE tab_notrep AS SELECT generate_series(1,10) AS a"); $node_publisher->safe_psql('postgres', "CREATE TABLE tab_ins (a int)"); $node_publisher->safe_psql('postgres', "CREATE TABLE tab_full AS SELECT generate_series(1,10) AS a"); $node_publisher->safe_psql('postgres', "CREATE TABLE tab_rep (a int primary key)"); # Setup structure on subscriber $node_subscriber->safe_psql('postgres', "CREATE TABLE tab_notrep (a int)"); $node_subscriber->safe_psql('postgres', "CREATE TABLE tab_ins (a int)"); $node_subscriber->safe_psql('postgres', "CREATE TABLE tab_full (a int)"); $node_subscriber->safe_psql('postgres', "CREATE TABLE tab_rep (a int primary key)"); # Setup logical replication my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres'; $node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub"); $node_publisher->safe_psql('postgres', "CREATE PUBLICATION tap_pub_ins_only WITH (nopublish delete, nopublish update)"); $node_publisher->safe_psql('postgres', "ALTER PUBLICATION tap_pub ADD TABLE tab_rep, tab_full"); $node_publisher->safe_psql('postgres', "ALTER PUBLICATION tap_pub_ins_only ADD TABLE tab_ins"); my $appname = 'tap_sub'; $node_subscriber->safe_psql('postgres', "CREATE SUBSCRIPTION tap_sub CONNECTION '$publisher_connstr application_name=$appname' PUBLICATION tap_pub, tap_pub_ins_only"); # Wait for subscriber to finish initialization my $caughtup_query = "SELECT pg_current_xlog_location() <= replay_location FROM pg_stat_replication WHERE application_name = '$appname';"; $node_publisher->poll_query_until('postgres', $caughtup_query) or die "Timed out while waiting for subscriber to catch up"; my $result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM tab_notrep"); is($result, qq(0), 'check non-replicated table is empty on subscriber'); $node_publisher->safe_psql('postgres', "INSERT INTO tab_ins SELECT generate_series(1,50)"); $node_publisher->safe_psql('postgres', "DELETE FROM tab_ins WHERE a > 20"); $node_publisher->safe_psql('postgres', "UPDATE tab_ins SET a = -a"); $node_publisher->safe_psql('postgres', "INSERT INTO tab_rep SELECT generate_series(1,50)"); $node_publisher->safe_psql('postgres', "DELETE FROM tab_rep WHERE a > 20"); $node_publisher->safe_psql('postgres', "UPDATE tab_rep SET a = -a"); $node_publisher->poll_query_until('postgres', $caughtup_query) or die "Timed out while waiting for subscriber to catch up"; $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_ins"); is($result, qq(50|1|50), 'check replicated inserts on subscriber'); $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_rep"); is($result, qq(20|-20|-1), 'check replicated changes on subscriber'); # insert some duplicate rows $node_publisher->safe_psql('postgres', "INSERT INTO tab_full SELECT generate_series(1,10)"); # add REPLICA IDENTITY FULL so we can update $node_publisher->safe_psql('postgres', "ALTER TABLE tab_full REPLICA IDENTITY FULL"); $node_subscriber->safe_psql('postgres', "ALTER TABLE tab_full REPLICA IDENTITY FULL"); $node_publisher->safe_psql('postgres', "ALTER TABLE tab_ins REPLICA IDENTITY FULL"); $node_subscriber->safe_psql('postgres', "ALTER TABLE tab_ins REPLICA IDENTITY FULL"); # and do the update $node_publisher->safe_psql('postgres', "UPDATE tab_full SET a = a * a"); # Wait for subscription to catch up $node_publisher->poll_query_until('postgres', $caughtup_query) or die "Timed out while waiting for subscriber to catch up"; $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_full"); is($result, qq(10|1|100), 'update works with REPLICA IDENTITY FULL and duplicate tuples'); # check that change of connection string and/or publication list causes # restart of subscription workers. Not all of these are registered as tests # as we need to poll for a change but the test suite will fail none the less # when something goes wrong. my $oldpid = $node_publisher->safe_psql('postgres', "SELECT pid FROM pg_stat_replication WHERE application_name = '$appname';"); $node_subscriber->safe_psql('postgres', "ALTER SUBSCRIPTION tap_sub CONNECTION 'application_name=$appname $publisher_connstr'"); $node_publisher->poll_query_until('postgres', "SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = '$appname';") or die "Timed out while waiting for apply to restart"; $oldpid = $node_publisher->safe_psql('postgres', "SELECT pid FROM pg_stat_replication WHERE application_name = '$appname';"); $node_subscriber->safe_psql('postgres', "ALTER SUBSCRIPTION tap_sub SET PUBLICATION tap_pub_ins_only"); $node_publisher->poll_query_until('postgres', "SELECT pid != $oldpid FROM pg_stat_replication WHERE application_name = '$appname';") or die "Timed out while waiting for apply to restart"; $node_publisher->safe_psql('postgres', "INSERT INTO tab_ins SELECT generate_series(1001,1100)"); $node_publisher->safe_psql('postgres', "DELETE FROM tab_rep"); $node_publisher->poll_query_until('postgres', $caughtup_query) or die "Timed out while waiting for subscriber to catch up"; $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_ins"); is($result, qq(150|1|1100), 'check replicated inserts after subscription publication change'); $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_rep"); is($result, qq(20|-20|-1), 'check changes skipped after subscription publication change'); # check alter publication (relcache invalidation etc) $node_publisher->safe_psql('postgres', "ALTER PUBLICATION tap_pub_ins_only WITH (publish delete)"); $node_publisher->safe_psql('postgres', "ALTER PUBLICATION tap_pub_ins_only ADD TABLE tab_full"); $node_publisher->safe_psql('postgres', "DELETE FROM tab_ins WHERE a > 0"); $node_publisher->safe_psql('postgres', "INSERT INTO tab_full VALUES(0)"); $node_publisher->poll_query_until('postgres', $caughtup_query) or die "Timed out while waiting for subscriber to catch up"; # note that data are different on provider and subscriber $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_ins"); is($result, qq(50|1|50), 'check replicated deletes after alter publication'); $result = $node_subscriber->safe_psql('postgres', "SELECT count(*), min(a), max(a) FROM tab_full"); is($result, qq(11|0|100), 'check replicated insert after alter publication'); # check all the cleanup $node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub"); $result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM pg_subscription"); is($result, qq(0), 'check subscription was dropped on subscriber'); $result = $node_publisher->safe_psql('postgres', "SELECT count(*) FROM pg_replication_slots"); is($result, qq(0), 'check replication slot was dropped on publisher'); $result = $node_subscriber->safe_psql('postgres', "SELECT count(*) FROM pg_replication_origin"); is($result, qq(0), 'check replication origin was dropped on subscriber'); $node_subscriber->stop('fast'); $node_publisher->stop('fast');


特に、主キーなしでテーブルを作成し、そこから値を削除するには、次のように記述する必要があります。 







 ALTER TABLE tablename REPLICA IDENTITY FULL


私はこれがどのように機能するのかわかりません、明らかにいくつかのIDがその場で生成されます。 誰かが論理複製に関する詳細情報を持っている場合は、コメントでplizを共有してください。







結論



結論は非常に単純です。組織の問題の全層を解決するので、実稼働状態のPostgreSQL 10を本当に楽しみにしています（slonyを捨てることも可能です）。 誰かにとって、これはMySQLからPostgresに移行する最後のストローかもしれません。







一方、これが実際にどのように機能するかは誰にもわかりません。 それは十分に速く、維持するのに便利です。 トピックに関する詳細情報がある場合は、コメントでplizを共有してください。







それまでの間、PostgreSQL 10を待っています。確かに、現在のレプリケーション方法のトレーニングについて多くの質問があります。 PG Day'17では、PostgreSQLに関する多数の興味深いレポートとマスタークラスが見つかります。 たとえば、 Ilya KosmodemyanskyがGHGのセットアップ、トランザクションの処理、自動バキュームに関するすべてを説明し、もちろん、よくある間違いを避ける方法を説明します。 急いで登録してください！







More articles:


All Articles