How to migrate BLOBS between DB with less steps???

Hello all!

I would like to know if there is a more simple way to migrate BLOB between diferent databases?
I have done it in 7 steps:

1. Export source database
pg_dump -o DB1 > /tmp/DB1.sql

2. Create target database

psql DB1

> create database db2

3. Change to source DB and export OID naming them with the primary key.

\c DB1
select lo_export(proy_foto.foto,'/tmp/fotos/' || numero ) from proy_foto;

4. Create target DB as a copy of source DB.

psql DB2 < /tmp/DB1.sql

5. Change to target DB.
\c DB2

6. Create a temp table with just 1 column ( the primary key of source table) in order to make an special update later.

create temp table kk as select numero from proy_foto;

7. Update BLOB column en target DB with the previous exported rows from source DB.

update proy_foto set foto = lo_import('/tmp/fotos/' || proy_foto.numero) from kk where kk.numero=proy_foto.numero;

------------
Evelio Martínez
Testanet. Dept. desarrollo software.
Av. Reino de Valencia, 15 - 5
46005 Valencia (Spain)
Tel: +34 96 395 90 00
Fax: +34 96 316 23 19
http://www.testanet.com