PostgreSQL/MediaWiki

PostgreSQL was initially used as our database for a number of reasons, from stability to compatibility with other apps to support for JSONB values.

It is clearly not the most popular choice of database for Mediawiki, but if the extensions you need (if you even used any) support PostgreSQL, it should be fine. Thus, we made some workarounds to support this unique use case, though eventually, we acquiesced and migrated to MariaDB. These mods are noted below.

Installation

• Ubuntu 16.04 - https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-16-04
• CentOS 7 - https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-centos-7

PostgreSQL with UNIX Sockets

As noted in the Mediawiki tutorial, you generally connect to the PostgreSQL over a TCP connection, and use md5 password authentication.

However, if the PostgreSQL database is on the same server, it's a better idea to dispense with the TCP overhead and connect to the UNIX socket directly.

First, make sure that you've set a password for the postgres superuser, so you can log into it without using peer authentication, which we are going to switch to md5.

$ sudo su # must become root to become postgres user first
# su postgres
$ psql
postgres=# \password
Enter new password:
Enter it again:
postgres=#

Next, we need to enable md5 authentication to the UNIX socket. On PostgreSQL 9.6 on Debian, edit the file /etc/postgresql/9.6/main/pg_hba.conf and change the following lines to match the below:

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

On Debian, the PostgreSQL UNIX Socket is at /var/run/postgresql/.s.PGSQL.5432, so in LocalSettings.php set these following lines (make sure to comment out $wgDBPort, which is not needed)

## Database settings
$wgDBtype = "postgres";
$wgDBserver = "/var/run/postgresql/.s.PGSQL.5432"; # UNIX port path

# Postgres specific settings
#$wgDBport = "5432"; # disable this

Note: Obviously if the PostgreSQL database is on another server you have to use TCP and not UNIX sockets to communicate with it, so just expose the port on that server and put the IP in $wgDBServer in LocalSettings.php.

Migrating MediaWiki PostgreSQL to MySQL

Sooner or later, you might find a crucial extension that only supports MySQL/MariaDB, such as the Translate extension.

You can attempt to convert the SQL create table schema from MySQL to PostgreSQL in the extension: or you could just choose the path of least resistance and join the MariaDB bandwagon.

We ended up choosing to transfer. Special steps are required, but all your wiki data will be migrated over (except your user accounts...)

Make an XML backup

Use DumpBackup.php to create a full XML dump of all your wikipages and their edit history. SQL backups cannot be used in this transfer since, clearly PostgreSQL is incompatible with MySQL. However, XML dumps were designed to database independent with this in mind.

Make an Images backup

Note that all images have to be backed up and reimported for the database to recognize them. Just use DumpUploads.php to make the backup, and it will be imported in the next step.

Move to MySQL

1. Copy your current /var/www/mediawiki directory to a copy: for example, /var/www/mediawiki-mariadb.
2. In /var/www/mediawiki-mariadb, move LocalSettings.php to LocalSettings.php.bak.
3. Enable access to the /mw-config/ directory on the webserver, and run the installation script as if it were a new wiki.
4. Provide the root MySQL username and password to the setup, and have MediaWiki create a nonprivileged DB user.
5. Run setup to create a new admin account and get as close to your original LocalSettings.php.bak configuration as you can.
6. But after the MySQL database is initialized, move only the MySQL specific settings from the downloaded LocalSettings.php setup gives you, to overwrite the PostgreSQL settings in LocalSettings.php.bak.
7. Once the MySQL settings are moved in, rename LocalSettings.php.bak to LocalSettings.php.

Restoring Wiki Data

1. Now, restore your XML Dump, and make sure to regenerate RecentChanges as stated there.
2. Next, restore your images by extracting the tarball created by mediawikiwiki:Manual:DumpUploads.php, and then using mediawikiwiki:Manual:ImportImages.php (add the --search-recursively argument to search under subdirectories).
3. Finally, to make sure your extensions work, update your database.

Cleanup

1. The Main Page may still have the MediaWiki default content, but just open Page History and "revert" to your previous main page.
2. All user accounts have to be recreated, since XML dumps do not save sensitive information like password hashes. However, usernames in edit history and user pages will remain the same.
   • You could try to transfer account information through conversion, but I didn't care enough to do so.
3. All page protections have to be recreated, so hurry and protect your main page and templates before new users sneak in some edits.