Use barmano recurso synchronous_standby_namespostgresql.conf no arquivo do PostgreSQL

synchronous_standby_names = 'barman_receive_wal'

Não é possível fazer estas operações:

• criarusuário(suspenso)
• criadob(suspenso)
• não pode salvar dados no banco de dados primário se for usado repmgrpara replicação

A configuração principal para eles é

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'

Se remover synchronous_standby_namesdesta configuração, funciona bem.

Segui o documento oficial repmgr , configurei esses itens para o arquivo de configuração:

  shared_preload_libraries = 'repmgr'
  max_wal_senders = 10
  max_replication_slots = 10
  wal_level = 'hot_standby'
  hot_standby = on
  archive_mode = on

No entanto, no /var/lib/pgsql/10/data/postgresql.confarquivo, algo padrão como

#wal_level = replica                    # minimal, replica, or logical
                                        # (change requires restart)

Não tem hot_standbyopção.

Se eu usar a configuração como documento repmgr, quando eu quiser criar um banco de dados ou usuário, sempre travando.

createuser -s myuser
createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O myuser

Mas usar a configuração padrão do postgresql funcionará bem.

Então, como usar repmgrcom o PostgreSQL 10 corretamente?

Use esta tarefa para criar um banco de dados no CentOS pelo Ansible

- name: Create mydb database
  command: createdb -E UNICODE -l en_US.UTF-8 -T template0 mydb -O mydbuser
  become_user: postgres

Ficará pendente. Resultado:

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": []}

Às vezes, executar o comando diretamente no servidor também ficará pendente.

Se reiniciar o serviço PostgreSQL, o problema será corrigido. Qual é o problema?

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)

Como mudar de Cpara utf8?

Eu tentei isso, mas parece que não é permitido.

postgres=# ALTER DATABASE mydb SET "Collate" To 'en_US.UTF-8';
ERROR:  unrecognized configuration parameter "Collate"

servidor pgbouncer (440a:90a0:302:4525:lda0:1:2:3)

pgbouncer.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

Conexão de teste:

-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?

Portas de rede:

sudo lsof -i -P -n | grep LISTEN | grep 6432
pgbouncer 160039 postgres    7u  IPv4 39990293      0t0  TCP 127.0.0.1:6432 (LISTEN)

servidor postgresql (340b:c210:402:4545:3a1a:1:2:3)

Servidor Telnet pgbouncer com porta 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

Tente se conectar à porta 5432 do servidor pgbouncer (servidor postgresql instalado no servidor pgbouncer):

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 '^]'.

Por que não pode aceitar a porta 6432 no servidor postgresql (340b:c210:402:4545:3a1a:1:2:3)?

Se usar essas ferramentas pode fazer um ótimo cluster de banco de dados moderno com recursos completos:

• pgbouncer (com HAProxy para HA)
• repmgr
• barman

O banco de dados principal será executado nos servidores repmgr. Tem primário e standby. Se o primário estiver inativo, o modo de espera pode subir. Mas vai demorar alguns segundos. Nesse momento, o usuário não consegue acessar o banco de dados por pendência? Não existe uma boa maneira de fazer uma resposta em tempo real?

Esta configuração do pgbouncer.ini não pode iniciar o 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

Nenhum arquivo pid gerado, nenhum arquivo de log criado. Nenhum processo em execução ( ps aux | grep pgbouncer).

Mas se definido logfilee pidfilepara:

logfile = pgbouncer.log
pidfile = pgbouncer.pid

pgbouncer pode ser iniciado por run pgbouncer -d pgbouncer.ini. Ele criará o arquivo de log e o arquivo pid no mesmo caminho de pgbouncer.ini.

Estou usando o usuário normal fazendo isso.

Segue o documento oficial, só é necessário instalar o barman pelo yum ou apt direto:

http://docs.pgbarman.org/release/2.10/#installation

Desnecessário instalar o servidor PostgreSQL no servidor barman.

Mas para usar o barman, essas 2 bibliotecas são necessárias: pg_receivewale pg_basebackup. Eles estão no caminho /usr/pgsql-10/bin/se instalar o servidor PostgreSQL.

Como decifrá-los sem instalar o servidor PostgreSQL? Existe alguma maneira?

pgbouncer.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

lista de usuários.txt

"postgres" "postgres1"

Pode ser conectado por psql:

psql -p 6432 -h 127.0.0.1 -U postgres jiradb
(Prompt password: postgres1)

Mas tente conectar do aplicativo:

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>

Ao reiniciar o Jira, o banco de dados não pode ser conectado novamente:

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.)

Estou testando em um servidor. O Jira está instalado no jirausuário. O pgBouncer está sendo executado em postgresuser.

Fez operações:

barman cron
barman receive-wal --create-slot pg
barman receive-wal pg
barman switch-wal --force --archive pg

Em seguida, execute o comando check and backup:

-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)

Não pode terminar depois de muito tempo. /var/lib/barman/pg/base/20200305T124531/Caminho verificado :

• backup.info
• dados

Em data, parece que os dados do servidor pg já foram copiados. Veja o conteúdo em 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

Verifique o processo, obteve:

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"

Por que não pode terminar? status=STARTEDNão foi atualizado para concluir o status.

Estou usando o PostgreSQL 10 agora. Instalou o mais novo Barman no RHEL 7.

Quando executo este comando pelo usuário barman:

