[PostgreSQL] - Listando as 10 maiores tabelas no PostgreSQL

Para listar as maiores tabelas do seu banco de dados, utilize a consulta abaixo:

WITH table_stats AS ( SELECT schemaname, tablename, pg_relation_size(schemaname || '.'|| tablename) as table_size, (pg_total_relation_size(schemaname || '.'|| tablename) - pg_relation_size(schemaname || '.'|| tablename)) as index_size, pg_total_relation_size(schemaname || '.'|| tablename) as total_size FROM pg_tables ) SELECT table_stats.schemaname, table_stats.tablename, pg_size_pretty(table_stats.table_size) as table_size, pg_size_pretty(table_stats.index_size) as index_size, pg_size_pretty(table_stats.total_size) as total_size FROM table_stats WHERE -- ajuste o filtro conforme sua necessidade! table_stats.schemaname = 'public' ORDER BY table_stats.total_size desc, table_stats.index_size desc, table_stats.table_size desc LIMIT 10;

Fonte: http://swebber.me/blog/2013/01/listando-as-10-maiores-tabelas-no-postgresql/

[PostgreSQL] - Tamanho das tabelas no PostgreSQL

Em muitos casos é preciso saber o espaço em que as tabelas do banco de dados estão utilizando em disco, para fins de monitoramento e decisão de estruturação de banco. Para tal tarefa no PostgreSQL o comando abaixo resolve o problema.

SELECT esquema, tabela,
       pg_size_pretty(pg_relation_size(esq_tab)) AS tamanho,
       pg_size_pretty(pg_total_relation_size(esq_tab)) AS tamanho_total
  FROM (SELECT tablename AS tabela,
               schemaname AS esquema,
               schemaname||'.'||tablename AS esq_tab
          FROM pg_catalog.pg_tables
         WHERE schemaname NOT
            IN ('pg_catalog', 'information_schema', 'pg_toast') ) AS x
 ORDER BY pg_total_relation_size(esq_tab) DESC;

a coluna tamanho mostra o tamanho que os registros (tuplas) da tabela está ocupando no disco, e a coluna tamanho_total inclui também os TOASTs e os índices associados à tabela.

Com essa consulta, é possível inclusive montar scripts (em shellscript, perl, python, groovy, etc..) de monitoramento do banco, por exemplo: caso a tabela tal, ou a soma de todas as tabelas chegar a X Mb, envie um email de alerta. Outra utilização seria de geração de indicadores para análise de tendência da expansão do banco.

Fonte: https://arezi.wordpress.com/2008/04/03/tamanho-das-tabelas-no-postgresql/

[PostgreSQL] - Monitorando Locks no Banco de dados

Bom pessoal, segue abaixo consulta para monitorar os locks(Bloqueios de Tabelas e Registros) existentes no seu Banco de Dados PostgreSQL:

  SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.current_query       AS blocking_statement,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.current_query        AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.procpid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.procpid = kl.pid
  WHERE NOT bl.granted;

For PostgreSQL >= 9.2:

  SELECT bl.pid                 AS blocked_pid,
         a.usename              AS blocked_user,
         ka.query               AS blocking_statement,
         now() - ka.query_start AS blocking_duration,
         kl.pid                 AS blocking_pid,
         ka.usename             AS blocking_user,
         a.query                AS blocked_statement,
         now() - a.query_start  AS blocked_duration
  FROM  pg_catalog.pg_locks         bl
   JOIN pg_catalog.pg_stat_activity a  ON a.pid = bl.pid
   JOIN pg_catalog.pg_locks         kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
   JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
  WHERE NOT bl.granted;

[PostgreSQL] - Alterando o encoding de um banco

Bem, essa dica não serve para converter um banco já existente de UTF-8 para LATIN1, para isso existem outras técnicas. A idéia apresentada aqui é para quando você tem um Dump LATIN1 e precisa restaurar em um novo servidor com o mesmo encoding, mas  o Locale de seu Sistema Operacional não permite que você crie um banco LATIN1.

O erro de Locale aparece quando você executa:

# CREATE DATABASE xpto ENCODING ‘latin1′ TEMPLATE template0;
ERROR: encoding “LATIN1″ does not match locale “pt_BR.UTF-8″
DETAIL: The chosen LC_CTYPE setting requires encoding “UTF8″.

Ocorre devido ao Locale estar configurado para UTF-8, você pode corrigir o locale, mas também pode simplesmente criar o banco em UTF-8 e depois alterar para LATIN1

