Dicas de PostgreSQL

Criando banco

**************************************************************************

torne-se root

gutocarvalho@pgsql01:~$ sudo -i

torne-se postgresql

root@pgsql:~$# su postgres

acesse o CLI do postgres

postgres@pgsql01:~$ psql

agora estamos na CLI

postgres=#

Criando usuario com senha

postgres=# CREATE USER fernandao WITH PASSWORD 'senha';

Criando o banco

postgres=# CREATE DATABASE eleicoes_carga;

Dando permissoes ao usuario para acessar o banco

postgres=# GRANT ALL PRIVILEGES ON DATABASE projeto_carga to fernando;

alterando owner do banco

postgres=# ALTER DATABASE projeto_carga OWNER TO fernando;

liberando acesso de uma maquina ao banco

após isto basta liberar o ip o cara no pg_hba.conf, veja um exemplo

# IPv4 local connections:  

host    all         all         127.0.0.1/32          md5  
host    all         all         172.16.10.20/32       md5 # gandalf  

configurando postgres para listar em todas as interfaces

e tenha certeza que o postgresql está rodando em todas as interfaces, veja isso em postgresql.conf

listen_addresses = '0.0.0.0'            # what IP address(es) to listen on;

precisa reiniciar o banco.

dump

dump de um banco inteiro - formato sql puro

pg_dump -U usuario -h ip_servidor nomedobanco > nomedobanco.sql

dump de um banco inteiro - formato binario

pg_dump -U usuario -h ip_servidor -Fc -f banco.dump nome_do_banco

dump de uma tabela do banco

pg_dump -U usuario -h ip_servidor -Fc -t tabela -f tabela.pgdump nome_do_banco

dump de um table de um schema

pg_dump -U postgres -h 127.0.0.1 -Fc -n schema -t tabela -f tabela.pgdump banco

restore

para restaurar um banco com dump em formato SQL

psql -U usuario -h ip_do_servidor -f nomeodabanco.sql nomedobanco

para restaurar um banco com dump em formato binário

pg_restore -U usuario -h ip_servidor -d nome_do_banco arquivo.dump

para restaurar somente os dados:

pg_restore -U usuario -h ip_servidor -a -d nome_do_banco arquivo.dump

para restaurar somente um determinado esquema

pg_restore -U usuario -h ip_servidor -n meu_esquema -d nome_do_banco arquivo.dump

para restaurar apenas os dados de uma tabela especifica

pg_restore -U usuario -h ip_servidor -a -t tabela_especifica -d nome_do_banco arquivo.dump

exemplos

fazendo dump de tabela da radioagencia

pg_dump -Fc -U drupal -h 127.0.0.1 -t drupal_webform -f arquivo -d drupal drupal_webform

restaurando tabela da radioagencia

pg_restore -Fc -U drupal -h 127.0.0.1 -t drupal_webform -d drupal drupal_webform

Observe o formato do DUMP, ele precisa ser tipo C ou tipo F para que o pg_restore consiga recuperar, se for plain text vai dar erro.

dicas soltas

alterando senha do usuario

postgres=#alter user fernando with password 'senha';

tamanho de uma base

dados sem arrendodamento, tamanho do bancos apenas

postgres=# SELECT pg_database_size('geekdb');

arredondando, tamanho do banco apenas

postgres=# SELECT pg_size_pretty(pg_database_size('geekdb'));

tamanho real do banco no disco arredondando

postgres=# SELECT pg_size_pretty(pg_database_size('somedatabase')) As fulldbsize;

tamanho de uma tabela

postgres=# SELECT pg_size_pretty(pg_total_relation_size('drupal_cache'));

nde

tamanho de todas as tabelas

postgres=# SELECT
  relname as "Table",
  pg_size_pretty(pg_total_relation_size(relid)) As "Size",
  pg_size_pretty(pg_total_relation_size(relid) - pg_relation_size(relid)) as "External Size"
  FROM pg_catalog.pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC;
  

tamanho de todos as tabelas e objetos maiores que 64k

postgres=# SELECT
  relname AS objectname,
  relkind AS objecttype,
  reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
  FROM pg_class
  WHERE relpages >= 8
  ORDER BY relpages DESC;
  
  

tamanho da maior tabela para menor

SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;

psql

usando o console

listando bancos

postgres=# \l

conectando a um banco

postgres=# \c nome_do_banco

outros

work mem

Quando algum processo pesado é executado, por exemplo a geracao de indices do drupal ou consultas que usam muito ORDER BY, um diretorio pgsqltemp será criado caso o uso da memoria setado na variavel WORKMEM seja execedido.

Nestes caso o aconselhável é aumentar - perante monitoramento - o parametro WORK_MEM na sessão em que está executando a query ou rodando os indices.

SET WORK_MEM TO "64MB"

Esse valor é por consulta, se colocar um valor muito alto a máquina vai sentar bonito, procure um valor entre 64 e 128, monitorando o servidor para verificar o impacto.