MySQL To PostgreSQL Migration

CarlosinFL · #1 (**permalink**) 04-27-11, 12:56

I have a Wiki running 'MediaWiki' software that was using MySQL as it's back end database. We want to migrate the Wiki to new hardware / server but more important, we wont to stop using MySQL. I found that MediaWiki has a 'mysql2postgres.pl' tool that converts the database from MySQL for PostgreSQL use:

Code:

[root@ideweb1 postgres]# ./mediawiki_mysql2postgres.pl --db=wiki --user=mediawiki --pass=**************
Writing file "mediawiki_upgrade.pg"

That tool above created the 112 MB file you see below which I assume has all the MediaWiki schema structure & data:

Code:

[root@ideweb1 postgres]# ls -lh mediawiki_upgrade.pg 
-rw-r--r-- 1 root root 112M Apr 27 09:24 mediawiki_upgrade.pg

So now I have this file and I want to get the data over to my true production PostgreSQL server (8.4.7) but don't know how to approach this. Do I need to create a new wiki database using template0 or template1 ot just normally create a database and associated role?

Code:

postgres=# CREATE ROLE wiki LOGIN ENCRYPTED PASSWORD 'letmein' NOSUPERUSER NOCREATEDB NOCREATEROLE;
CREATE ROLE
postgres=# CREATE DATABASE mediawiki OWNER wiki;
CREATE DATABASE

CarlosinFL · #2 (**permalink**) 04-27-11, 15:00

I found the instructions and it failed for some reason. Anyone know what this error means?

Code:

cmennens@tuna:~/Desktop$ psql -p 5432 -h db1 -U wiki -f mediawiki_upgrade.pg 
Password for user wiki: 
BEGIN
SET
SET
SET
psql:mediawiki_upgrade.pg:25: ERROR:  relation "category" does not exist

Internet Services

Web Development

Digital Marketing

Consumer Tech