gitlab-postgresql-12.3
一、简介
1、gitlab 仅支持postgresql数据库管理系统，因此，数据库服务器有两个选项可用于linux软件包安装：
- 使用Linux软件包安装附带的打包PostgreSQL服务器(不需要配置，推荐)
- 使用外部postgresql服务器,(需要配置，并且需要手动为数据库设定种子)
2、 本章节使用redis 哨兵模式:
- reids3.2开始必须定义一个密码
- 必须配置为tcp连接
- 不支持redis群集，只支持独立redis或sentinel的高可用
3、本章节主要内容：
- 使用外部postgresql，并单独部署高可用
- 使用外部redis，并单独部署高可用
- 使用主从keepalived配置gitlab的访问的vip地址
二、环境
2.1 规划
| 主机IP | 服务 | 备注 |
|---|---|---|
| 192.168.150.61 | redis-6.2.9,redis-sentinel,gitlab-12.3,postgrersql-12,pool-II-12,keepalived | master |
| 192.168.150.62 | redis-6.2.9,redis-sentinel,gitlab-12.3,postgrersql-12,pool-II-12,keepalived | node |
| 192.168.150.188 | gitlab-vip | |
| 192.168.150.187 | redis-vip | |
| 192.168.150.186 | pgpool-vip |
2.2 初始化环境
1、系统版本：centos7.8
2、服务器用户之间免密(root、postgres)
3、配置hosts文件
4、关闭防火墙
三、redis-install
3.1 redis主节点
# 先手动配置redis-vip地址
ifconfig eth0:3 192.168.150.187/24
# 安装包下载地址
http://download.redis.io/releases/
# 下载
[root@master ~]# wget http://download.redis.io/releases/redis-6.2.9.tar.gz
2、 安装gcc
[root@master ~]# yum install -y gcc
3、解压
[root@master ~]# tar xf redis-6.2.9.tar.gz
4、编译
[root@master ~]# cd redis-6.2.9/
[root@master ~]# make && make install
5、编辑配置文件
[root@master ~]# egrep -v "^#|^$" redis-6.2.9/redis.conf
bind 0.0.0.0 # 监听的地址
protected-mode no # 关闭保护模式，开启只有本机才可访问
port 6379 # 监听端口
tcp-backlog 511
timeout 0
tcp-keepalive 300
daemonize yes # 后台启动
pidfile "/var/run/redis_6379.pid"
loglevel notice
logfile "/var/log/redis/redis.log"
databases 16
always-show-logo yes
set-proc-title yes
proc-title-template "{title} {listen-addr} {server-mode}"
save 900 1
save 300 10
save 60 10000
stop-writes-on-bgsave-error yes
rdbcompression yes
rdbchecksum yes
dbfilename "dump.rdb"
rdb-del-sync-files no
dir "/root/redis-6.2.9"
masterauth "gitlab@123" # master节点设置该属性后,所有redis节点均需配masterauth属性
replica-serve-stale-data yes
replica-read-only yes
repl-diskless-sync no
repl-diskless-sync-delay 5
repl-diskless-load disabled
repl-disable-tcp-nodelay no
replica-priority 100
acllog-max-len 128
requirepass "gitlab@123" # 连接密码
lazyfree-lazy-eviction no
lazyfree-lazy-expire no
lazyfree-lazy-server-del no
replica-lazy-flush no
lazyfree-lazy-user-del no
lazyfree-lazy-user-flush no
oom-score-adj no
oom-score-adj-values 0 200 800
disable-thp yes
appendonly no
appendfilename "appendonly.aof"
appendfsync everysec
no-appendfsync-on-rewrite no
auto-aof-rewrite-percentage 100
auto-aof-rewrite-min-size 64mb
aof-load-truncated yes
aof-use-rdb-preamble yes
lua-time-limit 5000
slowlog-log-slower-than 10000
slowlog-max-len 128
latency-monitor-threshold 0
notify-keyspace-events ""
hash-max-ziplist-entries 512
hash-max-ziplist-value 64
list-max-ziplist-size -2
list-compress-depth 0
set-max-intset-entries 512
zset-max-ziplist-entries 128
zset-max-ziplist-value 64
hll-sparse-max-bytes 3000
stream-node-max-bytes 4kb
stream-node-max-entries 100
activerehashing yes
client-output-buffer-limit normal 0 0 0
client-output-buffer-limit replica 256mb 64mb 60
client-output-buffer-limit pubsub 32mb 8mb 60
hz 10
dynamic-hz yes
aof-rewrite-incremental-fsync yes
rdb-save-incremental-fsync yes
jemalloc-bg-thread yes
6、 启动服务
[root@master ~]# redis-server ./redis.conf
3.2 redis备节点
# 安装配置同主节点
# 启动服务
[root@node ~]# redis-server ./redis.conf
3.3 sentinel主节点
redis sentinel具有如下几个功能：
- 监控：sentinel节点会定期检测Redis数据节点、其余sentinel节点是否可达
- 通知：sentinel节点会将故障转移的结果通知给应用方
- 主节点故障转移：实现从节点晋升为主节点并维护后继的正确的主从关系
- 配置提供者：在redis sentinel结构中，客户端在初始化的时候连接的是sentinel节点集合，从中获取主节点信息
# 配置文件
[root@master ~]# egrep -v "^#|^$" redis-6.2.9/sentinel.conf
bind 192.168.150.61
protected-mode no
port 26379
masterauth "gitlab@123"
daemonize yes
pidfile "/var/run/redis-sentinel.pid"
logfile "/var/log/redis/sentinel.log"
dir "/tmp"
# 监控的是master的地址，主备都一样
sentinel monitor master 192.168.150.61 6379 1 # 表示集群中有1个sentinel认为master死了，则表示不可用
sentinel auth-pass master gitlab@123 # sentinel连接主从密码验证，主从必须为相同
acllog-max-len 128
requirepass "gitlab@123" # 连接密码
sentinel failover-timeout master 30000
sentinel deny-scripts-reconfig yes
sentinel resolve-hostnames no
sentinel myid 7fa3653efc0e6699880d41ba48c00d4d27bd5bf7
sentinel config-epoch master 618
sentinel leader-epoch master 618
sentinel known-replica master 192.168.150.62 6379
sentinel known-sentinel master 192.168.150.62 26379 064378128898c3f6d84cdb8de79abb4975ba04ca
sentinel current-epoch 618
sentinel announce-hostnames no
# 启动服务
[root@master ~]# redis-sentinel redis-6.2.9/sentinel.conf
3.4 sentinel备节点
# 配置文件
[root@node ~]# egrep -v "^#|^$" redis-6.2.9/sentinel.conf
bind 192.168.150.62
sentinel myid 064378128898c3f6d84cdb8de79abb4975ba04ca
protected-mode no
port 26379
masterauth "gitlab@123"
daemonize yes
pidfile "/var/run/redis-sentinel.pid"
logfile "/var/log/redis/sentinel.log"
dir "/tmp"
sentinel monitor master 192.168.150.61 6379 1
sentinel auth-pass master gitlab@123
acllog-max-len 128
requirepass "gitlab@123"
sentinel failover-timeout master 30000
sentinel deny-scripts-reconfig yes
sentinel resolve-hostnames no
sentinel config-epoch master 618
sentinel leader-epoch master 618
sentinel known-replica master 192.168.150.62 6379
sentinel known-sentinel master 192.168.150.61 26379 7fa3653efc0e6699880d41ba48c00d4d27bd5bf7
sentinel current-epoch 618
sentinel announce-hostnames no
# 启动服务
[root@node ~]# redis-sentinel redis-6.2.9/sentinel.conf
3.5 验证
# 主服务器查看状态
[root@master redis-6.2.9]# redis-cli
127.0.0.1:6379> AUTH gitlab@123
OK
127.0.0.1:6379> info replication
# Replication
role:master
connected_slaves:1
slave0:ip=192.168.150.62,port=6379,state=online,offset=513788,lag=0
master_failover_state:no-failover
master_replid:39a7e1fa7d3883f17f1d7e7b68118ffdc3762f6d
master_replid2:ade3c909314712f8a7b6a569eca1792617c321d2
master_repl_offset:513788
second_repl_offset:48621
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:1
repl_backlog_histlen:513788
# 从服务器查看状态
redis-cli
127.0.0.1:6379> AUTH gitlab@123
OK
127.0.0.1:6379> info replication
# Replication
role:slave
master_host:192.168.150.61
master_port:6379
master_link_status:up
master_last_io_seconds_ago:2
master_sync_in_progress:0
slave_read_repl_offset:522717
slave_repl_offset:522717
slave_priority:100
slave_read_only:1
replica_announced:1
connected_slaves:0
master_failover_state:no-failover
master_replid:39a7e1fa7d3883f17f1d7e7b68118ffdc3762f6d
master_replid2:0000000000000000000000000000000000000000
master_repl_offset:522717
second_repl_offset:-1
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:67784
repl_backlog_histlen:454934
# 杀掉主服务器redis
[root@master redis-6.2.9]# ps -ef | grep redis
root 4753 1 0 10:24 ? 00:00:10 redis-server 0.0.0.0:6379
root 4759 1 0 10:24 ? 00:00:14 redis-sentinel 192.168.150.61:26379 [sentinel]
root 4852 1381 0 11:27 pts/0 00:00:00 grep --color=auto redis
[root@master redis-6.2.9]# kill -9 4753
[root@master redis-6.2.9]# ps -ef | grep redis
root 4759 1 0 10:24 ? 00:00:14 redis-sentinel 192.168.150.61:26379 [sentinel]
root 4854 1381 0 11:27 pts/0 00:00:00 grep --color=auto redis
# 查看从服务器已切换为主
127.0.0.1:6379> AUTH gitlab@123
OK
127.0.0.1:6379> info replication
# Replication
role:master
connected_slaves:0
master_failover_state:no-failover
master_replid:be5ac5004bc96e7b7dc54fe99d1bdffd754ae38f
master_replid2:39a7e1fa7d3883f17f1d7e7b68118ffdc3762f6d
master_repl_offset:542806
second_repl_offset:528574
repl_backlog_active:1
repl_backlog_size:1048576
repl_backlog_first_byte_offset:67784
repl_backlog_histlen:475023
四、postgresql-install
4.1 postgresql-主
4.1.1 install
# 网址
PostgreSQL的官方地址：https://www.postgresql.org
PostgreSQL的国内社区：http://www.postgres.cn/v2/home
# 下载rpm包
[root@master ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装postgresql，postgresql12-contrib附加功能包
[root@master ~]# yum install -y postgresql12-server postgresql12-contrib
# 初始化数据库
[root@master ~]# /usr/pgsql-12/bin/postgresql-12-setup initdb
# 启动并设置开机自启
[root@master ~]# systemctl enable postgresql-12 --now
# 验证
# postgreSQL不推荐使用root管理，在安装postgreSQL后，他默认会给你创建一个用户：postgres，无密码，可以修改密码：ALTER USER postgres WITH PASSWORD '新密码';
● 以超级用户身份登录到postgreSQL数据库： sudo -u postgres psql
● 查看有那些库： \l，如下，有三个库，分别是postgres，template0, template1
● 退出postgreSQL： \q
[root@node1 rhd]# sudo -u postgres psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(3 行记录)
# 注意： 先切换到： su postgres,再通过命令进入：psql，也是ok的
4.1.2 配置postgresql
# 配置文件
postgresql.conf 是postgreSQL的主要配置文件，它包含了许多数据库服务器的全局设置选项。通过修改这个文件，您可以配置数据库服务器的各种参数，如监听地址、端口号、最大连接数、日志记录等。这个文件的修改通常需要重启数据库服务器才能生效。
[root@master ~]# egrep -v "^#|^$" /var/lib/pgsql/12/data/postgresql.conf
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
listen_addresses = '*' # what IP address(es) to listen on; ######监听地址
# comma-separated list of addresses;
# defaults to 'localhost'; use '*' for all
# (change requires restart)
max_connections = 200 # (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
# 0 selects the system default
shared_buffers = 128MB # min 128kB
# (change requires restart)
# (change requires restart)
# (change requires restart)
# supported by the operating system:
# mmap
# sysv
# windows
# (change requires restart)
dynamic_shared_memory_type = posix # the default is the first option
# supported by the operating system:
# posix
# sysv
# windows
# mmap
# (change requires restart)
# in kB, or -1 for no limit
# (change requires restart)
# can be used in parallel operations
# (change requires restart)
wal_level = replica # minimal, replica, or logical
# (change requires restart)
# (turning this off can cause
# unrecoverable data corruption)
# off, local, remote_write, remote_apply, or on
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux and FreeBSD)
# fsync
# fsync_writethrough
# open_sync
wal_log_hints = on # also do full page writes of non-critical updates
# (change requires restart)
# (change requires restart)
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on # enables archiving; off, on, or always
# (change requires restart)
archive_command = 'cp "%p" "/var/lib/pgsql/12/backups/%f"' # command to use to archive a logfile segment #####备份
# placeholders: %p = path of file to archive
# %f = file name only
# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
# number of seconds; 0 disables
# placeholders: %p = path of file to restore
# %f = file name only
# e.g. 'cp /mnt/server/archivedir/%f %p'
# (change requires restart)
# consistent state is reached
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# (change requires restart)
# just after the specified recovery target (on)
# just before the recovery target (off)
# (change requires restart)
# (change requires restart)
# (change requires restart)
max_wal_senders = 10 # max number of walsender processes
# (change requires restart)
max_replication_slots = 10 # max number of replication slots
# (change requires restart)
# (change requires restart)
# method to choose sync standbys, number of sync standbys,
# and comma-separated list of application_name
# from standby(s); '*' = all
# (change requires restart)
# (change requires restart)
hot_standby = on # "off" disallows queries during recovery
# (change requires restart)
# when reading WAL from archive;
# -1 allows indefinite delay
# when reading streaming WAL;
# -1 allows indefinite delay
# 0 disables
# query conflicts
# communication from master
# in milliseconds; 0 disables
# retrieve WAL after a failed attempt
# (change requires restart)
# and query more expensive than this;
# -1 disables
# more expensive than this; -1 disables
# query is more expensive than this;
# -1 disables
# JOIN clauses
# force_custom_plan
log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog, and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
log_directory = 'log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
# same name as the new log file will be
# truncated rather than appended to.
# But such truncation only occurs on
# time-driven rotation, not on restarts
# or size-driven rotation. Default is
# off, meaning append to existing files
# in all cases.
log_rotation_age = 1d # Automatic rotation of logfiles will
# happen after that time. 0 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
# and their durations, > 0 logs only
# statements running at least this number
# of milliseconds
# are logged regardless of their duration. 1.0 logs all
# statements from all transactions, 0.0 never logs.
log_line_prefix = '%m [%p] ' # special values:
# %a = application name
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %n = timestamp with milliseconds (as a Unix epoch)
# %i = command tag
# %e = SQL state
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
log_timezone = 'Asia/Shanghai'
# (change requires restart)
# requires track_counts to also be on.
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
# (change requires restart)
# vacuum
# analyze
# (change requires restart)
# before forced vacuum
# (change requires restart)
# autovacuum, in milliseconds;
# -1 means use vacuum_cost_delay
# autovacuum, -1 means use
# vacuum_cost_limit
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
# only default tablespace
# before index cleanup, 0 always performs
# index cleanup
datestyle = 'iso, ymd'
timezone = 'Asia/Shanghai'
# abbreviations. Currently, there are
# Default
# Australia (historical usage)
# India
# You can create your own file in
# share/timezonesets/.
# selects precise output mode
# encoding
lc_messages = 'zh_CN.UTF-8' # locale for system error message
# strings
lc_monetary = 'zh_CN.UTF-8' # locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8' # locale for number formatting
lc_time = 'zh_CN.UTF-8' # locale for time formatting
default_text_search_config = 'pg_catalog.simple'
# (change requires restart)
# (change requires restart)
# (max_pred_locks_per_transaction
# / -max_pred_locks_per_relation) - 1
# data?
# (change requires restart)
# a directory, e.g., 'conf.d'
4.1.3 配置pg_hba
pg_hba.conf是PostgreSQL的身份验证配置文件，它定义了那些客户端可以连接到数据库服务器以及如何进行身份验证。通过修改这个文件，您可以控制访问数据库的权限，配置不同用户的身份验证方法，以及定义访问控制规则。修改pg_hba.conf文件后，通常需要重新加载配置或重启数据库服务器才能使用更改生效。
[root@master ~]# egrep -v "^#|^$" /var/lib/pgsql/12/data/pg_hba.conf
local all all peer
host all all 127.0.0.1/32 md5
host all all 0.0.0.0/0 md5
host all all ::1/128 ident
local replication all peer
host replication all 127.0.0.1/32 md5
host replication all 0.0.0.0/0 md5
host replication all ::1/128 ident
模板参数说明：
● type: local代表本地连接，host代表可以指定连接的ADDRESS
● database： 数据库名，如果写all,代表所有库都可以连接
● user：连接的用户，可以写all，代表所有用户
● address：代表那些IP地址可以连接
● method：加密方式，一般直接md5
# 注意：配置尽量写在前面，因为是从上往下匹配的
4.1.4 启动服务
systemctl restart postgresql-12.service
4.1.5 创建数据库
# 创建数据库用户，后面gitlab使用
[root@master ~]# su - postgres
上一次登录：五 6月 14 10:41:47 CST 2024pts/0 上
-bash-4.2$ psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=#create database gitlabhq_production ENCODING = 'UTF8';
# 创建用户
postgres=# create role gitlab login encrypted password 'gitlab@123';
# 授权
postgres=# grant all privileges on database gitlabhq_production to gitlab;
# 更改postgres密码为gitlab@123
postgres=# \password
#
4.1.6 其他配置
配置日志或数据目录请参考postgresql安装文档
4.2 postgresql-备
4.2.1 install
# 下载rpm包
[root@master ~]# yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
# 安装postgresql，postgresql12-contrib附加功能包
[root@master ~]# yum install -y postgresql12-server postgresql12-contrib
4.2.2 同步
# 从主库同步数据，-U 用户， -D 数据目录 ， -h 主机名
[root@node ~]# pg_basebackup -h master -U postgres -D /var/lib/pgsql/12/data/ -X stream -P
4.2.3 配置postgresql
# 修改权限
chown -R postgres /var/lib/pgsql/12/data/
# 添加配置文件
[root@node ~]# tail /var/lib/pgsql/12/data/postgresql.conf
# Add settings for extensions here
# 连接主库同步，12版本后将recovery文件整合到postgresql.conf中,
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.150.61 port=5432 user=postgres password=gitlab@123'
promote_trigger_file = 'failover.now'
# 创建文件，指定该服务器为备库(有该文件表示是备库)
touch /var/lib/pgsql/12/data/standby.signal
# 启动服务
systemctl start postgresql-12.service
4.2.4 主备验证
# 主库查看
[root@master ~]# /usr/pgsql-12/bin/pg_controldata /var/lib/pgsql/12/data/
pg_control 版本: 1201
Catalog 版本: 201909212
数据库系统标识符: 7379808195541694874
数据库簇状态: 在运行中
pg_control 最后修改: 2024年06月14日 星期五 11时03分00秒
最新检查点位置: 0/933A5A0
最新检查点的 REDO 位置: 0/933A568
最新检查点的重做日志文件: 000000010000000000000009
最新检查点的 TimeLineID: 1
最新检查点的PrevTimeLineID: 1
最新检查点的full_page_writes: 开启
最新检查点的NextXID: 0:4107
最新检查点的 NextOID: 41039
最新检查点的NextMultiXactId: 1
最新检查点的NextMultiOffsetD: 0
最新检查点的oldestXID: 479
最新检查点的oldestXID所在的数据库：1
最新检查点的oldestActiveXID: 4107
最新检查点的oldestMultiXid: 1
最新检查点的oldestMulti所在的数据库：1
最新检查点的oldestCommitTsXid:0
最新检查点的newestCommitTsXid:0
最新检查点的时间: 2024年06月14日 星期五 11时03分00秒
不带日志的关系: 0/3E8使用虚假的LSN计数器
最小恢复结束位置: 0/0
最小恢复结束位置时间表: 0
开始进行备份的点位置: 0/0
备份的最终位置: 0/0
需要终止备份的记录: 否
wal_level设置： replica
wal_log_hints设置： 开启
max_connections设置： 200
max_worker_processes设置： 8
max_wal_senders设置: 10
max_prepared_xacts设置： 0
max_locks_per_xact设置： 64
track_commit_timestamp设置: 关闭
最大数据校准: 8
数据库块大小: 8192
大关系的每段块数: 131072
WAL的块大小: 8192
每一个 WAL 段字节数: 16777216
标识符的最大长度: 64
在索引中可允许使用最大的列数: 32
TOAST区块的最大长度: 1996
大对象区块的大小: 2048
日期/时间 类型存储: 64位整数
正在传递Flloat4类型的参数: 由值
正在传递Flloat8类型的参数: 由值
数据页校验和版本: 0
当前身份验证: 14f2a15581b262afd934b559ce51f89c9841bca6a195fbb18ce7903f5474d31d
# 备库查看
[root@node ~]# /usr/pgsql-12/bin/pg_controldata /var/lib/pgsql/12/data/
pg_control 版本: 1201
Catalog 版本: 201909212
数据库系统标识符: 7379808195541694874
数据库簇状态: 正在归档恢复
pg_control 最后修改: 2024年06月14日 星期五 13时11分55秒
最新检查点位置: 0/6006C38
最新检查点的 REDO 位置: 0/6006C00
最新检查点的重做日志文件: 000000010000000000000006
最新检查点的 TimeLineID: 1
最新检查点的PrevTimeLineID: 1
最新检查点的full_page_writes: 开启
最新检查点的NextXID: 0:496
最新检查点的 NextOID: 24655
最新检查点的NextMultiXactId: 1
最新检查点的NextMultiOffsetD: 0
最新检查点的oldestXID: 479
最新检查点的oldestXID所在的数据库：1
最新检查点的oldestActiveXID: 496
最新检查点的oldestMultiXid: 1
最新检查点的oldestMulti所在的数据库：1
最新检查点的oldestCommitTsXid:0
最新检查点的newestCommitTsXid:0
最新检查点的时间: 2024年06月13日 星期四 16时50分15秒
不带日志的关系: 0/3E8使用虚假的LSN计数器
最小恢复结束位置: 0/6006CB0
最小恢复结束位置时间表: 1
开始进行备份的点位置: 0/0
备份的最终位置: 0/0
需要终止备份的记录: 否
wal_level设置： replica
wal_log_hints设置： 开启
max_connections设置： 200
max_worker_processes设置： 8
max_wal_senders设置: 10
max_prepared_xacts设置： 0
max_locks_per_xact设置： 64
track_commit_timestamp设置: 关闭
最大数据校准: 8
数据库块大小: 8192
大关系的每段块数: 131072
WAL的块大小: 8192
每一个 WAL 段字节数: 16777216
标识符的最大长度: 64
在索引中可允许使用最大的列数: 32
TOAST区块的最大长度: 1996
大对象区块的大小: 2048
日期/时间 类型存储: 64位整数
正在传递Flloat4类型的参数: 由值
正在传递Flloat8类型的参数: 由值
数据页校验和版本: 0
当前身份验证: 14f2a15581b262afd934b559ce51f89c9841bca6a195fbb18ce7903f5474d31d
# 主库创建test7
[root@master ~]# su - postgres
上一次登录：五 6月 14 13:02:18 CST 2024pts/0 上
-bash-4.2$ psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
---------------------+----------+----------+-------------+-------------+-----------------------
gitlabhq_production | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
mydatabase | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test2 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test3 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test4 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test5 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test6 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(11 行记录)
创建
postgres=# create database test7;
CREATE DATABASE
查看
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
---------------------+----------+----------+-------------+-------------+-----------------------
gitlabhq_production | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
mydatabase | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test2 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test3 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test4 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test5 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test6 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test7 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(12 行记录)
# 从库查看
# 主库创建前
[root@node ~]# su - postgres
上一次登录：四 6月 13 16:37:20 CST 2024pts/0 上
-bash-4.2$ psql
psql (12.19)
输入 "help" 来获取帮助信息.
postgres=# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
---------------------+----------+----------+-------------+-------------+-----------------------
gitlabhq_production | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test2 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test3 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test4 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test5 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test6 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(10 行记录)
主库创建后
postgres-# \l
数据库列表
名称 | 拥有者 | 字元编码 | 校对规则 | Ctype | 存取权限
---------------------+----------+----------+-------------+-------------+-----------------------
gitlabhq_production | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
mydatabase | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
postgres | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
test1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test2 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test3 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test4 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test5 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test6 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
test7 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |
(12 行记录)
五、pgpool-II-install
5.1 pg-主
5.1.1 install
# 简介 ，pgpool-Ⅱ是一款介于数据库服务器和客户端之间的中间件。
- 连接池：pgpool 提供连接池功能，降低建立连接带来的开销，同时增加系统的吞吐量
- 负载均衡：当数据库运行在复制模式或主备模式下，SELECT语句运行在集群中任何一个节点都能返回一致的结果，pgpool能将查询语句分发到集群的各个数据库中，从而提升系统的吞吐量，负载均衡适用于只读场景。
- 高可用：当集群中的主库不可用时，pgpool 能够探测到并且激活备库，实现故障转移
- 复制：pgpool 可以管理多个 PostgreSQL 数据库，这是 pgpool 内置的复制特性，也可以使用外部复制方式，例如 PostgreSQL 的流复制等
- 限制超过限度的连接：PostgreSQL支持限制当前最大的连接数，拒绝新连接。pgpool-Ⅱ也支持限制最大连接数，它将超过限制的连接放入队列而不是立即返回一个错误。
- 并行查询：使用并行查询时，数据可以分割到多台服务器上同时执行，以减少总体的执行时间。但并行查询功能不完善。通常使用Postgres-XC|PL/Proxy方案实现PG数据水平拆分
# 安装yum源
[root@master ~]# yum install https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-release-4.2-1.noarch.rpm
# 安装pgpool-II与扩展
[root@master ~]# yum install pgpool-II-12 pgpool-II-12-extensions.x86_64
5.1.2 配置pool_hba
# 与pg_hba保持一致即可
[root@master ~]# egrep -v "^#|^$" /etc/pgpool-II-12/pool_hba.conf
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
host all all 0.0.0.0/0 trust
5.1.3 配置pcp.con
# 设置加密密码，gitlab@123为密码
[root@master ~]# pg_md5 gitlab@123
a32340746724027a7a65a9ebb40708c0
# 将上面生成的值加入到pcp.conf中
[root@master ~]# egrep -v "^#|^$" /etc/pgpool-II-12/pcp.conf
postgres:a32340746724027a7a65a9ebb40708c0
5.1.4 配置pgpool
# 异步流复制
[root@master ~]# egrep -v "^#|^$" /etc/pgpool-II-12/pgpool.conf
listen_addresses = '*'
# Host name or IP address to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
port = 9999 ##### 监听端口
# Port number
# (change requires restart)
socket_dir = '/tmp'
# Unix domain socket path
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
listen_backlog_multiplier = 2
# Set the backlog parameter of listen(2) to
# num_init_children * listen_backlog_multiplier.
# (change requires restart)
serialize_accept = off
# whether to serialize accept() call to avoid thundering herd problem
# (change requires restart)
reserved_connections = 0
# Number of reserved connections.
# Pgpool-II does not accept connections if over
# num_init_chidlren - reserved_connections.
pcp_listen_addresses = '*'
# Host name or IP address for pcp process to listen on:
# '*' for all, '' for no TCP/IP connections
# (change requires restart)
pcp_port = 9898
# Port number for pcp
# (change requires restart)
pcp_socket_dir = '/tmp'
# Unix domain socket path for pcp
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
backend_hostname0 = '192.168.150.61' ######## 主节点
# Host name or IP address to connect to for backend 0
backend_port0 = 5432
# Port number for backend 0
backend_weight0 = 1
# Weight for backend 0 (only in load balancing mode)
backend_data_directory0 = '/var/lib/pgsql/12/data' ########pgsql数据库目录
# Data directory for backend 0
backend_flag0 = 'ALLOW_TO_FAILOVER'
# Controls various backend behavior
# ALLOW_TO_FAILOVER, DISALLOW_TO_FAILOVER
# or ALWAYS_MASTER
backend_application_name0 = 'server0'
# walsender's application_name, used for "show pool_nodes" command
backend_hostname1 = '192.168.150.62' ######## 备节点
backend_port1 = 5432
backend_weight1 = 1
backend_data_directory1 = '/var/lib/pgsql/12/data'
backend_flag1 = 'ALLOW_TO_FAILOVER'
backend_application_name1 = 'server1'
enable_pool_hba = on
# Use pool_hba.conf for client authentication
pool_passwd = 'pool_passwd' ######这里为pd_md5命令设置的认证密码，该值为文件名
# File name of pool_passwd for md5 authentication.
# "" disables pool_passwd.
# (change requires restart)
authentication_timeout = 60
# Delay in seconds to complete client authentication
# 0 means no timeout.
allow_clear_text_frontend_auth = off
# Allow Pgpool-II to use clear text password authentication
# with clients, when pool_passwd does not
# contain the user password
ssl = off
# Enable SSL support
# (change requires restart)
# Path to the SSL private key file
# (change requires restart)
# Path to the SSL public certificate file
# (change requires restart)
# Path to a single PEM format file
# containing CA root certificate(s)
# (change requires restart)
# Directory containing CA root certificate(s)
# (change requires restart)
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
# Allowed SSL ciphers
# (change requires restart)
ssl_prefer_server_ciphers = off
# Use server's SSL cipher preferences,
# rather than the client's
# (change requires restart)
ssl_ecdh_curve = 'prime256v1'
# Name of the curve to use in ECDH key exchange
ssl_dh_params_file = ''
# Name of the file containing Diffie-Hellman parameters used
# for so-called ephemeral DH family of SSL cipher.
num_init_children = 32
# Number of concurrent sessions allowed
# (change requires restart)
max_pool = 4
# Number of connection pool caches per connection
# (change requires restart)
child_life_time = 300
# Pool exits after being idle for this many seconds
child_max_connections = 0
# Pool exits after receiving that many connections
# 0 means no exit
connection_life_time = 0
# Connection to backend closes after being idle for this many seconds
# 0 means no close
client_idle_limit = 500
# Client is disconnected after being idle for that many seconds
# (even inside an explicit transactions!)
# 0 means no disconnection
log_destination = 'syslog'
# Where to log
# Valid values are combinations of stderr,
# and syslog. Default to stderr.
log_line_prefix = '%t: pid %p: ' # printf-style string to output at beginning of each log line.
log_connections = on
# Log connections
log_hostname = on
# Hostname will be shown in ps status
# and in logs if connections are logged
log_statement = on
# Log all statements
log_per_node_statement = on
# Log all statements
# with node and backend informations
log_client_messages = on
# Log any client messages
log_standby_delay = 'none'
# Log standby delay
# Valid values are combinations of always,
# if_over_threshold, none
syslog_facility = 'LOCAL0'
# Syslog local facility. Default to LOCAL0
syslog_ident = 'pgpool'
# Syslog program identification string
# Default to 'pgpool'
# debug5
# debug4
# debug3
# debug2
# debug1
# log
# notice
# warning
# error
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic
pid_file_name = '/var/run/pgpool-II-12/pgpool.pid'
# PID file name
# Can be specified as relative to the"
# location of pgpool.conf file or
# as an absolute path
# (change requires restart)
logdir = '/var/log/pgpool-II-12'
# Directory of pgPool status file
# (change requires restart)
connection_cache = on
# Activate connection pools
# (change requires restart)
# Semicolon separated list of queries
# to be issued at the end of a session
# The default is for 8.3 and later
reset_query_list = 'ABORT; DISCARD ALL'
# The following one is for 8.2 and before
replication_mode = off
# Activate replication mode
# (change requires restart)
replicate_select = off
# Replicate SELECT statements
# when in replication mode
# replicate_select is higher priority than
# load_balance_mode.
insert_lock = on
# Automatically locks a dummy row or a table
# with INSERT statements to keep SERIAL data
# consistency
# Without SERIAL, no lock will be issued
lobj_lock_table = ''
# When rewriting lo_creat command in
# replication mode, specify table name to
# lock
replication_stop_on_mismatch = off
# On disagreement with the packet kind
# sent from backend, degenerate the node
# which is most likely "minority"
# If off, just force to exit this session
failover_if_affected_tuples_mismatch = off
# On disagreement with the number of affected
# tuples in UPDATE/DELETE queries, then
# degenerate the node which is most likely
# "minority".
# If off, just abort the transaction to
# keep the consistency
load_balance_mode = on ########### 负载均衡模式
# Activate load balancing mode
# (change requires restart)
ignore_leading_white_space = on
# Ignore leading white spaces of each query
white_function_list = ''
# Comma separated list of function names
# that don't write to database
# Regexp are accepted
black_function_list = 'currval,lastval,nextval,setval'
# Comma separated list of function names
# that write to database
# Regexp are accepted
black_query_pattern_list = ''
# Semicolon separated list of query patterns
# that should be sent to primary node
# Regexp are accepted
# valid for streaming replicaton mode only.
database_redirect_preference_list = ''
# comma separated list of pairs of database and node id.
# example: postgres:primary,mydb[0-4]:1,mydb[5-9]:2'
# valid for streaming replicaton mode only.
app_name_redirect_preference_list = ''
# comma separated list of pairs of app name and node id.
# example: 'psql:primary,myapp[0-4]:1,myapp[5-9]:standby'
# valid for streaming replicaton mode only.
allow_sql_comments = off
# if on, ignore SQL comments when judging if load balance or
# query cache is possible.
# If off, SQL comments effectively prevent the judgment
# (pre 3.4 behavior).
disable_load_balance_on_write = 'transaction'
# Load balance behavior when write query is issued
# in an explicit transaction.
# Note that any query not in an explicit transaction
# is not affected by the parameter.
# 'transaction' (the default): if a write query is issued,
# subsequent read queries will not be load balanced
# until the transaction ends.
# 'trans_transaction': if a write query is issued,
# subsequent read queries in an explicit transaction
# will not be load balanced until the session ends.
# 'always': if a write query is issued, read queries will
# not be load balanced until the session ends.
statement_level_load_balance = on
# Enables statement level load balancing
master_slave_mode = on
# Activate master/slave mode
# (change requires restart)
master_slave_sub_mode = 'stream'
# Master/slave sub mode
# Valid values are combinations stream, slony
# or logical. Default is stream.
# (change requires restart)
sr_check_period = 5
# Streaming replication check period
# Disabled (0) by default
sr_check_user = 'gitlab' ######### 检查的数据库用户
# Streaming replication check user
# This is necessary even if you disable
# streaming replication delay check with
# sr_check_period = 0
sr_check_password = 'gitlab@123' ######### 检查的数据库密码
# Password for streaming replication check user.
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
sr_check_database = 'gitlabhq_production' #########检查的数据库名
# Database name for streaming replication check
delay_threshold = 0
# Threshold before not dispatching query to standby node
# Unit is in bytes
# Disabled (0) by default
follow_master_command = ''
# Executes this command after master failover
# Special values:
# %d = failed node id
# %h = failed node host name
# %p = failed node port number
# %D = failed node database cluster path
# %m = new master node id
# %H = new master node hostname
# %M = old master node id
# %P = old primary node id
# %r = new master port number
# %R = new master database cluster path
# %N = old primary node hostname
# %S = old primary node port number
# %% = '%' character
health_check_period = 5 ######### 健康检查相关设置
# Health check period
# Disabled (0) by default
health_check_timeout = 20
# Health check timeout
# 0 means no timeout
health_check_user = 'postgres' ######### 用户名
# Health check user
health_check_password = 'gitlab@123' ######### 密码
# Password for health check user
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
health_check_database = 'gitlabhq_production' ######### 数据库
# Database name for health check. If '', tries 'postgres' frist, then 'template1'
health_check_max_retries = 0
# Maximum number of times to retry a failed health check before giving up.
health_check_retry_delay = 1
# Amount of time to wait (in seconds) between retries.
connect_timeout = 10000
# Timeout value in milliseconds before giving up to connect to backend.
# Default is 10000 ms (10 second). Flaky network user may want to increase
# the value. 0 means no timeout.
# Note that this value is not only used for health check,
# but also for ordinary conection to backend.
failover_command = '/etc/pgpool-II-12/failover_stream.sh %H' 主备切换的故障处理脚本
# Executes this command at failover
# Special values:
# %d = failed node id
# %h = failed node host name
# %p = failed node port number
# %D = failed node database cluster path
# %m = new master node id
# %H = new master node hostname
# %M = old master node id
# %P = old primary node id
# %r = new master port number
# %R = new master database cluster path
# %N = old primary node hostname
# %S = old primary node port number
# %% = '%' character
failback_command = ''
# Executes this command at failback.
# Special values:
# %d = failed node id
# %h = failed node host name
# %p = failed node port number
# %D = failed node database cluster path
# %m = new master node id
# %H = new master node hostname
# %M = old master node id
# %P = old primary node id
# %r = new master port number
# %R = new master database cluster path
# %N = old primary node hostname
# %S = old primary node port number
# %% = '%' character
failover_on_backend_error = on
# Initiates failover when reading/writing to the
# backend communication socket fails
# If set to off, pgpool will report an
# error and disconnect the session.
detach_false_primary = off
# Detach false primary if on. Only
# valid in streaming replicaton
# mode and with PostgreSQL 9.6 or
# after.
search_primary_node_timeout = 300
# Timeout in seconds to search for the
# primary node when a failover occurs.
# 0 means no timeout, keep searching
# for a primary node forever.
auto_failback = off
# Dettached backend node reattach automatically
# if replication_state is 'streaming'.
auto_failback_interval = 60
# Min interval of executing auto_failback in
# seconds.
recovery_user = 'nobody'
# Online recovery user
recovery_password = ''
# Online recovery password
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
recovery_1st_stage_command = ''
# Executes a command in first stage
recovery_2nd_stage_command = ''
# Executes a command in second stage
recovery_timeout = 90
# Timeout in seconds to wait for the
# recovering node's postmaster to start up
# 0 means no wait
client_idle_limit_in_recovery = 0
# Client is disconnected after being idle
# for that many seconds in the second stage
# of online recovery
# 0 means no disconnection
# -1 means immediate disconnection
use_watchdog = on
# Activates watchdog
# (change requires restart)
trusted_servers = ''
# trusted server list which are used
# to confirm network connection
# (hostA,hostB,hostC,...)
# (change requires restart)
ping_path = '/bin'
# ping command path
# (change requires restart)
wd_hostname = 'master' ##### 本机主机名
# Host name or IP address of this watchdog
# (change requires restart)
wd_port = 9000
# port number for watchdog service
# (change requires restart)
wd_priority = 1
# priority of this watchdog in leader election
# (change requires restart)
wd_authkey = ''
# Authentication key for watchdog communication
# (change requires restart)
wd_ipc_socket_dir = '/tmp'
# Unix domain socket path for watchdog IPC socket
# The Debian package defaults to
# /var/run/postgresql
# (change requires restart)
delegate_IP = '192.168.150.186' ######## 虚拟IP地址
# delegate IP address
# If this is empty, virtual IP never bring up.
# (change requires restart)
#if_cmd_path = '/sbin'
# path to the directory where if_up/down_cmd exists
# If if_up/down_cmd starts with "/", if_cmd_path will be ignored.
# (change requires restart)
####### 以下为设置虚拟IP地址，$_IP_为delegate_IP指定的IP地址
####### 网卡名根据实际修改
if_up_cmd = '/usr/bin/sudo /sbin/ip addr add $_IP_$/24 dev eth0 label eth0:0'
# startup delegate IP command
# (change requires restart)
if_down_cmd = '/usr/bin/sudo /sbin/ip addr del $_IP_$/24 dev eth0'
# shutdown delegate IP command
# (change requires restart)
#arping_path = '/usr/sbin'
# arping command path
# If arping_cmd starts with "/", if_cmd_path will be ignored.
# (change requires restart)
arping_cmd = '/usr/bin/sudo /usr/sbin/arping -U $_IP_$ -w 1 -I eth0'
# arping command
# (change requires restart)
clear_memqcache_on_escalation = on
# Clear all the query cache on shared memory
# when standby pgpool escalate to active pgpool
# (= virtual IP holder).
# This should be off if client connects to pgpool
# not using virtual IP.
# (change requires restart)
wd_escalation_command = ''
# Executes this command at escalation on new active pgpool.
# (change requires restart)
wd_de_escalation_command = ''
# Executes this command when master pgpool resigns from being master.
# (change requires restart)
failover_when_quorum_exists = on
# Only perform backend node failover
# when the watchdog cluster holds the quorum
# (change requires restart)
failover_require_consensus = on
# Perform failover when majority of Pgpool-II nodes
# aggrees on the backend node status change
# (change requires restart)
allow_multiple_failover_requests_from_node = off
# A Pgpool-II node can cast multiple votes
# for building the consensus on failover
# (change requires restart)
enable_consensus_with_half_votes = off
# apply majority rule for consensus and quorum computation
# at 50% of votes in a cluster with even number of nodes.
# when enabled the existence of quorum and consensus
# on failover is resolved after receiving half of the
# total votes in the cluster, otherwise both these
# decisions require at least one more vote than
# half of the total votes.
# (change requires restart)
wd_monitoring_interfaces_list = '' # Comma separated list of interfaces names to monitor.
# if any interface from the list is active the watchdog will
# consider the network is fine
# 'any' to enable monitoring on all interfaces except loopback
# '' to disable monitoring
# (change requires restart)
wd_lifecheck_method = 'heartbeat'
# Method of watchdog lifecheck ('heartbeat' or 'query' or 'external')
# (change requires restart)
wd_interval = 10
# lifecheck interval (sec) > 0
# (change requires restart)
wd_heartbeat_port = 9694
# Port number for receiving heartbeat signal
# (change requires restart)
wd_heartbeat_keepalive = 2
# Interval time of sending heartbeat signal (sec)
# (change requires restart)
wd_heartbeat_deadtime = 30
# Deadtime interval for heartbeat signal (sec)
# (change requires restart)
heartbeat_destination0 = '192.168.150.62' #########对端的IP地址
# Host name or IP address of destination 0
# for sending heartbeat signal.
# (change requires restart)
heartbeat_destination_port0 = 9694
# Port number of destination 0 for sending
# heartbeat signal. Usually this is the
# same as wd_heartbeat_port.
# (change requires restart)
heartbeat_device0 = 'eth0' #########对端的IP地址网卡名
# Name of NIC device (such like 'eth0')
# used for sending/receiving heartbeat
# signal to/from destination 0.
# This works only when this is not empty
# and pgpool has root privilege.
# (change requires restart)
wd_life_point = 3
# lifecheck retry times
# (change requires restart)
wd_lifecheck_query = 'SELECT 1'
# lifecheck query to pgpool from watchdog
# (change requires restart)
wd_lifecheck_dbname = 'template1'
# Database name connected for lifecheck
# (change requires restart)
wd_lifecheck_user = 'nobody'
# watchdog user monitoring pgpools in lifecheck
# (change requires restart)
wd_lifecheck_password = ''
# Password for watchdog user in lifecheck
# Leaving it empty will make Pgpool-II to first look for the
# Password in pool_passwd file before using the empty password
# (change requires restart)
# Host name or IP address to connect to for other pgpool 0
# (change requires restart)
# Port number for other pgpool 0
# (change requires restart)
# Port number for other watchdog 0
# (change requires restart)
other_pgpool_hostname1 = '192.168.150.62' #########对端的IP地址
other_pgpool_port1 = 9999
other_wd_port1 = 9000
relcache_expire = 0
# Life time of relation cache in seconds.
# 0 means no cache expiration(the default).
# The relation cache is used for cache the
# query result against PostgreSQL system
# catalog to obtain various information
# including table structures or if it's a
# temporary table or not. The cache is
# maintained in a pgpool child local memory
# and being kept as long as it survives.
# If someone modify the table by using
# ALTER TABLE or some such, the relcache is
# not consistent anymore.
# For this purpose, cache_expiration
# controls the life time of the cache.
relcache_size = 256
# Number of relation cache
# entry. If you see frequently:
# "pool_search_relcache: cache replacement happend"
# in the pgpool log, you might want to increate this number.
check_temp_table = catalog
# Temporary table check method. catalog, trace or none.
# Default is catalog.
check_unlogged_table = on
# If on, enable unlogged table check in SELECT statements.
# This initiates queries against system catalog of primary/master
# thus increases load of master.
# If you are absolutely sure that your system never uses unlogged tables
# and you want to save access to primary/master, you could turn this off.
# Default is on.
enable_shared_relcache = on
# If on, relation cache stored in memory cache,
# the cache is shared among child process.
# Default is on.
# (change requires restart)
relcache_query_target = master # Target node to send relcache queries. Default is master (primary) node.
# If load_balance_node is specified, queries will be sent to load balance node.
memory_cache_enabled = off
# If on, use the memory cache functionality, off by default
# (change requires restart)
memqcache_method = 'shmem'
# Cache storage method. either 'shmem'(shared memory) or
# 'memcached'. 'shmem' by default
# (change requires restart)
memqcache_memcached_host = 'localhost'
# Memcached host name or IP address. Mandatory if
# memqcache_method = 'memcached'.
# Defaults to localhost.
# (change requires restart)
memqcache_memcached_port = 11211
# Memcached port number. Mondatory if memqcache_method = 'memcached'.
# Defaults to 11211.
# (change requires restart)
memqcache_total_size = 67108864
# Total memory size in bytes for storing memory cache.
# Mandatory if memqcache_method = 'shmem'.
# Defaults to 64MB.
# (change requires restart)
memqcache_max_num_cache = 1000000
# Total number of cache entries. Mandatory
# if memqcache_method = 'shmem'.
# Each cache entry consumes 48 bytes on shared memory.
# Defaults to 1,000,000(45.8MB).
# (change requires restart)
memqcache_expire = 0
# Memory cache entry life time specified in seconds.
# 0 means infinite life time. 0 by default.
# (change requires restart)
memqcache_auto_cache_invalidation = on
# If on, invalidation of query cache is triggered by corresponding
# DDL/DML/DCL(and memqcache_expire). If off, it is only triggered
# by memqcache_expire. on by default.
# (change requires restart)
memqcache_maxcache = 409600
# Maximum SELECT result size in bytes.
# Must be smaller than memqcache_cache_block_size. Defaults to 400KB.
# (change requires restart)
memqcache_cache_block_size = 1048576
# Cache block size in bytes. Mandatory if memqcache_method = 'shmem'.
# Defaults to 1MB.
# (change requires restart)
memqcache_oiddir = '/var/log/pgpool/oiddir'
# Temporary work directory to record table oids
# (change requires restart)
white_memqcache_table_list = ''
# Comma separated list of table names to memcache
# that don't write to database
# Regexp are accepted
black_memqcache_table_list = ''
# Comma separated list of table names not to memcache
# that don't write to database
# Regexp are accepted
5.1.5 添加pg数据库用户密码
[root@master ~]# pg_md5 -p -m -u postgres pool_passwd
输入密码
# 在pgpool中添加pg数据库的用户名和密码，数据库登录用户是postgres
5.1.6 设置命令setuid权限
[root@master ~]# chmod +s /sbin/ifconfig
[root@master ~]# chmod +s /usr/sbin/arping
[root@master ~]# chmod +s /usr/sbin/ip
5.1.7 添加sudo权限
# 为用户添加sudo权限
postgres ALL=(ALL) NOPASSWD: /sbin/ip,/usr/sbin/arping,/usr/sbin/ifconfig
5.1.8 故障处理脚本
[root@master ~]# cat /etc/pgpool-II-12/failover_stream.sh
#! /bin/sh
# Failover command for streaming replication.
# Arguments: $1: new master hostname.
new_master=$1
trigger_command="/usr/pgsql-12/bin/pg_ctl promote -D /var/lib/pgsql/12/data/"
# Prompte standby database.
/usr/bin/ssh -T $new_master $trigger_command
exit 0
5.1.9 修改文件权限
[root@master ~]# chown -R postgres:postgres /etc/pgpool-II-12/
[root@master ~]# chown -R postgres:postgres /var/run/pgpool-II-12/
[root@master ~]# chmod +x /etc/pgpool-II-12/failover_stream.sh
5.1.10 配置日志记录
[root@master ~]# vim /etc/rsyslog.conf
*.info;mail.none;authpriv.none;cron.none,LOCAL1.none /var/log/messages
LOCAL1.* /var/log/pgpool-II-12/pgpool.log
5.1.11 启动服务
[root@master ~]# systemctl restart rsyslog
[root@master ~]# systemctl enable pgpool-II-12.service --now
[root@master ~]# systemctl status pgpool-II-12.service
5.2 pg-备
5.2.1 install
# 安装yum源
[root@master ~]# yum install https://www.pgpool.net/yum/rpms/4.2/redhat/rhel-7-x86_64/pgpool-II-release-4.2-1.noarch.rpm
# 安装pgpool-II与扩展
[root@master ~]# yum install pgpool-II-12 pgpool-II-12-extensions.x86_64
5.2.2 配置
同 pg-主所有配置
pgpool.conf配置文件中的所有IP对调
5.2.3 启动服务
[root@master ~]# systemctl restart rsyslog
[root@master ~]# systemctl enable pgpool-II-12.service --now
[root@master ~]# systemctl status pgpool-II-12.service
六、gitlab-install
6.1 gitlab-主
6.1.1 install
# 软件包地址
https://mirrors.tuna.tsinghua.edu.cn/gitlab-ce/yum/el7/
# 下载
wget https://mirrors.tuna.tsinghua.edu.cn/gitlab-ce/yum/el7/gitlab-ce-12.3.5-ce.0.el7.x86_64.rpm
# 安装依赖
yum -y install policycoreutils-python
# 安装
rpm -ivh gitlab-ce-12.3.5-ce.0.el7.x86_64.rpm
6.1.2 配置gitlab.rb
# 创建目录
[root@master ~]# mkdir /etc/gitlab/
# 编辑配置文件
[root@master ~]# egrep -v "^#|^$" /etc/gitlab/gitlab.rb
external_url 'http://192.168.150.188'
gitlab_rails['backup_path'] = "/mnt/gitlab-data/backups"
gitlab_rails['backup_keep_time'] = 604800
git_data_dirs({
"default" => {
"path" => "/mnt/gitlab-data/git-data" # 数据目录
}
})
gitlab_rails['shared_path'] = '/mnt/gitlab-data/shared'
gitlab_rails['gitlab_shell_ssh_port'] = 22 # 指定ssh端口
gitlab_rails['uploads_directory'] = "/mnt/gitlab-data/uploads"
gitlab_rails['initial_root_password'] = "gitlab@123" # 指定网页root的密码
gitlab_rails['auto_migrate'] = false
gitlab_rails['db_adapter'] = "postgresql" # 指定外部数据库类型postgresql
gitlab_rails['db_encoding'] = "utf8"
gitlab_rails['db_pool'] = 30
gitlab_rails['db_username'] = "postgres" # 数据库用户
gitlab_rails['db_password'] = "gitlab@123" # 数据库密码
gitlab_rails['db_host'] = "192.168.150.186" # 数据库访问地址，pgpool地址
gitlab_rails['db_port'] = 9999 # 数据库访问地址，pgpool端口
gitlab_rails['redis_host'] = "192.168.150.187" # redis的vip地址
gitlab_rails['redis_port'] = 6379
gitlab_rails['redis_password'] = "gitlab@123" # redis密码
postgresql['enable'] = false # 关闭使用自带的数据库
redis['enable'] = false # 关闭使用自带的redis
gitlab_ci['builds_directory'] = '/mnt/gitlab-data/builds'
6.1.3 刷新配置
gitlab-ctl reconfigure # 刷新配置
6.1.4 导入种子库
# Linux 软件包安装不会为外部数据库设定种子。运行以下命令 要导入架构并创建第一个管理用户
# 关闭gitlab
gitlab-ctl stop
# postgresql添加扩展模块
su - postgres
psql
postgres=# CREATE EXTENSION pg_trgm;
# 只开启gitaly
gitlab-ctl start gitaly
# 导入种子库表
gitlab-rake gitlab:setup
6.1.5 验证
# 先手动配置gitlab的vip地址
ifconfig eth0:0 192.168.150.188/24
# 启动gitlab
gitlab-ctl start
# 验证gitlab配置
gitlab-rake gitlab:check
# 查看状态
gitlab-ctl status
# 查看日志
gitlab-ctl tail
# 访问
http://192.168.150.188
6.1.6 crontab
# 创建目录,并挂载远程目录做备份
[root@master ~]# mkdir /mnt/backup/gitlab-backups/ -p
# /opt/script/pgsql_backup.sh脚本
cat /opt/script/pgsql_backup.sh
#!/bin/bash
su - postgres -c "pg_dumpall > /mnt/var/pgsql/11/backups/$[RANDOM*1024]_$(date +%Y_%m_%d)_postgresql_backup.sql"
su - postgres -c "pg_basebackup -U postgres -D /mnt/var/pgsql/11/backups/$(date +%Y_%m_%d)_data -X stream -P"
# 编辑计划任务备份
[root@master ~]# crontab -l
# 创建全部备份tar文件
0 2 * * * /opt/gitlab/bin/gitlab-backup create CRON=1 && cd /mnt/gitlab-data/backups && cp -a $(ls -t | head -1) /mnt/backup/gitlab-backups/
# 备份数据库文件
0 3 * * * /opt/script/pgsql_backup.sh
# 备份配置文件
0 3 * * 1-5 gitlab-ctl backup-etc && cd /etc/gitlab/config_backup && cp -a $(ls -t | head -n1) /mnt/backup/gitlab-backups/config_backup
cat /opt/script/pgsql_backup.sh
#!/bin/bash
su - postgres -c "pg_dumpall > /mnt/var/pgsql/12/backups/$[RANDOM*1024]_$(date +%Y_%m_%d)_postgresql_backup.sql"
su - postgres -c "pg_basebackup -U postgres -D /mnt/var/pgsql/12/backups/$(date +%Y_%m_%d)_data -X stream -P"
6.2 gitlab-备
6.2.1 创建用户
# 由于node节点使用的是master节点的数据，所以需保证git 、gitlab-www 用户的UID、GID与master上保持一致
1、可以使用nis\ldap方式
2、手动指定，本章采用手动指定
# 查看主节点的用户uid
[root@master ~]# id git
uid=995(git) gid=992(git) 组=992(git)
[root@master ~]# id gitlab-www
uid=996(gitlab-www) gid=993(gitlab-www) 组=993(gitlab-www)
# 创建用户
[root@node ~]# groupadd -g 992 git
[root@node ~]# useradd -u 995 -g 992 git
[root@node ~]# groupadd -g 993 gitlab-www
[root@node ~]# useradd -u 996 -g 993 gitlab-www
6.2.2 install
# 软件包地址
https://mirrors.tuna.tsinghua.edu.cn/gitlab-ce/yum/el7/
# 下载
wget https://mirrors.tuna.tsinghua.edu.cn/gitlab-ce/yum/el7/gitlab-ce-12.3.5-ce.0.el7.x86_64.rpm
# 安装依赖
yum -y install policycoreutils-python
# 安装
rpm -ivh gitlab-ce-12.3.5-ce.0.el7.x86_64.rpm
6.2.3 配置gitlab.rb
# 创建目录
[root@node ~]# mkdir /mnt/gitlab-data
# 编辑配置文件
[root@node ~]# egrep -v "^#|^$" /etc/gitlab/gitlab.rb
external_url 'http://192.168.150.188'
gitlab_rails['backup_path'] = "/mnt/gitlab-data/backups"
gitlab_rails['backup_keep_time'] = 604800
git_data_dirs({
"default" => {
"path" => "/mnt/gitlab-data/git-data" # 数据目录
}
})
gitlab_rails['shared_path'] = '/mnt/gitlab-data/shared'
gitlab_rails['gitlab_shell_ssh_port'] = 22 # 指定ssh端口
gitlab_rails['uploads_directory'] = "/mnt/gitlab-data/uploads"
gitlab_rails['initial_root_password'] = "gitlab@123" # 指定网页root的密码
gitlab_rails['auto_migrate'] = false
gitlab_rails['db_adapter'] = "postgresql" # 指定外部数据库类型postgresql
gitlab_rails['db_encoding'] = "utf8"
gitlab_rails['db_pool'] = 30
gitlab_rails['db_username'] = "postgres" # 数据库用户
gitlab_rails['db_password'] = "gitlab@123" # 数据库密码
gitlab_rails['db_host'] = "192.168.150.186" # 数据库访问地址，pgpool地址
gitlab_rails['db_port'] = 9999 # 数据库访问地址，pgpool端口
gitlab_rails['redis_host'] = "192.168.150.187" # redis的vip地址
gitlab_rails['redis_port'] = 6379
gitlab_rails['redis_password'] = "gitlab@123" # redis密码
postgresql['enable'] = false # 关闭使用自带的数据库
redis['enable'] = false # 关闭使用自带的redis
gitlab_ci['builds_directory'] = '/mnt/gitlab-data/builds'
user['username'] = "git"
user['group'] = "git"
user['uid'] = 995
user['gid'] = 992
web_server['username'] = 'gitlab-www'
web_server['group'] = 'gitlab-www'
web_server['uid'] = 993
web_server['gid'] = 996
七、keepalived-install
7.1 keepalived-主
7.1.1 install
# 安装
[root@master ~]# yum install keepalived -y
7.1.2 配置
[root@master ~]# egrep -v "^#|^$" /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
[email protected]
}
notification_email_from [email protected]
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id web1
}
vrrp_script chk_gitlab {
script "/opt/script/check_gitlab.sh" # gitlab检测脚本
interval 2
}
vrrp_instance VI_1 {
state BACKUP # 非抢占模式，谁先启动谁就会获得vip
nopreempt
interface eth0
virtual_router_id 10
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.150.188 # gitlab虚拟IP地址
}
track_script {
chk_gitlab
}
}
vrrp_script chk_redis {
script "/opt/script/check_redis.sh" # redis检测脚本
interval 2
}
vrrp_instance VI_3 {
state BACKUP
nopreempt
interface eth0
virtual_router_id 30
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
track_script {
chk_redis
}
}
7.1.3 脚本
# gitlab检测脚本
[root@master ~]# cat /opt/script/check_gitlab.sh
#!/bin/bash
gitlab-ctl status
if [[ $? -ne 0 ]]; then
gitlab-ctl restart
gitlab-ctl status
if [[ $? -ne 0 ]]; then
systemctl restart keepalived.service
fi
fi
IP=$(ip a |grep -w 192.168.150.188 |awk '{print $2}' |awk -F/ '{print $1}')
PID=$(pgrep inotifywait)
if [ -n "$IP" ];then
#kill -9 $PID 2>&1
#killall -9 rsync.sh 2>&1
if [ -z "$PID" ];then
/opt/script/rsync.sh
fi
else
sed -n '$p' /opt/script/rsync.log | grep -q 'total size'
[ $? -eq 0 ] && /usr/bin/kill -9 $PID 2>&1
fi
exit 0
# 数据同步脚本,拷贝主节点数据到备节点
[root@master ~]# cat /opt/script/rsync.sh
#!/bin/bash
FROM_DIR="/mnt/gitlab-data"
RSYNC_CMD="rsync -az --delete --exclude=backups --log-file=/opt/script/rsync.log $FROM_DIR [email protected]:/mnt/gitlab-data"
while inotifywait -rqq -e modify,move,create,delete,attrib $FROM_DIR
do
if [ $(pgrep -c rsync) -le 5 ];then
$RSYNC_CMD
fi
done &
# redis检测脚本
[root@master ~]# cat /opt/script/check_redis.sh
#!/bin/bash
VIP='192.168.150.187'
if_up_cmd="ifconfig eth0:3 $VIP"
if_down_cmd="ifconfig eth0:3 down"
passwd='gitlab@123'
#systemctl status redis &>/dev/null
num=$(pidof redis-server | wc -l)
if [[ $num -ne 1 ]];then
$if_down_cmd &> /dev/null
ssh node "$ip_cmd"
fi
redis-cli -h 127.0.0.1 -a $passwd info replication 2>/dev/null | awk -F: '$1~/role/{print $2}' > /opt/role.txt
if grep -q slave /opt/role.txt;then
$if_down_cmd &> /dev/null
ping -c3 -W1 -i 0.1 $VIP &> /dev/null
if [ $? -ne 0 ];then
ssh node "$if_up_cmd"
fi
else
ping -c3 -W1 -i 0.1 $VIP &> /dev/null
if [ $? -ne 0 ];then
$if_up_cmd
fi
fi
exit 0
7.1.4 重新配置gitlab
[root@master ~]# gitlab-ctl reconfigure
[root@master ~]# gitlab-ctl status
7.1.5 启动keepalived
[root@master ~]# systemctl enable keepalived –now
7.2 keepalived-备
7.2.1 install
# 安装
[root@node ~]# yum install keepalived -y
7.2.2 配置文件修改
[root@node ~]# egrep -v "^#|^$" /etc/keepalived/keepalived.conf
同主节点
7.2.3 脚本
脚本同主节点，脚本中IP地址或者主机名需做微调
7.2.4 启动服务
[root@master ~]# systemctl enable keepalived –now
八、验证与故障排除
8.1 查看IP
[root@master ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 52:54:00:37:5a:17 brd ff:ff:ff:ff:ff:ff
inet 192.168.150.61/24 brd 192.168.150.255 scope global noprefixroute eth0
valid_lft forever preferred_lft forever
inet 192.168.150.187/24 brd 192.168.150.255 scope global secondary eth0:3
valid_lft forever preferred_lft forever
inet 192.168.150.188/24 brd 192.168.150.255 scope global secondary eth0:2
valid_lft forever preferred_lft forever
inet6 fe80::554b:efb3:8ff0:7f34/64 scope link noprefixroute
valid_lft forever preferred_lft forever
8.2 postgresql
# 主库宕机后查看
psql -h 192.168.150.186 -p 9999 -U postgres -c "show pool_nodes;"
# 主库宕机后恢复
1、同步现主库数据
-bash-4.2$ /usr/pgsql-12/bin/pg_rewind --target-pgdata=/var/lib/pgsql/12/data --source-server='host=node port=5432 user=postgres dbname=gitlabhq_production password=gitlab@123'
2、配置文件中添加recovery配置
# 添加配置文件
[root@master ~]# tail /var/lib/pgsql/12/data/postgresql.conf
# Add settings for extensions here
# 连接主库同步，12版本后将recovery文件整合到postgresql.conf中,
recovery_target_timeline = 'latest'
primary_conninfo = 'host=192.168.150.62 port=5432 user=postgres password=gitlab@123'
promote_trigger_file = 'failover.now'
# 创建文件，指定该服务器为备库(有该文件表示是备库)
touch /var/lib/pgsql/12/data/standby.signal
# 启动服务
systemctl start postgresql-12.service
3、重新加入集群
[root@master ~]# psql -h 192.168.150.186 -p 9999 -U postgres -c "show pool_nodes;"
[root@master ~]# pcp_attach_node -d -h 192.168.150.186 -U postgres -n 0
# 0 是要加入的节点的ID号
4、再次查看节点状态
psql -h 192.168.150.186 -p 9999 -U postgres -c "show pool_nodes;"
5、登录主库查看同步状态
psql -h 192.168.150.62
6、通过函数查看,主库为f,从库为t
select pg_is_in_recovery();
7、其他命令
pcp_watchdog_info -h 192.168.150.186 -p9898 -Upostgres -v
pcp_node_count -h 192.168.150.186 -p9898 -Upostgres -v
九、参考文档
https://docs.gitlab.com/omnibus/settings/database.html#seed-the-database-fresh-installs-only
https://www.pgpool.net/docs/pgpool-II-3.5.4/doc/pgpool-zh_cn.html#master_slave_mode
https://www.cnblogs.com/binliubiao/p/13181143.html
https://blog.csdn.net/dazuiba008/article/details/103769890
https://cloud.tencent.com/developer/article/1698770