O PostgreSQL tem um monte de botões e chavinhas para você ajustar uma série de parâmetros. A maioria destes parâmetros podem ser configurados a partir de 2 arquivos:
- postgresql.conf: aqui são ajustadas as configurações do PostgreSQL que serão utilizadas em todo o cluster (entenda aqui clister como o conjunto de bases associadas à uma única instância ou conjunto de processos do PostgreSQL).
- pg_hba.conf: aqui são ajustadas configurações de permissão de acesso e autenticação que serão utilizadas para todo o cluster, por base, por usuário ou role.
A versão 9.4 do PostgreSQL adicionou uma nova cláusula SQL chamada ALTER SYSTEM. Com este comando, você pode editar diretamente as configurações do arquivo posqtgresql.conf.
Conhecendo a view pg_setting
Para entender melhor como funcionam as configurações do PostgreSQL (também conhecidas como GUCs ou Grand Unified Configuration) , é fundamental conhecer a view pg_settings (que é baseada na função pg_show_all_setting):
postgres=# d pg_settings View "pg_catalog.pg_settings" Column | Type | Modifiers ------------+---------+----------- name | text | setting | text | unit | text | category | text | short_desc | text | extra_desc | text | context | text | vartype | text | source | text | min_val | text | max_val | text | enumvals | text[] | boot_val | text | reset_val | text | sourcefile | text | sourceline | integer |
Vamos analisar cada campo detalhadamente, pois eles envolvem vários conceitos importantes no meio:
- name: nome da variável ou GUC, como os desenvolvedores do postgres gostam de chamar;
- setting: o valor atual no contexto atual para aquela variável. Note que o contexto pode variar de acordo com varias coisas, como a forma como o PostgreSQL foi compilado, parâmetros passados para o postmastar ao subir o serviço, configurações do postgresql.conf, base em que você está conectada, usuário conectado e finalmente, alterações realizadas na sessão atual;
- unit: Algumas variáveis podem ter valores que remetem a unidades específicas como: ms (milisegundos), s (segundos), min (minutos), kB (kiloBytes) e 8kB. Preste a atenção nas unidades, particularmente quando o valor estiver em unidades de 8kB, pois o valor em setting, pode lhe confundir. Um valor de 256 com uma unidade de 8kB significa na verdade 256 x 8kB = 2mB;
- category: As variáveis estão agrupadas em categorias, vide a documentação. Fazer uma consulta apenas nas variáveis que estão na categoria que você procura pode ajudar a filtrar seus resultados ou ordenações.
- short_desc: Descrição sucinta sobre a variável;
- extra_desc: Descrição e detalhes adicionais sobre a variável. Não chega a substituir a documentação, mas pode lhe ajudar em alguns momentos;
- context: Diz em qual tipo de situação a variável pode ser alterada. Os níveis mais baixos sempre podem ser alterados nos níveis mais baixos, mas não o contrário:
- internal – não podem ser alteradas de jeito nenhum. Algumas podem ser alteradas durante a compilação do PostgreSQL. Ou seja, não adianta tentar sair alterando. Estão lá mais por referência.
- postmaster – só podem ser alteradas após reiniciar o postgres. Você pode alterar parâmetros no postgresql.conf (ou usar o ALTER SYSTEM), mas as novas configurações só entrarão em vigor após reiniciar o postgres. Você também pode ajustar algumas variáveis passando parâmetros para o postmaster. Não recomendo fazer isso.
- sighup – podem ser alterando com um reload (sinal SIGHUP ou usar a função pg_reload_conf).
- backend – também podem serem alterados quando a conexão é estabelecida utilizando as variáveis de ambiente PGOPTIONS. No entanto uma vez iniciada a sessão, o valor configurado não pode ser mais alterado durante toda a vida desta sessão;
- superuser – podem ser alterados com os comandos SET, ALTER ROLE e ALTER DATABASE, mas apenas super usuários podem executar estas alterações. Na prática isto significa que sessões de usuários que não são superusuários não podem ser alteradas no meio de uma sessão.
- user – também podem ser alterados com SET, ALTER ROLE e ALTER DATABASE, mas não precisa ser um superusuário para fazê-lo.
- vartype: sim senhores, os valores em no campo setting vem todos no formato texto… então você precisa saber que tipo de valor é permitido neste campo! As variáveis podem ser dos tipos bool, enum, string, integer ou real.
- source: Esta opção lhe diz de onde vem o valor configurado para aquela variável. Uma vez que dependendo do contexto, a variável pode ter sido ajustada em diversos contextos, ou apenas estar assumindo o valor padrão: o valor default no campo source significa que em nenhum momento esta variável foi alterada. Os possíveis valores para o campo source são: default,override, configuration file, environment variable, client, database ou user.
- min_val: valor mínimo permitido caso o tipo do valor seja numérico
- max_val: valor máximo permitido caso o tipo do valor seja numérico
- enumvals: valores permitidos caso o tipo do valor seja enum
- boot_val: valor assumido pela variável ao iniciar o postgres se ela não for configurada em nenhum local
- reset_val: valor assumido pela variável se um RESET for solicitado no meio de uma sessão
- sourcefile: nome do arquivo de configuração onde a variável foi ajustada. Só faz sentido utilizar este campo se você utilizar includes dentro do postgresql.conf
- sourceline: linha do arquivo que alterou aquela variável.
Como você vê, muito da vida das suas GUCs pode ser bem investigada com um bom SELECT na pg_settings. Existem outras formas mais simples de visualizar o valor atual de uma variável, como o comando SHOW ou a função current_setting:
postgres=# SHOW shared_buffers ;
shared_buffers
----------------
512MB
(1 row)
postgres=# SELECT current_setting('shared_buffers');
current_setting
-----------------
512MB
(1 row)
postgres=# SELECT setting, unit FROM pg_settings WHERE name = 'shared_buffers';
setting | unit
---------+------
65536 | 8kB
Note aqui que utilizando o comando SHOW ou a função current_setting, ocorre uma conversão do valor numa unidade mais fácil de se entender.
Alterando valores
Existem várias formas de se alterar um parâmetro, mas isto depende do contexto permitido. Veja o campo context da pg_settings.
context = ‘internal’
Uma variável cuja o contexto é internal, não pode ser alterado em nenhum momento, depois que o binário foi compilado. Veja:
postgres=# SELECT name FROM pg_settings WHERE context = 'internal'; name ----------------------- block_size data_checksums integer_datetimes lc_collate lc_ctype max_function_args max_identifier_length max_index_keys segment_size server_encoding server_version server_version_num wal_block_size wal_segment_size (14 rows) postgres=# ALTER SYSTEM SET block_size = 4096; ERROR: parameter "block_size" cannot be changed
context = ‘postmaster’
Agora, se tentarmos alterar um parâmetro cuja o contexto é postmaster, o valor só será alterado após reiniciar o postgres:
postgres=# ALTER SYSTEM SET shared_buffers = '64MB'; ALTER SYSTEM postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# SHOW shared_buffers ; shared_buffers ---------------- 128MB (1 row) postgres=# q postgres@debian:~$ /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/9.4 stop -m fast waiting for server to shut down.... done server stopped postgres@debian:~$ /usr/local/pgsql/bin/pg_ctl -D /var/lib/pgsql/9.4 -l logfile start server starting postgres@debian:~$ /usr/local/pgsql/bin/psql psql (9.4.0) Type "help" for help. postgres=# SHOW shared_buffers ; shared_buffers ---------------- 64MB (1 row)
Note que alteramos o parâmetro shared_buffers, com sucesso, mas ao rodar a função pg_reload_conf, o valor não foi alterado. Apenas após reiniciar o banco a alteração surtiu efeito. Por outro lado, se tentarmos alterar a mesma variável no meio de uma sessão com o comando SET teremos um erro:
postgres=# SET shared_buffers = '256MB'; ERROR: parameter "shared_buffers" cannot be changed without restarting the server
context = ‘sighup’
Agora vejamos o que acontece quando tentamos alterar uma variável de contexto SIGHUP:
postgres=# SHOW fsync; fsync ------- on (1 row) postgres=# ALTER SYSTEM SET fsync = off; ALTER SYSTEM postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# SHOW fsync; fsync ------- off (1 row) postgres=# SET fsync = on; ERROR: parameter "fsync" cannot be changed now
Como você vê, não foi preciso reiniciar o postgres, apenas dar um reload nele. E assim como antes, o comando SET não pode ser executado neste contexto de variável.
context = ‘backend’
postgres=# SHOW log_connections ; log_connections ----------------- off (1 row) postgres=# ALTER SYSTEM SET log_connections = on; ALTER SYSTEM postgres=# SHOW log_connections ; log_connections ----------------- off (1 row) postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# SHOW log_connections ; log_connections ----------------- off (1 row) postgres=# q postgres@debian:~$ /usr/local/pgsql/bin/psql psql (9.4.0) Type "help" for help. postgres=# SHOW log_connections ; log_connections ----------------- on (1 row)
Note aqui que apenas após enviar o sinal de SIGHUP e reiniciar a conexão que o valor aparece. Existe ainda a opção de ajustar a variável de ambiente PGOPTIONS antes de estabelecer a conexão:
postgres@debian:~$ export PGOPTIONS="-c log_connections=off" postgres@debian:~$ /usr/local/pgsql/bin/psql psql (9.4.0) Type "help" for help. postgres=# SHOW log_connections ; log_connections ----------------- off (1 row)
A alteração da variável log_connections só é válida para esta sessão específica. Diferentes linguagens de programação que utilizam a bibilioteca libpg para se conectar no postgres tem formas semelhantes de ajustar opções ajustando o PGOPTIONS ao estabelecer a conexão.
context = ‘superuser’
Agora vamos finalmente poder utilizar o comando SET, ALTER ROLE e também o ALTER DATABASE:
postgres=# SHOW log_min_messages; log_min_messages ------------------ warning (1 row) postgres=# ALTER SYSTEM SET log_min_messages = 'INFO'; ALTER SYSTEM postgres=# SHOW log_min_messages; log_min_messages ------------------ warning (1 row) postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# SHOW log_min_messages; log_min_messages ------------------ info (1 row) postgres=# SET log_min_messages = 'ERROR'; SET postgres=# SHOW log_min_messages; log_min_messages ------------------ error (1 row) postgres=# q postgres@debian:~$ /usr/local/pgsql/bin/psql psql (9.4.0) Type "help" for help. postgres=# SHOW log_min_messages; log_min_messages ------------------ info (1 row)
Aqui, quando utilizamos o ALTER SYSTEM, precisamos rodar o pg_reload_conf para a alteração surtir efeito. Mas a alteração vale para todas as conexões. Quando rodamos o SET, a alteração entra em vigor imediatamente, mas só vale para aquela sessão.
Agora vamos tentar alterar o parâmetro para uma base específica:
postgres=# SELECT current_database(); current_database ------------------ postgres (1 row) postgres=# SHOW log_min_messages; log_min_messages ------------------ info (1 row) postgres=# CREATE DATABASE teste; CREATE DATABASE postgres=# ALTER DATABASE teste SET log_min_messages = 'NOTICE'; ALTER DATABASE postgres=# c teste You are now connected to database "teste" as user "postgres". teste=# SHOW log_min_messages ; log_min_messages ------------------ notice (1 row)
Aqui, criamos uma nova base e configuramos um valor diferente para o parâmetro log_mim_messages apenas para aquela base. Podemos fazer a mesma coisa com um usuário qualquer:
postgres=# CREATE ROLE abizi LOGIN; CREATE ROLE postgres=# ALTER ROLE abizi SET log_min_messages = 'DEBUG1'; ALTER ROLE postgres=# q postgres@debian:~$ /usr/local/pgsql/bin/psql -U abizi postgres psql (9.4.0) Type "help" for help. postgres=> SHOW log_min_messages ; log_min_messages ------------------ debug1 (1 row)
Agora note que o usuário novo não é um superusuário, portanto ele não pode alterar o parâmetro:
postgres@debian:~$ /usr/local/pgsql/bin/psql -U abizi postgres psql (9.4.0) Type "help" for help. postgres=> SHOW log_min_messages ; log_min_messages ------------------ debug1 (1 row) postgres=> SET log_min_messages = 'PANIC'; ERROR: permission denied to set parameter "log_min_messages"
context = ‘user’
A única diferença aqui é que até mesmo um usuário comum pode alterar parâmetros com este contexto no meio da sua sessão.
postgres@debian:~$ /usr/local/pgsql/bin/psql -U abizi postgres
psql (9.4.0)
Type "help" for help.
postgres=> SET search_path = '$user, public, information_schema';
SET
postgres=> SHOW search_path ;
search_path
-------------------------------------
"$user, public, information_schema"
(1 row)
contexto: dentro de uma transação
Por padrão, sempre que você altera uma configuração usando o comando SET, esta configuração dura até o final da sessão ou até que outro comando SET seja executado alterando o mesmo parâmetro. No entanto, existe a opção LOCAL que faz com que esta configuração dure apenas a transação corrente. Ou seja: após um COMMIT ou ROLLBACK, o parâmetro retorna para o valor anterior, veja:
postgres=# SHOW work_mem; work_mem ---------- 4MB (1 registro) postgres=# BEGIN; BEGIN postgres=# INSERT INTO teste VALUES(1); INSERT 0 1 postgres=# SHOW work_mem; work_mem ---------- 4MB (1 registro) postgres=# SET LOCAL work_mem = '16MB'; SET postgres=# SHOW work_mem; work_mem ---------- 16MB (1 registro) postgres=# INSERT INTO teste VALUES(2); INSERT 0 1 postgres=# INSERT INTO teste VALUES(3); INSERT 0 1 postgres=# SHOW work_mem; work_mem ---------- 16MB (1 registro) postgres=# COMMIT; COMMIT postgres=# SHOW work_mem; work_mem ---------- 4MB (1 registro)
Outra opção equivalente ao comando SET é a função set_config, que possui uma opção chamada is_local que determina também se a alteração deverá durar para toda a sessão ou apenas para aquela transação. O único cuidado é que o valor a ser configurado deve sempre vir entre aspas simples, pois a função recebe o valor utilizando o tipo de dado text.
SELECT set_config('log_statement_stats', 'off', false);
set_config
------------
off
(1 row)
GUCs rocks!
A flexibilidade como podemos ajustar diversos parâmetros tornam uma série de atividades mais simples. Agora com o comando ALTER SYSTEM, você pode trabalhar com todos os parâmetros de contexto SIGHUP para baixo só utilizando SQL. A gama de situações em que podemos ajustar em situações específicas é enorme. Um exemplo seria ajustes no work_mem para conexões que precisem gerar relatórios mais complexos. Ou alterações nos logs para gerar debug em situações específicas. E por aí vai. Se você aprender a trabalhar corretamente com as GUCs, vai descobrir que a vida pode ser mais simples e dinâmica.
Boa diversão!
Muito bom post parabéns
SHOW ‘Parabéns, excelente post’