Debian, PostgreSQL e o (concluido)

[hugo]

Relembrando a primeira mensagem :

[Você precisa estar registrado e conectado para ver esta imagem.]

O PostgreSQL é um sistema para gerenciamento de banco de dados relacional, ou SGBD, e é desenvolvido sob uma licença nos padrões BSD, servindo como alternativa a outros gerenciadores de banco de dados, livres (MySQL, FireBird) ou proprietários (Oracle, SyBase, Microsoft SQL Server). A licença garante o livre uso, a cópia, distribuição ou modificação do software e de toda a sua documentação, desde que mantida a licença.

Dica quente
Instalando o debian 5.04 e marcando a opcao banco de dados sql o postgresql 8.3 ja sera instalado

ATENCAO:

Código:

O material de onde estou estudando trata do postgreesql 8.1, logo estou adaptando todo material para o 8.3, se aparecer alguma citacao de 8.1 considerem 8.3!

Código:

Para saber a versão instalada de um pacote basta utilizar o comando:
dpkg -s <nome do pacote>


Créditos:
cdtc

[hugo]

Junções (Joins)

Você pode estar imaginando: Mas só é possível ver dados de apenas uma tabela por vez?
A resposta é não. É possível fazer junções de tabelas para obter dados cruzados. Veremos como isso é possível. Imagine que temos uma nova tabela chamada cidades. Esta tabela guarda informações sobre a localização de uma determinada cidade e possui colunas como: nome, latitude, longitude.
Vamos fazer uma pesquisa com o tempo das cidades juntamente com a localização de cada cidade.

Código:

SELECT * FROM tempo, cidades WHERE cidade = nome;

Existem duas colunas contendo o nome da cidade, o que está correto porque a lista de colunas das tabelas tempo e cidades estão concatenadas. Na prática isto não é desejado, sendo preferível, portanto, escrever a lista das colunas de saída explicitamente em vez de utilizar o *:

Código:

SELECT cidade, maximo, minimo, data, latitude, longitude
FROM tempo, cidades WHERE cidade = nome;

Como todas as colunas possuem nomes diferentes, o analisador encontra automaticamente a tabela que a coluna pertence, mas é um bom recurso qualificar completamente os nomes das colunas nas consultas de junção:

Código:

SELECT tempo.cidade, tempo.maximo, tempo.minimo,
tempo.data, cidades.latitude, cidades.longitude
FROM tempo, cidades
WHERE cidades.nome = tempo.cidade;

As consultas de junção do tipo visto até agora também poderiam ser escritas da seguinte forma alternativa:

Código:

SELECT *
FROM tempo INNER JOIN cidades ON (tempo.cidade = cidades.nome);

A utilização desta sintaxe não é tão comum quanto a usada acima, mas é mostrada para ajudar a entender os próximos tópicos.
Com esta sintaxe é possível cruzar os dados de maneira mais específica. Imagine que desejamos o seguinte: que a consulta varra a tabela tempo e, para cada uma de suas linhas, encontre a linha correspondente na tabela cidades. Se não for encontrada nenhuma linha correspondente, desejamos que sejam colocados "valores vazios" nas colunas da tabela cidades. Este tipo de consulta é chamada de junção externa (outer join). As consultas vistas até agora são junções internas (inner join). O comando, então, fica assim:

Código:

SELECT *
FROM tempo LEFT OUTER JOIN cidades ON (tempo.cidade = cidades.nome);

Esta consulta é chamada de junção externa esquerda (left outer join), porque a tabela mencionada à esquerda do operador de junção terá cada uma de suas linhas aparecendo na saída pelo menos uma vez, enquanto a tabela à direita terá somente as linhas correspondendo a alguma linha da tabela à esquerda aparecendo na saída. Ao listar uma linha da tabela à esquerda, para a qual não existe nenhuma linha correspondente na tabela à direita, são colocados valores vazios (null) nas colunas da tabela à direita.

Visões (Views)

Supondo que a consulta combinando os registros de tempo e de localização das cidades seja de particular interesse para um aplicativo, mas que não se deseja digitar esta consulta toda vez que for necessária, então é possível criar uma visão baseada na consulta, atribuindo um nome a esta consulta pelo qual será possível referenciá-la como se fosse uma tabela comum.

Código:

CREATE VIEW minha_visao AS
SELECT cidade, maximo, minimo, data, localizacao
FROM tempo, cidades
WHERE cidade = nome;

SELECT * FROM minha_visao;

Fazer livre uso de visões é um aspecto chave de um bom projeto de banco de dados SQL. As visões permitem encapsular, atrás de interfaces que não mudam, os detalhes da estrutura das tabelas, que podem mudar na medida em que os aplicativos evoluem.

As visões podem ser utilizadas em praticamente todos os lugares onde uma tabela real pode ser utilizada. Construir visões baseadas em visões não é raro.

[hugo]

Funções de agregação

Como a maioria dos produtos de banco de dados relacional, o PostgreSQL suporta funções de agregação. Uma função de agregação computa um único resultado para várias linhas de entrada. Por exemplo, existem funções de agregação para contar (count), somar (sum), calcular a média (avg), o valor máximo (max) e o valor mínimo (min) para um conjunto de linhas.

Para servir de exemplo, é possível encontrar a maior temperatura mínima ocorrida em qualquer lugar usando:

