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.
- 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
$ 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
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
- Copy your current
/var/www/mediawikidirectory to a copy: for example,
- Enable access to the
/mw-config/directory on the webserver, and run the installation script as if it were a new wiki.
- Provide the root MySQL username and password to the setup, and have MediaWiki create a nonprivileged DB user.
- Run setup to create a new admin account and get as close to your original
LocalSettings.php.bakconfiguration as you can.
- But after the MySQL database is initialized, move only the MySQL specific settings from the downloaded
LocalSettings.phpsetup gives you, to overwrite the PostgreSQL settings in
- Once the MySQL settings are moved in, rename
Restoring Wiki Data
- Now, restore your XML Dump, and make sure to regenerate RecentChanges as stated there.
- Next, restore your images by extracting the tarball created by mediawikiwiki:Manual:DumpUploads.php, and then using mediawikiwiki:Manual:ImportImages.php (add the
--search-recursivelyargument to search under subdirectories).
- Finally, to make sure your extensions work, update your database.
- The Main Page may still have the MediaWiki default content, but just open Page History and "revert" to your previous main page.
- 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.
- All page protections have to be recreated, so hurry and protect your main page and templates before new users sneak in some edits.