# CREATE DATABASE xpto TEMPLATE template0;

Assim você acabou de criar o banco xpto com encoding UTF-8, para conferir basta dar um \l no PSQL.

Agora para alterar para LATIN1 basta executar:
# update pg_database set encoding = pg_char_to_encoding(‘LATIN1′) where datname = ‘xpto’;

Pronto, confira novamente com \l que o encoding já esta alterado.

Agora é só restaurar seu dump.

Referencia:
http://jf.eti.br/alterando-o-encoding-de-um-banco-postgresql/

[PostgreSQL] - Script para Pré-Configuração do Linux

Bom pessoal,

Abaixo, Script de pré-configuração de SO Linux :

#!/bin/bash
# insere paramentros no /etc/sysctl.conf para o postgres
echo ' ' >> /etc/sysctl.conf
echo '# postgres' >> /etc/sysctl.conf
echo 'kernel.shmmni = 4096' >> /etc/sysctl.conf
echo 'kernel.sem = 250 32000 100 128' >> /etc/sysctl.conf
echo 'net.ipv4.ip_local_port_range = 9000 65500' >> /etc/sysctl.conf
echo 'net.core.rmem_default = 1048576' >> /etc/sysctl.conf
echo 'net.core.rmem_max = 1048576' >> /etc/sysctl.conf
echo 'net.core.wmem_default = 262144' >> /etc/sysctl.conf
echo 'net.core.wmem_max = 262144' >> /etc/sysctl.conf

# recarrega confs do /etc/sysctl.conf
sysctl -p

# insere limits de utilizacao do postgres no SO
echo ' ' >> /etc/security/limits.conf
echo '# postgres' >> /etc/security/limits.conf
echo 'postgres  soft    nofile  1024' >> /etc/security/limits.conf
echo 'postgres  hard    nofile  65536' >> /etc/security/limits.conf

[PostgreSQL] - Checklist de Performance

Cinco Princípios de Hardware para Configurar o seu Servidor PostgreSQL

Discos > RAM > CPU

Se você vai gastar dinheiro em um servidor PostgreSQL, gaste em arranjos de discos de alta performance e tenha processadores medianos e uma memória adequada. Se você tiver um pouco mais de dinheiro, adquira mais RAM. PostgreSQL, como outros SGDBs que suportam ACID, utilizam E/S muito intensamente e é raro uma aplicação utilizar mais a CPU do que a placa SCSI (com algumas exceções, claro). Isto se aplica tanto a pequenos como grandes servidores; obtenha uma CPU com custo baixo se isso permitir você comprar uma placa RAID de alta performance ou vários discos.

Mais unidades de discos == Melhor

Tendo múltiplos discos, o PostgreSQL e a maioria dos sistemas operacionais irão paralelizar as requisições de leitura e gravação no banco de dados. Isto faz uma enorme diferença em sistemas transacionais, e uma significativa melhoria em aplicações onde o banco de dados inteiro não cabe na RAM. Com os tamanhos mínimos de discos (72GB) você será tentado a utilizar apenas um disco ou um único par espelhado em RAID 1; contudo, você verá que utilizando 4, 6 ou até 14 discos irá render um impulso na performance. Ah, e SCSI é ainda significativamente melhor em fluxo de dados em BD que um IDE ou mesmo um Serial ATA.

Separe o Log de Transações do Banco de Dados:

Assumindo que você já investiu dinheiro num arranjo com tamanho decente num conjunto de discos, existe um monde de opções mais inteligentes do que jogar tudo num único RAID. De inicio coloque o log de transações (pg_xlog) no seu próprio recurso de discos (um arranjo ou um disco), o que causa uma diferença de cerca de 12% na performance de bancos de dados com grande volume de gravações. Isto é especialmente vital em pequenos sistemas com discos SCSI ou IDE lentos: mesmo em um servidor com dois discos, você pode colocar o log de transações sobre o disco do sistema operacional e tirar algum benefício.

RAID 1+0/0+1 > RAID 5:

RAID 5 com 3 discos tem sido um desafortunado padrão entre vendedores de servidores econômicos. Isto possibilita a mais lenta configuração de discos possível para o PostgreSQL; você pode esperar pelo menos 50% a menos de velocidade nas consultas em relação ao obtido com discos SCSI normais. Por outro lado, foque em RAID 1 ou 1+0 para um conjunto de 2, 4 ou 6 discos. Acima de 6 discos, o RAID 5 começa a ter uma performance aceitável novamente, e a comparação tende a ser bem melhor com base na sua controladora individual. No entanto, o mais importante, usar uma placa RAID barata pode ser um risco; é sempre melhor usar RAID por software do que um incorporado numa placa Adaptec que vem com seu servidor.