Código:

SELECT max(minimo) FROM tempo;

Se for desejado saber a cidade (ou cidades) onde esta temperatura ocorreu pode-se tentar usar:

Código:

SELECT cidade FROM tempo WHERE minimo = max(minimo);

Mas não vai funcionar, porque a função de agregação max não pode ser usada na cláusula WHERE (Esta restrição existe porque a cláusula WHERE determina as linhas que vão passar para o estágio de agregação e, portanto, precisa ser avaliada antes das funções de agregação serem computadas). Entretanto, como é geralmente o caso, a consulta pode ser reformulada para obter o resultado pretendido, o que será feito por meio de uma subconsulta:

Código:

SELECT cidade FROM tempo
WHERE minimo = (SELECT max(minimo) FROM tempo);

Isto está correto porque a subconsulta é uma ação independente, que calcula sua agregação em separado do que está acontecendo na consulta externa.
As agregações também são muito úteis em combinação com a cláusula GROUP BY. Por exemplo, pode ser obtida a maior temperatura mínima observada em cada cidade usando:

Código:

SELECT cidade, max(minimo)
FROM tempo
GROUP BY cidade;

A saída será algo do tipo:

cidade | max
----------+-----
Brasilia | 25
Goiania | 27
(2 linhas)

Produzindo uma linha de saída para cada cidade. Cada resultado da agregação é computado sobre as linhas da tabela correspondendo a uma cidade. As linhas agrupadas podem ser filtradas utilizando a cláusula HAVING:

Código:

SELECT cidade, max(minimo)
FROM tempo
GROUP BY cidade
HAVING max(minimo) < 26;

cidade | max
---------+-----
Brasilia | 25
(1 linha)

Que mostra os mesmos resultados, mas apenas para as cidades que possuem todos os valores de mínimo abaixo de 26. Para concluir, se desejarmos somente as cidades com nome começando pela letra "B" podemos escrever:

Código:

SELECT cidade, max(minimo)
FROM tempo
WHERE cidade LIKE 'B%'
GROUP BY cidade
HAVING max(minimo) < 26;

É importante compreender a interação entre as agregações e as cláusulas WHERE e HAVING do SQL. A diferença fundamental entre WHERE e HAVING é esta: WHERE seleciona as linhas de entrada antes dos grupos e agregações serem computados (portanto, controla quais linhas irão para o computo da agregação), enquanto HAVING seleciona linhas de grupo após os grupos e agregações serem computados.
Portanto, a cláusula WHERE não pode conter funções de agregação, não faz sentido tentar utilizar uma agregação para determinar quais linhas serão a entrada da agregação. Por outro lado, a cláusula HAVING sempre contém funções de agregação (A rigor, é permitido escrever uma cláusula HAVING que não possua agregação, mas é desperdício: A mesma condição poderia ser utilizada de forma mais eficiente no estágio do WHERE).

No exemplo anterior, a restrição do nome da cidade pode ser aplicada na cláusula WHERE, porque não necessita de nenhuma agregação, sendo mais eficiente que colocar a restrição na cláusula HAVING, porque evita realizar os procedimentos de agrupamento e agregação em todas as linhas que não atendem a cláusula WHERE.

[hugo]

Modificando dados
Atualizamos os dados de uma tabela com o comando UPDATE. Vejamos sua sintaxe:

Código:

UPDATE nome_da_tabela SET nome_da_coluna = 'valor' WHERE condição;

Este comando altera o valor da coluna 'nome_da_coluna' para 'valor' em todas as linhas que satisfizerem a condição.
Por exemplo:

Código:

UPDATE tempo SET maximo = 29 WHERE cidade = 'Brasilia';

Atualiza o valor máximo para 29 em todas as linhas em que cidade é igual a 'Brasília'.

Nota: Sem o uso da cláusula WHERE, todas as linhas da tabela são alteradas.

Removendo dados

Removemos os dados de uma tabela com o comando DELETE. Sua sintaxe é a seguinte:

Código:

DELETE FROM nome_da_tabela WHERE condição;

Isto paga da tabela todas as linhas que satisfazem a condição. Por exemplo:

Código:

DELETE FROM tempo WHERE cidade = 'Brasilia';

Isto apagaria todas as linhas da tabela tempo onde o campo cidade fosse igual a 'Brasilia';
Deve-se tomar um especial cuidado com este comando, analisando bem a condição a ser usada, para que não se apague dados importantes. Observe que:

Código:

DELETE FROM tempo;

Apaga TODOS os registros da tabela tempo, já que não existe nenhuma condição. E, a não ser que este seja seu objetivo, não deve ser usado o comando de tal forma.

[hugo]

Transações

Transação é um conceito fundamental de todo sistema de banco de dados. O ponto essencial da transação é englobar vários passos em uma única operação de tudo ou nada. Os estados intermediários entre os passos não são vistos pelas demais transações simultâneas e, se ocorrer alguma falha que impeça a transação chegar até o fim, então nenhum dos passos intermediários irá afetar o banco de dados de forma alguma.

Por exemplo, considere um banco de dados de uma instituição financeira contendo o saldo da conta corrente de vários clientes, assim como o saldo total dos depósitos de cada agência. Suponha que se deseje transferir $100.00 da conta da Alice para a conta do Bob. Simplificando, barbaramente, os comandos SQL para esta operação seriam:

