PostgreSQL

O PostgreSQL é um sistema gerenciador de banco de dados objeto relacional (SGBDOR), desenvolvido como projeto de código aberto. Veja mais nesse link para instalação/configuração PostgreSQL e phpPgAdmin. Algumas versões mais recentes do pgAdmin permitem acesso via browser, através do IP da servidora do banco de dados seguido de login e senha (de modo geral).

Elefante, símbolo do PostgreSQL (não exatamente esse elefante)
Elefante, símbolo do PostgreSQL (não exatamente esse elefante)

Muda bem pouca coisa do MySQL para o PostgreSQL (por exemplo, mysql_connect muda para pg_connect) – veja mais sobre Banco de Dados, MySQL, linguagem SQL e integração com PHP clicando no link. Uma diferença é que o agrupamento de tabelas utiliza outro conceito, o “schema”: relaciona objetos (tabelas, índices, sequence, etc) que estão no mesmo banco de dados, mas em estruturas lógicas (namespace) distintas.A localização de uma tabela fica subordinada ao schema e à database na forma “database_name.schema_name.table_name”.

Front-end de terminal psql

Ao conectar via linha de comando, já deve ser informada a database a ser utilizada, como se segue (instalação através de “sudo apt install postgresql-client-common”):

psql -h $host -U $user -W $dbname

Veja alguns comandos próprios do psql:

\d - lista as tabelas do banco de dados
\l - lista os bancos de dados
\h - lista os comandos SQL
\q - sair do psql

Segue um exemplo de um script que conecta em um banco de dados sem a necessidade de digitar a senha (parâmetro “-w”) e executa duas queries, definidas previamente em duas variáveis:

query1="SELECT * FROM table;"
query2="INSERT INTO table(local,valor,data) VALUES ($local,$id,$valor,'$data');"
host=nome_pc
user=usuario
dbname=base_dados
psql -h $host -U $user -W $dbname -w << EOF
$query1
$query2
EOF
# Alternativa se as queries estiverem no arquivo queries.sql
psql -h $host -U $user -W $dbname -f queries.sql
# Alternativa para guardar o resultado em uma variável: usar -qtA
resultado=$(psql -qtA -h $host -U $user -W $dbname -f queries.sql)

Outra opção para fazer um login sem ter que digitar a senha, é possível usar o arquivo “~/.pgpass” com a seguinte sintaxe: hostname:port:database:username:password (a porta padrão é a 5432). Esse arquivo deve estar com permissões de leitura e escrita somente para o usuário (600). Setar a senha como variável de ambiente (set PGPASSWORD=your_password) está obsoleto.

Arquivos CSV

Outra forma de inserir dados na base seria criar um arquivo CSV com as informações a serem adicionadas e executar a seguinte query:

COPY schema.table_name FROM '/home/user/file.csv' using delimiters ',';

Essa linha também pode ser executada diretamente no terminal SQL do pgAdmin III (outra interface de administração do PostgreSQL). Para isso, deve-se antes acessar o banco de dados e clicar na tabela criada previamente a receber os dados para então abrir o terminal SQL.

Veja também como inserir arquivos CSV no banco de dados Postgres clicando no link.

Criar tabela

O script SQL a seguir cria a tabela “nome_tabela” no schema “public”, com a primeira linha comentada para excluir totalmente alguma tabela que já exista através do comando DROP (o comando TRUNCATE mantém a estrutura). Os identificadores de objeto (OIDs), utilizados internamente pelo PostgreSQL como chaves primárias, não serão utilizados (depreciado). Além disso, é alterado o dono da tabela e são adicionadas permissões de uso total para o usuário “operacao” e de seleção para o usuário de web “www”.

-- DROP TABLE public.nome_tabela;
CREATE TABLE public.nome_tabela
(
  datarodada timestamp with time zone NOT NULL,
  dataprevisao timestamp with time zone NOT NULL,
  var1 integer,
  var2 real,
  var3 character varying,
  CONSTRAINT nome_tabela_pkey PRIMARY KEY (var1)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE public.nome_tabela
  OWNER TO operacao;
GRANT ALL ON TABLE nome_tabela TO operacao;
GRANT SELECT ON TABLE nome_tabela TO www;

Com relação às colunas, são criadas duas para receber datas com horários no formato “timestamp” (com fuso horário) e que não devem estar vazia (NOT NULL), uma para receber valores inteiros, outra para ponto flutuante (equivalente a float(24)) e a última recebendo caracteres de comprimento variável (varying) sem limite (para limite, usar (n), onde n é o número de caracteres.

O comando “CONSTRAINT” é usado para especificar regras para dados em uma tabela. Nesse caso, especifica a coluna “var1” como chave primária (seus valores nunca devem se repetir na tabela, o que permite ser usada como referência de cada linha).

Monitorar conexões

Uma forma de monitorar as conexões realizadas no Postgres é executar o comando “ps -aux | grep pgsql” no terminal do computador que estiver com o programa instalado – acrescente “wc -l” depois de um pipe no final do comando para saber o total de processos. Outra forma é executar o seguinte comando na database “pgsql” schema “Catalogs”:

SELECT * FROM pg_stat_activity;

A tabela que retorna contém as conexões no momento da consulta – uma descrição de cada coluna é informada no link. A seguinte query revela o máximo de conexões que está autorizado a ter:

SELECT min_val,max_val FROM pg_settings where name='max_connections';

O máximo de consultas geralmente é definido no campo “max_connections” dentro do arquivo “postgresql.conf”. O padrão é de 100 conexões; se ultrapassar do limite estabelecido, será retornada a mensagem “postgres[ID] FATAL: sorry, too many clients already” no log do sistema (arquivo “/var/log/messages”). Somente reiniciando o banco de dados para retomar o serviço e abrir novas conexões:

$ sudo /usr/local/etc/rc.d/postgresql stop
$ sudo /usr/local/etc/rc.d/postgresql start

Se algum script abrir uma conexão sem fechá-las, o Postgres dá um tempo após sua última ação e a fecha por “timeout”.

Compartilhe :)

2 comments

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.