Aplicações devem rodar bem junto:

Outro grande erro que eu vejo em muitas organizações e colocar o PostgreSQL em um servidor com várias outras aplicações competindo pelos mesmos recursos. O pior caso é colocar o PostgreSQL junto com outros SGDBs na mesma máquina; ambos bancos de dados irão lutar pela banda de acesso aos discos e o cache de disco do SO, e ambos vão ter uma performance pobre. Servidores de arquivo e programas de log de segurança também são ruins. O PostgreSQL pode compartilhar a mesma máquina com aplicações que utilizam principalmente CPU e RAM intensamente, como o Apache, garantindo que exista RAM suficiente.

[MySQL] - Migrando MySQL para PostgreSQL

Bom pessoal, segue abaixo instruções para migração de bancos de dados MySQL para PostgreSQL com a ferramenta da EnterpriseDB.

Link para download da Ferramenta:

https://www.dropbox.com/s/0en05h9goejgapz/EnterpriseDB-MigrationToolkit.zip?dl=0

Podemos também fazer o download pelo site da EnterpriseDB:
http://www.enterprisedb.com/products-services-training/products-overview/postgres-plus-solution-pack/migration-toolkit

Migra Somente estrutura do Banco de dados sem chaves estrangeiras

cmd>runMTK.bat -schemaOnly -allTables -constraints -skipFKConst -sourcedbtype mysql employees

Migra somente os dados através de lotes de dados

cmd>runMTK.bat -dataOnly -fetchSize 1 -sourcedbtype mysql employees

Migra todo banco de dados com constraints e dados

cmd>runMTK.bat -fetchSize 1 -sourcedbtype mysql employees

-------

Configuração do arquivo "toolkit.properties":

SRC_DB_URL=jdbc:mysql://127.0.0.1/employees
SRC_DB_USER=root
SRC_DB_PASSWORD=teste

TARGET_DB_URL=jdbc:postgresql://localhost:5432/exemplo
TARGET_DB_USER=postgres
TARGET_DB_PASSWORD=teste

-------

Dentro da pasta do aplicativo, existe um arquivo chamado "mtk-readme.txt" que possui o help do utilitário.

-------

OBS:

Tendo o Java já instalado na sua maquina copie todos os .jar da pasta lib para a pasta C:\Program Files (x86)\Java\jre6\lib\ext ou lib\ext dentro da sua versão do Java que está instalada.

[SQL] - BD de Cep 2014 para MySQL, PostgreSQL e Oracle

Bom pessoal, venho compartilhar a base de CEP 2014(17/01/2014) do Brasil em vários bancos de dados para facilitar o cadastro de endereçamento em diversa aplicações.

Segue abaixo, link para download:
https://www.dropbox.com/s/78zuhdotwdqr4kb/banco_de_dados_cep_17_01_2014.rar?dl=0

Espero que possa ajudar desenvolvedores que precisem de uma base de dados de endereçamento atualizada.

[SQL] - BD de Municípios IBGE 2013 e 2014 ( Oracle, MySQL, PostgreSQL e MS SQL Server)

Bom pessoal, venho compartilhar base de municípios do IBGE 2013 e 2014 atualizada para diversos bancos de dados, sendo Oracle, MySQL, PostgreSQL e MS SQL Server.

Segue abaixo, link para download:
https://www.dropbox.com/s/we4vis6p96cpkux/municipio_ibge.zip?dl=0

Espero que possa ajudar.

Transações no PostgreSQL

O que são transações?

Transações são conjuntos de instruções enviadas ao banco de dados que devem ser tratadas com uma única operação. Ou o servidor realiza tudo ou não realiza nada. O exemplo clássico usado em 110% dos cursos de bancos de dados é a famosa transação financeira de transferência de fundos. Imagine que Mônica deseje transferir 100,00 reais para a conta de Cebolinha. Esta operação na realidade se divide em duas. Um débito na conta de Mônica e um crédito de mesmo valor na conta de Cebolinha (a ordem não interfere). Agora, se logo após o débito na primeira conta, o servidor sair do ar antes de que possa executar a segunda. Para onde foram os 100,00 reais?
Existem quatro características que os SGBDs devem garantir se pretendem lidar com transações com segurança. O conjunto dessas características é conhecido como ACID (atomicity, consistency, isolation, durability). Vejamos o que significam:

