How to Create and Manage PostgreSQL Databases in cPanel

PostgreSQL Databases offer a convenient way to store large amounts of data. Many web applications, such as bulletin boards and content management systems, require PostgreSQL databases. To use a database, it must be created, and only users with access privileges can read from or write to it. Note that these users are separate from email or other types of users.

• How to Create a PostgreSQL Database in cPanel
• How to Manage PostgreSQL Users in cPanel
• How to Enable PL/pgSQL
• How to Import or Export a PostgreSQL Database via SSH
• How to Delete Column in PostgreSQL
• Summary

How to Create a PostgreSQL Database in cPanel

1. Access cPanel from your account.
2. In cPanel, scroll down to Databases and select PostgreSQL Databases.
   
3. In the Create New Database section, type a name for your database, then click the Create Database button.
   
4. Click Go Back to manage databases.

How to Manage PostgreSQL Users in cPanel

After creating the database, a user must be created. Please note that PostgreSQL user accounts must be created separately from the mail, MySQL, and web administrator accounts.

Creating a PostgreSQL User

1. To quickly access the PostgreSQL Users, click Jump to PostgreSQL Users.
2. Under Add New User, enter a Username.
   

   Note: When setting up a database, it's important to note that the username you choose cannot exceed 63 characters. If you decide to enable database prefixing, remember that the maximum length also includes the prefix and underscore character.

3. Enter your password in the Password field.
4. Retype your password in the Password (Again) field.
5. Click Create User.

Adding a User to the Database

1. Choose the appropriate user by clicking on the User drop-down menu.
2. Next, select the database you want to add the user to from the Database drop-down menu.
3. Click the Submit button.
   

How to Enable PL/pgSQL

PL/pgSQL is a programming language used with the PostgreSQL database. It extends the functionality of the PostgreSQL database server and is specifically designed to create user-defined functions, stored procedures, and triggers.

PL/pgSQL can be enabled on your databases individually by our Level III technicians. Also, if you wish to have the function plpgsql_call_handler() created in your database, please make sure you mention it since this is no longer done automatically.

How to Import or Export a PostgreSQL Database via SSH

1. Log in to your account using SSH.
2. Enter one of the following commands.
   • To import:

     pg_restore -d db_name file_name.sql

   • To export:

     pg_dump -cif file_name.sql db_name

In the example above, the db_name represents the database name, and the file_name is the file name.

How to Delete Column in PostgreSQL Database via SSH

You can drop multiple or individual columns by using the DROP COLUMN command in SSH. The DROP COLUMN statement goes together with the ALTER TABLE command.

Options such as CASCADE and IF NOT EXISTS can also be used with the DROP COLUMN command for different functionalities.

Use the command below to delete a column in PostgreSQL:

ALTER TABLE tbl_name
DROP COLUMN col_name;

In the example above, ALTER TABLE is the command, and tbl_name is the table name. The DROP COLUMN is also a command, and col_name represents the column to be deleted or dropped.

Summary

PostgreSQL is a powerful tool that can handle vast amounts of data. Learn how to create a PostgreSQL database in cPanel, manage PostgreSQL users, enable PL/pgSQL, import or export a PostgreSQL database via SSH, and delete a column in a PostgreSQL database via SSH. Following these instructions ensures that your PostgreSQL database is set up correctly and managed effectively.