Caprover - Rad Web Hosting Blog

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. Confirm PostgreSQL Is Enabled on Your cPanel Host Not all hosting providers enable PostgreSQL by default. Here's how to check: Log in to your cPanel. Scroll to the Databases section. 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. Create a PostgreSQL Database Using the PostgreSQL Databases tool: Go to PostgreSQL Databases under the Databases section. In the Create New Database field, enter a name (e.g., myapp_db). Click Create Database. The actual database name will usually be prefixed with your cPanel username (e.g., user_myapp_db). Create a PostgreSQL User You must create a user and assign it to the database. Scroll to the Add New User section. Enter a username and password. Click Create User. Make sure the password is strong — use a generator if needed. Assign User to Database In the Add User to Database section: Select the user and database from the dropdowns. Click Add. On the next screen, set user privileges (typically "ALL PRIVILEGES" for web apps). Click Make Changes. Manage the Database Once your database is set up, you can manage it in a few ways: a. phpPgAdmin (GUI tool) Open phpPgAdmin from cPanel. Select the database. 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. In cPanel, go to Remote PostgreSQL (if available). Add your IP address to allow connections. Use connection details: Host: your domain or server IP Port: 5432 (default) DB/User: as created Password: as set Backup and Restore PostgreSQL Databases Backup Go to phpPgAdmin. Select the database. Click Export. Choose format (SQL or plain text) and options. Click Download. Restore Go to phpPgAdmin. Select the database. Click SQL. Paste SQL script or upload the file. Click Execute. Integrating with Web Applications In your web app’s config file, use your PostgreSQL connection details: $db pg_connect("hostyourdomain.com dbnameuser_myapp_db useruser_pg passwordyourpass"); Ensure your host supports persistent connections and that your app’s code is secure (e.g., using parameterized queries). 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 Security Tips Use strong passwords. Regularly update your credentials. Only whitelist trusted IPs for remote access. Periodically review database users and remove unused ones. 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.

Rad Web Hosting Blog

Tag: caprover

🚀 How to Deploy CapRover on Ubuntu VPS

Services

Categories

Recent Posts

Save on VPS Hosting!

We’re Social!

Rad Web Hosting Blog