Resumo de Comando PostgreSQL

Criando uma senha
sudo su postgres -c psql postgres
ALTER USER postgres WITH PASSWORD 'sua_senha';

Gerenciar bancos de dados

Criar uma base pelo terminal
createdb -U username -E utf8 dbname -h localhost

Criar uma base pelo psql
create database dbname with owner=postgres encoding='utf8';

Renomear uma base
alter database "old_name" rename to "new_name";

Apagar uma base
drop database dbname;

Backup (dump) e restore

Dump de uma base
pg_dump dbname -h localhost -U postgres > backup.sql

Dump de uma tabela apenas
pg_dump dbname -h localhost -U postgres > backup.sql --table=nome_tabela;

Dump de uma base apenas com algumas tabelas
pg_dump dbname -h localhost -U postgres > backup.sql --table=nome_tabela --table=outra_tabela;

Dump de uma base sem determinada tabela
pg_dump dbname -h localhost -U postgres > backup.sql --exclude-table=nome_tabela;

Dump de uma base excluindo várias tabelas
pg_dump dbname -h localhost -U postgres > backup.sql --exclude-table=nome_tabela --exclude-table=outra_tabela;

Restore de uma base
psql dbname -h localhost -U postgres < backup.sql

Dump dos usuários de uma base
pg_dumpall -g -U postgres -h localhost > users.sql

Comandos especiais em queries

Data

select (current_date + integer '7') as nome_campo;

Veja mais exemplos aqui.

Erros comuns

Duplicate key value violates unique constraint

Ao migrar uma base para PostgreSQL ou fazer alguma coisa errada sem querer, o seguinte erro pode aparecer:

PGError: ERROR: duplicate key value violates unique constraint “tablename_pkey”

Para resolver:

select setval('entities_id_seq', (select max(id) from entities)+1);

Personalizando o psql

alterando o pager

\pset pager off

\pset pager off

\pset pager always

\pset pager occasionally

\pset pager at random

Exibir ‘(null)’ quando um campo for null

\pset null '(null)'

Veja mais configurações aqui.

Instalação do PostgreSQL 9.4

Instalando os Repositórios

Debian (Squeeze) 6.x

Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository

deb http://apt.postgresql.org/pub/repos/apt/ squeeze-pgdg main

Debian (Wheezy) 7.x

Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository

deb http://apt.postgresql.org/pub/repos/apt/ wheezy-pgdg main

Debian (Jessie) 8.x

Create the file /etc/apt/sources.list.d/pgdg.list, and add a line for the repository

deb http://apt.postgresql.org/pub/repos/apt/ jessie-pgdg main

Importar a chave de assinatura do repositório, e atualizar as listas de pacotes

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | \

apt-key add - 

# apt-get update

Instalação

Para instalar o PostgreSQL em Debian , use o apt- get ( ou outro apt- condução) de comando

# apt-get install postgresql-9.4

O repositório contém muitos pacotes diferentes, incluindo addons de terceiros. Maioria pacotes de comando e importantes são ( substitua o número da versão , se necessário) :

• postgresql-client-9.4 - client libraries and client binaries
• postgresql-9.4 - core database server
• postgresql-contrib-9.4 - additional supplied modules
• libpq-dev - libraries and headers for C language frontend development
• postgresql-server-dev-9.4 - libraries and headers for C language backend development
• pgadmin3 - pgAdmin III graphical administration utility

Fonte: http://www.postgresql.org/download/linux/debian/

Restaurando um Backup do PostgeSQL

Restaurando um backup do postgresql com extensão .sql

# /usr/lib/postgresql/9.4/bin/psql -h [host] -p [porta] -U [usuario] -d [nomebanco] < [/diretório/nome-arquivo].sql

Backup do PostgreSQL

Backup Postgresql com saída .sql

# /usr/bin/pg_dump -i -h [host] -p [porta] -U [usuario] -b -f [/diretório/nome-arquivo].sql [nome_database]

Compactar 

# tar zcvf nome_arquivo.tar.gz nome_arquivo.sql

Backup Postgresql com saída .backup

# /usr/bin/pg_dump -i -h [host] -p [porta] -U [usuario] -F c -b -f  [/diretório/nome_arquivo].backup [database]