Código:

UPDATE conta_corrente SET saldo = saldo - 100.00
WHERE nome = 'Alice';
UPDATE filiais SET saldo = saldo - 100.00
WHERE nome = (SELECT nome_filial FROM conta_corrente WHERE nome = 'Alice');
UPDATE conta_corrente SET saldo = saldo + 100.00
WHERE nome = 'Bob';
UPDATE filiais SET saldo = saldo + 100.00
WHERE nome = (SELECT nome_filial FROM conta_corrente WHERE nome = 'Bob');

Os detalhes destes comandos não são importantes aqui. O importante é o fato de existirem várias atualizações distintas envolvidas para realizar uma operação bem simples. A contabilidade quer ter certeza que todas as atualizações são realizadas ou que nenhuma delas são realizadas. Não é interessante uma falha no sistema que fazeria com que Bob recebesse $100.00 que não foi debitado da Alice.

Também a Alice não continuará sendo uma cliente satisfeita se o dinheiro for debitado da conta dela e não for creditado na de Bob. É necessário garantir que, caso aconteça algo errado no meio da operação, nenhum dos passos executados até este ponto produza efeito. Agrupar as atualizações em uma transação dá esta garantia. Uma transação é dita como sendo atômica: do ponto de vista das outras transações a transação acontece completamente ou nada acontece.

Desejamos, também, ter a garantia de estando a transação completa e aceita pelo sistema de banco de dados, que esta fique permanentemente gravada e não seja perdida mesmo no caso de acontecer uma pane logo em seguida. Por exemplo, se estiver sendo registrado saque em dinheiro pelo Bob não se deseja, de forma alguma, que o débito em sua conta corrente desapareça por causa de uma pane ocorrida logo depois dele sair da agência. Um banco de dados transacional garante que todas as atualizações realizadas por uma transação ficam registradas em meio de armazenamento permanente (ou seja, em disco), antes da transação ser considerada completa.

Outra propriedade importante dos bancos de dados transacionais está muito ligada à noção de atualizações atômicas: quando várias transações estão sendo executadas simultaneamente, cada uma delas não devem enxergar as alterações incompletas efetuadas pelas outras. Por exemplo, se uma transação está ocupada totalizando o saldo de todas as agências, não pode ser visto o débito efetuado na agência da Alice mas ainda não creditado na agência do Bob, nem o contrário. Portanto, as transações devem ser tudo ou nada, não apenas em termos do efeito permanente no banco de dados, mas também em termos de visibilidade durante o processamento. As atualizações feitas por uma transação em andamento não podem ser vistas pelas outras transações enquanto não terminar, quando todas as atualizações se tornam visíveis ao mesmo tempo.

No PostgreSQL a transação é definida envolvendo os comandos SQL da transação pelos comandos BEGIN e COMMIT . Sendo assim, a nossa transação bancária ficaria:

Código:

BEGIN;
UPDATE conta_corrente SET saldo = saldo - 100.00
WHERE nome = 'Alice';
-- etc etc
COMMIT;

Se no meio da transação for decidido que esta não deve ser efetivada (talvez porque tenha sido visto que o saldo da Alice ficou negativo), pode ser executado o comando ROLLBACK em vez do COMMIT para fazer com que todas as atualizações sejam canceladas.

O PostgreSQL, na verdade, trata todo comando SQL como sendo executado dentro de uma transação. Se não for utilizado o comando BEGIN, então, cada comando possui um BEGIN e, se der tudo certo, um COMMIT individual envolvendo-o. Um grupo de comandos envolvidos por um BEGIN e um COMMIT é algumas vezes chamado de bloco de transação.

É possível controlar os comandos na transação de uma forma mais granular utilizando os pontos de salvamento (savepoints). Os pontos de salvamento permitem cancelar partes da transação seletivamente e efetivar as demais partes. Após definir o ponto de salvamento, através da instrução SAVEPOINT, é possível cancelar a transação até o ponto de salvamento, se for necessário, usando ROLLBACK TO. Todas as alterações no banco de dados efetuadas entre a definição do ponto de salvamento e o cancelamento são desprezadas, mas as alterações efetuadas antes do ponto de salvamento são mantidas.

Após cancelar o ponto de salvamento, ele continua definido e, portanto, é possível ser cancelado várias vezes. Ao contrário, havendo certeza que não vai ser mais necessário cancelar o ponto de salvamento, ele pode ser liberado para que o sistema possa liberar alguns recursos. Deve-se ter em mente que liberar ou cancelar um ponto de salvamento libera, automaticamente, todos os ponto de salvamento definidos após o mesmo.

Tudo isto acontece dentro do bloco de transação e, portanto, nada disso é visto pelas outras seções do banco de dados. Quando o bloco de transação é efetivado, as ações efetivadas se tornam visíveis como uma unidade para as outras seções, enquanto as ações canceladas nunca se tornam visíveis.

Recordando o banco de dados da instituição financeira, suponha que devesse ser debitado $100.00 da conta da Alice e creditado na conta do Bob, mas que foi descoberto em seguida que era para ser creditado na conta do Wally. Isso poderia ser feito utilizando pontos de salvamento como mostrado abaixo:

Código:

