PostgreSQL para usuários Mysql

Eu sei Mysql, então PostgreSQL não vai ser problema certo ? Errado.

Segue uma lista de soluções que enfrentei , que sirva de guia para quem já enfrentou os mesmos desafios.

Criando o primeiro Banco de Dados

Versão MySQL:

$ mysql -u root -p
 mysql> CREATE DATABASE dbTest;
 mysql> USE dbTest;

No Linux,  você deve logar com o usuário postgres  (após instalar o postgresql ).

$ su - postgres
 postgres@x220:~$ psql # Inicia o console
 postgres=# \h # Lista os comandos SQL disponíveis.
 postgres=# \h [comando] # Mostra o uso do [comando]

Cadê o CREATE DATABASE ?  Não tem, o comando é dado fora do console, deslogue do console com Ctrl-D ou \q e digite:

postgres@x220:~$ createdb dbTest

Legal, vamos entrar no console e usar o USE dbTest… Oops, não tem USE.

postgres=# \? # Lista os comandos internos
postgres=# \c dbTest

Agora você está conectado ao banco de dados “dbTest” como usuário “postgres”.

dbTest=#

OS X

No OS X o usuário postgres não é criado, então você deve rodar o psql no terminal.

CrIANDO UM NOVO USUÁRIO

No Mysql os comandos são parecidos com esses aqui:

mysql> CREATE USER milestone IDENTIFIED BY 'milestone';
mysql> GRANT ALL ON dbTest.* TO milestone;

No postgres a sintaxe é diferente, o comando \h CREATE USER explica.

dbTest=# CREATE USER milestone WITH PASSWORD "milestone";
ERROR: syntax error at or near ""milestone""

dbTest=# CREATE USER milestone WITH PASSWORD 'milestone';
postgres=# GRANT ALL ON DATABASE dbTest TO milestone;
ERROR: database "dbtest" does not exist

Oops, deu erro !!! O problem é que dbtaste realmente não existe, o nome do banco de dados foi convertido para lowercase no comando ( e foi executado dessa forma). A solução é usar aspas duplas como no exemplo abaixo:

postgres=# GRANT ALL ON DATABASE "dbTest" TO milestone;
GRANT

Legal! Agora vamos logar com esse usuário.

postgres=# \c - milestone
FATAL: Peer authentication failed for user "milestone"

hum…. Você deve configurar o  PostgreSQL  para permitir  que o usuario milestone tenha permissão de logar via password.  No arquivo /etc/postgresql/9.<x>/main/pg_hba.conf . Mude as linhas:

local all all peer

para

local all all md5

ou a linha abaixo para permitir apenas para o usuário milestone:

local all milestone md5

Obviamente essa linha deve ser colocada antes da regra válida para todos usuário ou não vai funcionar. Mais ou menos assim:

local all milestone md5
 local all all md5

PostgreSQL deve ser reiniciado:

# service postgresql restart

ou

# /etc/init.d/postgresql restart

PERMISSões Nas TABeLAS

Infelizmente todas as tentativas de ler ou escrever nas tabelas do banco vão falhar.

A especificação define uma hierarquia fixa de catalog.schema.table . No PG “catalog” é o banco de dados.

dbTest=# \dn
List of schemas
Name | Owner
--------+----------
 public | postgres
(1 row)

Então nosso usuário milestone precisa de permissão para acessar dbTest.public.* , tanto para todas as tabelas como as sequencias. A sequencia é usada (e alterada nos inserts) para alguns tipos de dados.

dbTest=# GRANT ALL ON ALL TABLES IN SCHEMA public TO milestone;
dbTest=# GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO milestone;

AUTO_INCREMENT

Obviamente não existe. Mas existe um tipo de dados para isso.

dbTest=# CREATE TABLE foo (id serial PRIMARY KEY, bar text);

O equivalente MySQL é:

mysql> CREATE TABLE foo (id int PRIMARY KEY AUTO_INCREMENT, bar text);

SHOW TABLES, DATABASES etc.

 postgres=# \l # SHOW DATABASES
 postgres=# \dt # SHOW TABLES
 postgres=# \d myTable # DESCRIBE myTable

Arquivos de configuração e testes

Uma instância PostgreSQL possui (e mantém) sua própria área de dados. Esse diretório (também conhecido como datadir) contém arquivos de configuração e a coleção de bancos de dados. Isso permite ter multiplas instâncias do PostgreSQL aceitando conexões em multiplas portas, mas isso também significa ao iniciar o postgres você sempre deverá informar o path dessa área de dados.

postgres@x220:~$ mkdir db13 # Cria nova area de dados
postgres@x220:~$ initdb -D db13 # Inicializa area de dados
postgres@x220:~$ postgres -D db13 & # Roda o PostgreSQL em background
postgres@x220:~$ pg_ctl status -D db13 # Checa se esta rodando
postgres@x220:~$ createdb dbTest # Cria novo banco
postgres@x220:~$ psql # Conecta ao PostgreSQL rodando na porta default (5432)
postgres@x220:~$ pg_ctl stop -D db13 # Para a instancia rodando em db13
postgres@x220:~$ postgres -D db12 -p 6543 & # Roda outro servidor, mas em uma area diferente e em outra porta (6543)
postgres@x220:~$ createdb dbTest -p 6543 # Cria novo DB (mantido pela segunda instancia na porta 6543)
postgres@x220:~$ psql -p 6543 # Conecta em db12

Para encontrar em qual diretório sua instancia do postgres está rodando, você pode usar algo como ps aux | grep postgres .

Socket file errors

Erros como:  /var/run/postgresql/.s.PGSQL.5432.lock not found podem acontecer em sistemas  Debian onde o PostgreSQL não está configurado para subir na inicialização ( rcconf –off postgresql por exemplo)
Existem duas soluções para esse problema, ou você cria /var/run/postgresql :

# mkdir /var/run/postgresql 
# chmod a+w /var/run/postgresql # adiciona permissão de escrita para o diretório

Ou você muda o arquivo postgresql.conf para escrever o socket em outro diretório, como o /tmp

unix_socket_directory = '/tmp' # (reinicie o postgres)

Por outro lado, você pode indicar ao postgreSQL o novo diretório de socket direto da linha de comando:

postgres@x220:~$ psql -h /tmp # Our socket directory is now /tmp

fonte: http://granjow.net/postgresql.html