Creating PostgreSQL Database

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

To manage PostgreSQL databases:

1. Log in as CP+ admin.
2. Choose the System Administration CP+ mode.
3. Scroll down to the Databases section and click the PostgreSQL Database Server icon. You will have the following options to choose from:
   - PostgreSQL Databases to create and edit databases;
   - User Options to manage PostgreSQL users, groups and privileges.
   Also you will see buttons: and

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 button.
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: add | delete | manage database users.
• PostgreSQL Groups: add | delete | manage database groups.
• Allowed Hosts: add allowed hosts.
• Granted Privileges: add privileges to users.

PostgreSQL Users

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

To add a new user of database, click Create a new user link. Fill out the following form:
enter user's name, password, allow/disallow to create users and databases, click Create. The new user will appear in the users' list.

To edit existing user, click on it's name in the Username column and edit the needed.
To delete a user, check the box against it's name, click the button.

PostgreSQL Groups

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

To add a group, click Create a new group link. Fill out the following form:
enter group's name, ID, choose members of this group and click Create.

To edit existing group, click on it's name in the Group name column and edit the needed.
To delete a group, check the box against it's name, click the button.

 Allowed Hosts

Option 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:
click Create a new allowed host link, fill out the following form.

• choose host address;
• tick off if SSL connection required;
• choose database from the drop-down box;
• specify users;
• set authentication mode.

Click Create.

To edit existing host entry, click on it's address in the Host address column and edit the needed.
To delete a host, check the box against it's name, click the button.

 Granted Privileges

This option allows to view/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.