I was referring to the script from Alan Etkin in your first post. It logs every step - what did the log steps say when you ran it?
On Tuesday, March 31, 2015 at 3:19:13 PM UTC-4, Gael Princivalle wrote: > > I'm sorry Willoughby but I don't understand your question. > > With this csv export/import the problem is that id's change. > > For example if you take a look to the auth_user table in my previous post, > table auth_membership will have wrong auth_user id's: > auth_membership.id auth_membership.user_id > <https://dev.tasko.it/mompala/appadmin/select/db?orderby=auth_membership.group_id>1 > > 3 (John) > 2 7 (Sally) > > And in each application that I have most of them are in relation, so > that's a problem. > I've take a look to pgloader but it seems complicated to make a migration. > If someone have a detailed example it could be useful. > > Il giorno martedì 31 marzo 2015 20:52:18 UTC+2, Willoughby ha scritto: >> >> Well it has a bunch of log statements, were all of those OK? >> >> On Tuesday, March 31, 2015 at 2:19:41 PM UTC-4, Gael Princivalle wrote: >>> >>> Thanks Ron but:with CSV export: >>> auth_user_id user >>> 3 John >>> 7 Sally >>> >>> And then you import your CSV file you will have: >>> auth_user_id user >>> 1 John >>> 2 Sally >>> >>> And so all relations between tables will be broken. >>> Someone have a solution? >>> >>> Il giorno martedì 31 marzo 2015 15:10:53 UTC+2, Ron Chatterjee ha >>> scritto: >>>> >>>> As an alrenative. you can upload a csv file into web2py. Not a solution >>>> if you want to do things dynamically and all the time. But you can use the >>>> csv option. >>>> >>>> On Tuesday, March 31, 2015 at 4:11:18 AM UTC-4, Gael Princivalle wrote: >>>>> >>>>> 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.
