使用PostgreSQL文件中barman的synchronous_standby_names特性postgresql.conf
synchronous_standby_names = 'barman_receive_wal'
不能做这些操作：
repmgr如果用于复制，则无法将数据保存在主数据库中他们的主要配置是
max_wal_senders = 10
max_replication_slots = 10
shared_preload_libraries = 'repmgr'
wal_level = 'replica'
archive_mode = on
archive_command = 'rsync -a %p barman@backup_server:/var/lib/barman/mydb/incoming/%f'
synchronous_standby_names = 'barman_receive_wal'
如果synchronous_standby_names从此配置中删除，它工作得很好。
按照官方repmgr文档，我将这些项目设置到配置文件中：
shared_preload_libraries = 'repmgr'
max_wal_senders = 10
max_replication_slots = 10
wal_level = 'hot_standby'
hot_standby = on
archive_mode = on
但是，在/var/lib/pgsql/10/data/postgresql.conf文件中，默认为
#wal_level = replica # minimal, replica, or logical
# (change requires restart)
没有hot_standby选项。
如果我使用配置作为repmgr 文件，当我想创建数据库或用户时，总是挂起。
createuser -s myuser
createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O myuser
但是使用默认的 postgresql 配置会很好用。
那么如何repmgr正确使用 PostgreSQL 10 呢？
使用此任务通过 Ansible 在 CentOS 上创建数据库
- name: Create mydb database
command: createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O mydbuser
become_user: postgres
它将悬而未决。输出：
TASK [db_primary : Create mydb database] *********************************************************************************************************
fatal: [stg-master]: FAILED! => {"changed": true, "cmd": ["createdb", "-E", "UNICODE", "-l", "en_US.UTF-8", "-T", "template0", "mydb", "-O", "mydbuser"], "delta": "0:00:44.323037", "end": "2020-09-17 18:44:48.873994", "msg": "non-zero return code", "rc": 1, "start": "2020-09-17 18:44:04.550957", "stderr": "WARNING: canceling the wait for synchronous replication and terminating connection due to administrator command\nDETAIL: The transaction has already committed locally, but might not have been replicated to the standby.\ncreatedb: database creation failed: server closed the connection unexpectedly\n\tThis probably means the server terminated abnormally\n\tbefore or while processing the request.", "stderr_lines": ["WARNING: canceling the wait for synchronous replication and terminating connection due to administrator command", "DETAIL: The transaction has already committed locally, but might not have been replicated to the standby.", "createdb: database creation failed: server closed the connection unexpectedly", "\tThis probably means the server terminated abnormally", "\tbefore or while processing the request."], "stdout": "", "stdout_lines": []}
有时直接在服务器上运行命令也会被挂起。
如果重新启动 PostgreSQL 服务将解决此问题。有什么问题？
postgres=# SELECT * FROM pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath
---------------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+------------------+---------+----------
virtualxid | | | | | 7/10 | | | | | 7/10 | 30451 | ExclusiveLock | t | t
virtualxid | | | | | 6/3 | | | | | 6/3 | 30290 | ExclusiveLock | t | t
relation | 13808 | 11577 | | | | | | | | 3/3 | 29004 | AccessShareLock | t | t
virtualxid | | | | | 3/3 | | | | | 3/3 | 29004 | ExclusiveLock | t | t
relation | 0 | 2676 | | | | | | | | 7/10 | 30451 | RowExclusiveLock | t | f
transactionid | | | | | | 569 | | | | 7/10 | 30451 | ExclusiveLock | t | f
relation | 0 | 1260 | | | | | | | | 7/10 | 30451 | RowExclusiveLock | t | f
transactionid | | | | | | 568 | | | | 7/10 | 30451 | ShareLock | f | f
transactionid | | | | | | 568 | | | | 6/3 | 30290 | ExclusiveLock | t | f
relation | 0 | 2677 | | | | | | | | 7/10 | 30451 | RowExclusiveLock | t | f
relation | 0 | 1260 | | | | | | | | 6/3 | 30290 | RowExclusiveLock | t | f
(11 rows)
比如从C变为utf8?
我试过这个，但似乎不允许。
postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8';
ERROR: unrecognized configuration parameter "Collate"
弹跳器.ini
[databases]
app_db = host=pg_server port=5432 dbname=app_db
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = someuser
测试连接：
-bash-4.2$ psql -p 6432 -h pg_server -U postgres app_db
psql: could not connect to server: Connection refused
Is the server running on host "pg_server" (340b:c210:402:4545:3a1a:1:2:3) and accepting
TCP/IP connections on port 6432?
网络端口：
sudo lsof -i -P -n | grep LISTEN | grep 6432
pgbouncer 160039 postgres 7u IPv4 39990293 0t0 TCP 127.0.0.1:6432 (LISTEN)
Telnet pgbouncer 服务器与 6432 端口：
$ telnet -6 440a:90a0:302:4525:lda0:1:2:3 6432
Trying 440a:90a0:302:4525:lda0:1:2:3...
telnet: connect to address 440a:90a0:302:4525:lda0:1:2:3: Connection refused
尝试连接到 pgbouncer 服务器的 5432 端口（安装在 pgbouncer 服务器上的 postgresql 服务器）：
telnet -6 440a:90a0:302:4525:lda0:1:2:3 5432
Trying 440a:90a0:302:4525:lda0:1:2:3...
Connected to 440a:90a0:302:4525:lda0:1:2:3.
Escape character is '^]'.
为什么不能在 postgresql 服务器上接受 6432 端口（340b:c210:402:4545:3a1a:1:2:3）？
如果使用这些工具可以创建一个具有完整功能的现代数据库集群：
主数据库将在 repmgr 服务器上运行。它有主备。如果primary down，standby可以up。但需要几秒钟。那个时候，用户不能通过pending访问数据库？难道没有一个很好的方法来进行实时响应吗？
此 pgbouncer.ini 设置无法启动 pgbouncer：
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
admin_users = someuser
没有生成 pid 文件，没有创建日志文件。没有进程正在运行（ps aux | grep pgbouncer）。
但如果设置logfile为pidfile：
logfile = pgbouncer.log
pidfile = pgbouncer.pid
pgbouncer 可以通过 run 启动pgbouncer -d pgbouncer.ini。它将在同一路径创建日志文件和 pid 文件pgbouncer.ini。
我正在使用普通用户这样做。
按照官方文档，只需通过yum或apt直接安装barman即可：
http://docs.pgbarman.org/release/2.10/#installation
无需在酒保服务器上安装 PostgreSQL 服务器。
但是为了使用酒保，这两个库是必需的：pg_receivewal和pg_basebackup. /usr/pgsql-10/bin/如果安装 PostgreSQL 服务器，它们在路径中。
如何在不安装 PostgreSQL 服务器的情况下制作它们？到底有没有？
弹跳器.ini
[databases]
jiradb = host=127.0.0.1 port=5432 dbname=jiradb
[pgbouncer]
listen_port = 6432
listen_addr = 127.0.0.1
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
用户列表.txt
"postgres" "postgres1"
它可以通过以下方式连接psql：
psql -p 6432 -h 127.0.0.1 -U postgres jiradb
(Prompt password: postgres1)
但尝试从应用程序连接：
Jira dbconfig.xml：
<?xml version="1.0" encoding="UTF-8"?>
<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>postgres72</database-type>
<schema-name>public</schema-name>
<jdbc-datasource>
<url>jdbc:postgresql://127.0.0.1:6432/jiradb</url>
<driver-class>org.postgresql.Driver</driver-class>
<username>postgres</username>
<password>postgres1</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>
</jdbc-datasource>
</jira-database-config>
重启jira后，无法再次连接数据库：
Database: JIRA couldn't connect to your database
JIRA failed to establish a connection to your database.
This could be because:
Your database isn't running
The configuration of your dbconfig.xml file is incorrect (user, password, or database URL etc.)
There is a network issue between JIRA and your database (e.g. firewall, database doesn't allow remote access etc.)
我正在一台服务器上对其进行测试。Jira 安装在jira用户处。pgBouncer 在postgres用户下运行。
做过操作：
barman cron
barman receive-wal --create-slot pg
barman receive-wal pg
barman switch-wal --force --archive pg
然后运行检查和备份命令：
-bash-4.2$ barman check pg
Server pg:
PostgreSQL: OK
is_superuser: OK
PostgreSQL streaming: OK
wal_level: OK
replication slot: OK
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
pg_basebackup: OK
pg_basebackup compatible: OK
pg_basebackup supports tablespaces mapping: OK
systemid coherence: OK (no system Id stored on disk)
pg_receivexlog: OK
pg_receivexlog compatible: OK
receive-wal running: OK
archive_mode: OK
archive_command: OK
archiver errors: OK
-bash-4.2$ barman backup pg
Starting backup using postgres method for server pg in /var/lib/barman/pg/base/20200305T124531
Backup start at LSN: 0/5100A7B8 (000000010000000000000051, 0000A7B8)
Starting backup copy via pg_basebackup for 20200305T124531
(pending)
很长一段时间后它无法完成。检查/var/lib/barman/pg/base/20200305T124531/路径：
在data中，似乎已经复制了来自 pg 服务器的数据。请参阅中的内容backup.info：
backup_label=None
begin_offset=42936
begin_time=2020-03-05 12:45:31.020705+09:00
begin_wal=000000010000000000000051
begin_xlog=0/5100A7B8
config_file=/var/lib/pgsql/10/data/postgresql.conf
copy_stats=None
deduplicated_size=None
end_offset=None
end_time=None
end_wal=None
end_xlog=None
error=None
hba_file=/var/lib/pgsql/10/data/pg_hba.conf
ident_file=/var/lib/pgsql/10/data/pg_ident.conf
included_files=None
mode=postgres
pgdata=/var/lib/pgsql/10/data
server_name=pg
size=None
status=STARTED
systemid=6795775088862271947
tablespaces=None
timeline=1
version=100012
xlog_segment_size=16777216
检查过程，得到：
barman 5661 0.0 0.2 258560 19204 ? Ss 12:45 0:00 /usr/bin/python2 /bin/barman -c /etc/barman.conf -q receive-wal pg
barman 5671 0.0 0.0 176384 3416 ? S 12:45 0:00 /usr/pgsql-10/bin/pg_receivewal --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_receive_wal --verbose --no-loop --no-password --directory=/var/lib/barman/pg/streaming --slot=barman
postgres 5675 0.0 0.0 397052 3844 ? Ss 12:45 0:00 postgres: wal sender process streaming_barman 127.0.0.1(41570) streaming 0/534365C8
barman 5745 0.0 0.2 258564 19080 pts/0 S+ 12:45 0:00 /usr/bin/python2 /bin/barman backup pg
barman 5751 0.0 0.0 176132 3316 pts/0 S+ 12:45 0:00 /bin/pg_basebackup --dbname=dbname=replication host=pg options=-cdatestyle=iso replication=true user=streaming_barman application_name=barman_streaming_backup -v --no-password --pgdata=/var/lib/barman/pg/base/20200305T124531/data --no-slot --wal-method=none
postgres 5752 0.0 0.0 396724 7980 ? Ss 12:45 0:00 postgres: wal sender process streaming_barman 127.0.0.1(41618) sending backup "pg_basebackup base backup"
为什么不能完成？status=STARTED未更新完成状态。
我现在正在使用 PostgreSQL 10。在 RHEL 7 上安装了最新的 Barman。
当我由酒保用户运行此命令时：
-bash-4.2$ barman receive-wal pg
Starting receive-wal for server pg
ERROR: ArchiverFailure:pg_receivexlog not present in $PATH
WAL streaming
Barman can reduce the Recovery Point Objective (RPO) by allowing users to add continuous WAL streaming from a PostgreSQL server, on top of the standard archive_command strategy.
Barman relies on pg_receivewal, a utility that has been available from PostgreSQL 9.2 which exploits the native streaming replication protocol and continuously receives transaction logs from a PostgreSQL server (master or standby). Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.
IMPORTANT: Barman requires that pg_receivewal is installed on the same server. For PostgreSQL 9.2 servers, you need pg_receivexlog of version 9.2 installed alongside Barman. For PostgreSQL 9.3 and above, it is recommended to install the latest available version of pg_receivewal, as it is back compatible. Otherwise, users can install multiple versions of pg_receivewal/pg_receivexlog on the Barman server and properly point to the specific version for a server, using the path_prefix option in the configuration file.
In order to enable streaming of transaction logs, you need to:
setup a streaming connection as previously described
set the streaming_archiver option to on
The cron command, if the aforementioned requirements are met, transparently manages log streaming through the execution of the receive-wal command. This is the recommended scenario.
However, users can manually execute the receive-wal command:
barman receive-wal <server_name>
它说：
Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.
为什么是版本10，有pg_receivexlog错误？
当我检查备份时，得到：
-bash-4.2$ barman check pg
Server pg:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
is_superuser: OK
PostgreSQL streaming: OK
wal_level: OK
replication slot: FAILED (slot 'barman' not initialised: is 'receive-wal' running?)
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
pg_basebackup: OK
pg_basebackup compatible: OK
pg_basebackup supports tablespaces mapping: OK
systemid coherence: OK (no system Id stored on disk)
pg_receivexlog: FAILED
pg_receivexlog compatible: FAILED (PostgreSQL version: 10.12, pg_receivexlog version: None)
receive-wal running: FAILED (See the Barman log file for more details)
archiver errors: OK
如何修复失败的项目？
当我pg_receivewal按postgres用户检查时：
$ sudo su - postgres
-bash-4.2$ pg_receivewal
-bash: pg_receivewal: command not found
-bash-4.2$ psql
psql (9.2.24, server 10.12)
WARNING: psql version 9.2, server version 10.0.
Some psql features might not work.
Type "help" for help.
PostgreSQL 版本：10.12，pg_receivewal不存在？
由普通用户安装 PostgreSQL 10。
启用并启动其服务：
sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10
然后使用postgres用户登录：
sudo su - postgres
将此设置添加到.bash_profile文件：
export PATH=$PATH:/usr/pgsql-10/bin/
通过以下方式重新启动 PostgreSQL pg_ctl：
-bash-4.2$ pg_ctl restart
然后退出到普通用户并再次检查postgresql的状态，它失败了。即使用这种方式重启也不能成功：
sudo systemctl restart postgresql-10
sudo systemctl stop postgresql-10
它总是failed。
但是如果我使用postgres用户来测试重启，停止，启动，它们都可以成功。
这两种方式不是控制同一个过程吗？
添加postgresql-10服务内容
$ sudo systemctl cat postgresql-10.service
# /usr/lib/systemd/system/postgresql-10.service
# It's not recommended to modify this file in-place, because it will be
# overwritten during package upgrades. If you want to customize, the
# best way is to create a file "/etc/systemd/system/postgresql-10.service",
# containing
# .include /usr/lib/systemd/system/postgresql-10.service
# ...make your changes here...
# For more info about custom unit files, see
# http://fedoraproject.org/wiki/Systemd#How_do_I_customize_a_unit_file.2F_add_a_custom_unit_file.3F
# Note: changing PGDATA will typically require adjusting SELinux
# configuration as well.
# Note: do not use a PGDATA pathname containing spaces, or you will
# break postgresql-setup.
[Unit]
Description=PostgreSQL 10 database server
Documentation=https://www.postgresql.org/docs/10/static/
After=syslog.target
After=network.target
[Service]
Type=notify
User=postgres
Group=postgres
# Note: avoid inserting whitespace in these Environment= lines, or you may
# break postgresql-setup.
# Location of database directory
Environment=PGDATA=/var/lib/pgsql/10/data/
# Where to send early-startup messages from the server (before the logging
# options of postgresql.conf take effect)
# This is normally controlled by the global default set by systemd
# StandardOutput=syslog
# Disable OOM kill on the postmaster
OOMScoreAdjust=-1000
Environment=PG_OOM_ADJUST_FILE=/proc/self/oom_score_adj
Environment=PG_OOM_ADJUST_VALUE=0
ExecStartPre=/usr/pgsql-10/bin/postgresql-10-check-db-dir ${PGDATA}
ExecStart=/usr/pgsql-10/bin/postmaster -D ${PGDATA}
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
# Do not set any timeout value, so that systemd will not kill postmaster
# during crash recovery.
TimeoutSec=0
[Install]
WantedBy=multi-user.target
我编辑了一个 PostgreSQL 配置文件：
vi /var/lib/pgsql/10/data/postgresql.conf
我没有成功保存它然后它创建了一个 .swp 文件。
每次我尝试再次编辑此文件时，终端都会停止而没有任何动作。我必须关闭终端。
我试图通过 root 用户停止 PostgreSQL 进程：
sudo systemctl stop postgresql-10
终端再次陷入沉默。不能再接受任何输入。
当我运行时：
sudo ls -la /var/lib/pgsql/10/data/
同一个航站楼停了下来。
当我运行时：
sudo ls -la /var/lib/pgsql/10/
有用。
我不知道data文件夹里发生了什么。我知道swp文件存在那里。但是现在不能从那条路径上移开。
当我再次尝试编辑 postgresql.conf 文件时，它显示：
E325: ATTENTION
Found a swap file by the name "~/10/data/.postgresql.conf.swp"
owned by: postgres dated: Thu Feb 27 17:57:51 2020
file name: ~postgres/10/data/postgresql.conf
modified: YES
user name: postgres host name: server
process ID: 25896 (still running)
While opening file "/var/lib/pgsql/10/data/postgresql.conf"
dated: Thu Feb 27 17:57:57 2020
NEWER than swap file!
(1) Another program may be editing the same file. If this is the case,
be careful not to end up with two different instances of the same
file when making changes. Quit, or continue with caution.
(2) An edit session for this file crashed.
If this is the case, use ":recover" or "vim -r /var/lib/pgsql/10/data/postgresql.conf"
to recover the changes (see ":help recovery").
If you did this already, delete the swap file "/var/lib/pgsql/10/data/.postgresql.conf.swp"
to avoid this message.
我用这种方式杀死了那个进程号：
sudo kill -9 25896
然后可以列出/var/lib/pgsql/10/data/路径下的文件。
但是当试图postgresql.conf再次编辑时，它又停止了！
我关闭终端并登录到服务器，做了这些事情。它们不能被删除。
[user@server ~]$ ps aux | grep postgresql
postgres 12569 0.0 0.0 126476 1920 ? D 09:20 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 25896 0.0 0.0 126416 2156 ? D Feb27 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 26080 0.0 0.0 126320 1856 ? D Feb27 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 26542 0.0 0.0 126476 1916 ? D 13:35 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 26741 0.0 0.0 126472 1832 ? D 13:37 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
root 26924 0.0 0.0 241036 4552 ? S 13:39 0:00 sudo rm /var/lib/pgsql/10/data/postgresql.conf
root 26926 0.4 0.0 107984 640 ? D 13:39 0:00 rm /var/lib/pgsql/10/data/postgresql.conf
postgres 27027 0.0 0.0 126332 1828 ? D 13:40 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
user 27085 0.0 0.0 112684 1000 pts/14 S+ 13:40 0:00 grep --color=auto postgresql
[user@server ~]$ sudo kill -9 12569
[user@server ~]$ sudo kill -9 25896
[user@server ~]$ sudo kill -9 26080
[user@server ~]$ sudo kill -9 26542
[user@server ~]$ sudo kill -9 26741
[user@server ~]$ sudo kill -9 26924
[user@server ~]$ sudo kill -9 26926
[user@server ~]$ sudo kill -9 27027
[user@server ~]$ ps aux | grep postgresql
postgres 12569 0.0 0.0 126476 1920 ? D 09:20 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 25896 0.0 0.0 126416 2156 ? D Feb27 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 26080 0.0 0.0 126320 1856 ? D Feb27 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 26542 0.0 0.0 126476 1916 ? D 13:35 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
postgres 26741 0.0 0.0 126472 1832 ? D 13:37 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
root 26926 0.4 0.0 107984 640 ? D 13:39 0:00 rm /var/lib/pgsql/10/data/postgresql.conf
postgres 27027 0.0 0.0 126332 1828 ? D 13:40 0:00 vi /var/lib/pgsql/10/data/postgresql.conf
user 27155 0.0 0.0 112684 996 pts/14 S+ 13:41 0:00 grep --color=auto postgresql
什么原因？如何解决？
对于 Jira 系统，如果使用 Jira 的默认连接配置如下：
<?xml version="1.0" encoding="UTF-8"?>
<jira-database-config>
<name>defaultDS</name>
<delegator-name>default</delegator-name>
<database-type>postgres72</database-type>
<schema-name>public</schema-name>
<jdbc-datasource>
<url>jdbc:postgresql://[IP]:5432/jiradb</url>
<driver-class>org.postgresql.Driver</driver-class>
<username>jiradbuser</username>
<password>{PASSWORD}</password>
<pool-min-size>20</pool-min-size>
<pool-max-size>20</pool-max-size>
<pool-max-wait>30000</pool-max-wait>
<validation-query>select 1</validation-query>
<min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
<time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
<pool-max-idle>20</pool-max-idle>
<pool-remove-abandoned>true</pool-remove-abandoned>
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-on-borrow>false</pool-test-on-borrow>
<pool-test-while-idle>true</pool-test-while-idle>
</jdbc-datasource>
</jira-database-config>
在 postgresql 的配置文件中：
postgresql.conf
max_connections = 300
shared_buffers = 80MB
在 /etc/sysctl.conf 文件中：
kernel.shmmax=100663296
在测试阶段，它可以工作。但是在生产环境中，1000个用户使用这个系统，如果一个app server服务500个用户，那么在postgresql.conf文件中设置多少pool connection size比较好呢？而且，如果使用 pgbouncer，它可以减少多少连接？
$ brew install postgresql
Updating Homebrew...
==> Downloading https://homebrew.bintray.com/bottles/postgresql-12.1.mojave.bottle.tar.gz
Already downloaded: /Users/user/Library/Caches/Homebrew/downloads/525a3637cd6f7b7a7e0bfe5adb102483ae01f4be31269aaf4348f6f4291f8a86--postgresql-12.1.mojave.bottle.tar.gz
==> Pouring postgresql-12.1.mojave.bottle.tar.gz
==> /usr/local/Cellar/postgresql/12.1/bin/initdb --locale=C -E UTF-8 /usr/local/var/postgres
==> Caveats
To migrate existing data from a previous major version of PostgreSQL run:
brew postgresql-upgrade-database
To have launchd start postgresql now and restart at login:
brew services start postgresql
Or, if you don't want/need a background service you can just run:
pg_ctl -D /usr/local/var/postgres start
==> Summary
? /usr/local/Cellar/postgresql/12.1: 3,217 files, 37.3MB
$ brew services list
postgresql started user /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist
$ psql
psql: error: could not connect to server: FATAL: database "user" does not exist
$ psql -U postgres
psql: error: could not connect to server: FATAL: database "postgres" does not exist
它已经初始化了数据库，为什么postgres不存在呢？
对于这个 PostgreSQL 配置示例：
https://repmgr.org/docs/4.4/quickstart-authentication.html
local replication repmgr trust
host replication repmgr 127.0.0.1/32 trust
host replication repmgr 192.168.1.0/24 trust
local repmgr repmgr trust
host repmgr repmgr 127.0.0.1/32 trust
host repmgr repmgr 192.168.1.0/24 trust
我有两个问题。
这就是网络掩码查找 IPv4 的方式：192.168.1.0/24.
如何处理 IPv6 IP 地址230b:c010:103:5858:a6a3:3:0:1？
如果使用 JDBC 连接到 PostgreSQL 服务器，我可以使用包含192.168.1.11:5432. 如何用 IPv6 做到这一点？是这样230b:c010:103:5858:a6a3:3:0:1:5432吗？
有两种方法可以尝试。
使用 JDBC 连接 PostgreSQL 集群（仅限主、备 2 台服务器）：
jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb
但失败了：
您的 dbconfig.xml 文件的配置不正确（用户、密码或数据库 URL 等）
如何从 jdbc 连接多个服务器？
使用pgbouncer和repmgr
三台服务器：
在 pgbouncer 服务器（pgbouncer.ini）中设置配置：
[databases]
postgres = host=192.168.0.1 port=5432 dbname=postgres
postgres = host=192.168.0.2 port=5432 dbname=postgres
[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = userlist.txt
logfile = pgbouncer.log
pidfile = pgbouncer.pid
admin_users = postgres
unix_socket_dir = /tmp
当主服务器关闭时，repmgr 可以切换到备用服务器作为主服务器。但是 pgbouncer 没有自动连接第二个数据库服务器。为什么？的配置设置databases不起作用。是错误的用法吗？还是pgbouncer没有这个自动切换功能？
最流行的第三方工具是 pgpool 和 regmgr。
pgpool 可以做 HA 和自动故障转移，regmgr 也可以做复制和自动故障转移。一些文章显示在集群中同时使用它们。为什么？实现目标的最佳实践是什么？