Migrating from Postgres Gallery installation to MySQL


Joined: 2006-12-01
Posts: 2
Posted: Fri, 2006-12-01 20:08

Hi, I use gallery to run a photo album for my girlfriend, and she has about a gig of photos stored in it (complete with comments on every one, etc). That install is running on a machine that I consider to be unreliable, and want to move the installation over to a managed hosting provider that I do consider to be reliable. The current installation is on linux, using postgresql as the database. The managed hosting does not offer postgresql, and I need to find a way to migrate the install to MySQL. I have tried several database "migration" tools, and they would "work" but nothing would show up in gallery (even with the g2data folder copied over).

Anyone have any insight into this problem? How do I pull this off? Losing any data will result in very bad things. I need everything to come over, she refuses to re-do any of it.

Thank you in advance,


Joined: 2003-01-04
Posts: 32509
Posted: Fri, 2006-12-01 23:34

g2 has DBMS specific db schemas. they are not necessarily the same.
PG and MySQL have mostly the same db schema, but there are some differences.

1. SequenceId, SequenceLock
- MySQL uses normal tables to emulate sequences
- PostgreSQL uses real DB sequences

-> Easy to migrate. Just create 2 tables in MySQL.
The SequenceLock table can be empty.
The SequenceId table needs 1 row, value = SELECT max(g_id)+100 from g2_Entity; (+100 is just reasonably larger than the max id).

2. AccessMap, PermissionSetMap
- MySQL uses an integer field for permissions
- PostgreSQL uses a bit-array (32 bits) to store permissions

-> You'll need to convert the data.
When exporting from PG, you'll need to use PG functions to cast the bit-array to a 32 bit integer.


Joined: 2006-12-01
Posts: 2
Posted: Fri, 2006-12-01 23:42

Thank you for your suggestions. When the current host site comes back online (like I said, unreliable), I will work on moving things over with this in mind. If I have any issues I'll be sure to come back here.

Thanks again!


Joined: 2005-10-03
Posts: 3
Posted: Mon, 2007-05-28 16:09
valiant wrote:
-> You'll need to convert the data.
When exporting from PG, you'll need to use PG functions to cast the bit-array to a 32 bit integer.

This is exactly what I've been looking for, but I haven't a clue how to do it. Furthermore, even after seeking help from "man psql", "man pg_dump", Mr. Google, and the docs at postgresql.org, I can't seem to find any instructions for this.

I'd never ask for help before hunting for an answer, but my hunt has been futile, so far. Can someone kindly point me to the docs that explain this?

Thanks in advance.



Joined: 2005-10-03
Posts: 3
Posted: Sat, 2007-06-02 14:56

Update: I could not find how to "cast the bit-array to a 32 bit integer" but I found how to map the values, and used sed to substitute them in my dump before importing it into MySQL. That part worked perfectly, and I thought all was well. But I tried to register a new user account and got an error. Putting it into debug mode revealed it was trying to create a duplicate key in g2_Users. (sigh...). I'll have to try again when I have more time to troubleshoot.



Joined: 2008-12-15
Posts: 7
Posted: Mon, 2008-12-15 05:05

There's another thread on this same issue at http://gallery.menalto.com/node/49207

I just went thru the process of migration from PostgreSQL -> MySQL and will post my results here. (See next post)


Joined: 2008-12-15
Posts: 7
Posted: Mon, 2008-12-15 05:06

When doing this work, I found the HOWTO on the reverse process (MySQL -> PostgreSQL) located here: http://gallery.menalto.com/node/52954 to be helpful.


Joined: 2008-12-15
Posts: 7
Posted: Wed, 2008-12-17 19:25

The following is a cut-and-paste from my blog entry at toddgee.com. http://toddgee.com/node/14 (This might change/is not currently externally addressable -- just set that site up)

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. Dream Host supports only MySQL. 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:

* http://gallery.menalto.com/node/57730
* http://gallery.menalto.com/node/52954
* http://gallery.menalto.com/node/49207

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 my new host. 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 (Dream Host), I did a fresh checkout of the gallery 2 source:
svn co "https://gallery.svn.sourceforge.net/svnroot/gallery/branches/BRANCH_2_3/gallery2"
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:
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:
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.

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.


Joined: 2008-12-15
Posts: 7
Posted: Wed, 2008-12-17 19:29

Oh, one thing I forgot -- after doing the install on the database, you need to put the one row in the SequenceId table as Valiant pointed out in the 2nd comment on this thread.


Joined: 2008-12-15
Posts: 7
Posted: Wed, 2008-12-17 19:44

SELECT max(g_id)+1 FROM g2_Entity;
Now, with the results of this query (I'll call X) run the following update:


Joined: 2007-01-11
Posts: 16
Posted: Thu, 2008-12-18 03:16

Todd, thanks for this - I came to the forums with this exact question (even moving to Dreamhost) just a few hours after you posted. I'll run through your script tomorrow; you've saved me hours, I can see already.


Joined: 2007-01-11
Posts: 16
Posted: Wed, 2008-12-24 01:38

FYI, the list of find-and-replaces isn't complete for my database - I have other binary values your list doesn't include. I'm working on a script to do the transformation which I will post here if I'm successful.


Joined: 2007-01-11
Posts: 16
Posted: Wed, 2008-12-24 03:28

Save this perl code in a file named string_binary.pl, make sure it's executable, and run it with your old db file as the first argument (it won't be changed) and a new filename as the second argument. It will produce a copy of your dumpfile with the bitfields translated to decimal integers.

#!/usr/bin/perl -w

unless (@ARGV) {
	print "Usage: string_binary.pl <OLD_DB_FILE> <NEW_DB_FILE>\n";
	print "Where <OLD_DB_FILE> is the path to the dump of your old database file, and\n";
	print "<NEW_DB_FILE> is the name you wish to give to the new file with the\n";
	print "bitfields replaced by decimal integers.\n";

open(DBFILE, "$ARGV[0]") or die "Cannot open $ARGV[0]: $!"; # Original DB file
open(NEWFILE, ">new_db.sql") or die "Cannot create new database file: $!"; # Output file
print "Checking $ARGV[0]...\n";
while (<DBFILE>) {
	if (/(B'[01]{32}')/) { # Match the binary string
		my $decimal = 0; # This will be the replacement value
		my $bin_string = substr($1, 2, 32); # Taking the actual ones and zeroes out of the match
		my $i = 0; # Initialize a counter
		while ($bin_string) {
			$a = chop($bin_string); # Take the last character
			if ($a eq '1') { # If it's a one
				$decimal = $decimal + (2 ** $i); # Add two to the $i power
			$i++; # Increment $i
		# print "$1\t$decimal\n"; # Debug
		$_ =~ s/$1/$decimal/; # Replace
	print NEWFILE $_;
close DBFILE;

Joined: 2010-01-17
Posts: 1
Posted: Sun, 2010-01-17 16:41

Thanks toddgee! Great HOWTO! It should probably go into gallery2 wiki? Here is my expierence.

1. Mysql primary key on a string field is case-insensitive. Because of this i got errors when inserting postgresql data. So i need to search and rename this strings (some of them appear 2 times, some of them appear 3 times) inside migrate.insert.sql and also find and rename corresponding album directories inside g2data directory tree.

2. I did migration from Debian Etch gallery 2.1.2 with posgresql to Debiam Lenny gallery 2.3.1 with mysql. After I start gallery2/upgrade/index.php i got error about empty table g2_SequenceEventLog. So, i inserted into it same value as in g2_SequenceId.

PS. Gallery should have some intermediate format for exporting-importing.