BEGIN;
UPDATE conta_corrente SET saldo = saldo - 100.00
WHERE nome = 'Alice';
SAVEPOINT meu_ponto_de_salvamento;
UPDATE conta_corrente SET saldo = saldo + 100.00
WHERE nome = 'Bob';
-- uai ... o certo é na conta do Wally
ROLLBACK TO meu_ponto_de_salvamento;
UPDATE conta_corrente SET saldo = saldo + 100.00
WHERE nome = 'Wally';
COMMIT;


Obviamente, este exemplo está simplificado ao extremo, mas é possível efetuar um grau elevado de controle sobre a transação através do uso de pontos de salvamento. Além disso, a instrução ROLLBACK TO é a única forma de obter novamente o controle sobre um bloco de transação colocado no estado interrompido devido a um erro, fora cancelar completamente e começar tudo de novo.

[hugo]

Heranças

Herança é um conceito de banco de dados orientado a objeto, que abre novas possibilidades interessantes ao projeto de banco de dados.
Vamos criar duas tabelas: a tabela cidades e a tabela capitais. Como é natural, as capitais também são cidades e, portanto, deve existir alguma maneira para mostrar implicitamente as capitais quando todas as cidades são mostradas. Se formos bastante perspicazes, poderemos criar um esquema como este:

Código:

CREATE TABLE capitais (
nome text,
populacao real,
altitude int,
estado char(2)
);

CREATE TABLE interior (
nome text,
populacao real,
altitude int
);

CREATE VIEW cidades AS
SELECT nome, populacao, altitude FROM capitais
UNION
SELECT nome, populacao, altitude FROM interior;


Não se preocupe aqui com a instrução UNION, apenas saiba que a visão acima seleciona todas as cidades, seja da tabela capitais seja da interior.

Este esquema funciona bem para as consultas, mas não é bom quando é necessário atualizar várias linhas, entre outras coisas.
Esta é uma solução melhor:

Código:

CREATE TABLE cidades (
nome text,
populacao real,
altitude int -- (em pés)
);

CREATE TABLE capitais (
estado char(2)
) INHERITS (cidades);


Neste caso, as linhas da tabela capitais herdam todas as colunas (nome, população e altitude) da sua tabela ancestral cidades. O tipo da coluna nome é text, um tipo nativo do PostgreSQL para cadeias de caracteres de tamanho variável. As capitais dos estados possuem uma coluna a mais, chamada estado, que armazena a sigla do estado. No PostgreSQL uma tabela pode herdar de nenhuma, de uma ou de várias tabelas.

Por exemplo, a consulta abaixo retorna os nomes de todas as cidades, incluindo as capitais dos estados, localizadas a uma altitude superior a 500 metros:

Código:

SELECT nome, altitude
FROM cidades
WHERE altitude > 500;

Por outro lado, a consulta abaixo traz todas as cidades que não são capitais de estado e estão situadas a uma altitude superior a 500 pés:

Código:

SELECT nome, altitude
FROM ONLY cidades
WHERE altitude > 500;

Nesta consulta a palavra chave ONLY antes de cidades indica que a consulta deve ser efetuada apenas na tabela cidades, sem incluir as tabelas abaixo de cidades na hierarquia de herança. Muitos comandos mostrados até agora - SELECT, UPDATE e DELETE - permitem usar a notação ONLY.

[hugo]

Autenticação de clientes

Quando um aplicativo cliente se conecta ao servidor de banco de dados especifica o nome de usuário do PostgreSQL a ser usado na conexão, de forma semelhante à feita pelo usuário para acessar o sistema operacional Unix. Dentro do ambiente SQL, o nome de usuário do banco de dados determina os privilégios de acesso aos objetos do banco de dados . Portanto, é essencial controlar como os usuários de banco de dados podem se conectar.

A autenticação é o processo pelo qual o servidor de banco de dados estabelece a identidade do cliente e, por extensão, determina se o aplicativo cliente (ou o usuário executando o aplicativo cliente) tem permissão para se conectar com o nome de usuário que foi informado.

O PostgreSQL possui vários métodos diferentes para autenticação de clientes. O método utilizado para autenticar uma determinada conexão cliente pode ser selecionado tomando por base o endereço de hospedeiro (do cliente), o banco de dados ou o usuário.

Os nomes de usuário do PostgreSQL são logicamente distintos dos nomes de usuário do sistema operacional onde o servidor executa. Se todos os usuários de um determinado servidor de banco de dados também possuem conta no sistema operacional do servidor, é razoável atribuir nomes de usuário do banco de dados correspondendo aos nomes de usuário do sistema operacional.

Entretanto, um servidor que aceita conexões remotas pode possuir muitos usuários de banco de dados que não possuem conta no sistema operacional local e, nestes casos, a associação entre os nomes de usuário do banco de dados e os nomes de usuário do sistema operacional não é necessária.

O arquivo pg_hba.conf:

A autenticação do cliente é controlada pelo arquivo que por tradição se chama pg_hba.conf e é armazenado no diretório de dados do agrupamento de bancos de dados. HBA significa autenticação baseada no hospedeiro (host-based authentication). É instalado um arquivo pg_hba.conf padrão quando o diretório de dados é inicializado pelo utilitário initdb.

