Editing PostgreSQL/MediaWiki

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

However, it is clearly not the most popular choice of database for Mediawiki, so we do make some workarounds to support this unique use case. These mods are noted below.

=== PostgreSQL with UNIX Sockets ===

As noted in the Mediawiki tutorial, you generally connect to the PostgreSQL over a TCP connection, and use <code>md5</code> 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 <code>postgres</code> superuser, so you can log into it without using <code>peer</code> authentication, which we are going to switch to <code>md5</code>.

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

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

<pre>
# "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
</pre>

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

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

# Postgres specific settings
#$wgDBport = "5432"; # disable this
</pre>

{{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 <code>$wgDBServer</code> 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 [[mediawikiwiki:Manual:DumpBackup.php|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.

As for your images, if you leave them in images/ (or wherever you specified in {{ic|LocalSettings.php}}) after importing the XML backup in the next step, all the images should still work.

=== Move to MySQL ===

# Copy your current {{ic|/var/www/mediawiki}} directory to a copy: for example, {{ic|/var/www/mediawiki-mariadb}}.
# In {{ic|/var/www/mediawiki-mariadb}}, move {{ic|LocalSettings.php}} to {{ic|LocalSettings.php.bak}}.
# Enable access to the {{ic|/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 {{ic|LocalSettings.php.bak}} configuration as you can.
# But after the MySQL database is initialized, move only the MySQL specific settings from the downloaded {{ic|LocalSettings.php}} setup gives you, to overwrite the PostgreSQL settings in {{ic|LocalSettings.php.bak}}.
# Once the MySQL settings are moved in, rename {{ic|LocalSettings.php.bak}} to {{ic|LocalSettings.php}}.
# Now, restore your [[mediawikiwiki:Manual:Restoring_a_wiki_from_backup#From_an_XML_dump|XML Dump]], and make sure to regenerate RecentChanges as stated there.
# Finally, to make sure your extensions work, [[mediawikiwiki:Manual:Update.php#From_the_command_line|update your database.]]

=== Cleanup ===

# 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.
@@ Line 1: / Line 1: @@
-PostgreSQL was initially used as our database for a number of reasons, from stability to compatibility with other apps to support for JSONB values.
+PostgreSQL is 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.
+However, it is clearly not the most popular choice of database for Mediawiki, so we do make some workarounds to support this unique use case. 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 ===
@@ Line 62: / Line 57: @@
 Use [[mediawikiwiki:Manual:DumpBackup.php|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 ===
+As for your images, if you leave them in images/ (or wherever you specified in {{ic|LocalSettings.php}}) after importing the XML backup in the next step, all the images should still work.
-Note that all images have to be backed up and reimported for the database to recognize them. Just use [[mediawikiwiki:Manual:DumpUploads.php|DumpUploads.php]] to make the backup, and it will be imported in the next step.
 === Move to MySQL ===
@@ Line 75: / Line 68: @@
 # But after the MySQL database is initialized, move only the MySQL specific settings from the downloaded {{ic|LocalSettings.php}} setup gives you, to overwrite the PostgreSQL settings in {{ic|LocalSettings.php.bak}}.
 # Once the MySQL settings are moved in, rename {{ic|LocalSettings.php.bak}} to {{ic|LocalSettings.php}}.
-=== Restoring Wiki Data ===
 # Now, restore your [[mediawikiwiki:Manual:Restoring_a_wiki_from_backup#From_an_XML_dump|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 {{ic|--search-recursively}} argument to search under subdirectories).
 # Finally, to make sure your extensions work, [[mediawikiwiki:Manual:Update.php#From_the_command_line|update your database.]]