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  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  superuser, so you can log into it without using   authentication, which we are going to switch to.

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

Next, we need to enable  authentication to the UNIX socket. On PostgreSQL 9.6 on Debian, edit the file  and change the following lines to match the below:

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

On Debian, the PostgreSQL UNIX Socket is at, so in LocalSettings.php set these following lines (make sure to comment out  , which is not needed)

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


 * 1) Postgres specific settings
 * 2) $wgDBport = "5432"; # disable this

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  directory to a copy: for example,.
 * 2) In, move  to.
 * 3) Enable access to the  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  configuration as you can.
 * 6) But after the MySQL database is initialized, move only the MySQL specific settings from the downloaded  setup gives you, to overwrite the PostgreSQL settings in.
 * 7) Once the MySQL settings are moved in, rename  to.

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  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.
 * 3) * You could try to transfer account information through conversion, but I didn't care enough to do so.
 * 4) All page protections have to be recreated, so hurry and protect your main page and templates before new users sneak in some edits.