Merge lp:~therp-nl/openupgrade-server/7.0-migrate_script_with_template into lp:openupgrade-server

[Stefan Rijnhart (Opener)]

[RFR] Copy database within the database server

This change copies the database within the database server instead of performing the pg_dump/restore round trip. Apart from a code simplification, this change also allows the script to continue in this environment that I am currently working in, in which the pg utils mysteriously ignore the PGUSER/PASSWORD environment variables.

[Holger Brunn (Therp) (hbrunn)]

This will fail miserably if someone uses the database (an open cursor is enough) to be migrated.

But indeed, the template way is faster and nicer. How about trying that first, catch the exception if the db is in use and then do it with pg_dump?

review: Needs Fixing

[lp:~therp-nl/openupgrade-server/7.0-migrate_script_with_template]

4647. By Stefan Rijnhart (Opener) on 2014-03-14

[RVT] Revert previous fix

4648. By Stefan Rijnhart (Opener) on 2014-03-14

[FIX] Attempt to copy database server-internally but keep fallback
       on external method

[Stefan Rijnhart (Opener) (stefan-opener)]

Thanks for the suggestion! Now committed.

[Pedro Manuel Baeza (pedro.baeza)]

I haven't tried, but seems OK, and the fallback part is the same as previously, so at least this part will work.

Regards.

review: Approve (code review)

[Holger Brunn (Therp) (hbrunn)]

review: Approve (code review)

=== modified file 'scripts/migrate.py'
--- scripts/migrate.py	2014-01-20 21:56:01 +0000
+++ scripts/migrate.py	2014-03-14 09:29:30 +0000
@@ -201,32 +201,39 @@
     conn.set_isolation_level(psycopg2.extensions.ISOLATION_LEVEL_AUTOCOMMIT)
     cur=conn.cursor()
     cur.execute('drop database if exists "%(db)s"' % {'db': db})
-    cur.execute('create database "%(db)s"' % {'db': db})
-    cur.close()
-
-    os.environ['PGUSER'] = db_user             
-    if ('host' in conn_parms and conn_parms['host']
-    and not os.environ.get('PGHOST')):
-        os.environ['PGHOST'] = conn_parms['host']             
-
-    if ('port' in conn_parms and conn_parms['port']
-    and not os.environ.get('PGPORT')):
-        os.environ['PGPORT'] = conn_parms['port']             
-
-    password_set = False
-    if ('password' in conn_parms and conn_parms['password']
-    and not os.environ.get('PGPASSWORD')):
-        os.environ['PGPASSWORD'] = conn_parms['password']             
-        password_set = True
-
-    os.system(
-        ('pg_dump --format=custom --no-password %(db_name)s ' +
-         '| pg_restore --no-password --dbname=%(db)s') %
-        {'db_name': db_name, 'db': db}
-    )
-
-    if password_set:
-        del os.environ['PGPASSWORD'] 
+    try:
+        print "Copying the database using 'with template'"
+        cur.execute('create database "%(db)s" with template "%(db_name)s"' % {
+            'db': db, 'db_name': db_name})
+        cur.close()
+    except psycopg2.OperationalError:
+        print "Failed, fallback on creating empty database + loading a dump"
+        cur.execute('create database "%(db)s"' % {'db': db})
+        cur.close()
+
+        os.environ['PGUSER'] = db_user             
+        if ('host' in conn_parms and conn_parms['host']
+                and not os.environ.get('PGHOST')):
+            os.environ['PGHOST'] = conn_parms['host']             
+
+        if ('port' in conn_parms and conn_parms['port']
+                and not os.environ.get('PGPORT')):
+            os.environ['PGPORT'] = conn_parms['port']             
+
+        password_set = False
+        if ('password' in conn_parms and conn_parms['password']
+                and not os.environ.get('PGPASSWORD')):
+            os.environ['PGPASSWORD'] = conn_parms['password']             
+            password_set = True
+
+        os.system(
+            ('pg_dump --format=custom --no-password %(db_name)s ' +
+             '| pg_restore --no-password --dbname=%(db)s') %
+            {'db_name': db_name, 'db': db}
+        )
+
+        if password_set:
+            del os.environ['PGPASSWORD'] 
 
 for version in options.migrations.split(','):
   print 'running migration for '+version