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 (13.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 13.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; 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:
%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 ---------------- 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:
\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)
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:
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 dadosbancocom o usuáriousuariona 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\efe\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 2020-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- ⋮
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=#
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