[PostgreSQL] - Consultando e eliminando sessões ativas

Bom pessoal vou mostrar como listar/matar as sessões ativas no PostgreSQL . O sql a seguir lista todas as sessões ativas.
   
select datname,
       procpid,
       usename,
       application_name,
       client_addr,
       client_hostname,
       backend_start
  from pg_stat_activity

Obs: A coluna procid foi renomeada para pid a partir da versão 9.2 do PostgreSQL

Com a lista de usuário em mãos, podemos optar por "matar" a sessão de algum usuário ativo, para isto basta executar o comando abaixo, substituindo o "procpid' pelo valor retornado da consulta anterior.
   
select pg_terminate_backend(procpid);

E para eliminar todas as conexões ativas, menos a conexão atual.
   
SELECT pg_terminate_backend(procpid)
   FROM pg_stat_activity
  WHERE procpid <> pg_backend_pid();

Fonte:
http://fabriciodev.blogspot.com.br/2012/03/consultando-e-eliminando-sessoes-ativas_20.html

[PostgreSQL] - Formatação de Data e Hora com as funções TO_CHAR e TO_DATE

Dados do tipo data e hora apresentam muitas variações de formatação. Para o usuário as datas se apresentam como campos textuais, mas para o SGBD, são tipos de dados específicos, com características próprias, representando valores temporais. Para trabalhar com valores deste tipo, os sistemas fazem frequentemente conversões entre esses dois tipos.

Este post apresenta o uso das funções TO_DATE e TO_CHAR para formatar e tratar datas e horas.

* TO_CHAR - Formatando campos do tipo data para texto.

- Exemplo 1: Formatação padrão

postgres=# SELECT TO_CHAR(current_date, 'DD-MM-YYYY');
  to_char 
------------
 17-06-2013
(1 registro)
- Exemplo 2: Formatação padrão utilizando o separador "/"

postgres=# SELECT TO_CHAR(current_date, 'DD/MM/YYYY');
  to_char 
------------
 17/06/2013
(1 registro)
- Exemplo 3: Alterando a ordem de visualização do mês, dia e ano apresentados.

postgres=# SELECT TO_CHAR(current_date, 'DD-MM-YYYY'), TO_CHAR(current_date, 'MM-DD-YYYY'), TO_CHAR(current_date, 'YYYY-MM-YY');
  to_char   |  to_char   |  to_char 
------------+------------+------------
 17-06-2013 | 06-17-2013 | 2013-06-13
(1 registro)
- Exemplo 4: Separando mês, dia e ano em campos distintos.

postgres=# SELECT TO_CHAR(current_date, 'DD') AS DIA, TO_CHAR(current_date, 'MM') AS MES, TO_CHAR(current_date, 'YYYY') AS ANO;
 dia | mes | ano  
-----+-----+------
 17  | 06  | 2013
(1 registro)
- Exemplo 5: Apresentando o mês e o dia por extenso.

postgres=# SELECT TO_CHAR(current_date, 'DAY') AS DIA, TO_CHAR(current_date, 'MON') AS MES;
    dia    | mes
-----------+-----
 MONDAY    | JUN
(1 registro)
- Exemplo 6: Apresentando a hora do sistema formatada.

postgres=# SELECT TO_CHAR(current_timestamp, 'HH24:MI:SS');
 to_char
----------
 07:29:13
(1 registro)- Exemplo 7: Separando hora, minuto e segundos em camops distintos.

postgres=# SELECT TO_CHAR(current_timestamp, 'HH24'), TO_CHAR(current_timestamp, 'MI'), TO_CHAR(current_timestamp, 'SS');
 to_char | to_char | to_char
---------+---------+---------
 07      | 29      | 13
(1 registro)
- Exemplo 8: Usando TO_CHAR para inserção e atualização de dados.

postgres=#
postgres=# CREATE TABLE char_date_tst (momento varchar(10));
CREATE TABLE
postgres=# INSERT INTO char_date_tst (momento) VALUES (TO_CHAR(current_date, 'MM-DD-YYYY'));
INSERT 0 1
postgres=# UPDATE char_date_tst SET momento = '01-05-2010' WHERE momento = TO_CHAR(current_date, 'MM-DD-YYYY');
UPDATE 1
postgres=# SELECT * FROM char_date_tst;
  momento 
