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
