====== 01 PostgreSQL streaming replica ====== WALをスタンバイサーバへ転送する事で高可用性と読取に対するスケールアウトを実現するストリーミングレプリケーションを作成 ^hostname^IP^ |pg1001|172.16.0.51| |pg1002|172.16.0.52| |pg1003|172.16.0.53| ===== インストール ===== yum -y install epel-release centos-release-scl ===== PostgreSQL リポジトリ ===== yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm ===== PostgreSQLインストール ===== yum -y install postgresql13-contrib postgresql13-devel ===== 自動起動 defautl: disabled ===== systemctl enable postgresql-13.service systemctl disable postgresql-13.service ===== Dataディレクトリ変更 ===== mkdir /data/ chown postgres:postgres /data/ chmod 750 /data/ # grep Environment=PGDATA /usr/lib/systemd/system/postgresql-13.service Environment=PGDATA=/var/lib/pgsql/13/data/ ↓ Environment=PGDATA=/data/ ==== 反映 ==== systemctl daemon-reload ===== 初期化 ===== PGSETUP_INITDB_OPTIONS="-E UTF8 --no-locale" /usr/pgsql-13/bin/postgresql-13-setup initdb ===== PATH追加 ===== cat << __EOM__ | tee /var/lib/pgsql/.pgsql_profile PATH=/usr/pgsql-13/bin:$PATH export PATH __EOM__ source /var/lib/pgsql/.pgsql_profile ===== PGDATA変更 ===== # vi /var/lib/pgsql/.bash_profile #PGDATA=/var/lib/pgsql/12/data PGDATA=/data source /var/lib/pgsql/.bash_profile ===== パスワード ===== # su - postgres $ psql alter role postgres with password 'new_password'; ===== 起動 ===== systemctl status postgresql-13.service ===== 確認 ===== -bash-4.2$ psql -V psql (PostgreSQL) 13.7 -bash-4.2$ psql -l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) ===== ユーザ、DB作成 ===== -bash-4.2$ createuser --login --pwprompt testuser Enter password for new role: Enter it again: -bash-4.2$ createdb --owner=testuser testdb ==== Sampleデータを投入 ==== [[https://www.postgresqltutorial.com/postgresql-getting-started/postgresql-sample-database/]] curl -L -O https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip unzip dvdrental.zip cd sample_data tar zxvf ../dvdrental.zip ===== 外からの接続許可 ===== vi /data/postgresql.conf #listen_addresses = 'localhost' ↓ listen_addresses = '*' vi /data/pg_hba.conf ↓追加 ### ローカルホストから、testdb testuser でアクセス local testdb testuser scram-sha-256 ### 他のホストからネットワーク経由でアクセス host all all 172.16.0.0/24 scram-sha-256 ### 他のホストからreplicationを許可 host replication all 172.16.0.0/24 scram-sha-256 ==== 反映 ==== # systemctl reload postgresql-13.service ===== バックアップ ===== 他のノードから、node1へ接続してバックアップを取得 # pg_basebackup -R -D /data/ -h 172.16.0.51 -U postgres ===== 切り替え ===== Secondaryは下記を追加 /data/postgresql.auto.conf primary_conninfo = へ追加 application_name=postgresql ## Secondaryは下記をコメントアウト # grep synchronous_standby_names /data/postgresql.conf synchronous_standby_names = 'postgresql' # standby servers that provide sync rep ===== 確認 ===== ==== Primary ==== [root@pg1001 ~]# systemctl status postgresql-13.service ● postgresql-13.service - PostgreSQL 13 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled) Active: active (running) since Mon 2022-07-11 05:18:21 UTC; 38min ago Docs: https://www.postgresql.org/docs/13/static/ Process: 19243 ExecReload=/bin/kill -HUP $MAINPID (code=exited, status=0/SUCCESS) Process: 8558 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 8564 (postmaster) CGroup: /system.slice/postgresql-13.service ├─ 8564 /usr/pgsql-13/bin/postmaster -D /data/ ├─ 8567 postgres: logger ├─ 8569 postgres: checkpointer ├─ 8570 postgres: background writer ├─ 8571 postgres: walwriter ├─ 8572 postgres: autovacuum launcher ├─ 8573 postgres: stats collector ├─ 8574 postgres: logical replication launcher ├─19397 postgres: walsender postgres 172.16.0.52(53948) streaming 0/6000060 └─19404 postgres: walsender postgres 172.16.0.53(41534) streaming 0/6000060 Primaryではwalsenderプロセスが存在する。 # ps auxw| grep [w]alsender postgres 19068 0.0 0.0 398732 4288 ? Ss 06:36 0:00 postgres: walsender postgres 172.16.0.53(43426) streaming 0/8000148 postgres 19071 0.0 0.0 398228 3768 ? Ss 06:37 0:00 postgres: walsender postgres 172.16.0.51(39280) streaming 0/8000148 ==== Secondary ==== # systemctl status postgresql-13.service ● postgresql-13.service - PostgreSQL 13 database server Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; disabled; vendor preset: disabled) Active: active (running) since Mon 2022-07-11 05:44:48 UTC; 18min ago Docs: https://www.postgresql.org/docs/13/static/ Process: 8147 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS) Main PID: 8154 (postmaster) CGroup: /system.slice/postgresql-13.service ├─ 8154 /usr/pgsql-13/bin/postmaster -D /data/ ├─ 8155 postgres: logger ├─ 8156 postgres: startup recovering 000000010000000000000006 ├─ 8157 postgres: checkpointer ├─ 8158 postgres: background writer ├─ 8159 postgres: stats collector └─18601 postgres: walreceiver streaming 0/6004FA0 Secondaryでは、walreceiverも動いている # ps auxw| grep [w]alreceiver postgres 19711 0.0 0.0 408884 4712 ? Ss 06:37 0:03 postgres: walreceiver streaming 0/8000148 ==== 更新確認 ==== === Primary更新 === Primaryが更新されると、Secondaryもちゃんとデータ更新されている。 $ psql -h 172.16.0.51 -U postgres testdb -c "UPDATE actor set first_name='BBB' where actor_id=1" UPDATE 1 $ psql -h 172.16.0.52 -U postgres testdb -c "SELECT * FROM actor where actor_id=1" actor_id | first_name | last_name | last_update ----------+------------+-----------+---------------------------- 1 | BBB | Guiness | 2022-07-11 06:02:21.621425 (1 row) $ psql -h 172.16.0.53 -U postgres testdb -c "SELECT * FROM actor where actor_id=1" actor_id | first_name | last_name | last_update ----------+------------+-----------+---------------------------- 1 | BBB | Guiness | 2022-07-11 06:02:21.621425 (1 row) Primary以外では更新できない。 ubuntu@jump:~$ psql -h 172.16.0.52 -U postgres testdb -c "UPDATE actor set first_name='BBB' where actor_id=1" ERROR: cannot execute UPDATE in a read-only transaction ===== Primary障害時 ===== pg1001が障害発生で、落ちた事を想定 [root@pg1001 ~]# systemctl stop postgresql-13.service pg1002,pg1003ではデータのReadだけはできる $ psql -h 172.16.0.52 -U postgres testdb -c "SELECT * FROM actor where actor_id=1" actor_id | first_name | last_name | last_update ----------+------------+-----------+---------------------------- 1 | CCC | Guiness | 2022-07-11 06:19:15.283699 (1 row) $ psql -h 172.16.0.53 -U postgres testdb -c "SELECT * FROM actor where actor_id=1" actor_id | first_name | last_name | last_update ----------+------------+-----------+---------------------------- 1 | CCC | Guiness | 2022-07-11 06:19:15.283699 pg1002をPrimaryへ昇格させる Secondaryの要素をコメントアウト [root@pg1002 ~]# vi /data/postgresql.auto.conf #primary_conninfo = Primaryの要素を追加 [root@pg1002 ~]# vi /data/postgresql.conf synchronous_standby_names = 'postgresql' Primaryへ昇格 [root@pg1002 ~]# systemctl reload postgresql-13.service [root@pg1002 ~]# su - postgres -bash-4.2$ /usr/pgsql-13/bin/pg_ctl promote waiting for server to promote.... done server promoted 他のSecondaryの接続先を変更しリロード [root@pg1003 ~]# vi /data/postgresql.auto.conf host=172.16.0.52 ↓ host=172.16.0.51 [root@pg1003 ~]# systemctl reload postgresql-13.service ===== 参考 ===== インストール [[https://qiita.com/mkyz08/items/e9316fecd57bff6679cd]] pgpool-IIについて [[https://qiita.com/tom-sato/items/c01d4df39a6bb19e2b17]] 動作モード　master_slave_mode master_slave_sub_mode stream [[https://www.pgpool.net/docs/pgpool-II-4.0.4/ja/html/runtime-config-running-mode.html]] ロジカルレプリケーション　ストリーミングレプリケーション違い [[https://www.sraoss.co.jp/tech-blog/pgsql/logical-replication-1/]] {{tag>PostgreSQL}}