[Tópico Único] - Dicas e Links Úteis

[Marcos Guedes]

Download do Postgre:
http://www.enterprisedb.com/products/pgdownload.do#windows

[Marcos Guedes]

Funções no PostgreSQL:
http://www.linux-magazine.com.br/images/uploads/pdf_aberto/LM07_postgresql.pdf

[hugo]

Replicação bem simples de dados entre duas bases:

[hugo]

Gerenciando memoria em linux para banco postgresql

http://keniamilene.wordpress.com/2007/09/02/gerenciamento-de-memoria-em-bancos-postgresql/

[hugo]

Saber quantidade de registros no banco inteiro:

Código:

SELECT sum(C.reltuples)::int
FROM pg_class C
WHERE c.relkind = ‘r’::”char”

Saber quantidade de registros por tabela:

Código:

SELECT n.nspname AS schemaname, c.relname AS tablename, C.reltuples::int AS Registros
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

Saber quantidade de registros por schema:

Código:

SELECT n.nspname, sum(c.reltuples)::int
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
GROUP BY n.nspname

Saber quantidade de registros de um determinado schema

Código:

SELECT n.nspname, sum(c.reltuples)::int
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’::”char”
AND nspname = ‘aaa’
GROUP BY n.nspname

Credito:
Kenia Milene
http://keniamilene.wordpress.com/category/categorias/banco-de-dados/postgresql/

[hugo]

Atencao
isto ai vai sem o CODE porque com ele caracteres "||" estao sendo mostrados
erroneamente


– Tamanho por tabela

Código:

