Copy table with an upload field from one database to another without disturbing the files.

[James McGlynn]

Hello, 

I am using web2py on heroku. I have two installed applications using two different postgres databases. I am trying to copy all the data from one of the databases to the other. One of the tables has an upload field which uses s3 as the filesystem. 

I don't want to duplicate the files, I just want the new database to know where the files are and be able to download them from s3. I tried just passing whatever came from db1 to db2 and that didn't error, but then when I download the file it is empty (they are gzips of csv files). I also tried passing a file handle that I got from s3 using boto, but that complained and I also thought that it might duplicate the file on s3, which I don't want. 

What is a good way to do this?

Here is my table definition for application and db 1:

from datetime import datetime

import fs.s3fs, os

myfs = fs.s3fs.S3FS(os.environ['AWS_S3_BUCKET_NAME'],os.environ['AWS_S3_BUCKET_PREFIX'],os.environ['AWS_S3_KEY'], os.environ['AWS_S3_SECRET'])

db.define_table('log_files',

Field('das_id'),

Field('device_id'),

Field('das_id_dev_id'),

Field('log_filename'),

Field('log_file', 'upload'),

Field('date_added','datetime'),

)
db.log_files.log_file.uploadfs=myfs

And here is my table definition for app and db 2 (it's basically the same)

from datetime import datetime

import fs.s3fs, os

myfs = fs.s3fs.S3FS(os.environ['AWS_S3_BUCKET_NAME'],os.environ['AWS_S3_BUCKET_PREFIX'],os.environ['AWS_S3_KEY'], os.environ['AWS_S3_SECRET'])

db.define_table('log_files',

Field('das_id' ,type='string'),

Field('device_id' ,type='string'),

Field('das_id_dev_id' ,type='string'),

Field('log_filename' ,type='string'),

Field('log_file' ,type='upload'),

Field('date_added' ,type='datetime'),

)

db.log_files.log_file.uploadfs=myfs

And here is the python script that I've been playing around with to try and get what I want:

import os, psycopg2, psycopg2.extras, boto

from psycopg2.extensions import AsIs

table_name="log_files"

params=dict(table_name=AsIs(table_name))

# Open the connections to each database

conn_old = psycopg2.connect(os.environ['HEROKU_POSTGRESQL_ONYX_URL'])

conn_new = psycopg2.connect(os.environ['HEROKU_POSTGRESQL_BROWN_URL'])

## Create Curstors for each database

cur_old = conn_old.cursor(cursor_factory = psycopg2.extras.DictCursor)

cur_new = conn_new.cursor()

## Select files from the source db and fetch them. 

cur_old.execute("SELECT * FROM %(table_name)s WHERE id = 4451744", params)

bucket=connect_to_s3()

for rec_old in cur_old:

columns = rec_old.keys()

values = rec_old

insert_statement = ('INSERT INTO %(table_name)s ( das_id, device_id, das_id_dev_id, log_filename, log_file, date_added )'

'VALUES ( %(das_id)s, %(device_id)s, %(das_id_dev_id)s, %(log_filename)s, %(log_file)s, %(date_added)s )'

)

params=dict(

table_name = AsIs(table_name),

das_id = values['das_id'],

device_id = values['device_id'],

das_id_dev_id = values['das_id_dev_id'],

log_filename = values['log_filename'],

date_added = values['date_added'],

## Help!

log_file         = get_key(bucket, values['log_file']),

)

try:

cur_new.execute(insert_statement, params)

conn_new.commit()

except psycopg2.IntegrityError:

conn_new.rollback()

#close new connection

cur_new.close()

conn_new.close()

# close old connection

cur_old.close()

conn_old.close()

[Anthony]

Why can't you just copy the existing values in the log_file field?

Anthony