postgres postgresql psql
O psql
é o cliente de linha de comando mais usado para administração do
PostgreSQL. Com ele conseguimos testar a conectividade com o banco, criar e
destruir objetos e usuários, consultar o estado do sistema e várias outras
atividades.
O cenário mais simples é quando o PostgreSQL está no ar,
instalado pelo PGDG, e estamos no usuário
postgres
da mesma máquina do serviço. Nesse momento, uma execução do psql
,
sem mais argumentos, irá colocar o usuário em uma interface direta com o banco
de dados padrão na qual podemos executar comandos SQL:
[postgres@pg-1 ~]$ psql psql (16.3, server 15.7) Type "help" for help. postgres=# SELECT version(); version -------------------------------------------------------------------------------------------------------- PostgreSQL 15.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.3.1 20190507 (Red Hat 8.3.1-4), 64-bit (1 row) postgres=# show server_version; server_version ---------------- 15.7 (1 row) postgres=#
As primeiras duas linhas são uma mensagem de boas vindas que identificam a
versão do cliente (o próprio psql, versão 16.3) e do servidor (versão 15.7).
Quando o cliente e servidor estão na mesma versão, apenas um número é mostrado.
A versão do psql pode ser descoberta com um psql --version
e a versão do
servidor pode ser investigada com o SELECT ou com o SHOW mostrados acima. A
seguir ele diz que o comando 'help' traz mais informações.
Também vemos que o prompt é postgres=#
, o que indica o nome do banco de dados
no qual estamos conectados (postgres
), o estado do buffer de entrada (=
indica o início de um comando) e o tipo de usuário (#
indica superusuário; >
indica usuário comum).
Antes de seguirmos adiante, vamos incrementar o prompt com mais informações, especialmente aquelas que nos ajudem a interagir com o PostgreSQL.
O arquivo .psqlrc
, que reside na raiz de cada usuário, é lido pelo psql quando
o cliente inicia. É nele que guardamos as customizações. Coloque as seguintes
linhas no arquivo .psqlrc
(criando o arquivo se não existir):
\set QUIET 1 \set PROMPT1 '[%M:%>] %n@%/%R%#%x ' \set PROMPT2 '[%M:%>] %n@%/%R%#%x '
E abra novamente o psql:
[[local]:5432] postgres@postgres=# BEGIN; [[local]:5432] postgres@postgres=#* SHOW server_version [[local]:5432] postgres@postgres-#* ; server_version ---------------- 15.7 (1 row) [[local]:5432] postgres@postgres=#* ROLLBACK; [[local]:5432] postgres@postgres=#
A primeira linha do arquivo (\set QUIET 1
) fez com que a mensagem de boas
vindas não fosse apresentada. As outras duas linhas mudam o formato do prompt
(PROMPT1
é usado para novos comandos enquanto que PROMPT2
é usado para
comandos em andamento). Cada %
indica uma informação diferente que é
apresentada no prompt. Uma lista completa está na
documentação oficial,
mas a seguir temos a lista dos que colocamos no prompt:
-
%M
: hostname completo da máquina (ou[local]
para conexões por socket UNIX) -
%>
: porta do serviço do PostgreSQL -
%n
: nome do usuário que está sendo usado -
%/
: nome do banco de dados no qual estamos conectados -
%R
: caractere que indica o estado do prompt de comando:=
para comandos novos,-
para continuação de comandos,(
quando dentro de parênteses… -
%#
: caractere que indica se o usuário é superusuário (#
) ou se é usuário comum (>
) -
%x
: caractere que indica que estamos em uma transação aberta explicitamente (*
), mas vazio caso contrário
A mesma saída de antes, mas agora comentada (comentários começam com --
e
terminam no final da linha):
[[local]:5432] postgres@postgres=# -- PROMPT1 indicando que estamos fora de uma transação e aguardando um comando novo (=) [[local]:5432] postgres@postgres=# BEGIN; [[local]:5432] postgres@postgres=#* -- PROMPT1 indicando que estamos dentro de uma transação (*) e aguardando um comando novo (=) [[local]:5432] postgres@postgres=#* SHOW server_version [[local]:5432] postgres@postgres-#* -- PROMPT2 indicando que estamos dentro de uma transação (*) e aguardando a continuidade do comando anterior (-) [[local]:5432] postgres@postgres-#* ; -- um ponto-e-vírgula finaliza o comando, e apenas neste momento ele é enviado ao servidor para execução server_version ---------------- 15.7 (1 row) [[local]:5432] postgres@postgres=#* ROLLBACK; [[local]:5432] postgres@postgres=#
Muitas outras customizações podem ser feitas no prompt, inclusive adicionando cores, negrito, itálico, saídas de comandos externos e assim por diante. E algumas outras podem ser feitas para interação com saídas de comandos, macros e afins.
Algumas a mais para explorar:
-
\x auto
: modo expandido de apresentação de tabela -
\setenv PAGER less
: use o less como paginador de saída -
\pset border 2
: bordas em torno nas tuplas -
\pset linestyle unicode
: linhas com caracteres unicode -
\pset unicode_border_linestyle double
: estilo duplo em bordas externas -
\pset unicode_column_linestyle single
: estilo simples em linhas de colunas -
\pset unicode_header_linestyle double
: estilo duplo em linhas de cabeçalho -
\pset footer off
: desabilita rodapé com contagem de linhas retornadas -
\set ON_ERROR_ROLLBACK 1
: usa um SAVEPOINT em torno de cada comando (útil em modo interativo para evitar que comandos com erros de digitaçào quebrem as transações em andamento)
Como via de regra, comandos que começam com uma barra invertida (\
) são
chamados de metacomandos do psql e não são enviados ao servidor, mas sim
tratados de alguma forma pelo cliente (que pode enviar outros comandos ao
servidor no seu lugar). E comandos que não começam com a barra invertida são
comandos SQL enviados diretamente ao servidor. As únicas exceções são os
metacomandos help
, exit
e quit
, que são comandos de cliente apesar de não
iniciarem com um \
.
Metacomandos básicos:
-
help
: mostra alguns metacomandos de ajuda -
\?
,\? options
,\? variables
: mostra a lista completa de metacomandos, opções e variáveis -
\h
: mostra ajuda sobre comandos SQL, por exemplo\h CREATE TABLE
-
\q
,exit
,quit
: sai do psql -
\echo
: mostra uma mensagem na saída padrão -
\c
: reconecta ao mesmo banco de dados, na mesma instância e com o mesmo usuário -
\c banco usuario host porta
: conecta ao banco de dadosbanco
com o usuáriousuario
na máquinahost
, portaporta
, mas parâmetros que forem omitidos serão preenchidos com os valores da conexão atual - ⋮
Metacomandos de edição e execução:
-
\e
: abre o editor de textos padrão para editar o comando atual ou o último executado -
\e arq.sql
: abre o editor de textos padrão para editar o arquivoarq.sql
-
\ef func()
: abre o editor de textos padrão para editar a funçãofunc()
-
\ev visao
: abre o editor de textos padrão para editar a visãovisao
-
\sf
,\sv
: como\ef
e\ev
, mas apenas mostra o corpo da função ou visão -
\g
: termina a edição do comando atual e envia ele para execução, assim como um;
(ponto-e-vírgula) faria, por exemplo:SELECT 1 \g
-
\g arq.txt
: termina a edição do comando atual e envia ele para execução, assim como um;
(ponto-e-vírgula) faria, armazenando a resposta no arquivoarq.txt
-
\g | cmd
: termina a edição do comando atual e envia ele para execução, assim como um;
(ponto-e-vírgula) faria, enviando o resultado para o comando externo:select 1 \g | wc -l
-
\gexec
: executa o comando atual e então executa o valor de retorno dele, útil para comandos dinâmicos; exemplo de comando dinâmico que executa um VACUUM ANALYZE em todas as tabelas da aplicação:[[local]:5432] postgres@postgres=# SELECT format('VACUUM ANALYZE %I.%I;', table_schema, table_name) [[local]:5432] postgres@postgres-# FROM information_schema.tables [[local]:5432] postgres@postgres-# WHERE table_schema IN ('app', 'public') AND table_type = 'BASE TABLE' [[local]:5432] postgres@postgres-# \gexec [[local]:5432] postgres@postgres=#
-
\gset
: executa o comando armazenando os resultados em variáveis do psql para uso posterior, com um prefixo opcional, por exemplo:[[local]:5432] postgres@postgres=# SELECT now() AS agora, COUNT(*) AS contagem FROM information_schema.tables \gset c_ [[local]:5432] postgres@postgres=# \echo temos :c_contagem tabelas no momento :c_agora temos 194 tabelas no momento 2024-09-24 17:53:45.856442+00 [[local]:5432] postgres@postgres=#
-
\watch
: executa o comando periodicamente, por exemplo consultando o horário do servidor uma vez por segundo:SELECT now() \watch 1
-
\set
: lista as variáveis do psql -
\set VAR valor
: atribui umvalor
à variávelVAR
-
\unset VAR
: remove a variávelVAR
- ⋮
Metacomandos de informação:
Esses são metacomandos que trazem informações sobre objetos do seu banco de
dados. Eles normalmente estão limitados aos schemas do seu search_path
atual
e omitem objetos dos schemas de sistema (information_schema
e pg_catalog
),
que podem ser incluídos com a adição de um S
no comando. Além disso,
informações mais detalhadas podem ser obtidas adicionando um +
no comando.
Por fim, eles podem receber um padrão para comparar com os nomes, o que ajuda a
filtrar os objetos de interesse.
-
\l
: lista de bancos de dados da instância -
\d
: lista tabelas, visões, índices e outros objetos básicos -
\dt
: lista tabelas de usuário -
\dtS
: lista de tabelas de usuário e de sistema -
\dtS+
: lista de tabelas de usuário e de sistema, com mais colunas de detalhes -
\dtS+ pg_stat_*
: lista de tabelas de usuário e de sistema, com mais colunas de detalhes, mas apenas tabelas com nome iniciando porpg_stat_
-
\dv
: lista de visões de usuário -
\di
: lista de índices de usuário -
\du
: lista de usuários -
\dn
: lista de schemas (também conhecidos como namespaces) -
\db
: lista de tablespaces -
\dp
: lista de permissões -
\dT
: lista de tipos de dados - ⋮
O psql tem a capacidade de armazenar dados em variáveis em tempo de execução.
Essas variáveis podem ser populadas pelo .psqlrc
, pelo \set
e \unset
,
assim como pelo \gset
. Posteriormente, o conteúdo de qualquer variável pode
ser usado em outros momentos, tanto para construir consultas quanto para
apresentar para o usuário (\echo
, \qecho
) ou construir scripts mais
complexos (\i
, \ir
, \if
…).
Variáveis podem ser usadas com o prefixo :
para serem usadas diretamente, por exemplo:
[[local]:5432] postgres@postgres=# \echo :c_contagem 194
Se o conteúdo delas for usado como literais (valores numéricos, textuais e
outros), elas devem ser usadas entre aspas simples. E se for usado como
identificadores (nomes de tabelas, de colunas), então devem ser usadas entre
aspas duplas. O psql coloca as aspas corretamente quando usamos as formas
:'var'
e :"var"
:
[[local]:5432] postgres@postgres=# SELECT * [[local]:5432] postgres@postgres-# FROM :"nome_da_tabela" [[local]:5432] postgres@postgres-# WHERE id = :'valor';
Por fim, blocos completos de código podem ser guardados em variáveis e
executados posteriormente. Por exemplo, se uma consulta comum for armazenada em
uma variável no .psqlrc
:
\set consulta_uid 'SELECT nome FROM tabela WHERE id = :''uid'';'
Então o usuário pode consultar uids quaisquer com agilidade:
[[local]:5432] postgres@postgres=# \set uid 123 [[local]:5432] postgres@postgres=# :consulta_uid Alice [[local]:5432] postgres@postgres=# \set uid 456 [[local]:5432] postgres@postgres=# :consulta_uid Bob [[local]:5432] postgres@postgres=# \set uid 789 [[local]:5432] postgres@postgres=# :consulta_uid Charlie [[local]:5432] postgres@postgres=#
Exemplo de variável contendo um comando de redirecionamento do EXPLAIN para o site https://explain.depesz.com, que apresenta o plano de execução de forma mais clara.
A variável é guardada no .psqlrc
como:
\set gexplain '\\g | curl --silent --data-urlencode is_public=0 --data-urlencode "plan@-" -w "%{redirect_url}\n" https://explain.depesz.com/'
Depois de abrir o psql, qualquer consulta pode ser executada com EXPLAIN e direcionada para o site, recebendo a URL:
[[local]:5432] postgres@postgres=# EXPLAIN (ANALYZE, VERBOSE) [[local]:5432] postgres@postgres-# SELECT count(*) FROM pg_stat_activity [[local]:5432] postgres@postgres-# :gexplain https://explain.depesz.com/s/NOPR
O psql
, assim como o pg_dump
, pg_dumpall
, pg_basebackup
, pg_is_ready
e diversos outros programas, usa a biblioteca libpq
para conectar ao
PostgreSQL. Por isso, todos eles compartilham as mesmas
formas de conexão ao
banco de dados.
A primeira segue o formato padrão de utilitários de linha de comando em sistemas UNIX:
[postgres@pg-1 ~]$ psql -h 127.0.0.1 -p 5432 -U postgres -d postgres
Dessa forma, cada parâmetro é informado após uma flag específica, como -h
para host, -p
para porta, -U
para usuário e -d
para o nome do banco de
dados.
A segunda segue o formato chave-valor conhecido como connection info
, ou
conninfo
,
usado também em configurações de replicação do PostgreSQL:
[postgres@pg-1 ~]$ psql 'host=127.0.0.1 port=5432 user=postgres dbname=postgres'
Nele, os parâmetros são fornecidos como pares de chave-valor separados por
espaços em um único argumento para o psql
, que deve ser contido entre aspas
no shell.
A terceira opção é pelo formato URI, muito usado com JDBC:
[postgres@pg-1 ~]$ psql postgresql://postgres@127.0.0.1:5432/postgres
Assim, os parâmetros são colocados em um argumento único que contém toda a informação necessária. Em alguns casos pode ser necessário também conter ele entre aspas, dependendo dos parâmetros.
Por fim, também é possível fornecer variáveis de ambiente ao psql, evitando o uso de parâmetros na linha de comando:
[postgres@pg-1 ~]$ export PGHOST=127.0.0.1 [postgres@pg-1 ~]$ export PGPORT=5432 [postgres@pg-1 ~]$ export PGUSER=postgres [postgres@pg-1 ~]$ export PGDATABASE=postgres [postgres@pg-1 ~]$ psql
Também podemos guardar todas as informações de conexão em um arquivo simples na
raiz do diretório do usuário, chamado .pg_service.conf
e então acessar o
banco de dados através de um apelido simples. Considerando um arquivo
.pg_service.conf
com o seguinte conteúdo:
[meubd] host=127.0.0.1 port=5432 dbname=postgres user=postgres
Então podemos acessá-lo com um comando simplificado, fornecendo apenas o nome do serviço:
[postgres@pg-1 ~]$ psql service=meubd
IMPORTANTE: Note que não fornecemos a senha da conexão em nenhuma das opções
acima. Isso porque qualquer segredo, como uma senha, colocado na linha de
comando é considerado prática insegura, já que outros usuários e processos da
mesma máquina conseguem ler a linha de comando dos outros processos (por
exemplo por /proc/<pid>/cmdline
). Em muitos sistemas operacionais também é
possível ler o conteúdo de variáveis de ambiente de outros processos com a
mesma facilidade (por exemplo por /proc/<pid>/environ
). Portanto, apesar de
ser possível fornecer a senha dessas formas, não é recomendado.
A forma recomendada é armazenando a senha em um local que apenas o próprio
usuário tenha acesso de leitura e, portanto, que apenas processos daquele
usuário façam a leitura. A libpq
novamente traz uma solução através do
arquivo .pgpass
, também armazenado na raiz do diretório do usuário (mas
outros arquivos podem ser fornecidos sob demanda) e com permissões mais
estritas (0600). Considerando que nos casos anteriores o meu usuário tinha a
senha supersecreta
, o arquivo .pgpass
poderia ter o seguinte conteúdo:
# host:port:dbname:user:password 127.0.0.1:5432:postgres:postgres:supersecreta
A primeira linha é apenas um comentário (pois inicia com uma #
) apontando o
propósito de cada campo. Cada linha a seguir tem cinco colunas separadas por
dois-pontos (:
), com as mesmas informações que fornecemos na conexão. Quando
a libpq
percebe que está estabelecendo uma conexão cujo método de
autenticação escolhido é por senha (essa é uma decisão do servidor, não do
cliente), a libpq
consulta o arquivo .pgpass
(apenas se as permissões dele
estiverem corretas, caso contrário ele é ignorado) e busca uma linha cujas
primeiros quatro colunas sejam iguais às fornecidas (por qualquer um dos
métodos) para a conexão. Se a libpq
encontrar tal linha, usará o valor da
quinta coluna como senha enviada automaticamente ao servidor, sem interação com
o usuário.
Por comodidade, também é possível substituir qualquer das primeiras quatro
colunas por um asterisco (*
) para que a linha seja aplicada independentemente
daquele valor. Por exemplo, como a senha do usuário seria a mesma para todo
banco daquela máquina, poderíamos escrever a regra como:
# host:port:dbname:user:password 127.0.0.1:5432:*:postgres:supersecreta
Obs.: Nunca use o parâmetro -W
no psql
. Ele nunca é necessário, mas causa
problemas ao usuário que tenta usar. Está planejado ser removido no futuro.