------------
 01-05-2010
(1 registro)* TO_DATE - Formatando campos do tipo texto para data.

- Exemplo 1: Usando TO_DATE para data em formato DD-MM-YYYY.

postgres=# SELECT TO_DATE('30-04-2010', 'DD-MM-YYYY');
  to_date 
------------
 2010-04-30
(1 registro)
- Exemplo 2: Usando TO_DATE para data em formato MM-DD-YYYY.

postgres=# SELECT TO_DATE('04-30-2010', 'MM-DD-YYYY');
  to_date 
------------
 2010-04-30
(1 registro)
- Exemplo 3: Usando TO_DATE para data com separador "/"

postgres=# SELECT TO_DATE('04/30/2010', 'MM/DD/YYYY');
  to_date 
------------
 2010-04-30
(1 registro)
- Exemplo 4: Teste de igualdade entre datas de formatos distintos usando TO_DATE.

postgres=# SELECT TO_DATE('04-30-2010', 'MM-DD-YYYY') = TO_DATE('30-04-2010', 'DD-MM-YYYY') AS IGUALDADE;
 igualdade
-----------
 t
(1 registro)
- Exemplo 5: Usando TO_DATE para inserção e atualização de dados.

postgres=# CREATE TABLE date_tst (momento date);
CREATE TABLE
postgres=# INSERT INTO date_tst VALUES (TO_DATE('04-30-2010', 'MM-DD-YYYY'));
INSERT 0 1
postgres=# UPDATE date_tst SET momento = momento + 1 WHERE momento = TO_DATE('04-30-2010', 'MM-DD-YYYY');
UPDATE 1
postgres=# SELECT * FROM date_tst;
  momento 
------------
 2010-05-01
(1 registro)* Considerações Finais

Os exemplos acima não cobrem todas as possibilidades, mas podem ajudar nas principais operações que lidem com a formatação de datas e horas no postgresql. A cláusula CAST é uma alternativa para conversão entre estes tipos de dados, mas não realiza formatações sobre os mesmos.

Fonte:
http://postgresqlbr.blogspot.com.br/2013/06/formatacao-de-data-e-hora-com-as.html

[PostgreSQL] - Os Comandos BEGIN, COMMIT e ROLLBACK

O comando BEGIN inicia um bloco de comandos SQL que fazem parte de uma transação. Os comandos realizados após o este comando só são persistidos em disco e seus resultados só são apresentados aos demais usuários do banco após a efetivação com o comando COMMIT.

A transação é finalizada pelo comando COMMIT, o qual dispara a efetivação da transação no banco de dados e torna visível para os demais usuários os resultados da execução dos seus comandos.

O comando ROLLBACK aborta a transação que está em andamento, impedindo que os as alterações nos dados nela realizadas sejam persistidos no banco de dados.

Sintaxe:

BEGIN [ WORK | TRANSACTION ] [ transaction_mode [, ...] ]

onde o transaction_mode é um dos listados abaixo:

ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED | READ UNCOMMITTED }
READ WRITE | READ ONLY

Obs.: os transaction modes serão discutidos em maior profundidade ao se detalhar o comando SET TRANSACTION.

COMMIT [ WORK | TRANSACTION ]


ROLLBACK [ WORK | TRANSACTION ]

Exemplos:

1 - Sintaxe mais simples com BEGIN e COMMIT

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT;

2 - Sintaxe mais simples com BEGIN e COMMIT. A cláusula WORK não tem efeito é é mantida por compatibilidade.

BEGIN WORK;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT WORK;

3 - Sintaxe mais simples com BEGIN e COMMIT. A cláusula TRANSACTION não tem efeito é é mantida por compatibilidade.

BEGIN TRANSACTION;
insert into tstdel values (1,'teste', current_timestamp);
insert into tstdel values (2,'teste2', current_timestamp);
SELECT * FROM tstdel;
COMMIT TRANSACTION;

4 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK;

5 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados. A cláusula WORK não tem efeito e é mantida por compatibilidade.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK WORK;

6 - A transação abaixo é abortada com o ROLLBACK. A inserção realizada não é gravada no banco de dados. A cláusula TRANSACTION não tem efeito e é mantida por compatibilidade.

BEGIN;
insert into tstdel values (1,'teste', current_timestamp);
ROLLBACK TRANSACTION;

