Full Guide to PostgreSQL Database Management in cPanel

Here’s a comprehensive guide on PostgreSQL database management in cPanel accounts, designed for developers, sysadmins, or site owners using cPanel-managed hosting.

What is PostgreSQL?

PostgreSQL is an open-source, object-relational database management system (ORDBMS) known for its robustness, feature set, and standards compliance.

Key Points:

• Open Source: Free to use, modify, and distribute.
• Relational & Object-Oriented: Supports traditional SQL along with advanced data types and custom functions.
• ACID Compliant: Ensures data reliability through Atomicity, Consistency, Isolation, and Durability.
• Extensible: You can define your own data types, operators, and functions.
• Cross-Platform: Runs on Windows, macOS, and Unix/Linux systems.
• Strong SQL Compliance: Implements a large part of the SQL standard.
• Advanced Features:
• MVCC (Multi-Version Concurrency Control)
• Full-text search
• Table inheritance
• JSON and JSONB support for document-based queries
• Geospatial data with PostGIS extension

Typical Use Cases:

• Web applications (e.g., Django, Ruby on Rails, Laravel)
• Data warehousing and analytics
• GIS (geographic information systems)
• Enterprise systems needing strong consistency and complex queries

In short, PostgreSQL is a reliable choice for any application where data integrity, scalability, and advanced SQL features are priorities.

PostgreSQL Database Management in cPanel: A Complete Guide

This guide walks you through how to create, configure, and manage PostgreSQL databases from your cPanel account.

1. Confirm PostgreSQL Is Enabled on Your cPanel Host

   Not all hosting providers enable PostgreSQL by default. Here’s how to check:

   1. Log in to your cPanel.
   2. Scroll to the Databases section.
   3. Look for an option like PostgreSQL Databases or PostgreSQL Database Wizard.

   ❗ If you don’t see these options, your host may not support PostgreSQL, or you may need to upgrade your plan.

2. Create a PostgreSQL Database

   Using the PostgreSQL Databases tool:

   1. Go to PostgreSQL Databases under the Databases section.
   2. In the Create New Database field, enter a name (e.g., myapp_db).
   3. Click Create Database.

   The actual database name will usually be prefixed with your cPanel username (e.g., user_myapp_db).

3. Create a PostgreSQL User

   You must create a user and assign it to the database.

   1. Scroll to the Add New User section.
   2. Enter a username and password.
   3. Click Create User.

   Make sure the password is strong — use a generator if needed.

4. Assign User to Database

   1. In the Add User to Database section:
   • Select the user and database from the dropdowns.
   1. Click Add.
   2. On the next screen, set user privileges (typically “ALL PRIVILEGES” for web apps).
   3. Click Make Changes.

5. Manage the Database

   Once your database is set up, you can manage it in a few ways:

   a. phpPgAdmin (GUI tool)

   1. Open phpPgAdmin from cPanel.
   2. Select the database.
   3. You can:
   • Run SQL queries.
   • Create/modify tables.
   • Browse data.
   • Backup or restore.

   phpPgAdmin is the PostgreSQL equivalent of phpMyAdmin.

   b. Remote Access

   You can connect to your PostgreSQL server remotely with tools like pgAdmin or DBeaver.

   1. In cPanel, go to Remote PostgreSQL (if available).
   2. Add your IP address to allow connections.
   3. Use connection details:
   • Host: your domain or server IP
   • Port: 5432 (default)
   • DB/User: as created
   • Password: as set

6. Backup and Restore PostgreSQL Databases

   Backup

   1. Go to phpPgAdmin.
   2. Select the database.
   3. Click Export.
   4. Choose format (SQL or plain text) and options.
   5. Click Download.

   Restore

   1. Go to phpPgAdmin.
   2. Select the database.
   3. Click SQL.
   4. Paste SQL script or upload the file.
   5. Click Execute.

7. Integrating with Web Applications

   In your web app’s config file, use your PostgreSQL connection details:

   $db = pg_connect("host=yourdomain.com dbname=user_myapp_db user=user_pg password=yourpass");

   Ensure your host supports persistent connections and that your app’s code is secure (e.g., using parameterized queries).

8. Common Issues & Fixes

   Issue	Cause	Fix
   Can’t connect remotely	IP not whitelisted	Add your IP in Remote PostgreSQL
   “Permission denied” errors	Insufficient privileges	Recheck privileges when assigning user to DB
   phpPgAdmin not loading	Server misconfig or not enabled	Contact host or use local pgAdmin with remote connection

9. Security Tips

   • Use strong passwords.
   • Regularly update your credentials.
   • Only whitelist trusted IPs for remote access.
   • Periodically review database users and remove unused ones.

10. Automating PostgreSQL Management

    If your host supports cron jobs or SSH access, you can:

    • Use pg_dump for automated backups.
    • Write bash scripts for export/import.
    • Use REST APIs (if provided by your host) to automate DB creation or user provisioning.

Conclusion

PostgreSQL database management in cPanel is straightforward once you’re familiar with the steps. You can do everything from creating databases and users to remote access and backups — all through a web interface.