O formato geral do arquivo pg_hba.conf é um conjunto de registros, sendo um por linha. As linhas em branco são ignoradas, da mesma forma que qualquer texto após o caractere de comentário #. Um registro é formado por vários campos separados por espaços ou tabulações. Os campos podem conter espaços em branco se o valor do campo estiver entre aspas. Os registros não podem ocupar mais de uma linha.

Cada registro especifica um tipo de conexão, uma faixa de endereços de IP de cliente (se for relevante para o tipo de conexão), um nome de banco de dados, um nome de usuário e o método de autenticação a ser utilizado nas conexões que correspondem a estes parâmetros. O primeiro registro com o tipo de conexão, endereço do cliente, banco de dados solicitado e nome de usuário que corresponder é utilizado para realizar a autenticação. Não existe fall-through (procura exaustiva) ou backup: se um registro for escolhido e a autenticação não for bem-sucedida, os próximos registros não serão levados em consideração. Se não houver correspondência com nenhum registro, então o acesso é negado.

O registro pode ter um dos sete formatos a seguir:

* local banco_de_dados usuário método_de_autenticação [opção_de_autenticação]
* host banco_de_dados usuário endereço_de_CIDR método_de_autenticação [opção_de_autenticação]
* hostssl banco_de_dados usuário endereço_de_CIDR método_de_autenticação [opção_de_autenticação]
* hostnossl banco_de_dados usuário endereço_de_CIDR método_de_autenticação [opção_de_autenticação]
* host banco_de_dados usuário endereço_de_IP máscara_de_IP método_de_autenticação [opção_de_autenticação]
* hostssl banco_de_dados usuário endereço_de_IP máscara_de_IP método_de_autenticação [opção_de_autenticação]
* hostnossl banco_de_dados usuário endereço_de_IP máscara_de_IP método_de_autenticação [opção_de_autenticação]


O significado de cada campo está descrito abaixo:

* local

Este registro corresponde às tentativas de conexão feitas utilizando soquete do domínio Unix. Sem um registro deste tipo não são permitidas conexões através de soquete do domínio Unix.

* host

Este registro corresponde às tentativas de conexão feitas utilizando o protocolo TCP/IP. Os registros host correspondem tanto às conexões SSL (Secure Socket Layer), quanto às não SSL.

Nota: Não serão possíveis conexões TCP/IP remotas, a menos que o servidor seja inicializado com o valor apropriado para o parâmetro de configuração listen_addresses, uma vez que o comportamento padrão é aceitar conexões TCP/IP apenas no endereço retornante (loopback) localhost.

* hostssl

Este registro corresponde às tentativas de conexão feitas utilizando o protocolo TCP/IP, mas somente quando a conexão é feita com a criptografia SSL. Para esta opção poder ser utilizada, o servidor deve ser construído com o suporte a SSL habilitado. Além disso, o SSL deve ser habilitado na inicialização do servidor através do parâmetro de configuração ssl.

* hostnossl

Este registro é o oposto lógico de hostssl: só corresponde a tentativas de conexão feitas através do protocolo TCP/IP que não utilizam SSL.

* banco_de_dados

Especifica quais bancos de dados este registro corresponde. O valor all especifica que corresponde a todos os bancos de dados. O valor sameuser especifica que o registro corresponde ao banco de dados com o mesmo nome do usuário fazendo o pedido de conexão. O valor samegroup especifica que o usuário deve ser membro do grupo com o mesmo nome do banco de dados do pedido de conexão. Senão, é o nome de um banco de dados específico do PostgreSQL. Podem ser fornecidos vários nomes de banco de dados separados por vírgula. Pode ser especificado um arquivo contendo nomes de banco de dados, precedendo o nome do arquivo por @.

* usuário

Especifica quais usuários do PostgreSQL este registro corresponde. O valor all especifica que corresponde a todos os usuários. Senão, é o nome de um usuário específico do PostgreSQL. Podem ser fornecidos vários nomes de usuários separados por vírgula. Podem ser especificados nomes de grupo precedendo o nome do grupo por +. Pode ser especificado um arquivo contendo nomes de usuário precedendo o nome do arquivo por @.

* endereço_de_CIDR

Especifica a faixa de endereços de IP da máquina cliente que este registro corresponde. Contém um endereço de IP na notação padrão decimal com pontos, e o comprimento da máscara de CIDR (Os endereços de IP somente podem ser especificados numericamente, e não como domínios ou nomes de hospedeiro). O comprimento da máscara indica o número de bits de mais alta ordem que o endereço de IP do cliente que deve corresponder. Os bits à direita devem ser zero em um determinado endereço de IP. Não pode haver espaços em branco entre os endereços de IP, a / e o comprimento da máscara de CIDR. Um endereço_de_CIDR típico seria 172.20.143.89/32 para um único hospedeiro, ou 172.20.143.0/24 para uma rede. Para especificar um único hospedeiro deve ser utilizada uma máscara de CIDR igual a 32 para o IPv4, ou igual a 128 para o IPv6. Um endereço especificado no formato IPv4 corresponde às conexões IPv6 que possuem o endereço correspondente como, por exemplo, 127.0.0.1 corresponde ao endereço de IPv6 ::ffff:127.0.0.1. Uma entrada especificada no formato IPv6 corresponde apenas às conexões IPv6, mesmo que represente um endereço na faixa IPv4-em-IPv6. Deve ser observado que as entradas no formato IPv6 serão rejeitadas se a biblioteca C do sistema não possuir suporte a endereços IPv6. Este campo se aplica apenas aos registros host, hostssl e hostnossl.