-bash-4.2$ barman receive-wal pg
Starting receive-wal for server pg
ERROR: ArchiverFailure:pg_receivexlog not present in $PATH

Introdução oficial

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>

Ele disse:

Prior to PostgreSQL 10, pg_receivewal was named pg_receivexlog.

Por que é a versão 10, tem pg_receivexlogerro?

Verificar backup

Quando eu verifico o backup, tenho:

-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

Como corrigir os itens com falha?

Editar

Quando eu verifico pg_receivewalpor postgresusuário:

$ 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.

Versão do PostgreSQL: 10.12, pg_receivewalnão existe?

PostgreSQL 10 instalado por um usuário normal.

Habilitado e iniciado seu serviço:

sudo systemctl start postgresql-10
sudo systemctl enable postgresql-10

Em seguida, faça o login com postgreso usuário:

sudo su - postgres

Adicione esta configuração ao .bash_profilearquivo:

export PATH=$PATH:/usr/pgsql-10/bin/

Reinicie o PostgreSQL por pg_ctl:

-bash-4.2$ pg_ctl restart

Em seguida, saia para o usuário normal e verifique o status do postgresql novamente, ele falhou. Mesmo usar esta maneira de reiniciar não pode ter sucesso:

sudo systemctl restart postgresql-10
sudo systemctl stop postgresql-10

Sempre failed.

Mas se eu usar postgreso usuário para testar reiniciar, parar, iniciar, todos eles podem ter sucesso.

Essas 2 maneiras não estão controlando o mesmo processo?

Adicionar conteúdo de serviço 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

Editei um arquivo de configuração do PostgreSQL:

vi /var/lib/pgsql/10/data/postgresql.conf

Eu não salvei com sucesso, então ele criou um arquivo .swp.

Toda vez que tento editar este arquivo novamente, o terminal irá parar sem nenhuma ação. Tenho que fechar o terminal.

Tentei parar o processo do PostgreSQL pelo usuário root:

sudo systemctl stop postgresql-10

Mais uma vez, o terminal ficou em silêncio. Não é possível aceitar mais nenhuma entrada.

Quando eu corro:

sudo ls -la /var/lib/pgsql/10/data/

O mesmo terminal parou.

Quando eu corro:

sudo ls -la /var/lib/pgsql/10/

Funciona.

Não sei o que aconteceu na datapasta. Eu sei que o arquivo swp existe lá. Mas não posso sair desse caminho agora.

Quando tentei editar o arquivo postgresql.conf novamente, ele mostrou:

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.

Eu usei dessa maneira matou esse número de processo:

sudo kill -9 25896

Em seguida, pode listar os arquivos em /var/lib/pgsql/10/data/caminho.

Mas quando tentei editar postgresql.confnovamente, parou de novo!

Fecho o terminal e entro no servidor, fiz essas coisas. Eles não podem ser excluídos.

[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

Qual é o motivo? Como corrigi-lo?

Para um sistema Jira, use a configuração de conexão padrão do Jira conforme abaixo:

<?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>

No arquivo de configuração do postgresql:

postgresql.conf

max_connections = 300
shared_buffers = 80MB

No arquivo /etc/sysctl.conf:

kernel.shmmax=100663296

Na fase de teste, pode funcionar. Mas no ambiente de produção, para 1000 usuários usam este sistema, se um servidor de aplicativos atende 500 usuários, quanto tamanho de conexão de pool é bom para definir no arquivo postgresql.conf? E, se usar o pgbouncer, quantas conexões ele pode ser reduzido?

Instalar

$ 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

Verificar status

$ brew services list
postgresql     started user /Users/user/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

Conectar

$ 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

Já iniciou banco de dados, por postgresque não existe?

Para este exemplo de configuração do 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

Eu tenho duas perguntas.

Questão 1

É assim que uma máscara de rede se parece com o IPv4: 192.168.1.0/24.

Como fazer com um endereço IP IPv6 como 230b:c010:103:5858:a6a3:3:0:1?

Questão 2

Se usar JDBC para conectar a um servidor PostgreSQL, posso usar uma URL de conexão contendo 192.168.1.11:5432. Como fazer isso com IPv6? É como 230b:c010:103:5858:a6a3:3:0:1:5432?

Existem duas maneiras de tentar.

Método 1

Use JDBC para conectar o cluster PostgreSQL (somente servidores primários em espera 2):

jdbc:postgresql://192.168.0.1:5432,192.168.0.2:5432/jiradb

Mas falhou:

A configuração do seu arquivo dbconfig.xml está incorreta (usuário, senha ou URL do banco de dados etc.)

Como conectar vários servidores do jdbc?

Método 2

Usar pgbouncererepmgr

Três servidores:

• pgbouncer
• PostgreSQL Primário (192.168.0.1) Repmgr instalado, executando repmgrd
• PostgreSQL Standby (192.168.0.2) Repmgr instalado, executando repmgrd

Defina a configuração no servidor 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

Quando o primário estiver inativo, o repmgr pode alternar para o servidor em espera como primário. Mas o pgbouncer não conectou automaticamente o segundo servidor db. Por quê? A configuração para databasesnão funciona. É o uso errado? Ou o pgbouncer não possui esse recurso de troca automática?

As ferramentas de terceiros mais populares são pgpool e regmgr.

pgpool pode fazer HA e failover automático, regmgr também pode fazer replicação e failover automático. Alguns artigos mostram o uso de ambos para fazer no cluster. Por quê? Quais são as melhores práticas para ir para a meta?