[PostgreSQL] - Trate com BLOBs e CLOBs Diretamente no PSQL!

Armazenar grandes objetos binários (Binary Large Objects - BLOBs), tais como vídeos, imagens e arquivos PDF, e grandes objetos textuais (Character Large Objects - CLOBs) em um banco de dados é uma atividade que pode apresentar dificuldade maior que a vivenciada em se lidar com tipos de dados tradicionais.

Cada SGBD apresenta sua padronização e o PostgreSQL apresenta seus métodos de armazenamento, exclusão e recuperação de BLOBs e CLOBS. A portabilidade entre SGBDs não costuma ser fácil neste caso.

 Lidar com Large Objects pode não ser tão trivial!

Um recurso bastante simples para lidar com estes grandes objetos dentro do postgres é disponibilizado de dentro do psql, objeto deste post.

Quatro funções permitem operações sobre large objects, viabilizando a sua criação, recuperação, consulta e exclusão:
- \lo_import ARQUIVO [COMENTÁRIO] - Importa um arquivo do filesystem (levando em conta a search_path) para o postgresql, atribuindo ao mesmo um identificador de objeto (OID) e um comentário, quando fornecido este parâmetro
- \lo_list - Apresenta uma listagem dos objetos binários importados
- \lo_export OIDLOB ARQUIVO - Exporta um BLOB ou CLOB do postgres para o filesystem sem removê-lo do postgresql, referenciando-o pelo seu OID
- \lo_unlink OIDLOB - Elimina um large object do postgresql com base no seu OID.

Abaixo, sem a preocupação de esgotar este tema, a demonstração do tratamento de BLOBs e CLOBs no postgres via psql.

* Listagem de grandes objetos (large objets) armazenados

postgres-# \lo_list
Objetos grandes
 ID | Descrição 
----+-----------
(0 registro)

* Recuperação dos caminhos da Serch_path, utilizados para recuperar arquivos para o psql




postgres=# SHOW search_path;
  search_path   
----------------
 "$user",public
(1 registro)





* Importação de Arquivos

postgres=# \lo_import arqdoc.doc
lo_import 16400 (OID DO NOVO ARQUIVO IMPORTADO)
postgres=# \lo_import arqpdf.doc 'Arquivo Binario PDF'
não pôde abrir arquivo "arqpdf.doc": Arquivo ou diretório não encontrado
postgres=# \lo_import arqpdf.pdf 'Arquivo Binario PDF'
lo_import 16401
postgres=# \lo_import arqpng.png 'Arquivo PNG'
lo_import 16402
postgres=# \lo_list (LISTAGEM DE ARQUIVOS IMPORTADOS)
       Objetos grandes
  ID   |      Descrição    
-------+---------------------
 16400 |
 16401 | Arquivo Binario PDF
 16402 | Arquivo PNG
(3 registros)

* Exclusão de arquivo previamente importado

postgres=# \lo_unlink 16401
lo_unlink 16401
postgres=# \lo_list
   Objetos grandes
  ID   |  Descrição  
-------+-------------
 16400 | 
 16402 | Arquivo PNG
(2 registros)









* Exportação de arquivo do postgresql para o filesystem

postgres=# \lo_import novofile.pdf
lo_import 16403
postgres=# \lo_export 16403 testeexport.pdf (gera o arquivotesteexport.pdf no filesystem)

* Importação de CLOB

postgres=# \lo_import miniclob.txt 'Arquivo CLOB'
lo_import 16405
postgres=# \lo_list
   Objetos grandes
  ID   |  Descrição   
-------+--------------
 16400 | 
 16402 | Arquivo PNG
 16403 | 
 16404 | 
 16405 | Arquivo CLOB
(5 registros)





* Considerações Finais


Prefiro trabalhar com large objects no filesystem, ao invés de registrá-los no próprio banco de dados.O acréscimo de mais uma camada reduz a portabilidade e a velocidade de acesso, mas tende a aumentar de certa forma a segurança e a facilitar operações de backup e recovery.

Caso se deseje utilizar este recurso na aplicação, utilizando as funções dentro do SQL, o postgresql apresenta o recurso das Server-Side Functions, bastante similar e que oferece maior controle.

E você, como lida com os seus BLOBs e CLOBs?

Fonte:
http://postgresqlbr.blogspot.com.br/2013/04/trate-com-blobs-e-clobs-diretamente-no.html