HOWTO: Move from MySQL to PostgreSQL

LacKNafta

Joined: 2006-08-11
Posts: 1
Posted: Fri, 2006-08-11 23:05

First I want to thank the creators of Gallery2 for a great application.

Now I want to give you some tips about database conversion.

HOWTO: Move from MySQL to PostgreSQL

I just moved my gallery from a linux server running MySQL to a one running PostgreSQL. The database couldn't just be dumped from MySQL and then read into PostgreSQL as the databases use slight different formats. I can't guarantee that this will work for you right away but you should get a feeling on what you might have to fix if it doesn't.

Preparations

First we need to create the basic PostgreSQL database structure.

  1. Deactivate AND uninstall the url rewrite module.
  2. Install the gallery2 code (extract gallery2.tar.gz/zip) on the new host.
  3. Change the owner of gallery2 folder to the webserver user.
  4. Browse to newhost/gallery/install/
  5. Go through the whole setup process as you did the first time you set it up.
  6. When the installation is done, go to G2 Site Admin, Modules and activate all the modules you had activated on your old host.

Now the first step is done and you should have a working, empty installation that uses the PostgreSQL database.

Fixing old data

Now we want to get the old data in.

  1. Close your browser window containing the gallery. You won't be able to use it for a while.
  2. Replace the g2data folder on the new host with the g2data folder from the old host.
  3. Dump the data of the MySQL database on the old host to a file. DO NOT dump the structure of the database.
  4. Open the dump of the MySQL database data in your favourite text editor.
  5. Replace all '`' characters with ''. With that I mean that you remove them.
  6. Find the insert of g2_AccessMap data in the file.
    The data format differs for MySQL and PostgreSQL on the column g_permission.
    You'll have to convert the field from integer to a 32 bit bitfield.
    I used Windows calculator in Advanced Mode and filled in the integer in Deciamal mode and then pressed the Binary button as I'm pretty slow on reading binary. But you can do it manually if you want ;)
    Example:
    Before change

    INSERT INTO g2_AccessMap (g_accessListId,g_userOrGroupId,g_permission) VALUES 
     (725,181,1),
     (10,4,7),
     (743,4,263),
     (10,3,2147483647),
     ...

    After change

    INSERT INTO g2_AccessMap (g_accessListId,g_userOrGroupId,g_permission) VALUES 
     (725,181,'01111111111111111111111111111111'),
     (10,4,'00000000000000000000000000000111'),
     (743,4,'00000000000000000000000100000111'),
     (10,3,'01111111111111111111111111111111'),
     ...
  7. Find the insert of g2_permissionsetmap data in the file. It also contains a field, g_bits, which type differs between the databases. Repeat the previous step for that data.
  8. Remove all data in all tables on the new host.
  9. Restore the modified database dump to the PostgreSQL database.

Now, if everything worked as supposed to, you should be able to visit your gallery on the new host and have your old data in it.

If something goes wrong

I made the misstake that I did not activate all the modules I had activated on the old server before restoring the data. That gave me the trouble that I was missing a few tables when doing certain activities in the gallery. I fixed that with the following steps.

  1. Enable debugging in config.php. This will show you which table is missing.
  2. Dump the database from the MySQL server to a file. Be sure to include the structure of the tables.
  3. Find the structure of the table that was missing.
  4. For that table do the foolowing steps. Not all are needed for every table.
    • Replace the charater '`' with '', in other words, remove it.
    • Replace int(11) with int4
    • Replace PRIMARY KEY (g_id) with CONSTRAINT {table_name}_pkey PRIMARY KEY (g_id)
    • Replace UNIQUE KEY {field_name} {field_name} with CONSTRAINT g2_{table_name}_{field_name}_unique UNIQUE ({field_name})
    • Replace TYPE=InnoDB; with
      WITHOUT OIDS;
      ALTER TABLE g2_{table_name} OWNER TO gallery2;
    • Replace KEY {table_name}_75985 ({field_name}), with nothing and place
      CREATE INDEX {table_name}_75985
        ON {table_name}
        USING btree
        ({field_name});

      on a new line after the ALTER TABLE {table_name} OWNER TO gallery2;

    • Example:
      Before change

      CREATE TABLE `g2_FileSystemEntity` (
        `g_id` int(11) NOT NULL,
        `g_pathComponent` varchar(128) default NULL,
        PRIMARY KEY  (`g_id`),
        KEY `g2_FileSystemEntity_3406` (`g_pathComponent`)
      ) TYPE=InnoDB;

      After change

      CREATE TABLE g2_FileSystemEntity (
        g_id int4 NOT NULL,
        g_pathComponent varchar(128) default NULL,
        CONSTRAINT g2_FileSystemEntity_pkey PRIMARY KEY (g_id),
      )
      WITHOUT OIDS;
      ALTER TABLE g2_FileSystemEntity OWNER TO gallery2;
      
      CREATE INDEX g2_FileSystemEntity_3406
        ON g2_FileSystemEntity
        USING btree
        (g_pathComponent);
  5. Execute the CREATE command(s) on the PostgreSQL server to create the table and it's indexes if any.

