PostgreSQL高可用patroni （01-环境搭建）

软件版本：

[root@hecs-66a2 ~]# etcd --version
etcd Version: 3.3.11
Git SHA: 2cf9e51
Go Version: go1.10.3
Go OS/Arch: linux/amd64

[root@hecs-66a2 ~]# patroni --version
patroni 2.1.1

[root@hecs-66a2 ~]# /usr/local/postgresql/bin/pg_config
BINDIR = /usr/local/postgresql/bin
DOCDIR = /usr/local/postgresql/share/doc
HTMLDIR = /usr/local/postgresql/share/doc
INCLUDEDIR = /usr/local/postgresql/include
PKGINCLUDEDIR = /usr/local/postgresql/include
INCLUDEDIR-SERVER = /usr/local/postgresql/include/server
LIBDIR = /usr/local/postgresql/lib
PKGLIBDIR = /usr/local/postgresql/lib
LOCALEDIR = /usr/local/postgresql/share/locale
MANDIR = /usr/local/postgresql/share/man
SHAREDIR = /usr/local/postgresql/share
SYSCONFDIR = /usr/local/postgresql/etc
PGXS = /usr/local/postgresql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--prefix=/usr/local/postgresql'
CC = gcc -std=gnu99
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2
CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/usr/local/postgresql/lib',--enable-new-dtags
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -ldl -lm
VERSION = PostgreSQL 13.4

###

本次测试使用单台服务搭建测试环境。

配置文件参考地址：

https://github.com/zalando/patroni

https://github.com/zalando/patroni/blob/master/postgres0.yml

https://github.com/zalando/patroni/blob/master/postgres1.yml

https://github.com/zalando/patroni/blob/master/postgres2.yml

###

1，启动etcd

nohup etcd &

2，数据库初始化

[root@hecs-66a2 ~]# su  - postgres

3，初始化postgresql0数据库

-bash-4.2$ patroni /tmp/p0.yml
2021-11-11 15:08:51,210 INFO: Selected new etcd server http://localhost:2379
2021-11-11 15:08:51,214 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-11-11 15:08:51,218 INFO: Lock owner: None; I am postgresql0
2021-11-11 15:08:51,221 INFO: trying to bootstrap a new cluster
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are enabled.

creating directory data/postgresql0 ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/postgresql/bin/pg_ctl -D data/postgresql0 -l logfile start

2021-11-11 15:08:51,968 INFO: postmaster pid=29079
localhost:5432 - no response
2021-11-11 15:08:51.980 CST [29079] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-11-11 15:08:51.980 CST [29079] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-11-11 15:08:51.983 CST [29079] LOG:  listening on Unix socket "./.s.PGSQL.5432"
2021-11-11 15:08:51.988 CST [29081] LOG:  database system was shut down at 2021-11-11 15:08:51 CST
2021-11-11 15:08:51.993 CST [29079] LOG:  database system is ready to accept connections
localhost:5432 - accepting connections
localhost:5432 - accepting connections
2021-11-11 15:08:52,990 INFO: establishing a new patroni connection to the postgres cluster
2021-11-11 15:08:52,996 INFO: running post_bootstrap
2021-11-11 15:08:53,008 WARNING: Could not activate Linux watchdog device: "Can't open watchdog device: [Errno 2] No such file or directory: '/dev/watchdog'"
2021-11-11 15:08:53,019 INFO: initialized a new cluster
2021-11-11 15:09:03,015 INFO: no action. I am (postgresql0) the leader with the lock

4，初始化postgresql1

-bash-4.2$ patroni /tmp/p1.yml
2021-11-11 15:10:33,085 INFO: Selected new etcd server http://localhost:2379
2021-11-11 15:10:33,089 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-11-11 15:10:33,093 INFO: Lock owner: postgresql0; I am postgresql1
2021-11-11 15:10:33,095 INFO: trying to bootstrap from leader 'postgresql0'
pg_basebackup: initiating base backup, waiting for checkpoint to complete
WARNING:  skipping special file "./.s.PGSQL.5432"
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/2000028 on timeline 1
pg_basebackup: starting background WAL receiver
pg_basebackup: created temporary replication slot "pg_basebackup_29245"
WARNING:  skipping special file "./.s.PGSQL.5432"
pg_basebackup: write-ahead log end point: 0/2000100
pg_basebackup: waiting for background process to finish streaming ...
pg_basebackup: syncing data to disk ...
pg_basebackup: renaming backup_manifest.tmp to backup_manifest
pg_basebackup: base backup completed
2021-11-11 15:10:33,391 INFO: replica has been created using basebackup
2021-11-11 15:10:33,392 INFO: bootstrapped from leader 'postgresql0'
2021-11-11 15:10:33,587 INFO: postmaster pid=29251
localhost:5433 - no response
2021-11-11 15:10:33.598 CST [29251] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-11-11 15:10:33.598 CST [29251] LOG:  listening on IPv4 address "127.0.0.1", port 5433
2021-11-11 15:10:33.601 CST [29251] LOG:  listening on Unix socket "./.s.PGSQL.5433"
2021-11-11 15:10:33.605 CST [29253] LOG:  database system was interrupted; last known up at 2021-11-11 15:10:33 CST
2021-11-11 15:10:33.638 CST [29253] LOG:  entering standby mode
2021-11-11 15:10:33.641 CST [29253] LOG:  redo starts at 0/2000028
2021-11-11 15:10:33.643 CST [29253] LOG:  consistent recovery state reached at 0/2000100
2021-11-11 15:10:33.643 CST [29251] LOG:  database system is ready to accept read only connections
2021-11-11 15:10:33.647 CST [29257] FATAL:  could not start WAL streaming: ERROR:  replication slot "postgresql1" does not exist
2021-11-11 15:10:33.649 CST [29259] FATAL:  could not start WAL streaming: ERROR:  replication slot "postgresql1" does not exist
localhost:5433 - accepting connections
localhost:5433 - accepting connections
2021-11-11 15:10:34,611 INFO: Lock owner: postgresql0; I am postgresql1
2021-11-11 15:10:34,612 INFO: establishing a new patroni connection to the postgres cluster
2021-11-11 15:10:34,624 INFO: no action. I am a secondary (postgresql1) and following a leader (postgresql0)
2021-11-11 15:10:38.650 CST [29272] FATAL:  could not start WAL streaming: ERROR:  replication slot "postgresql1" does not exist
2021-11-11 15:10:43,051 INFO: no action. I am a secondary (postgresql1) and following a leader (postgresql0)
2021-11-11 15:10:43.653 CST [29308] LOG:  started streaming WAL from primary at 0/3000000 on timeline 1
2021-11-11 15:10:53,050 INFO: no action. I am a secondary (postgresql1) and following a leader (postgresql0)

