Hello.
Like a lot of users I need to clone an existing SQLlite db to new
PostgreSQL db.
I've read this post in web2py-developers by Alan Etkin:
https://groups.google.com/forum/#!searchin/web2py-developers/sqlite$20postgres/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ
However I've tried to use this script and my PosgreSQL db still empty.
My db:
dbsq = DAL('sqlite://storage.sqlite',pool_size=1,check_reserved=['all'])
dbpg = DAL('postgres://user:pass@localhost:5432/postg_myapp',pool_size=1,
check_reserved=['all'])
My controller:
def migrate_to_pg():
migrate(dbsq, dbpg)
return dict()
def disable_triggers(pgdb):
for tablename in pgdb.tables():
pgdb.executesql("ALTER TABLE %s DISABLE TRIGGER ALL;" % \
tablename)
def enable_triggers(pgdb):
for tablename in pgdb.tables():
pgdb.executesql("ALTER TABLE %s ENABLE TRIGGER ALL;" % \
tablename)
def migrate(sqlitedb, pgdb):
"""Transfer data to PostgreSQL
Arguments:
sqlitedb: the Sqlite db source connection
pgdb: the PostgreSQL db target connection
"""
import logging
setval = "select setval('%s'::regclass"
setval += ", (SELECT MAX(%s) FROM %s));"
logging.debug("Disabling pg triggers temporarily")
disable_triggers(pgdb)
for table in pgdb.tables:
logging.debug("copying %s" % table)
query = pgdb[table]._id > 0
rows = pgdb(query).select()
colnames = [k[k.index(".")+1:] for k in rows.colnames]
logging.debug(pgdb._lastsql)
logging.debug(colnames)
rows = sqlitedb.executesql(pgdb._lastsql)
# clean up table
pgdb.executesql("TRUNCATE %s" % table)
for i, row in enumerate(rows):
logging.debug("inserting", i, len(rows))
sql = "INSERT INTO %s (%s) VALUES (%s)" % (
table,
','.join(colnames),
','.join(["%s" for k in colnames]),
)
logging.debug(sql)
pgdb.executesql(sql, row)
# update serials
sequence = pgdb[table]._sequence_name
if sequence in ["", None]:
sequence = "%s_%s_seq" % (table, colnames[0])
pgdb.executesql(setval % (sequence,
colnames[0],
table))
logging.debug("Re-enabling pg triggers")
enable_triggers(pgdb)
pgdb.commit()
logging.debug("Migration done")
When I run migrate_to_pg I don't have any kind of error but the PosgreSQL
db still empty.
Someone can give me a help, or another solution for this migration?
I think it's necessary for web2py having a tool for this migration, giving
an easy way for fresh web2py users like me that understand that they must
migrate some existing applications to PostgreSQL to do it without headaches.
Thanks, regards.
--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups
"web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to web2py+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
