Here is the link he was referring to: https://groups.google.com/forum/#!searchin/web2py-developers/sqlite$20postgres/web2py-developers/QxeJNByj6qc/cpBHsa1ymUkJ
On Tuesday, March 31, 2015 at 3:25:29 PM UTC-4, Willoughby wrote: > > 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.
