Migrating from MySQL to PostgreSQL


Joined: 2005-12-27
Posts: 4
Posted: Mon, 2006-05-15 04:17

I'm trying to migrate from MySQL to PostgreSQL.
I've managed to get all the apps I use moved, except for Gallery2.

Two problems I've run into so far:
1. Different data types are used in the schemas for the different databases (eg, in MySQL, the AccessSubscriberMap table uses 'integer' for the 'g_permissions' column, whereas in PgSQL it's bit(32)

2. There seems to be some slightly more restrictive conditions in PgSQL than in MySQL.. if I convert g_permissions to 'integer' and try an import, I quickly start getting this error:

ERROR: duplicate key violates unique constraint "brandysbrats_accessmap_pkey"

Can anyone help??

Gallery version = 2.1-rc-1a core 1.0.29
PHP version = 5.1.2 apache
Webserver = Apache/1.3.34 (Unix) PHP/5.1.2 AuthMySQL/2.20
Database = mysql 5.0.20-log
Toolkits = ArchiveUpload, Exif, ImageMagick, NetPBM
Operating system = FreeBSD gooseberry.silverwraith.com 6.1-RELEASE FreeBSD 6.1-RELEASE #1: Sat May 13 01:44:45 PDT 2006

:/usr/obj/usr/src/sys/GOOSEBERRY i386
Browser = Mozilla/5.0 (Macintosh; U; PPC Mac OS X Mach-O; en-US; rv: Gecko/20060426 Firefox/


Joined: 2003-01-04
Posts: 32509
Posted: Mon, 2006-05-15 22:07

yes, we use slightly different data types for permission bit masks. basically, we're storing permissions as a long bit mask. if the database supports something like an array of bits as a datatype, we use that. else we try to use a single integer of 32 bits and do bit operations within the db with it.

basically, what you're trying there hasn't been done before i guess. but i seem to remember a forum topic about moving the other way. which should yield the same issues. i guess the user finally succeeded.

there's also no db independent gallery storage / export format which you could employ here.
so you're back to finding a manual solution or to script something together.


Joined: 2005-12-27
Posts: 4
Posted: Mon, 2006-05-15 22:49

Thanks valiant :-)

At the moment I see two somewhat reasonable solutions:
1: Find all the differences in the table schemas, and script something to pick each row out of MySQL, convert it, and import it.
Not an impossible task. Infact, not even all that difficult. I just need to find the differences. Are there any other glaring differences I should watch for?
I'm not sure how to address the non-unique key issues. Maybe just skip over them and deal with them by hand.
2: Create some kind of external storage format for Gallery2, and use that.

I think the first is far simpler. If you have any tips or gotchas you can think of I'd appreciate hearing about them :) AFAIT, I don't think Gallery2 uses MySQL's AUTO_INCREMENT, or PgSQL's serial function to generate item ID's does it? I'll find those schemas now.

I was almost ready to give up but I seem to have foudn a secnd wind for this :-)


Joined: 2005-12-27
Posts: 4
Posted: Mon, 2006-05-15 22:59

If you have any pointers on where I can find the schemas I'd really appreciate it.. they appear to be in a non-obvious place :-)


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

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

Having gone thru the PostgreSQL -> MySQL migration w/ Gallery2, I'll post there.