* endereço_de_IP e máscara_de_IP

Estes campos podem ser utilizados como uma alternativa à notação endereço_de_CIDR. Em vez de especificar o comprimento da máscara, a máscara é especificada como uma coluna em separado. Por exemplo, 255.0.0.0 representa uma máscara de CIDR para endereços de IPv4 com comprimento igual a 8, e 255.255.255.255 representa uma máscara de CIDR com comprimento igual a 32. Estes campos se aplicam apenas aos registros host, hostssl e hostnossl.

* método_de_autenticação

Especifica o método de autenticação a ser utilizado para se conectar através deste registro. Abaixo está mostrado um resumo das escolhas possíveis:
trust:
A conexão é permitida incondicionalmente. Este método permite a qualquer um que possa se conectar ao servidor de banco de dados PostgreSQL se autenticar como o usuário do PostgreSQL que for desejado, sem necessidade de senha.

reject :
A conexão é rejeitada incondicionalmente. É útil para "eliminar por filtragem" certos hospedeiros de um grupo.

md5:
Requer que o cliente forneça uma senha criptografada pelo método md5 para autenticação.

crypt :
Requer que o cliente forneça uma senha criptografada através de crypt() para autenticação. Deve-se dar preferência ao método md5 para os clientes com versão 7.2 ou posterior, mas os clientes com versão anterior a 7.2 somente suportam crypt.

password :
Requer que o cliente forneça uma senha não criptografada para autenticação. Uma vez que a senha é enviada em texto puro pela rede, não deve ser utilizado em redes não confiáveis.

krb4 :
É utilizado Kerberos V4 para autenticar o usuário. Somente disponível para conexões TCP/IP.

krb5 :
É utilizado Kerberos V5 para autenticar o usuário. Somente disponível para conexões TCP/IP.

ident :
Obtém o nome de usuário do sistema operacional do cliente (para conexões TCP/IP fazendo contato com o servidor de identificação no cliente, para conexões locais obtendo a partir do sistema operacional) e verifica se o usuário possui permissão para se conectar como o usuário de banco de dados solicitado consultando o mapa especificado após a palavra chave ident.

pam :
Autenticação utilizando o serviço Pluggable Authentication Modules (PAM) fornecido pelo sistema operacional.

* opção_de_autenticação

O significado deste campo opcional depende do método de autenticação escolhido.

Os arquivos incluídos pela construção @ são lidos como nomes de listas, que podem ser separadas por espaços em branco ou vírgulas. Os comentários são iniciados por #, como no arquivo pg_hba.conf, sendo permitidas construções @ aninhadas. A menos que o nome do arquivo que segue a @ seja um caminho absoluto, é considerado como sendo relativo ao diretório que contém o arquivo que faz referência.

Uma vez que os registros de pg_hba.conf são examinados seqüencialmente a cada tentativa de conexão, a ordem dos registros possui significado. Normalmente, os primeiros registros possuem parâmetros de correspondência de conexão mais exigentes e métodos de autenticação menos exigentes, enquanto os últimos registros possuem parâmetros de correspondência menos exigentes e métodos de autenticação mais exigentes. Por exemplo, pode-se desejar utilizar a autenticação trust para conexões TCP/IP locais, mas requerer o uso de senha para conexões TCP/IP remotas. Neste caso, o registro especificando a autenticação trust para conexões a partir de 127.0.0.1 deve aparecer antes do registro especificando autenticação por senha para uma faixa mais ampla de endereços de IP de cliente permitidos.

O arquivo pg_hba.conf é lido durante a inicialização e quando o processo servidor principal (postmaster) recebe um sinal SIGHUP. Se o arquivo for editado enquanto o sistema estiver ativo, será necessário enviar um sinal para o postmaster (utilizando pg_ctl reload ou kill -HUP) para fazer com que o arquivo seja lido novamente.

[hugo]

Administrando usuários

Conceitualmente, os usuários de banco de dados são completamente distintos dos usuários de sistema operacional. Na prática, pode ser conveniente manter correspondência, mas não é requerido. Os nomes dos usuários de banco de dados são globais para todo o agrupamento de bancos de dados (e não próprio de cada banco de dados). Para criar um usuário deve ser utilizado o comando SQL CREATE USER :

Código:

CREATE USER nome_do_usuário;

Onde nome_do_usuário segue as regras dos identificadores SQL: ou não contém caracteres especiais, ou está entre aspas. Para remover um usuário existente deve ser utilizado o comando DROP USER :

Código:

DROP USER nome_do_usuário;

Para facilitar, são fornecidos os programas createuser e dropuser que incorporam estes comandos SQL, e que podem ser executados a partir do interpretador de comandos:

Código:

$ createuser nome_do_usuário

$ dropuser nome_do_usuário

Para facilitar, são fornecidos os programas createuser e dropuser que incorporam estes comandos SQL, e que podem ser executados a partir do interpretador de comandos:

Código:

SELECT usename FROM pg_user;

Também pode ser utilizado o meta-comando \ du do programa psql para listar os usuários existentes.

