postgres postgresql conf config configuration setting

Configurações

O PostgreSQL guarda suas configurações em dois arquivos de texto chamados postgresql.conf e postgresql.auto.conf.

Dentro dos arquivos, linhas que começam com uma cerquilha (#) são comentários de e para humanos que forem editá-los e, portanto, são ignorados pelo PostgreSQL.

O PostgreSQL lê primeiro o arquivo postgresql.conf e depois o arquivo postgresql.auto.conf. Todo parâmetro encontrado nos dois é aplicado, mas quando uma mesma configuração está em dois pontos diferentes, o último valor lido é usado. Então, nos arquivos:

# conteúdo do arquivo postgresql.conf
shared_buffers = 128MB
listen_addresses = 'localhost'
# conteúdo do arquivo postgresql.auto.conf
listen_addresses = '*'

Os parâmetros shared_buffers = 128MB, proveniente do primeiro arquivo, e listen_addresses = '*', do segundo arquivo, são os valores efetivos, já que o listen_addresses = 'localhost' foi descartado pela linha do segundo arquivo.

Lendo os parâmetros de configuração

É possível ler o valor atual de qualquer parâmetro de configuração usando um cliente como o psql com um comando SHOW:

[[local]:5432] postgres@postgres=# SHOW listen_addresses;
╔══════════════════╗
║ listen_addresses ║
╠══════════════════╣
║ *                ║
╚══════════════════╝

Obs.: Use SHOW ALL para listar todas as configurações, com suas respectivas descrições.

Também é possível consultar essa mesma informação através da visão pg_settings (adicione também o campo description se quiser as descrições):

[[local]:5432] postgres@postgres=# SELECT name, setting, unit FROM pg_settings WHERE name = 'listen_addresses';
╔══════════════════╤═════════╗
║       name       │ setting ║
╠══════════════════╪═════════╣
║ listen_addresses │ *       ║
╚══════════════════╧═════════╝

Contextos de parâmetros de configuração

O contexto de uma configuração define quando o valor é aplicado e quando pode ser alterado.

Para saber qual é o contexto de um parâmetro, consulte pg_settings. Por exemplo, com três parâmetros diferentes:

[[local]:5432] postgres@postgres=# SELECT name, context FROM pg_settings WHERE name IN ('application_name', 'log_line_prefix', 'port');
╔══════════════════╤════════════╗
║       name       │  context   ║
╠══════════════════╪════════════╣
║ application_name │ user       ║
║ log_line_prefix  │ sighup     ║
║ port             │ postmaster ║
╚══════════════════╧════════════╝

Os contextos mais comuns são:

1 Contexto user (de usuário)

Parâmetros de contexto de usuário (user) podem ter valores diferentes para cada sessão estabelecida. Controlam a comunicação com o cliente (aplicação), otimização de consultas e consumo de recursos por backend.

Esse valor pode ser definido em vários níveis, sendo que a configuração usada é aquela do nível mais alto da lista a seguir:

SET
valor definido para a sessão, independente de outras sessões e resetado em toda nova conexão;
ALTER USER SET
valor definido para toda conexão estabelecida através daquele usuário (pode ser consultada com select useconfig from pg_user where usename = '...');
ALTER DATABASE SET
valor definido para toda conexão estabelecida para aquele banco de dados (pode ser consultada com \drds);
ALTER SYSTEM SET
valor definido no arquivo de configuração postgresql.auto.conf (precisa de um reload para aplicar a alteração); e,
postgresql.conf
valor definido no arquivo de configuração principal (também precisa de um reload para aplicar a alteração).

Por exemplo: application_name, work_mem, client_encoding, client_min_messages, search_path, TimeZone

2 Contexto sighup (reload, ou de recarga)

Parâmetros de contexto de recarga (reload, sighup) afetam todos os backends do PostgreSQL e podem ser alterados por ALTER SYSTEM ou editando o postgresql.conf (ambos seguidos de uma ação de reload).

Controlam aspectos de sistema que podem ser alterados a quente, como logs, backends de manutenção, background workers, arquivamento, replicação e autenticação.

Obs.: O nome do contexto vem do sinal hang up (sighup), muito usado para fazer diversos serviços recarregarem seus arquivos de configuração em ambientes derivados de UNIX, como o Linux. Mais informações na página de administração dos serviços do PostgreSQL

Por exemplo: log_line_prefix, max_wal_size, ssl, archive_command, bgwriter_delay, checkpoint_timeout

3 Contexto postmaster (de reinício)

Parâmetros de contexto de reinício (restart, postmaster) também afetam todos os backends do PostgreSQL e podem ser alterados por ALTER SYSTEM ou editando o postgresql.conf (ambos seguidos de uma ação de restart).

Contudo, eles só são efetivados após um reinício do serviço, já que eles controlam aspectos vitais do PostgreSQL, como alocação de memória compartilhada, de porta do serviço, de origem de replicação, de arquivos de configuração, entre outros.

Por exemplo: shared_buffers, port, primary_conninfo, wal_level, max_connections, listen_addresses

Alterando as configurações

Lembrando que as configurações são armazenadas nos dois arquivos postgresql.conf e postgresql.auto.conf, alterar uma configuração é o mesmo que alterar os arquivos.

Contudo, o primeiro arquivo, postgresql.conf, é destinado a edição por humanos com editores de texto e ferramentas de automação, enquanto que o segundo arquivo, postgresql.auto.conf é destinado a edição apenas direta do PostgreSQL através do comando ALTER SYSTEM.

Exemplo de um arquivo postgresql.auto.conf inicial, com o aviso avisando que ele não deve ser editado manualmente:

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

Quando usamos o comando ALTER SYSTEM, apenas o arquivo postgresql.auto.conf é alterado, sem aplicar o novo valor. Então um comando:

[[local]:5432] postgres@postgres=# ALTER SYSTEM SET work_mem TO '6MB';

Fará com que o postgresql.auto.conf tenha agora o seguinte conteúdo:

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
work_mem = '6MB'

Contudo, o PostgreSQL ainda não aplicou essa configuração no serviço, o que exigirá um próximo passo. Essa configuração em dois passos é desejável porque assim podemos aplicar mais de uma mudança nas configurações de forma atômica, o que é importante ao alterar conjuntos de parâmetros de ssl, de memória, de background workers, entre outros.

Para aplicar uma alteração de ao menos um parâmetro com contexto postmaster, a única opção é reiniciar (restart) o serviço. Isso também aplicará todas as outras alterações, mas causará uma pequena indisponibilidade do serviço.

Para aplicar uma alteração de ao menos um parâmetro com contexto sighup, será necessário recarregar as configurações do serviço, o que pode ser feito, sem indisponibilidade, recarregando (reload) o serviço ou chamando a função pg_reload_conf() como superusuário:

[[local]:5432] postgres@postgres=# SELECT pg_reload_conf();
╔════════════════╗
║ pg_reload_conf ║
╠════════════════╣
║ t              ║
╚════════════════╝

Se tudo correu bem, a visão pg_settings irá dizer que não há nenhuma configuração com pendência de reinício:

[[local]:5432] postgres@postgres=# SELECT count(*) FROM pg_settings WHERE pending_restart;
╔═══════╗
║ count ║
╠═══════╣
║     0 ║
╚═══════╝

E o SHOW irá mostrar o novo valor do parâmetro:

[[local]:5432] postgres@postgres=# SHOW work_mem;
╔══════════╗
║ work_mem ║
╠══════════╣
║ 6MB      ║
╚══════════╝

A visão pg_settings

Todas as configurações do PostgreSQL estão sempre visíveis através da visão pg_settings, incluindo configurações de funcionalidades desabilitadas em tempo de compilação ou disponíveis apenas em outras plataformas. Portanto, o PostgreSQL não tem configurações escondidas.

[[local]:5432] postgres@postgres=# SET work_mem TO '8MB';
[[local]:5432] postgres@postgres=# SELECT * FROM pg_settings WHERE name = 'work_mem';
╔═[ RECORD 1 ]════╤═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╗
║ name            │ work_mem                                                                                                              ║
║ setting         │ 8192                                                                                                                  ║
║ unit            │ kB                                                                                                                    ║
║ category        │ Resource Usage / Memory                                                                                               ║
║ short_desc      │ Sets the maximum memory to be used for query workspaces.                                                              ║
║ extra_desc      │ This much memory can be used by each internal sort operation and hash table before switching to temporary disk files. ║
║ context         │ user                                                                                                                  ║
║ vartype         │ integer                                                                                                               ║
║ source          │ configuration file                                                                                                    ║
║ min_val         │ 64                                                                                                                    ║
║ max_val         │ 2147483647                                                                                                            ║
║ enumvals        │                                                                                                                       ║
║ boot_val        │ 4096                                                                                                                  ║
║ reset_val       │ 6144                                                                                                                  ║
║ sourcefile      │ /var/lib/postgresql/14/postgresql.auto.conf                                                                           ║
║ sourceline      │ 3                                                                                                                     ║
║ pending_restart │ f                                                                                                                     ║
╚═════════════════╧═══════════════════════════════════════════════════════════════════════════════════════════════════════════════════════╝

Dentre as várias colunas dessa visão, algumas se destacam pela sua utilidade. As colunas name, setting e unit contêm o conteúdo que vemos através do comando SHOW. As colunas category, short_desc e extra_desc trazem informações que nos ajudam a entender o propósito do parâmetro. As colunas source, sourcefile e sourceline dizem de onde veio o valor atual do parâmetro. As colunas vartype, min_val, max_val e enum_vals, assim como unit, nos dizem quais valores são válidos para aquele parâmetro. As colunas boot_val e restart_val nos dizem o valor padrão do PostgreSQL e o valor caso o parâmetro seja resetado nesta sessão, respectivamente. A coluna context nos informa o contexto do parâmetro; e a coluna pending_restart informa se é necessário reiniciar o PostgreSQL para aplicar a alteração, mas apenas para contextos postmaster.

Prática

Com base no que foi visto até agora, altere alguns parâmetros e observe os seus valores antes e depois de cada passo.

Use estes para testes:

E lembre de conferir os valores com (substituindo os … pelo nome de cada parâmetro):