Migrating Gallery to Dreamhost (migrate PostgreSQL -> MySQL)

One of the last things to migrate to Dream Host is my collection of Gallery2 installations.

The biggest issue is a database disparity. On whip (my old server) my gallery2 installs were done using the multisite installation scheme on a PostgreSQL database. DreamHost supports only MySQL and gallery doesn't have an import/export function. So, I had two options: reimport the albums into fresh gallery2 installs or migrate the installations along with the data. I didn't want to loose all that meta-data so I gave migration a shot. These notes are being typed after doing the process a 2nd time. Learn from my mistakes!

Some pages on the Gallery site about conversion between MySQL and PostgreSQL (and vice-versa):

Here's what I did:

Packing up the old site
The first thing I did was to upgrade my existing galleries (on my old server, the ones using PostgreSQL) to the most recent version of gallery. This involved an upgrade from Gallery 2.2 to Gallery 2.3. Everything went fine. Using subversion to manage your install is the only way to go. After they were all up-to-date, I ran all the maintenance tasks -- cleared cache, optimized database, and rebuilt thumbnails. After that, I made a backup of things to transfer over to the new site. For each site I intended on migrating (and I migrated two, but I'll talk about them as if they were one site as I did the same for both), I included in the transfer:

  • The gallery directory (including index.php, config.php, etc.)
  • A copy of the g2data directory
  • A fresh database backup (this dump was made with pg_dump -D <db name>')

I now transfered this state over to DreamHost. In the rest of this document, I'll refer to this gallery site as the "migrate install".

Preparing the new site
On my new site (on DreamHost), I did a fresh checkout of the gallery 2 source:
svn co "https://gallery.svn.sourceforge.net/svnroot/gallery/branches/BRANCH_2_3/... gallery
into the directory serving as my multisite base install.

After this was done, I installed a new instance of gallery into a fresh directory using a new database. This new gallery wasn't related to the gallery I was migrating, I wanted to have a reference version from which I could copy configuration information, database schema, etc. I followed the multisite install directions; and, when my new gallery was created, imported a bunch of pictures from the local drive. In the rest of this document I'll call this the "reference install".

Creating a data migration script
Now that the reference site was up and running, I took a dump of the database using mysql dump:
mysqldump <auth parameters> -c --skip-extended-insert --add-drop-table <ref. db name> > reference.db.dump
(the flags were helpful in forming a useful file.)

From the dump, I created a 'createTables.sql' script by removing all the insert lines.
cat reference.db.dump | grep -v "^INSERT" > createTables.sql
I left the "DROP TABLE IF EXISTS" lines in there as I ended up running this several times...

Now, I need to hack the PostgreSQL database dump file from the migrate install to: a) not have any DDL (as that's all taken care of by createTables.sql) and b) be compatible with the MySQL schema.

(Aside: Before I did anything, I went thru a process of comparing the tables referenced by both the MySQL dump file and the PostgreSQL dump file.) By running these commands:
cat reference.db.dump | grep "^CREATE TABLE" | sed 's/CREATE TABLE `//' | sed 's/` (//' | tr [:upper:] [:lower:] | sort > mysql.tables.txt
cat migrate.dump | grep "^CREATE TABLE" | sed 's/CREATE TABLE //' | sed 's/ (//' | sort > postgres.tables.txt
diff postgres.tables.txt mysql.tables.txt
I could see which tables were where. I determined that while there were new tables in the mysql version (probably due to plugins I had installed during the installation), there weren't any tables in the PostgreSQL version I had to worry about. There was just one that wasn't accounted for -- 'g2_g1migratemap' that was a relic of my old Gallery1 installation. It didn't have any rows so I moved on.)

The first part is easy. I first made a working copy of my migrate database dump file ('migrate.dump') called 'migrate.install.sql' which would eventually become my data insert script. I manually edited this file to remove all the DDL. Fortunately, pg_dump puts all the 'create table's at the head of the file and the 'create index's at the end (unlike mysqldump which runs create table, inserts, create table, inserts, ...). I removed everything from the start of the file to the first 'INSERT INTO ....' and everything from the last 'INSERT INTO ...' to the end of the file. I made a line-in-the-sand backup of this and called it migrate.insert.sql.step1. (I did this at every step along the way in case I corrupted the working version I could go back...)

Now I needed to modify the PostgreSQL insert statements to be compatible with MySQL and the MySQL Gallery2 schema.

The first thing was to modify the bitfields in use by the tables g2_AccessMap and g2_PermissionSetMap. See the post on gallery.menalto.com mentioned above. While this HOWTO discusses moving from MySQL to PostgreSQL, the task is the same. PostgreSQL supports a bitfield type (used in these two tables for the g_permission and g_bits fields respectively) while MySQL does not. For these columns, the Gallery2 MySQL schema uses an int(11) field. The task is to take the fields that look like "B'01111111111111111111111111111111'" and covert them to (in this example) 2147483647. After trying to come up w/ some clever scripted way to do this (and failing), I broke down and did the work manually. For every instance of a bitfield string, I computed the decimal equivalent and used vi to mass find and replace the strings. Most bit field patterns existed a few times, so I worked thru the set fairly quickly. And, for you, gentle reader, I supply the list of vi global search/replace commands that took care of all fields in my entire migrate.install.sql file:
%s/B'01111111111111111111111111111111'/2147483647/
%s/B'00000000000000000001000000000000'/4096/
%s/B'00000000000000000000000000000111'/7/
%s/B'00000000000000000000000010000111'/135/
%s/B'00000000000000000001000000000111'/4103/
%s/B'00000000000000000000000000000100'/4/
%s/B'00000000000000000011000000000111'/12295/
%s/B'00000000000000000011000000000000'/12288/
%s/B'00000000000000000010000000000000'/8192/
%s/B'00000000000000000000000000000001'/1/
%s/B'00000000000000000000000000000010'/2/
%s/B'00000000000000000000000000001000'/8/
%s/B'00000000000000000000000000010000'/16/
%s/B'00000000000000000000000000100000'/32/
%s/B'00000000000000000000000001000000'/64/
%s/B'00000000000000000000000010000000'/128/
%s/B'00000000000000000000000100000000'/256/
%s/B'00000000000000000000001000000000'/512/
%s/B'00000000000000000000010000000000'/1024/
%s/B'00000000000000000000100000000000'/2048/
%s/B'00000000000000000000111100000000'/3840/
%s/B'00000000000000000000001100000111'/775/
%s/B'00000000000000000000001100000000'/768/

After I converted all these bitfields to (decimal) integers, I made another checkpoint of my script file called 'migrate.insert.sql.step2'.

The next thing to deal with was that the PostgreSQL dump file (now migrate.insert.sql) had INSERT statements with all lower-case table names. This didn't jive with the mixed-case table names used in the createTables.sql script. I ran the migrate.insert.sql script thru this shell script to figure things out:

cat reference.db.dump | grep "^CREATE TABLE" | sed 's/CREATE TABLE `//' | sed 's/` (//' | while read table; do echo "s/"`echo ${table} | tr [:upper:] [:lower:]`"/${table}/"; done | tac | while read sed; do echo $sed; cat migrate.insert.sql | sed $sed > foo; mv -f foo migrate.insert.sql; done

What this script does:
The first loop gets all the correctly named tables from the reference database dump and outputs one line for each table name in the form:
s/g2_exampletablename/g2_ExampleTableName/
These lines form an input into sed.
The 2nd loop iterates over each of these lines and calls sed with this as an argument converting all the INSERT statements for that one table and redirecting the output to a scratch file called 'foo'. Foo is then renamed to migrate.insert.sql back over the top of the original and it goes on to the next table name/sed argument. Of course, after this was done running, I made a copy of the insert script called migrate.insert.sql.step3

At this point, the migrate.insert.sql script is largely done. i did find I had a couple places in my insert script(s) that varchar value columns in the form of "E'...............'" that caused MySQL to choke. This seems to be some notation by PostgreSQL denoting special characters in the string literal. I manually edited these (search for " E'" - note space before the E) and removed the non-MySQL-compatible notation. Remember a double single-quote represents a single quote embedded in a (single-quoted) string literal.

Building the database
Now that the createTables.sql and migrate.install.sql scripts are complete, the only thing left to do was to run them.  Assuming everything came out alright, this should not result in any error messages.

One final thing that needs to be done to complete the database migration.  Gallery running on PostgreSQL uses sequences to maintain next-id information; for the MySQL usage, Gallery uses the table (g2_)SequenceId.  Therefore, we need to bootstrap this table. Connect to the MySQL db containing the newly installed data and run:
SELECT max(g_id)+1 FROM g2_Entity;
Now, with the results of this query (I'll call X) run the following update:
INSERT INTO g2_SequenceId VALUES (X);

Setting up the g2data directory
Part of the migrated data included the old g2data directory.  This directory should be placed in a web-server accessible location and the permissions set to allow read/write access to all the files/dirs in that directory.  This is exactly the same as the old server -- nothing else should change. 

Preparing the gallery directory
The final thing to do is to modify the 'gallery' directory (containing the index.php and config.php files) to contain the correct data for the new server/database.  For all files except config.php, this involves just ensuring the path to the new multisite base installation is correct.  For config.php, in addition to setting the URL to multisite base ('galleryBaseUrl'), and the (potentially) new location to the g2data ('data.gallery.base'), the database settings need to be adjusted.  The hostname, database, username, and password should be set as appropriate.  The database type line should be changed to:
$storeConfig['type'] = 'mysqli';

Thats it!
At this point, assuming all file/dir permissions are correct and the configuration files are all properly set you should be able to access your gallery site running on its new database.

 

Comments

Thanks for this howto, worked very good for me.

Very helpful. Thanks a lot!