SELECT n.nspname AS schemaname, c.relname AS tablename, C.reltuples::int AS Registros, pg_size_pretty(pg_relation_size(n.nspname ||’.'||c.relname)) as Tamanho
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace
WHERE c.relkind = ‘r’::”char”
–AND n.nspname = ‘acc’
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,’pg_toast’,'postgres’,'publico’,'public’)
ORDER BY n.nspname

– Tamanho por schema

Código:

SELECT n.nspname, sum(c.reltuples)::int as Registros,
pg_size_pretty(pg_relation_size(n.nspname ||’.'||c.relname)) as Tamanho
FROM pg_class c
LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ‘r’::”char”
AND nspname NOT IN
(‘dbateste’,'information_schema’,'pg_catalog’,'pg_temp_1′,
‘pg_toast’,'xmg’,'postgres’,'publico’,'public’)
GROUP BY n.nspname, c.relnamespace, c.relname

– Tamanho por TableSpace

Código:

SELECT spcname, pg_size_pretty(pg_tablespace_size (spcname))
FROM pg_tableSpace;

– Tamanho por Banco

Código:

SELECT datname, pg_size_pretty(pg_database_size(datname))
FROM pg_database;

Creditos:
Kenia Milene
http://keniamilene.wordpress.com/category/categorias/banco-de-dados/postgresql/

[hugo]

Conjunto de Dicas simples sobre PostgreSQL, obtidas da lista de usuários pgbr-geral e de outras fontes diversas na internet.

1. Para extrair o dia da data.

Código:

SELECT cast(to_char(data,’DD’) as int2) as dia FROM tabela;

ou

Código:

SELECT cast(to_char(data,’DD’) as char(2)) as dia FROM tabela;

—————————————————————————————
2. Para encontrar o espaço ocupado pelas tabelas.

Código:

SELECT relname, relpages*8 as Kb FROM pg_class WHERE relname = ‘tabela’;

ou

Código:

SELECT relname, (relpages*8)/1024 as MB FROM pg_class WHERE relname = ‘tabela’;

Obs.: A coluna relpages esta sendo multiplica por 8 porque cada page tem o equivalente a 8k, entao é só multiplicar para saber o tamanho em kb.

—————————————————————————————
3. Para encontrar tabelas com parte do nome. Igual a um grep do linux.

Código:

SELECT * FROM pg_tables WHERE tablename like ‘tabela%”

—————————————————————————————

4. Para encontrar triggers com parte do nome. Igual a um grep do linux.

Código:

SELECT tgname, tgconstrname FROM pg_triggers WHERE tgargs like ‘%tabela%’ ;

—————————————————————————————

5. Para visualizar as consultas correntes no PostgreSQL.

Código:

SELECT datname, procpid, current_query FROM pg_stat_activity ORDER BY procpid;

A partir da versão 8.2 o campo client_addr está disponível. Com isso podemos ver o IP também.

Código:

SELECT datname, client_addr, procpid, current_query FROM pg_stat_activity ORDER BY procpid;

—————————————————————————————

6. Para saber as tabelas que tem permissão para SELECT (uma ROLE para GRANT SELECT vinculada).

Código:

SELECT table_schema, table_name,grantee
FROM information_schema.role_table_grants
WHERE grantee = ‘bxs’ AND
privilege_type = ‘SELECT’;

—————————————————————————————
7. Para saber quantos conexões tem online em cada base.

Código:

SELECT datname, numbackends FROM pg_stat_database;

—————————————————————————————
8. Para fazer o ORDER BY colocar as palavras com acento junto as sem acento. Não considerar o acento na ordenação.

Código:

SELECT * FROM pessoa ORDER BY lower(translate(nomepessoa, ‘ÁÀÂÃÄáàâãäÉÈÊËéèêëÍÌÎÏíìîïÓÒÕÔÖóòôõöÚÙÛÜúùûüÇç’, ‘AAAAAaaaaaEEEEeeeeIIIIiiiiOOOOOoooooUUUUuuuuÇc’));

—————————————————————————————
9. Para não considerar os acentos no LIKE.

Código:

Use to_ascii(sua_coluna) LIKE to_ascii(’seu_filtro’)

SELECT * FROM cliente WHERE to_ascii(nome) ilike to_ascii(‘Águia%’)

—————————————————————————————

10. Para verificar as maiores tabelas e índices da base.

Código:

SELECT relname, (relpages * 8)/1024 AS size_mb
FROM pg_class ORDER BY relpages DESC LIMIT 20;

—————————————————————————————

11. Como parar e subir o BD PostgreSQL.

Código:

$ pg_ctl stop -m smart

smart –> quit after all clients have disconnected
fast –>quit directly, with proper shutdown
immediate –> quit without complete shutdown; will lead to recovery on restart

$ pg_ctl start -D /var/lib/postgres/data

—————————————————————————————

12. Para realizar um REINDEX DATABASE numa base específica.

Código:

$ pg_ctl stop -m fast; postgres -O -P -D /home/postgres/data/ base_especifica << !
reindex database base_especifica;
!

—————————————————————————————
13. Para subtrair timestamps (resultado em dias e horas/minutos/segundos).

Código:

SELECT timestamp ‘2009-06-24 01:55:23′ – timestamp ‘2009-06-24 01:49:46′;

—————————————————————————————

14. Para pegar os segundos de uma determinada hora.

Código:

SELECT extract(epoch from CAST(‘00:05:37′ AS time));

—————————————————————————————

15. Para retornar o CPF ou CNPJ com a respectiva mascara.

Código:

SELECT to_char(01234567890,’000″.”000″.”000″-”00′) AS cpf;

SELECT to_char(12345678000199,’00″.”000″.”000″/”0000″-”00′) AS cnpj;

—————————————————————————————-

16. Para saber quando foi rodado o ultimo vacuum na tabela.

Código:

SELECT relname, last_vacuum, last_autovacuum

FROM pg_stat_all_tables

WHERE schemaname = ‘public’ ORDER BY last_vacuum desc;

ou

Código:

SELECT relname, last_vacuum, last_autovacuum, last_analyze

FROM pg_stat_user_tables

WHERE schemaname = ‘public’ ORDER BY last_vacuum desc;

—————————————————————————————
17. Para saber quais tabelas não tem Comentário.

Código:

SELECT pg_class.relname FROM pg_namespace

JOIN pg_class ON pg_namespace.oid=pg_class.relnamespace

WHERE pg_namespace.nspname IN (‘public’)

AND pg_class.relkind=’r’

AND pg_class.oid NOT IN (SELECT objoid FROM pg_description);

—————————————————————————————

18. Para evitar novas conexões ao Banco de Dados específico.

Código:

BEGIN;
UPDATE pg_database SET datallowconn=FALSE WHERE datname=’nome_do_banco’;
COMMIT;


Para voltar a aceitar conexões:

Código:

BEGIN;
UPDATE pg_database SET datallowconn=TRUE WHERE datname=’nome_do_banco’;
COMMIT;

Também podemos utilizar o comando abaixo, mas quem for super-usuário poderá se conectar.

Código:

ALTER DATABASE foo CONNECTION LIMIT 0

—————————————————————————————

19. Para fazer backup e restaurar com pg_dump e psql.

Backup:

Código:

pg_dump -C database > arquivo.dump

Restaurar:

Código:

psql arquivo.dump

—————————————————————————————
20. Para saber quantas BUSCAS/SELECTs já foram feitos a uma tabela.

Código:

SELECT sum(seq_scan+idx_scan) AS total_de_leituras
FROM pg_stat_user_tables
WHERE relname=’nome_da_tabela’;

Obs.: Um select com um JOIN considera duas BUSCAS.

—————————————————————————————

21. Para saber as configurações usadas pelo Banco.

Código:

SELECT name, setting FROM pg_settings;

Para alterar na sessão atual

Código:

SET work_mem TO “10MB”;

Para voltar o default

Código:

SET work_mem TO DEFAULT;

creditos
http://valdeka.wordpress.com/2009/12/07/dicas-postgresql/

[hugo]

Concedendo ou Revogando Privilégios em Colunas


Antes da versão 8.4 só era possível fazer o controle de acesso de forma mais granular a uma tabela criando uma view especificando as colunas permitidas.

Agora pode dar ou tirar privilégios da seguinte forma:

Código:

GRANT SELECT (coluna1,coluna2,…) ON tabela TO role;

Para o exemplo prático a criação de role:

Código:

CREATE ROLE limitado LOGIN NOSUPERUSER;

Só terá acesso às colunas campoX e campoY, sendo de somente leitura da tabela tb_exemplo:

Código:

GRANT SELECT (campoX,campoY) ON tb_exemplo TO limitado;

De forma análoga será revogado (retirado) o acesso à coluna “campoX”:

Código:

REVOKE SELECT (campoX) ON tb_exemplo FROM limitado;

http://juliano777.wordpress.com/2009/12/15/concedendo-ou-revogando-privilegios-em-colunas/

[hugo]

adicionar apostrofo no registro

Código:

INSERT INTO “Teste”(
“Nome”)
VALUES (‘d”agua’);