Curso Rápido de Postgres

As anotações abaixo, assim como no antigo artigo sobre Oracle são focadas em usuários vindos do MySQL. Então vou assumir que você sabe o básico de SQL e está familiarizado com a linha de comando e operações como criar, exportar e importar bases de dados.

Lembre-se: a documentação do Postgres é sua melhor amiga!

Uma conta de usuário no Postgres é chamada de role. E ele (PostGres) espera uma role para seu usuário Unix. Então vamos criar uma nova role para o usuário logado:

sudo -u postgres createuser -s $USER

Estamos criando um super usuário (-s) para podermos criar novas bases de dados, outros usuários, etc.

Assim como o MySQL, o Postgres tem vários utilitários para a linha de comando, como o psql (que é o cliente padrão), pg_dump, pg_restore e até mesmo alguns programas que estão atalhos para comandos SQL, como o createdb e o createuser.

Crie uma base de dados com (replace <database>):

createdb <database>

Para se conectar a esta base e rodar comandos SQL:

psql -d <database>

Dentro do psql, comandos não SQL sempre começam como uma barra invertida, como \q usado para sair. Você pode consultar a ajuda usando \? para comandos do psql e \h para comandos SQL.

Para conectar a uma base de dados remota:

psql -U <user> -d <database> -h '<host>'

Para executar uma consulta simples a partir do terminal:

psql -U <user> -d <database> -h '<host>' -W -c 'SELECT * FROM <table>'

Aspas nas String

Strings devem usar aspas simples:

UPDATE produtos SET nome = 'cool-aid';

Se precisar usar uma aspas simples dentro da string, use ela duas vezes:

UPDATE users SET nome = 'Sant''Anna';

Auto Incremento

O Postgres imita o auto incremento usando *sequences** do SQL, mas usando um atalho mais simples chamado SERIAL.

Isso permite que criemos tabelas com auto incremento desse jeito:

CREATE TABLE produtos(
    id SERIAL
);

Disponível em três tamanhos:

Dump (Importar e exportar)

Para exportar uma base de dados, use o utilitário pg_dump:

pg_dump -F c --file=<database>-$(date "+%Y-%m-%d").sql.gz --dbname=<database> --no-owner

Explicando os parâmetros:

• -F c: formato customizado, que usa compressão por padrão
• --no-owner: não salva o proprietário dos objetos, então a propriedade vai pertencer ao usuário que irá importar os dados

Se você for exportar de uma base remota:

pg_dump -F c --file=<database>-$(date "+%Y-%m-%d").sql.gz --dbname=<database> --no-owner --username=<user> --host=<host> --port=5432

Importar

Importe uma base com (substitua <file> e <database>):

pg_restore -c <file>.sql.gz --dbname=<database>

O parâmetro -c serve para limpar os objetos da base antes de os recriar.

Se quiser ignorar o proprietário do objetos (a role do usuário logado será usada) você também pode usar o parâmetro --no-owner.

Conexões locais sem senha

Algumas IDEs irão tentar conectar ao banco via socket, e podemos configurar o postgres pra permitir conexões locais sem precisar entrar a senha do usuário.

Edite o arquivo /etc/postgresql/<version>/main/pg_hba.conf (substitua <version> pela versão correta) e modifique METHOD na seção relacionada a IPv4 para trust:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# local is for Unix domain socket connections only
# local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

E então reinicie o serviço do Postgres:

sudo service postgresql restart

Problemas Frequentes

Se alguma importação ou exportação falhar porque existe algum conectado a base de dados (ou seja, seu SQL está dando o erro ERROR: cannot drop the currently open database):

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = '<database>';

Se você estiver tentando importar um arquivo .gz usando pg_restore mas está enfrentando o erro pg_restore: error: input file does not appear to be a valid archive, isso acontece porque o arquivo não foi gerado usando a opção -F c. Ao invés disso, o dump foi primeiro gerado e depois compactado. Então tente importar o arquivo assim:

gunzip -c <file>.gz | psql <database>