5,初始化postgresql2

-bash-4.2$ patroni /tmp/p2.yml
2021-11-11 15:12:55,940 INFO: Selected new etcd server http://localhost:2379
2021-11-11 15:12:55,944 INFO: No PostgreSQL configuration items changed, nothing to reload.
2021-11-11 15:12:55,948 INFO: Lock owner: postgresql0; I am postgresql2
2021-11-11 15:12:55,950 INFO: trying to bootstrap from leader 'postgresql0'
WARNING:  skipping special file "./.s.PGSQL.5432"
WARNING:  skipping special file "./.s.PGSQL.5432"
2021-11-11 15:12:56,238 INFO: replica has been created using basebackup
2021-11-11 15:12:56,239 INFO: bootstrapped from leader 'postgresql0'
2021-11-11 15:12:56,466 INFO: postmaster pid=29460
localhost:5434 - no response
2021-11-11 15:12:56.477 CST [29460] LOG:  starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2021-11-11 15:12:56.477 CST [29460] LOG:  listening on IPv4 address "127.0.0.1", port 5434
2021-11-11 15:12:56.481 CST [29460] LOG:  listening on Unix socket "./.s.PGSQL.5434"
2021-11-11 15:12:56.486 CST [29462] LOG:  database system was interrupted; last known up at 2021-11-11 15:12:56 CST
2021-11-11 15:12:56.521 CST [29462] LOG:  entering standby mode
2021-11-11 15:12:56.524 CST [29462] LOG:  redo starts at 0/4000028
2021-11-11 15:12:56.526 CST [29462] LOG:  consistent recovery state reached at 0/4000100
2021-11-11 15:12:56.526 CST [29460] LOG:  database system is ready to accept read only connections
2021-11-11 15:12:56.530 CST [29466] FATAL:  could not start WAL streaming: ERROR:  replication slot "postgresql2" does not exist
2021-11-11 15:12:56.532 CST [29468] FATAL:  could not start WAL streaming: ERROR:  replication slot "postgresql2" does not exist
localhost:5434 - accepting connections
localhost:5434 - accepting connections
2021-11-11 15:12:57,490 INFO: Lock owner: postgresql0; I am postgresql2
2021-11-11 15:12:57,490 INFO: establishing a new patroni connection to the postgres cluster
2021-11-11 15:12:57,501 INFO: no action. I am a secondary (postgresql2) and following a leader (postgresql0)
2021-11-11 15:13:01.533 CST [29481] FATAL:  could not start WAL streaming: ERROR:  replication slot "postgresql2" does not exist
2021-11-11 15:13:03,055 INFO: no action. I am a secondary (postgresql2) and following a leader (postgresql0)
2021-11-11 15:13:06.538 CST [29487] LOG:  started streaming WAL from primary at 0/5000000 on timeline 1
2021-11-11 15:13:13,055 INFO: no action. I am a secondary (postgresql2) and following a leader (postgresql0)
2021-11-11 15:13:23,055 INFO: no action. I am a secondary (postgresql2) and following a leader (postgresql0)

6，查看集群状态

[root@hecs-66a2 ~]# patronictl -c /tmp/p1.yml list
+-------------+----------------+---------+---------+----+-----------+--------------------------+
| Member      | Host           | Role    | State   | TL | Lag in MB | Tags                     |
+ Cluster: batman (7029205887923261839) -+---------+----+-----------+--------------------------+
| postgresql0 | 127.0.0.1:5432 | Leader  | running |  1 |           |                          |
| postgresql1 | 127.0.0.1:5433 | Replica | running |  1 |         0 |                          |
| postgresql2 | 127.0.0.1:5434 | Replica | running |  1 |         0 | replicatefrom: postgres1 |
+-------------+----------------+---------+---------+----+-----------+--------------------------+