How to deploy WordPress with highly available PostgreSQL

Mohsin Ejaz Software Architect

WordPress uses MySQL as its default database management system. For users who prefer PostgreSQL, this raises a commonly asked question, “Is there any way to use WordPress with PostgreSQL rather than MySQL?”

You can use this guide to get an overview on how to do just that.

Introduction

WordPress is open source software that is commonly used to create websites, blogs, or applications. It comes with many designs and features/plugins that can be added to your WordPress installation. WordPress also supports many commercial plugins that can be used depending on your requirements.

Its flexible content management system makes it easy for you to manage your content—you can create drafts, schedule publication, and look at your post revisions. WordPress also has the option to make your content public or private and secure posts and pages with a password. WordPress is free and available under license GPLv2 (or later) from the Free Software Foundation.

Prerequisites

To run WordPress you should have at least PHP version 5.2.4+, a database (in our case we will be using PostgreSQL), and an Apache or Nginx server. One should always try to install the latest versions available according to your environment, as some of these versions may have reached end-of-life and you may expose your site to security vulnerabilities.

What is PG4WP?

As mentioned above, by default WordPress uses the MySQL database engine. If we want to use WordPress with PostgreSQL instead, we will need some kind of plugin. PostgreSQL for WordPress (PG4WP) is one such plugin that allows you to install and use WordPress with a PostgreSQL database as a backend.

How to install WordPress with PostgreSQL on Centos7

For our example we'll be using Centos 7 as our operating system.

Prerequisites

As discussed above, we will need these following items to be installed on our Centos7 Linux server:

  • Apache or Nginx as web server
  • PHP version 5.6 or higher (in our case we will be using v7.4)
  • Any version of PostgreSQL (in our case we will be using v12)

Step 1: Updating the system and making sure ports are not blocked

We need to make sure that our system firewall / iptables or SELinux is not blocking any port(s) that will be required; otherwise, we might face issues with proper user permissions.

 

To disable SELinux use following command:


       sudo setenforce 0

Once SELinux is disabled, we should also update our system. This is a good practice and will make sure that our system is up to date. This can be done by issuing following commands:


		sudo yum clean all
		sudo yum -y update

This action may require some time depending on the age of your system software and your internet speed.

Step 2: Setting up an Apache web server

To install Apache we need to run following command:


		sudo yum -y install httpd

This will install Apache, and you should see the installation complete message as shown below.

apache installation

Next, we need to start the service by issuing following command:


		sudo systemctl start httpd

If we want to start Apache at boot time, so it gets started automatically without requiring any manual commands, we need to run the following:


		sudo systemctl enable httpd

To confirm that Apache is up and running, we can check the status with the following command:


		sudo systemctl status httpd

This should display service status as running, as shown below:

service status

We can also test it by opening any browser and typing “localhost” or system IP. This will show a “Testing 123” page as shown below:

Test Screen

That's it! We are done with setting up Apache.

Step 3: Setting up PHP

The latest version of WordPress works with PHP versions 5.6 or above. Below we provide the steps to install the latest stable release of PHP, which is version 7.4.

First, we need to add the required repositories. Run the following commands:


sudo yum -y install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm 

sudo yum -y install https://rpms.remirepo.net/enterprise/remi-release-7.rpm

After installing the required repositories, we will now install PHP 7.4 by running the following commands:


		sudo yum -y install yum-utils
		sudo yum-config-manager --enable remi-php74
		sudo yum -y install php php-cli 

Once finished, you should see the following Installation Complete message:

installation complete

Now we have PHP installed, but we need to set up PHP with PostgreSQL. To do that, we will need to install a few other modules by running the following command:


sudo yum -y install php-mysqlnd php-pgsql

Once finished, you should see the Installation Complete message below:

second installation complete

After installing PHP v7.4, we need to restart the Apache service. That can be done by running following command:


		sudo systemctl restart httpd.service

Step 4: Installing the PostgreSQL database

To install PostgreSQL, we first need to install the repo. This can be done by running the following command:


wget 
https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

Once the repo file is downloaded, we need to install it by running the following command:


		sudo rpm -ivh pgdg-redhat-repo-latest.noarch.rpm

This will install the repo. Once done we can proceed to installing PostgreSQL. I will be installing version 12 by running the following command:


		sudo yum install -y postgresql12-*

