Converting mediawiki database from postgresql to mysql/mariadb
Why to use mysql/mariadb instead of postgresql?
Here is what MediaWiki says :
Most of the common maintenance scripts work with PostgreSQL however some of the more obscure ones might have problems.
Is PostgreSQL a good choice for MediaWiki?
Wikipedia uses MySQL so MediaWiki gets more testing on MySQL than PostgreSQL. While support for PostgreSQL is maintained by volunteers, most core functionality is working.
If you need to save only mediawiki data and pages modify history then the best and easiest way probably would be:
However user accounts, user contrubutions, logs, etc will not be migrated this way.
Why not to use tools for converting postgresql databases to mysql?
Mediawiki postgresql version is quite different from mysql version. For example: some table names are different, field order, timestamp format (pg: '2015-11-01 08:42:31+00', mysql: '20151101084231'), many fields that are NULL in postgresql are not allowed to be NULL in mysql. That is why there will be issues when dumping data from postgresql and importing it to mysql.
There is a script mentioned in http://www.winterrodeln.org/trac/wiki/MediaWikiPostgresqlToMysql that should be converting postgresql database to mysql. However in my case it did not work because of the issues mentioned above (that script was written in 2009-01-16).
In this example mediawiki files root is located in /var/www/wiki.wikiname.org/htdocs/w
Web server is lighttpd. /etc/lighttpd/lighttpd.conf contains lines:
Disallowing editing mediawiki until database is converted
$wgReadOnly = 'This wiki is currently being upgraded to a newer software version.';
Preparing dump files for importing data to mysql
Making postgresql dump
Making temporary postgresql database looking like mysql
SQL script modifying postgresql database
For avoiding issues when importing data to mysql we need to modify mediawiki postgresql database so that it would look like mediawiki mysql database. Basically the script will make field order in pg tables like in mysql and will set non-NULL values to the fields that are not allowed to be NULL in mysql
Making dump files for mysql import
Modifying dump files
Postgresql specific sql should be removed
Postgresql table mwuser corresponds to mysql table user
Postgresql table pagecontent corresponds to mysql table text
Back slashes should be replaced with double backslashes
Timestamp format in postgresql is different from mysql (pg: '2015-10-31 10:10:10+00', mysql: '20151031101010'). All timestamps should be converted to avoid errors like:
... Language::sprintfDate: The timestamp should be a number
In postgresql ipblocks table may contain records with duplicated field ipb_address. mysql requires that ipb_address field is unique.
Check manually ipblocks.sql file. Delete records with duplicated field ipb_address to prevent errors like:
# ERROR 1062 (23000) at line 152: Duplicate entry 'xxxxxx-0-0-0' for key 'ipb_address'
Changing mysql root password, creating user for mediawiki
Setting up new mediawiki
<?php header('Location: /wiki'); ?>
Open your wiki in browser http://wiki.wikiname.org, go through setup process (DB type:mysql, engin: innodb, charset: binary)
Compare LocalSettings.php file generated by setup process with old mediawiki LocalSettings.php file
Take needed settings from old LocalSettings.php
Copy new LocalSettings.php into mediawiki directory /var/www/wiki.wikiname.org/htdocs/w
Copy needed files from old mediawiki (images/*, extensions/..., resources/...)
Clearing mysql tables before import
Importing data into mysql
Running maintenance scripts
Old cache in l10n_cache table may couse problems. It can be deleted and regenerated again.