Para ser possível ativar o sistema de banco de dados, um sistema recém criado sempre contém um usuário pré-definido. É atribuído o valor 1 para o identificador deste usuário e, por padrão (a menos que seja alterado ao executar o utilitário initdb), possui o mesmo nome do usuário de sistema operacional que inicializou o agrupamento de bancos de dados. Geralmente este usuário se chama postgres. Para poder criar mais usuários, primeiro é necessário se conectar como este usuário inicial.

Em uma conexão com o servidor de banco de dados, está ativa a identidade de exatamente um usuário. O nome de usuário a ser utilizado em uma determinada conexão com o banco de dados é indicado pelo cliente ao fazer o pedido de conexão, de uma forma específica do aplicativo. Por exemplo, o programa psql utiliza a opção -U na linha de comando para indicar o usuário a ser utilizado na conexão. Muitos aplicativos assumem, por padrão, o nome do usuário corrente do sistema operacional (inclusive o createuser e o psql). Portanto, é conveniente manter uma correspondência de nomes entre estes dois conjuntos de usuários.

[hugo]

Privilégios

Quando um objeto do banco de dados é criado, é atribuído um dono ao mesmo. O dono é o usuário que executou o comando de criação. Para mudar o dono de uma tabela, índice, seqüência ou visão deve ser utilizado o comando ALTER TABLE . Por padrão, somente o dono (ou um superusuário) pode fazer qualquer coisa com o objeto. Para permitir o uso por outros usuários, devem ser concedidos privilégios.

Existem vários privilégios distintos: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, USAGE e ALL PRIVILEGES. Para obter mais informações sobre os diferentes tipos de privilégios suportados pelo PostgreSQL deve ser consultada a página de referência do comando GRANT . O direito de modificar ou remover um objeto é sempre um privilégio apenas de seu dono. É utilizado o comando GRANT para conceder privilégios. Portanto, se joel for um usuário existente, e tbl_contas for uma tabela existente, o privilégio de atualizar a tabela pode ser concedido pelo comando:

Código:

GRANT UPDATE ON tbl_contas TO joel;

Este comando deve ser executado pelo usuário dono da tabela. Para conceder privilégios para um grupo deve ser utilizado o comando:

Código:


GRANT SELECT ON tbl_contas TO GROUP grp_financas;

O nome especial de "usuário" PUBLIC pode ser utilizado para conceder o privilégio para todos os usuários do sistema. Escrevendo ALL no lugar do privilégio especifica a concessão de todos os privilégios.
Para revogar um privilégio deve ser utilizado o comando REVOKE :

Código:

REVOKE ALL ON tbl_contas FROM PUBLIC;

Os privilégios especiais do dono da tabela (ou seja, o direito de DROP (remover), GRANT (conceder), REVOKE (revogar), etc.) são sempre implícitos ao fato de ser o dono, não podendo ser concedidos ou revogados. Mas o dono da tabela pode decidir revogar seus próprios privilégios comuns como, por exemplo, tornando uma tabela somente para leitura para o próprio e para os outros.

[hugo]

Backups

Como tudo que contém dados importantes, devem ser feitas cópias de segurança dos bancos de dados do PostgreSQL regularmente. Embora o procedimento seja essencialmente simples, é importante possuir uma compreensão básica das técnicas e princípios subjacentes.

Existem três abordagens fundamentalmente diferentes para fazer cópia de segurança dos dados do PostgreSQL:

* Método SQL-dump
* Cópia de segurança no nível de sistema de arquivos
* Cópia de segurança em-linha


Cada uma tem seus próprios pontos fortes e pontos fracos. Vamos analisar aqui apenas o método SQL-dump:

Método SQL-dump:
A idéia por trás do Método SQL-dump é gerar um arquivo texto contendo comandos SQL que, ao serem processados pelo servidor, recriam o banco de dados no mesmo estado em que este se encontrava quando o arquivo foi gerado. O PostgreSQL disponibiliza o programa utilitário pg_dump para esta finalidade. A forma básica de utilização deste programa é:

Código:

pg_dump nome_do_banco_de_dados > arquivo_de_saída

Conforme pode ser visto, o programa pg_dump escreve o seu resultado na saída padrão. Será visto abaixo como isto pode ser útil.

O pg_dump é um aplicativo cliente normal do PostgreSQL (embora seja particularmente astuta). Isto significa que o procedimento de cópia de segurança pode ser realizado a partir de qualquer hospedeiro remoto que possua acesso ao banco de dados. Porém, deve ser lembrado que o pg_dump não opera com permissão especial. Em particular, é necessário possuir acesso de leitura a todas as tabelas que se deseja fazer cópia de segurança. Portanto, na prática, quase sempre é necessário ser um superusuário do banco de dados.

Para especificar qual servidor de banco de dados o pg_dump deve se conectar, devem ser utilizadas as opções de linha de comando -h hospedeiro e -p porta. O hospedeiro padrão é o hospedeiro local, ou o que estiver especificado na variável de ambiente PGHOST. De maneira semelhante, a porta padrão é indicada pela variável de ambiente PGPORT ou, na falta desta, pelo padrão de compilação (Por conveniência, o servidor normalmente é compilado usando o mesmo padrão).

