How to migrate BLOBS between DB with less steps??? - Mailing list pgsql-general

From Evelio Martínez
Subject How to migrate BLOBS between DB with less steps???
Date
Msg-id 015301c1688a$1fcba5a0$4ecd72c3@testanet.com
Whole thread Raw
List pgsql-general
 
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

pgsql-general by date:

Previous
From: Stephan Szabo
Date:
Subject: Re: Problem with function
Next
From: Evelio Martínez
Date:
Subject: How to optimize a column type change???