PostgreSQL is a powerful, open-source object-relational database system, and phpPgAdmin is a web-based administration tool for managing PostgreSQL databases. In this guide, we will walk you through the process of installing PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS. This setup is ideal for environments such as Windows VPS UK or other VPS hosting platforms.

Prerequisites

Before starting, ensure you have the following:

• An Ubuntu 20.04 LTS server, which can be hosted on a UK Windows VPS, Windows Virtual Private Server Hosting, or other VPS Windows Hosting solution.
• Root or sudo privileges on your server.

Step 1: Update Your System

First, update your system packages to ensure everything is up to date. Run the following commands:

sudo apt update && sudo apt upgrade

Step 2: Install PostgreSQL

PostgreSQL is available in the default Ubuntu repositories. To install PostgreSQL, run the following command:

sudo apt install postgresql postgresql-contrib

After installation, PostgreSQL will start automatically. You can check its status with the following command:

sudo systemctl status postgresql

Step 3: Configure PostgreSQL

Once installed, you can switch to the PostgreSQL user and create a new role with superuser privileges:

sudo -i -u postgres
psql

Inside the PostgreSQL shell, create a new role:

CREATE ROLE myuser WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'mypassword';

Once the role is created, exit the PostgreSQL shell:

\q
exit

Step 4: Install phpPgAdmin

phpPgAdmin is a web-based tool that simplifies the management of PostgreSQL databases. Install it using the following command:

sudo apt install phppgadmin

Step 5: Configure Apache for phpPgAdmin

Since phpPgAdmin runs on a web server, you will need to configure Apache to serve it. Open the Apache configuration file for phpPgAdmin:

sudo nano /etc/apache2/conf-available/phppgadmin.conf

Look for the line that restricts access to phpPgAdmin and change it to allow access from all IPs:

# Restrict access by IP
Require all granted

Save the file and enable the phpPgAdmin configuration:

sudo a2enconf phppgadmin
sudo systemctl restart apache2

Step 6: Access phpPgAdmin

You can now access phpPgAdmin by navigating to http://your-server-ip/phppgadmin in your web browser. Log in using the PostgreSQL user credentials you created earlier.

Step 7: Secure phpPgAdmin

By default, phpPgAdmin is accessible to anyone who knows your server’s IP address. To secure it, you can enable HTTPS by installing a Let's Encrypt SSL certificate. First, install Certbot:

sudo apt install certbot python3-certbot-apache

Next, generate and install the SSL certificate:

sudo certbot --apache -d your-domain.com

Certbot will automatically configure Apache to redirect HTTP requests to HTTPS, securing your phpPgAdmin installation.

Conclusion

By following this guide, you have successfully installed PostgreSQL and phpPgAdmin on Ubuntu 20.04 LTS. This setup provides a powerful database solution along with a user-friendly web-based interface for managing your PostgreSQL databases. Whether you are using a Windows VPS UK or other Windows Virtual Dedicated Server Hosting environments, this configuration is highly efficient and scalable for your needs.