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.