本文主要是介绍pg_auto_failover 之二 postgresql 1 master 1 slave，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
os: ubuntu 16.04
db: postgresql 10.9
pg_auto_failover 是 citus 开源的一款 postgresql 高可用软件,目前只支持 postgresql 10 及以上.
pg_auto_failover is an extension and service for PostgreSQL that monitors and manages automated failover for a Postgres cluster. It is optimized for simplicity and correctness and supports Postgres 10 and newer.
ip 规划
192.168.56.101 node1
192.168.56.102 node2
192.168.56.103 node3node1 为 monitor 节点
node2 node3 为 postgresql 主从
上一篇blog <<pg_auto_failover 之一 apt install / make make install>> 介绍了安装两种方式安装pg_auto_failover
这一篇介绍下初步的使用.
Using pg_auto_failover
1. Install and start a pg_auto_failover monitor on your monitor machine:
node1 上运行,创建 monitor
$ pg_autoctl create monitor --pgdata /data/pg10/main --pgport 5432 --nodename node114:48:40 INFO Initialising a PostgreSQL cluster at "/data/pg10/main"
14:48:40 INFO /usr/bin/pg_ctl --pgdata /data/pg10/main --options "-p 5432" --options "-h *" --wait start
14:48:41 INFO Granting connection privileges on 192.168.56.0/24
14:48:41 INFO Your pg_auto_failover monitor instance is now ready on port 5432.
14:48:41 INFO pg_auto_failover monitor is ready at postgres://autoctl_node@node1:5432/pg_auto_failover
14:48:41 INFO Monitor has been succesfully initialized.发现对pg_hba.conf追加了一条纪录
host "pg_auto_failover" "autoctl_node" 192.168.56.0/24 trust # Auto-generated by pg_auto_failover发现 pg_auto_failover 相关的进程
$ ps -ef|grep -i postgres |grep -v greppostgres 7616 1 0 14:48 ? 00:00:00 /usr/lib/postgresql/10/bin/postgres -D /data/pg10/main -p 5432 -h *
postgres 7620 7616 0 14:48 ? 00:00:00 postgres: checkpointer process
postgres 7621 7616 0 14:48 ? 00:00:00 postgres: writer process
postgres 7622 7616 0 14:48 ? 00:00:00 postgres: wal writer process
postgres 7623 7616 0 14:48 ? 00:00:00 postgres: autovacuum launcher process
postgres 7625 7616 0 14:48 ? 00:00:00 postgres: stats collector process
postgres 7627 7616 0 14:48 ? 00:00:00 postgres: bgworker: pg_auto_failover monitor
postgres 7628 7616 0 14:48 ? 00:00:00 postgres: bgworker: logical replication launcher
postgres 7629 7616 0 14:48 ? 00:00:00 postgres: bgworker: pg_auto_failover monitor worker
postgres 7635 7616 0 14:48 ? 00:00:00 postgres: bgworker: pg_auto_failover monitor worker $ ls -l /data/pg10/main
total 128
drwx------ 6 postgres postgres 4096 Jul 3 14:48 base
drwx------ 2 postgres postgres 4096 Jul 3 14:48 global
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_commit_ts
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_dynshmem
-rw------- 1 postgres postgres 4611 Jul 3 14:48 pg_hba.conf
-rw------- 1 postgres postgres 1636 Jul 3 14:48 pg_ident.conf
drwx------ 4 postgres postgres 4096 Jul 3 14:48 pg_logical
drwx------ 4 postgres postgres 4096 Jul 3 14:48 pg_multixact
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_notify
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_replslot
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_serial
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_snapshots
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_stat
drwx------ 2 postgres postgres 4096 Jul 3 14:49 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_subtrans
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_tblspc
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_twophase
-rw------- 1 postgres postgres 3 Jul 3 14:48 PG_VERSION
drwx------ 3 postgres postgres 4096 Jul 3 14:48 pg_wal
drwx------ 2 postgres postgres 4096 Jul 3 14:48 pg_xact
-rw------- 1 postgres postgres 88 Jul 3 14:48 postgresql.auto.conf
-rw-rw-r-- 1 postgres postgres 110 Jul 3 14:48 postgresql-auto-failover.conf
-rw------- 1 postgres postgres 22962 Jul 3 14:48 postgresql.conf
-rw------- 1 postgres postgres 76 Jul 3 14:48 postmaster.opts
-rw------- 1 postgres postgres 88 Jul 3 14:48 postmaster.pid
-rw-rw-r-- 1 postgres postgres 511 Jul 3 14:48 startup.log$ psql
psql (10.9 (Ubuntu 10.9-1.pgdg16.04+1))
Type "help" for help.postgres=# \duList of rolesRole name | Attributes | Member of
--------------+------------------------------------------------------------+-----------autoctl | | {}autoctl_node | | {}postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}postgres=# \lList of databasesName | Owner | Encoding | Collate | Ctype | Access privileges
------------------+----------+----------+-------------+-------------+-----------------------pg_auto_failover | autoctl | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +| | | | | postgres=CTc/postgres
(4 rows)启动 monitor
$ pg_autoctl run --pgdata /data/pg10/main
15:12:53 INFO Managing PostgreSQL installation at "/data/pg10/main"
15:12:53 INFO Postgres is not running, starting postgres
15:12:53 INFO /usr/bin/pg_ctl --pgdata /data/pg10/main --options "-p 5432" --options "-h *" --wait start
15:12:53 INFO PostgreSQL is running in "/data/pg10/main" on port 5432
15:12:53 INFO pg_auto_failover monitor is ready at postgres://autoctl_node@node1:5432/pg_auto_failover
15:12:53 INFO Contacting the monitor to LISTEN to its events.2. Get the Postgres URI (connection string) for the monitor node:
node1 上运行,获取 monitor 的 uri
$ pg_autoctl show uri --pgdata /data/pg10/main
postgres://autoctl_node@node1:5432/pg_auto_failoverThe following two steps are going to use the option --monitor which expects that connection string. So copy/paste your actual Postgres URI for the monitor in the next steps.
3. Install and start a primary PostgreSQL instance:
node2 上运行,初始化一个 master 节点,并向 monitor 注册
$ pg_autoctl create postgres --pgdata /data/pg10/main --pgport 5432 --nodename node2 \
--monitor postgres://autoctl_node@node1:5432/pg_auto_failover15:22:44 INFO Registered node node2:5432 with id 1 in formation "default", group 0.
15:22:44 INFO Writing keeper init state file at "/var/lib/postgresql/.local/share/pg_autoctl/data/pg10/main/pg_autoctl.init"
15:22:44 INFO Successfully registered as "single" to the monitor.
15:22:46 INFO Initialising a PostgreSQL cluster at "/data/pg10/main"
15:22:46 INFO Postgres is not running, starting postgres
15:22:46 INFO /usr/bin/pg_ctl --pgdata /data/pg10/main --options "-p 5432" --options "-h *" --wait start
15:22:46 INFO CREATE DATABASE postgres;
15:22:46 INFO The database "postgres" already exists, skipping.
15:22:46 INFO FSM transition from "init" to "single": Start as a single node
15:22:46 INFO Initialising postgres as a primary
15:22:46 INFO Transition complete: current state is now "single"
15:22:46 INFO Keeper has been succesfully initialized.$ ps -ef|grep -i postgres |grep -v greppostgres 31958 1 0 15:22 pts/1 00:00:00 /usr/lib/postgresql/10/bin/postgres -D /data/pg10/main -p 5432 -h *
postgres 31959 31958 0 15:22 ? 00:00:00 postgres: logger process
postgres 31961 31958 0 15:22 ? 00:00:00 postgres: checkpointer process
postgres 31962 31958 0 15:22 ? 00:00:00 postgres: writer process
postgres 31963 31958 0 15:22 ? 00:00:00 postgres: wal writer process
postgres 31964 31958 0 15:22 ? 00:00:00 postgres: autovacuum launcher process
postgres 31965 31958 0 15:22 ? 00:00:00 postgres: stats collector process
postgres 31966 31958 0 15:22 ? 00:00:00 postgres: bgworker: logical replication launcherNow that the installation is ready we can run the keeper service, which connects to the pg_auto_failover monitor every 5 seconds and implement the state transitions when needed:
运行 keeper
$ pg_autoctl run --pgdata /data/pg10/main15:25:46 INFO Managing PostgreSQL installation at "/data/pg10/main"
15:25:46 INFO pg_autoctl service is starting
15:25:46 INFO Calling node_active for node default/1/0 with current state: single, PostgreSQL is running, sync_state is "", WAL delta is -1.
15:25:51 INFO Calling node_active for node default/1/0 with current state: single, PostgreSQL is running, sync_state is "", WAL delta is -1.
15:25:56 INFO Calling node_active for node default/1/0 with current state: single, PostgreSQL is running, sync_state is "", WAL delta is -1.
15:26:01 INFO Calling node_active for node default/1/0 with current state: single, PostgreSQL is running, sync_state is "", WAL delta is -1.$ pg_autoctl show state --pgdata /data/pg10/main/Name | Port | Group | Node | Current State | Assigned State
------+--------+-------+-------+-------------------+------------------
node2 | 5432 | 0 | 5 | single | single4. Install and start a secondary PostgreSQL instance:
node3 节点执行,作为 node2 节点 postgresql 的 slave
$ cd /data/pg10;
mv main main.bak;$ pg_autoctl create postgres --pgdata /data/pg10/main --nodename node3 \
--monitor postgres://autoctl_node@node1:5432/pg_auto_failover16:38:06 INFO Continuing from a previous `pg_autoctl create` failed attempt
16:38:06 INFO PostgreSQL state at registration time was: PGDATA does not exists
16:38:06 INFO FSM transition from "wait_standby" to "catchingup": The primary is now ready to accept a standby
16:38:06 INFO The primary node returned by the monitor is node2:5432
16:38:06 INFO Initialising PostgreSQL as a hot standby
16:38:06 INFO Running /usr/bin/pg_basebackup -w -h node2 -p 5432 --pgdata /data/pg10/backup -U pgautofailover_replicator --write-recovery-conf --max-rate 100M --wal-method=stream --slot pgautofailover_standby ...
16:38:07 INFO pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
24050/24050 kB (100%), 1/1 tablespace
pg_basebackup: write-ahead log end point: 0/20000F8
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: base backup completed16:38:07 INFO Postgres is not running, starting postgres
16:38:07 INFO /usr/bin/pg_ctl --pgdata /data/pg10/main --options "-p 5432" --options "-h *" --wait start
16:38:08 INFO PostgreSQL started on port 5432
16:38:08 INFO Transition complete: current state is now "catchingup"
16:38:08 INFO Keeper has been succesfully initialized.Once the pg_autoctl create command is done, again, it’s important to run the keeper’s service:
运行 keeper
$ pg_autoctl show state --pgdata /data/pg10/main/Name | Port | Group | Node | Current State | Assigned State
------+--------+-------+-------+-------------------+------------------
node2 | 5432 | 0 | 5 | wait_primary | wait_primary
node3 | 5432 | 0 | 6 | catchingup | catchingup$ pg_autoctl run --pgdata /data/pg10/main16:43:40 INFO Managing PostgreSQL installation at "/data/pg10/main"
16:43:40 INFO pg_autoctl service is starting
16:43:40 INFO Calling node_active for node default/6/0 with current state: catchingup, PostgreSQL is running, sync_state is "", WAL delta is -1.
16:43:40 INFO FSM transition from "catchingup" to "secondary": Convinced the monitor that I'm up and running, and eligible for promotion again
16:43:40 INFO Transition complete: current state is now "secondary"
16:43:40 INFO Calling node_active for node default/6/0 with current state: secondary, PostgreSQL is running, sync_state is "", WAL delta is 0.
16:43:45 INFO Calling node_active for node default/6/0 with current state: secondary, PostgreSQL is running, sync_state is "", WAL delta is 0.
16:43:50 INFO Calling node_active for node default/6/0 with current state: secondary, PostgreSQL is running, sync_state is "", WAL delta is 0.新开一个窗口查看
$ pg_autoctl show state --pgdata /data/pg10/main/Name | Port | Group | Node | Current State | Assigned State
------+--------+-------+-------+-------------------+------------------
node2 | 5432 | 0 | 5 | primary | primary
node3 | 5432 | 0 | 6 | secondary | secondarySo when pg_autoctl create postgres is used on an existing ${PGDATA} where Postgres is running as a secondary node, then you have the following error message:
ERROR pg_autoctl doesn't know how to register an already existing standby server at the momentInstalling pg_auto_failover on-top of an existing Postgres setup
已经有 postgresql 运行,需要使用 pg_auto_failover 做 ha 的
$ pg_autoctl create postgres --pgdata /data/pg10/main/ --nodename node2Application and Connection Strings
To retrieve the connection string to use at the application level, use the following command:
$ pg_autoctl show uri --formation default --pgdata /data/pg10/main/
postgres://node2:5432,node3:5432/postgres?target_session_attrs=read-write相关的一些文件
ls -l "/var/lib/postgresql/.config/pg_autoctl/data/pg10/main/pg_autoctl.cfg" \
"/var/lib/postgresql/.local/share/pg_autoctl/data/pg10/main/pg_autoctl.state" \
"/var/lib/postgresql/.local/share/pg_autoctl/data/pg10/main/pg_autoctl.init" \
"/tmp/pg_autoctl/data/pg10/main/pg_autoctl.pid"参考:
https://github.com/citusdata/pg_auto_failover
https://pg-auto-failover.readthedocs.io/en/latest/quickstart.html
https://www.citusdata.com/blog/2019/05/30/introducing-pg-auto-failover/
https://cloudblogs.microsoft.com/opensource/2019/05/06/introducing-pg_auto_failover-postgresql-open-source-extension-automated-failover-high-availability/
这篇关于pg_auto_failover 之二 postgresql 1 master 1 slave的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
