postgres postgresql psql

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 (14.0, server 11.7)
Type "help" for help.

postgres=# SELECT version();
                                                version
--------------------------------------------------------------------------------------------------------
 PostgreSQL 11.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
 ----------------
  11.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 14.0) e do servidor (versão 11.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).

Customizando o psql

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
----------------
 11.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:

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
----------------
 11.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:

Metacomandos

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:

Metacomandos de edição e execução:

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.

Variáveis e Macros

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

Invocação

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.