Normalmente, quando se discute ter conexões “somente leitura” com um banco de dados PostgreSQL , é no contexto de conectar-se a uma réplica .

Há uma variedade de métodos disponíveis para rotear conexões com consultas somente leitura conhecidas (ou seja, consultas com SELECTinstruções... que não estão chamando VOLATILEfunções que modificam dados). Isso inclui software de proxy de conexão como o Pgpool-II ou mecanismos de estrutura como o roteador de banco de dados do Django .

No entanto, há situações em que você pode precisar forçar conexões somente leitura para sua instância primária (leitura-gravação) do Postgres. Alguns exemplos incluem colocar seu aplicativo em um estado degradado para executar uma movimentação ou atualização do banco de dados ou permitir que um administrador inspecione um sistema que pode estar acumulando logs de gravação antecipada que rastreiam todas as alterações no sistema.

O PostgreSQL tem uma chamada de parâmetro de configuração default_transaction_read_only. A configuração default_transaction_read_onlyglobal para onforça todas as conexões a não permitir gravações no banco de dados. default_transaction_read_onlyé um parâmetro recarregável, portanto, você não precisa reiniciar sua instância do Postgres para usá-lo.

Aqui está um exemplo rápido de como default_transaction_read_onlyfunciona. Primeiro, certifique-se de que seu sistema não tenha default_transaction_read_onlydefinido:

postgres=# SHOW default_transaction_read_only ;   default_transaction_read_only -------------------------------  off (1 row)  postgres=# CREATE TABLE IF NOT EXISTS abc (id int); INSERT INTO abc VALUES (1) RETURNING id; CREATE TABLE   id ----  1 (1 row)  INSERT 0 1 

Isso funciona como esperado: podemos criar uma tabela e inserir dados. Agora vamos colocar o sistema em default_transaction_read_onlymodo (observe que estou executando isso no PostgreSQL 14 )

ALTER SYSTEM SET default_transaction_read_only TO on; SELECT pg_reload_conf(); SHOW default_transaction_read_only; 

Certifique-se de que default_transaction_read_onlyestá ativado:

postgres=# SHOW default_transaction_read_only;   default_transaction_read_only -------------------------------  on (1 row) 

Agora verifique se as gravações não são permitidas:

postgres=# INSERT INTO abc VALUES (2) RETURNING id; ERROR: cannot execute INSERT in a read-only transaction 

Excelente!

Observe que default_transaction_read_onlynão é uma panacéia: existem algumas advertências que você deve estar ciente.

Primeiro, default_transaction_read_onlypode ser substituído em uma sessão, mesmo que o valor seja definido em todo o banco de dados. Por exemplo:

postgres=# SHOW default_transaction_read_only ;  default_transaction_read_only -------------------------------  on (1 row)  postgres=# SET default_transaction_read_only TO off; SET  postgres=# INSERT INTO abc VALUES (2) RETURNING id;   id ----  2 (1 row)  INSERT 0 1 

Em segundo lugar, ao utilizar default_transaction_read_onlycom um aplicativo, você também deve garantir que seu aplicativo possa ser configurado para enviar apenas consultas de leitura ao banco de dados, garantindo uma experiência de usuário tranquila.

Dito isso, se você tiver uma situação em que precisa colocar uma instância primária do PostgreSQL em um modo “somente leitura” temporariamente, você pode usar default_transaction_read_onlypara evitar transações de gravação.