Assim como qualquer outro aplicativo cliente do PostgreSQL, o pg_dump se conecta por padrão ao banco de dados cujo nome é igual ao nome do usuário corrente do sistema operacional. Para que seja outro, deve ser especificada a opção -U, ou definida a variável de ambiente PGUSER. Não deve ser esquecido que as conexões do pg_dump estão sujeitas aos mecanismos normais de autenticação de cliente.

As cópias de segurança criadas pelo pg_dump são consistentes internamente, ou seja, as atualizações feitas no banco de dados enquanto o pg_dump está executando não estão presentes na cópia de segurança. O pg_dump não bloqueia outras operações no banco de dados enquanto está executando.

Restauração da cópia de segurança:

Os arquivos texto criados pelo programa pg_dump são feitos para serem lidos pelo programa psql. A forma geral do comando para restaurar uma cópia de segurança é:

Código:

psql nome_do_banco_de_dados < arquivo_de_entrada

onde o arquivo_de_entrada é o que foi utilizado como arquivo_de_saída pelo programa pg_dump. O banco de dados nome_do_banco_de_dados não será criado por este comando, devendo ser criado a partir de template0 antes de executar o psql (por exemplo, usando createdb -T template0 nome_do_banco_de_dados). O psql possui opções semelhantes às do pg_dump para controlar a identificação do servidor de banco de dados e o nome do usuário.

Antes de começar a executar a restauração, não basta existir o banco de dados de destino. Devem existir, também, todos os usuários que possuam objetos ou concessões para os objetos contidos na cópia de segurança do banco de dados. Caso estes usuários não existam, a restauração não será capaz de recriar os objetos com os mesmos donos e concessões originais (Algumas vezes é o que se deseja, mas geralmente não é).

Uma vez feita a restauração, é sensato executar o comando ANALYZE em cada um dos bancos de dados, para que o otimizador possua estatísticas úteis. Uma forma fácil de se fazer é executando vacuumdb -a -z para efetuar o VACUUM ANALYZE de todos os bancos de dados; equivale a executar VACUUM ANALYZE manualmente.

A capacidade do pg_dump e do psql de escrever e ler de canais (pipes) torna possível replicar um banco de dados de um servidor para outro diretamente; por exemplo:

Código:

pg_dump -h hospedeiro1 nome_do_banco_de_dados | psql -h hospedeiro2 nome_do_banco_de_dados

Importante:

As cópias de segurança produzidas pelo pg_dump são relativas a template0. Isto significa que todas as linguagens, procedimentos, etc. adicionados a template1 também serão incluídos na cópia de segurança feita pelo pg_dump. Como resultado, se estiver sendo utilizado um banco de dados template1 personalizado, ao ser feita a restauração deve ser criado um banco de dados vazio a partir de template0, conforme mostrado no exemplo acima.

Utilização do pg_dumpall:

O mecanismo mostrado acima não é cômodo nem apropriado para fazer a cópia de segurança de todo o agrupamento de bancos de dados. Por este motivo é fornecido o programa pg_dumpall . O pg_dumpall faz a cópia de segurança de todos os bancos de dados de um agrupamento, e também salva dados de todo o agrupamento, como os usuários e grupos. A forma básica de utilização deste programa é:

Código:

pg_dumpall > arquivo_de_saída

A cópia de segurança gerada pode ser restaurada pelo psql usando:

Código:

psql template1 < arquivo_de_entrada

(Na verdade, pode ser especificado qualquer nome de banco de dados existente para começar, mas se estiver sendo feita a restauração em um agrupamento vazio, então template1 é a única escolha disponível). É sempre necessário possuir acesso de superusuário do banco de dados para fazer a restauração de uma cópia de segurança gerada pelo pg_dumpall, para poder restaurar as informações de usuário e de grupo.

[hugo]

Para utilizar um gerenciamento grafico voce pode usar o phppgadmin

# apt-get install phppgadmin

[hugo]

Galera, considero, a principio, terminado o material!

Postem duvidas, elogios, criticas e sugestoes!

Creditos
cdtc

[Marcos Guedes]

ótimo conteúdo!

[hugo]

obrigado patrao!

[hugo]

Mais informacoes a caminho

Vejam:

Administração de Bancos de Dados Mergeant
GNOME Database admin tool GUI for GNOME2
Mergeant is a program which helps administer a DBMS database using the gnome-db framework. Basically, it memorizes all the structure of the database, and some queries, and does the SQL queries instead of the user (not having to type all over again those SQL commands, although it is still possible to do so).
This package contains the frontend application.

Homepage: [Você precisa estar registrado e conectado para ver este link.]

Este pacote esta disponivel para instalacao no Debian!

So pro gostinho ai galera:

[Você precisa estar registrado e conectado para ver esta imagem.]Clique com o botao direito do mouse e selecione "Exibir Imagem"!

Vou brincar com ele e depois conto pra galera!

[hugo]

Caso voce nao tenha instalado o postgres com a instalacao do debian voce pode tera algumas dificuldades.

Caso seguindo o tutorial de instalação não consega diretamente compilar o postgres voce pode instalar as bibliotecas como abaixo:

Código:

apt-get install gcc g++ zlibc
apt-get install libreadline5-dev zlib1g-dev -y


Instale o build-essential caso o camando make não
funcione nem como usuário comum nem como root.

Código:

apt-get install build-essential

Valeu galera
Dica de Eros Vasconcelos