Mirror of the Rel4tion website/wiki source, view at <http://rel4tion.org>

Clone

HTTPS: git clone https://vervis.peers.community/repos/yEzqv

SSH: git clone USERNAME@vervis.peers.community:yEzqv

Branches

• master

Tags

gmg-db-migration.mdwn

[[!meta title=“GNU MediaGobin: Migrate Database from SQLite to PosgreSQL”]]

I’m not a MediaGoblin developer. I’m not a database developer. I just happen to run a MediaGoblin instance at [[!rel4sub media]], and at some point I decided to migrate the existing SQLite database to PosgtreSQL. I’m documenting my steps, maybe it will help someone.

Review, comments, etc. very welcome, of course.

Conventions

This is the usual in this wiki, but I’ll specify anyway just in case.

• In the code blocks, lines beginning with # are root prompts. If you are used to sudo, you can use it instead of becoming the root user.
• Lines beginning with $ are prompts for some other user. In this case it’s going to be mostly the mediagoblin user, but possibly others too.
• Other lines are examples of output generated by the commands which precede them.
• Some lines are unrelated to the shell, e.g. some config settings.

vim is assumed to be the text editor in the code blocks, but you are free as always to use your favorite free-software text editor.

System Info

I’m using MediaGoblin installed from git. Not sure exactly about the version, should be somewhere between 0.7.1 and 0.7.2. I think. The last commit is c7c26b17406cdcb03de4d2579f4851d2513d11e1, made on Friday, December 12 13:55:14 2014 UTC.

I’m also using Joar’s initscripts.

The platform is Trisquel GNU/Linux-libre 7. But unlike the default setup for root commands, which uses sudo (I suppose this is inherited from ubun7u), I just become the root user with su. If you prefer sudo it’s fine, just adapt the commands accordingly.

Stop Goblin

I’m not sure whether it’s required to stop the GMG daemon(s) before a database change or migration. Since I just run a small instance nobody would miss, I can just take the safe path and stop them until I’m done.

If you prefer not to stop them if possible, either skip this step or ask on IRC.

# service mediagoblin-paster stop
# service mediagoblin-celery-worker stop

Prepare Database

These steps are also described in the MediaGoblin documentation.

Install PosgreSQL:

# apt-get install postgresql postgresql-client python-psycopg2

Create new database user:

# su - postgres
$ createuser -A -D mediagoblin

Create new database:

$ createdb -E UNICODE -O mediagoblin media-gmg

The last part is the database name. Choose whatever suits you, just make sure to use it consistently everywhere. The part before the last is the name of the mediagoblin user, i.e. the system user which runs the MediaGoblin server daemon.

Configure MediaGoblin:

$ exit
# su - mediagoblin -s /bin/bash
$ cd mediagoblin
$ vim mediagoblin_local.ini

Under the [mediagoblin] section, add this: (or edit the commented-out line, if it exists)

sql_engine = postgresql:///media-gmg

Initialize database content:

$ ./bin/gmg dbupdate

Dump Old Database

In your MediaGoblin working directory there should be a file named mediagoblin.db. That’s the SQLite3 database. Dump its content into a plain-text SQL command file:

$ sqlite3 mediagoblin.db .dump > mg.sql

Prepare SQL Commands

Open the SQL command file:

$ vim mg.sql

This file has 3 parts. First, settings and transaction declaration:

PRAGMA foreign_keys=off;
BEGIN TRANSACTION;

You can remove the first line. Leave the second one as is.

The second part is a sequence of table declarations using CREATE TABLE statements, and table content declarations using INSERT INTO statements. Remove all the CREATE TABLE statements.

The last part actually commits the transaction, i.e. this what causes the actual change in the database. If there’s any error in the statements, nothing changes and the transaction won’t be executed unless this SQL file is valid.

COMMIT;

Leave it as is.

Right now the SQL file contains - inside the transaction - only INSERT statements. But some of these are made automatically by MediaGoblin when running the dbupdate we did earlier. You need to go over the tables and see which ones already have their content in the new database. For these tables, delete the corresponding statements from the SQL file.

Here’s how.

Suppose there’s a block of INSERT statements in mg.sql which looks like this:

INSERT INTO "core__privileges_users" VALUES(1,3);
INSERT INTO "core__privileges_users" VALUES(1,5);
INSERT INTO "core__privileges_users" VALUES(1,4);
INSERT INTO "core__privileges_users" VALUES(1,6);

core__privileges_users is a name of a table. We need to check whether the newly created PosgreSQL already contains these records. If it does, delete the above lines from mg.sql. Otherwise, leave them as is.

Use PostgreSQL client program to examine the table:

$ psql media-gmg
media-gmg=> select * from core__privileges_users;

The second line is the psql prompt. If the result of this SELECT query is an empty table, keep the lines in the SQL file. Otherwise, if you see the same data in the output, delete the lines from the SQL file.

Go over the entire SQL file, and check all the tables. The number of tables isn’t that big, and you can skip tables if it’s obvious they can’t already be in the DB. For example, the list of users and the list of media entries. If you want to be safe, just check all of them.

Inside psql, the help command shows… help, and \q exits back to the shell prompt.

There is another issue to solve: PostgreSQL supports sequences. They allow to automatically compute column values, e.g. by automatically incrementing a counter and using it as an ID for the next record. In mg.sql the id column values are specified directly, which means inserting them into the database won’t increase the sequence counters. This will cause errors later, when using MediaGoblin. TODO for myself: Explain how to fix. General direction: Replace the id values in mg.sql (almost all if not all tables have an id column) with some keyword, possibly default, but also watch out for errors due to references to IDs from other tables. A safer option may be to manually set the counters by going over the tables, e.g.

select setval('core__comment_subscriptions_id_seq', 20);

Migrate Table Contents

It’s time to try executing the SQL file, i.e. committing this transaction. Due to differences between SQLite and PosgreSQL, there will probably be errors. That’s okay - just fix the errors you get, until at some point the transaction will succeed, once you fix them all.

A useful tool for understanding errors is the table schemas. Use psql to see a table schema:

$ psql media-gmg
media-gmg=> \d core__privileges_users;

The part after the \d is the name of the table.

After each fix (unless it just works on the first try, of course), try to execute the SQL commands like this:

$ psql media-gmg
media-gmg=> \i mg.sql

There’s just one type of error I got. Fix by editing the SQL file: The type of some column is boolean, but the value in the INSERT is not a valid boolean value. SQLite dumped boolean values as 1 and 0 in the SQL file. Just change them to True and False respectively, where psql complains about errors.

When the \i finally succeeds, the last output line will be COMMIT.

Test

Launch MediaGoblin (or relaunch, if it’s still running). When it reads the INI files, it will detect the config changes and use the new PostgreSQL database instead of the SQLite one. Is another dbupdate required before that? I don’t know. I just launched the daemons, and it was enough:

# service mediagoblin-paster start
# service mediagoblin-celery-worker start

Now browse to your instance using a web browser, and see if all the content and all the users are there.

If there’s any problem, don’t panic - the original SQLite database is still there (mediagoblin.db), so nothing is lost. In the worst case, you can redo the steps safely. Also remember there an IRC channel (#mediagoblin @ Freenode) and a mailing list, and feel free to ask [[me|fr33domlover]] too.

Good luck, and have fun with MediaGoblin!