Creating PostgreSQL Database

This document tells you how to create and manage PostgreSQL databases.

To be able to manage PostgreSQL databases, you have to be logged in as CP+ admin. Once you are there, do the following

1. Choose the System Administration CP+ mode.
2. Scroll down to the Databases section and click the PostgreSQL Database Server icon. You will see the following two sections:
   - PostgreSQL Databases to create and edit databases;
   - User Options to manage PostgreSQL users, groups and privileges.
3. Also you will see two buttons: Stop PostgreSQL Server and Backup Databases

PostgreSQL Databases

To create a PostgreSQL database:

1. Go to the PostgreSQL Database Server page.



2. Click Create a new database.
3. Fill the database creation form that shows:



• Enter Database name;
• Enter Database File Path or choose default location.
• Specify the owner in Owned by user section from drop-down box or choose default owner.
4. Click Create. Your new database appears in the database list.
5. To add more tables to your database, go to the database and click Create a new table.
6. Fill the table creation form that shows:
   

   

   • Enter Table name;
   • Set the Initial fields structure: enter field names, data type and, optionally, type width; choose field options.
7. Click Create.

To edit a specific table, click the table name. On the page that shows you can see:

• Click the field name to modify field parameters. You can change field name, data type, type width. Make sure to click Save to preserve changes.
• Add more fields by choosing the data type from the drop-down box. Fill the form that shows and click Create.
• Click the View Data button to see the data entered into the table.
• Click the Drop Table button to delete table.
• Click the Drop Field button to delete field.

To manage databases, go to the table list and click the name of the database. On the page that shows:

• Click Drop database to delete a particular database.
• Click Backup database to back up all data stored in the database.
• Click Execute SQL to enter SQL command that can be executed on particular database every time you click this button.
• Click Restore to restore backed up database. Fill the form and click Restore.

Managing PostgreSQL Users

To change PostgreSQL user permissions and privileges, switch between User Options icons on the PostgreSQL Database Server page:

• PostgreSQL Users: click this icon to add, delete and manage database users.
• PostgreSQL Groups: click this icon to add, delete and manage database groups.
• Allowed Hosts: click this icon to add allowed hosts.
• Granted Privileges: click this icon to add privileges to users.

PostgreSQL Users

This option allows you to add users to your databases, allow them to create databases, users and set the period they are valid until. This option also allows to configure synchronization between Unix users created through CP+ and PostgreSQL users.

To add a new user of your database, click Create a new user. You will see the following form:

Enter user name, password, allow/disallow to create users and databases and click Save. The new user will appear in the users list.

To edit existing user, click the username and edit the form. To delete a user, click the Delete button on the Edit User page.

PostgreSQL Groups

This option allows you to create and edit PostgreSQL groups, set permissions for them.

To add a group, click Create a new group. The following form will show:

Enter group name, group ID, choose members of this group and click Create.

To edit existing group, click the group name and edit the form. To delete a group, click the Delete button on the Edit Group page.

 Allowed Hosts

This option allows you to add hosts allowed for database. When a client connects to the database, hosts are processed in order until one matches and the client is allowed or denied.

To add a new allowed host:

1. Click Create a new allowed host. The following form will show:

   

2. Fill the form:
• Choose host address;
• Tick off if SSL connection required;
• Choose database from the drop-down box;
• Specify users;
• Set authentication mode.
3. Click Create.

To edit existing host entry, click the host address and edit the form. To delete a host, click the Delete button on the Edit Allowed Host page.

 Granted Privileges

This option allows you can view and edit privileges and grant them to users.

To grant a privilege to a specific user:

1. Click the Granted Privileges icon on the PostgreSQL Server page.
2. Choose object from the list that shows.



3. On the page that shows select privileges and choose user to grant them to:

   

4. Click Save.

Backup All Databases

• Specify Backup files directory;
• Choose Backup file format from the drop-down box;
• Set Command to run before backup and Command to run after backup if needed;
• Scheduled backup enabled? - if you choose "Yes", it allows you to set the most convenient backup schedule