That's it!
I hope that this guide might help anyone that wants to move their database from MySQL to PostgreSQL. If you have any coments on it, feel free to post. I won't be able to check the replies in a week or so as I'm going away on a business trip.

Take care!

/LacKNafta

 
valiant

Joined: 2003-01-04
Posts: 32509
Posted: Sat, 2006-08-12 07:02

Thanks for your contribution LackNafta!

codex.gallery2.org is the place for howto's and other docs. It'd be nice if you could create a page over there and add a link at documentation -> how to's.

 
Inconceivable

Joined: 2006-09-27
Posts: 1
Posted: Wed, 2006-09-27 13:49

Your guide was really helpful, but it didn't work completely for me. Here's the exact steps I had to follow:

1. Open old Gallery2 installation, go to the Modules page. Record which modules you have installed and their exact configuration. Best way to do this is to just take a screenshot of the Modules list and each module's configuration page.

2. Copy config.php to a backup location and uninstall the old Gallery2, cleaning out or removing the directory as necessary.

3. Create a new PgSQL database: createdb [PgSQL database name] -E UNICODE

4. Reinstall Gallery2. Copy over the files and then visit the install/ page and make a fresh installation. Configure it to use a fresh g2data folder.

5. Visit the new installation's Modules page, install and configure any modules as necessary to match your old installation.

6. Dump the schema for your new database: pg_dump -s [PgSQL database name] > [PgSQL dump file name]

7. Drop and recreate the PgSQL database from schema:
dropdb [PgSQL database name]
createdb [PgSQL database name]

8. Dump the data from the MySQL database: mysqldump -t -c --single-transaction --skip-opt --skip-comments -p [MySQL database name] > [MySQL dump file name]
> the -t option drops the data only
> the -c option includes column names in INSERT statements, necessary since sometimes PgSQL and MySQL can have different column orderings
> --single-transaction doesn't really work for some reason but we fix that later
> --skip-comments obviously isn't necessary

9. Open the MySQL dump file in a text editor.

9a. Remove all backticks from the file by replacing them with an empty string

9b. Change the numbers to 32-bit bitfields for g2_AccessMap.g_permission and g2_PermissionSetMap.g_bits as described in the original HowTo.

9c. Destroy all the g2_SessionMap INSERT statements.

9d. If you are changing the path for your G2 installation as well, update it in the URL Rewrite config entry. (Just do a search for your old pathname; you'll find it.)

9e. Before the first line of the file, add a line that says "BEGIN;"

9f. After the last line of the file, add a line that says "COMMIT;"

9g. Locate the line "INSERT INTO g2_SequenceId VALUES ([number]);" and change it to "ALTER SEQUENCE g2_SequenceId RESTART WITH [number];"

9h. Comment out the line "INSERT INTO g2_SequenceLock VALUES ([number]);" -- I'm not quite sure what it's there for, but the problem is in PgSQL the sequence starts as 1, whereas in the dump file it usually has a value of 0, so I just remove it. Seems to work OK so far.

9i. Save the file to a new location. (We'll call it [modified MySQL data dump file].)

10. Import the file into your PgSQL database: psql -f [modified MySQL data dump file] [PgSQL database name]

11. Edit your config.php file, locate the config entry with the path to your new, temporary g2data folder and revert it to your old, existing g2data folder.

12. Remove the contents of the cache/ folder within your g2data folder.

13. Visit your G2 website, go to the admin panel, and recreate the cache and thumbnails.

14. If your source and target hosts have any differing module configurations, you might want to disable and enable any relevant modules to wipe out and recreate their database entries.

And viola! Somewhere along the road you can delete your MySQL database and the dump files you worked with, but hold on to them for a while in case something goes wrong.

 
toddgee

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

See http://gallery.menalto.com/node/57730#comment-295804 for a HOWTO on the reverse process -- converting a gallery installation from PostgreSQL to MySQL.