How to Create and Manage PostgreSQL Databases Print

PostgreSQL databases allow you to store and manage structured data for your websites and applications. You can create, manage, and delete PostgreSQL databases and users through your hosting control panel, making it easy to set up powerful database-driven sites without technical complexity.

This guide covers everything you need to know about working with PostgreSQL databases in your hosting environment, from creating your first database to managing users and maintaining your database structure.

Getting Started with PostgreSQL Databases

PostgreSQL is an open-source relational database management system that provides advanced features for data storage and retrieval. When you create a PostgreSQL database through your hosting control panel, you get a secure environment for storing information that your websites and applications can access.

For your first database setup, we recommend using the PostgreSQL Database Wizard interface, which guides you through the entire process step by step. This wizard makes it simple to create both a database and a user with proper permissions in just a few clicks.

Important Database Naming Rules

When creating databases, keep these naming requirements in mind:

• Database names can be up to 63 characters long
• If database prefixing is enabled, this length includes your username prefix and underscore character
• Use only letters, numbers, and underscores in database names
• Avoid special characters that might cause connection issues

How to Create a PostgreSQL Database

Creating a new PostgreSQL database is straightforward. Follow these steps to set up a database for your application:

1. Navigate to the PostgreSQL Databases interface in your control panel
2. In the Database Name text box, enter a descriptive name for your database
3. Click Create Database to initialize the new database
4. Click Go Back to return to the main interface

Your new database will appear in the Current Databases table immediately. You can now add users and assign them permissions to access this database.

Managing Database Users

After creating a database, you need to create users and grant them access. Database users are separate accounts that applications use to connect to and manipulate data in your databases.

Creating Database Users

To create a new PostgreSQL user:

1. Locate the Add New User section in the PostgreSQL Databases interface
2. Enter a username in the Username field
3. Create a strong password or use the password generator
4. Click Create User to add the new user account

Adding Users to Databases

After creating both a database and a user, you must grant the user permission to access the database:

1. Find the Add User to Database section
2. Select the user from the User dropdown menu
3. Select the database from the Database dropdown menu
4. Click Add to grant access
5. On the next page, select the privileges you want to grant
6. Click Make Changes to apply the permissions

Understanding Current Databases Table

The Current Databases table displays all databases in your account with the following information:

• Database: The full name of the database including any prefix
• Size: Current storage space used by the database
• Privileged Users: List of users who have access to manage this database
• Actions: Quick access icons for managing the database

This table helps you monitor database usage and quickly identify which users have access to each database.

Removing Users from Databases

If you need to revoke a user's access to a specific database without deleting the user account entirely, follow these steps:

1. Locate the database in the Current Databases table
2. Find the user you want to remove in the Privileged Users column
3. Click Delete next to that user's name
4. Click Yes to confirm the removal

This removes only the user's privileges for that specific database. The user account itself remains active and can still access other databases where they have permissions.

Renaming a PostgreSQL Database

You can rename existing databases if needed, but be aware of important considerations before proceeding. When you rename a database, the system terminates all active connections to that database to prevent data corruption during the rename process.

Before Renaming a Database

Keep these critical points in mind:

• All active database connections will be terminated immediately
• You must manually update configuration files in your applications to use the new database name
• Larger and more complex databases require more time to rename
• Test your applications thoroughly after renaming to ensure proper connectivity

Steps to Rename a Database

1. In the Current Databases table, locate the database you want to rename
2. Click the Rename icon in the Actions column
3. Enter the new database name
4. Click Proceed to complete the rename operation
5. Update all application configuration files with the new database name

Deleting PostgreSQL Databases

When you no longer need a database, you can permanently delete it to free up resources. This action cannot be undone, so make sure you have backups of any important data before proceeding.

To delete a database:

1. Find the database in the Current Databases table
2. Click Delete in the Actions column
3. Confirm the deletion by clicking Yes

The database and all its contents will be permanently removed from your account.

Working with phpPgAdmin

The phpPgAdmin interface provides a web-based tool for managing the data inside your PostgreSQL databases. You can use phpPgAdmin to run queries, view table contents, and manipulate data without writing code.

To access phpPgAdmin:

1. Navigate to the phpPgAdmin interface in your control panel
2. Select the database you want to manage
3. Use the interface to run SQL queries and manage data

Important: Do not use phpPgAdmin to create databases or database users. Always create databases and users through the PostgreSQL Databases interface to ensure proper configuration and permissions.

Best Practices for PostgreSQL Database Management

Follow these recommendations to maintain secure and efficient PostgreSQL databases:

• Use strong, unique passwords for all database users
• Grant only the minimum privileges necessary for each user
• Regularly back up your databases to prevent data loss
• Monitor database sizes to stay within your hosting plan limits
• Delete unused databases and users to improve security
• Keep application configuration files updated with correct database credentials
• Test database connections after making any changes to users or permissions

Troubleshooting Common Issues

Connection Failures

If your application cannot connect to the database, verify these settings:

• Database name is spelled correctly in your configuration files
• Database user has proper privileges assigned
• Username and password are correct
• Your application is using the correct hostname for database connections

Permission Denied Errors

When you see permission denied errors, the database user likely lacks necessary privileges. Return to the PostgreSQL Databases interface and ensure the user has been added to the database with appropriate permissions.

Database Size Limits

If you're approaching your hosting plan's database size limits, consider these options:

• Remove unnecessary data from your databases
• Optimize tables to reduce storage requirements
• Archive old data to separate databases
• Contact OBHost support to discuss upgrading your hosting plan

Frequently Asked Questions

Can I create PostgreSQL databases without using the wizard?

Yes, you can create databases directly through the main PostgreSQL Databases interface. Simply enter a database name and click Create Database. However, the wizard is recommended for first-time users because it guides you through creating both the database and user in one streamlined process.

What happens to database users when I delete a database?

Deleting a database does not automatically delete the users who had access to it. Database users remain in your account and can be assigned to other databases. If you no longer need a user account, you must delete it separately through the Current Users section.

How do I find my database connection details for my application?

Your application typically needs four pieces of information: the database name, username, password, and hostname. You can find the database name and assigned users in the Current Databases table. The hostname is usually localhost for applications on the same server. Check your hosting account details or contact support if you need specific connection information.

Is there a limit to how many PostgreSQL databases I can create?

Database limits depend on your hosting plan. You can view your current usage and limits in your hosting control panel. If you need additional databases beyond your plan's allocation, consider upgrading your hosting package or contact the OBHost team to discuss your requirements.

For assistance with PostgreSQL databases or any other hosting questions, our support team is available 24/7. Visit https://www.obhost.net/contact or email support@obhost.org for help with your database management needs.