That’s it! Our PostgreSQL database is installed.

Step 5: Setting up the cluster

Once PostgreSQL is installed, we need to set up the cluster. We can do that by browsing to the bin directory located at “/usr/pgsql-12/bin/” and by running the following commands:


		sudo su - postgres
		cd /usr/pgsql-12/bin/
		./initdb -D /tmp/testdata  --no-locale -E UTF8

This will initialize the database as shown below:

database initialization

Next we need to start the service. Run the following command:


		 ./pg_ctl -D /tmp/testdata start "-o -p 5444"

Step 6: Creating a database and user in PostgreSQL

To create a database, open your terminal, move to your PostgreSQL bin directory, and run following commands in sequence:


		sudo su - postgres
		psql -p 5444
		create database testdb;
		create user testuser with password 'testpassword';
		grant all privileges on database testdb to testuser;

This will create your testdb and testuser with testpassword and will grant the required privileges, as shown below:

test user

Step 7: Downloading and extracting the latest WordPress version

Now comes the part where we need to download the latest version of WordPress, which can be found on their website.

Go to your Apache server directory (i.e., “/var/www/html”) and download the file there by running following command:

  
     wget https://wordpress.org/latest.zip
  

Once the download is complete, unzip the folder and copy the contents back to your required folder. In my case I have created a folder named “test-with-postgres” in “/var/www/html”. This can be done by the running following commands:

  
		unzip latest.zip
		mkdir test-with-postgres
		cp -rp wordpress/* test-with-postgres/
   

Step 8: Configuring PG4WP plugin

Installing the PG4WP plugin before performing WordPress configuration is recommended to avoid issues. As this helps startup the database to ensure that the plugin is loaded successfully

The original PG4WP plugin can be found on the WordPress site. Over time there have been many improvements, and people have released forked versions of the pg4wp plugin, many of which are listed here. In our example we'll be using the version developed by Kevin Locke, as we have found this to be compatible with any version of wordpress so far.

As we need to clone the forked version we will be needing git. To install git run the following command:

    
         yum install git
    

Go to the wp-content directory—which in my case is located at "/var/www/html/test-with-postgres/wp-content“—and clone the PG4WP plugin by using the following command:


		git clone https://github.com/kevinoid/postgresql-for-wordpress.git

Move the content to the wp-content directory:


		mv postgresql-for-wordpress/pg4wp pg4wp

We also need to copy db.php file to the wp-content directory, which can be done by running the following command:


		cp pg4wp/db.php db.php

Step 9: Updating wp-config.php with your database credentials

A sample file by the name of wp-config-sample.php is created for us under our webroot directory, which in our case is “/var/www/html/test-with-postgres”. We can move there, make a copy of it, and rename it wp-config.php by running the following commands:


        cd /var/www/html/test-with-postgres
		cp -rp wp-config-sample.php wp-config.php

We can now update the wp-config.php file with the database name, username, and password that we created in Step 6, as shown below:

wpconfig setup

Step 10: Installing WordPress

Now we are ready to install WordPress. We can do that by going to “http://localhost/test-with-postgres/wp-admin/install.php” and entering the sitename, username, password, and email address as shown below:

Wordpress Installation

By pressing “Install WordPress” we should receive a confirmation message:

Confirmation Message

And we are done!

Step 11: Logging into WordPress and verification of PostgreSQL as backend

We can now log in to the admin panel of our newly created WordPress site by going to “http://localhost/test-with-postgres/wp-admin/” and providing the credentials that we have used in Step 10.

We can also visit the default WordPress page at http://localhost/test-with-postgres/.

To confirm that PostgreSQL is being used as backend, we can log in to the cluster and verify the records in our database by running the following commands:


		sudo su - postgres
		psql -U testuser -p 5444 testdb
		\dt

The \dt command will display all the relations in the current database so we should be able to view the tables that WordPress has created, and this will confirm that PostgreSQL has been used as backend for WordPress:

command dt

I hope that this article has proven helpful and given you some insight to the level of ease with which deployment of WordPress with PostgreSQL is possible.

Mohsin EjazSoftware Architect

Mohsin Ejaz is a Software Architect with over 14 years of experience in designing, building, packaging, and automating builds and test suites for business solutions and is part of the Configuration Management (CM) Team.