How to Deploy Highly Available PostgreSQL with Single Endpoint for WordPress

WordPress is an open source software you can use to create your website, blog, or application. There are many designs and features/plugins to add to your WordPress installation. WordPress is a free software, however, there are many commercial plugins to improve it depending on your requirements.

WordPress makes it easy for you to manage your content and it’s really flexible. Create drafts, schedule publication, and look at your post revisions. Make your content public or private, and secure posts and pages with a password.

To run WordPress you should have at least PHP version 5.2.4+, MySQL version 5.0+ (or MariaDB), and Apache or Nginx. Some of these versions have reached EOL and you may expose your site to security vulnerabilities, so you should install the latest version available according to your environment.

As we could see, currently, WordPress only supports the MySQL and MariaDB database engines. WPPG is a plugin based on PG4WP plugin, that gives you the possibility to install and use WordPress with a PostgreSQL database as a backend. It works by replacing calls to MySQL specific functions with generic calls that map them to other database functions and rewriting SQL queries on the fly when needed.

For this blog, we’ll install 1 Application Server with WordPress 5.1.1 and HAProxy, 1.5.18 in the same server, and 2 PostgreSQL 11 database nodes (Master-Standby). All the operating system will be CentOS 7. For the databases and load balancer deploy we’ll use the ClusterControl system.

This is a basic environment. You can improve it by adding more high availability features as you can see here. So, let’s start.

Database Deployment

First, we need to install our PostgreSQL database. For this, we’ll assume you have ClusterControl installed.

To perform a deployment from ClusterControl, simply select the option “Deploy” and follow the instructions that appear.

When selecting PostgreSQL, we must specify User, Key or Password and port to connect by SSH to our servers. We also need a name for our new cluster and if we want ClusterControl to install the corresponding software and configurations for us.

After setting up the SSH access information, we must define the database user, version and datadir (optional). We can also specify which repository to use.

In the next step, we need to add our servers to the cluster that we are going to create.

When adding our servers, we can enter IP or hostname.

In the last step, we can choose if our replication will be Synchronous or Asynchronous.

We can monitor the status of the creation of our new cluster from the ClusterControl activity monitor.

Once the task is finished, we can see our cluster in the main ClusterControl screen.

Once we have our cluster created, we can perform several tasks on it, like adding a load balancer (HAProxy) or a new replica.

Load Balancer Deployment

To perform a load balancer deployment, in this case, HAProxy, select the option “Add Load Balancer” in the cluster actions and fill the asked information.

We only need to add IP/Name, port, policy and the nodes we are going to use. By default, HAProxy is configured by ClusterControl with two different ports, one read-write and one read-only. In the read-write port, only the master is UP. In case of failure, ClusterControl will promote the most advanced slave and it’ll change the HAProxy configuration to enable the new master and disable the old one. In this way, we’ll have automatic failover in case of failure.

If we followed the previous steps, we should have the following topology:

So, we have a single endpoint created in the Application Server with HAProxy. Now, we can use this endpoint in the application as a localhost connection.

WordPress Installation

Let’s install WordPress on our Application Server and configure it to connect to the PostgreSQL database by using the local HAProxy port 3307.

First, install the packages required on the Application Server.

$ yum install httpd php php-mysql php-pgsql postgresql
$ systemctl start httpd && systemctl enable httpd

Download the latest WordPress version and move it to the apache document root.

$ wget https://wordpress.org/latest.tar.gz
$ tar zxf latest.tar.gz
$ mv wordpress /var/www/html/

Download the WPPG plugin and move it into the wordpress plugins directory.

$ wget https://downloads.wordpress.org/plugin/wppg.1.0.1.zip
$ unzip wppg.1.0.1.zip
$ mv wppg /var/www/html/wordpress/wp-content/plugins/

Copy the db.php file to the wp-content directory. Then, edit it and change the ‘PG4WP_ROOT’ path:

$ cp /var/www/html/wordpress/wp-content/plugins/wppg/pg4wp/db.php /var/www/html/wordpress/wp-content/
$ vi /var/www/html/wordpress/wp-content/db.php
define( 'PG4WP_ROOT', ABSPATH.'wp-content/plugins/wppg/pg4wp');

Rename the wp-config.php and change the database information:

$ mv /var/www/html/wordpress/wp-config-sample.php /var/www/html/wordpress/wp-config.php
$ vi /var/www/html/wordpress/wp-config.php
define( 'DB_NAME', 'wordpressdb' );
define( 'DB_USER', 'wordpress' );
define( 'DB_PASSWORD', 'wpPassword' );
define( 'DB_HOST', 'localhost:3307' );

Then, we need to create the database and the application user in the PostgreSQL database. On the master node:

$ postgres=# CREATE DATABASE wordpressdb;
CREATE DATABASE
$ postgres=# CREATE USER wordpress WITH PASSWORD 'wpPassword';
CREATE ROLE
$ postgres=# GRANT ALL PRIVILEGES ON DATABASE wordpressdb TO wordpress;
GRANT

And edit the pg_hba.conf file to allow the connection from the Application Server.

$ Vi /var/lib/pgsql/11/data/pg_hba.conf
host  all  all  192.168.100.153/24  md5
$ systemctl reload postgresql-11

Make sure you can access it from the Application Server:

$ psql -hlocalhost -p3307 -Uwordpress wordpressdb
Password for user wordpress:
psql (9.2.24, server 11.2)
WARNING: psql version 9.2, server version 11.0.
         Some psql features might not work.
Type "help" for help.
wordpressdb=>

Now, go to the install.php in the web browser, in our case, the IP Address for the Application Server is 192.168.100.153, so, we go to:

http://192.168.100.153/wordpress/wp-admin/install.php

Add the Site Title, Username and Password to access the admin section, and your email address.

Finally, go to Plugins -> Installed Plugins and activate the WPPG plugin.

Conclusion

Now, we have WordPress running with PostgreSQL by using a single endpoint. We can monitor our cluster activity on ClusterControl checking the different metrics, dashboards or many performance and management features.

There are different ways to implement WordPress with PostgreSQL. It could be by using a different plugin, or by installing WordPress as usual and adding the plugin later, but in any case, as we mentioned, PostgreSQL is not officially supported by WordPress, so we must perform an exhaustive testing process if we want to use this topology in production.