Applicable Plans - eApps Cloud Hosting Plans (eApps templates only)

User Guide - PostgreSQL and phpPgAdmin User Guide

Overview

“PostgreSQL is a powerful, open source object-relational database system. It has more than 15 years of active development and a proven architecture that has earned it a strong reputation for reliability, data integrity, and correctness” - from http://www.postgresql.org/about/

“phpPgAdmin is a web-based administration tool for PostgreSQL. It is perfect for PostgreSQL DBAs, newbies and hosting services” - from http://phppgadmin.sourceforge.net/

This User Guide is designed to help you install PostgreSQL and phpPgAdmin on your Virtual Machine, and to do some minor configuration. This User Guide is not intended to be a tutorial on how to use PostgreSQL. You will need to consult the official PostgreSQL documentation or any of the several books available about PostgreSQL if you need to come up to speed on how to actually use the database.

See the Links to other information at the end of this document if you need detailed information about PostgreSQL.

Installing PostgreSQL and phpPgAdmin

Creating databases and database users
Creating Users and Databases - command line
Creating Users and Databases - Control Panel
Creating Users and Databases - phpPgAdmin

Starting and stopping PostgreSQL
Control Panel
Command line

PostgreSQL configuration

Importing Content to a PostgreSQL database

PostgreSQL Remote Access

Backing up PostgreSQL databases - command line, phpPgAdmin, Control Panel
Command line
phpPgAdmin Control Panel

Upgrading PostgreSQL

Links to other information

Installing PostgreSQL and phpPgAdmin

PostgreSQL and phpPgAdmin are installed from the Applications section of the Control Panel. See the User Guide: Installing and Managing Applications - http://support.eapps.com/control_panel_eapps/app_manager for more information if needed.

Creating databases and database users

PostgreSQL databases can either be created from the command line, from the Control Panel, or from phpPgAdmin.

When PostgreSQL is installed, a postgres user is created that is the superuser for the PostgreSQL database server. If phpPgAdmin is installed, there is also a pgadmin user that is used to log in to the phpPgAdmin application.

Having multiple database users is not necessary unless you are going to have multiple people managing multiple databases. For most deployments the default postgres user is sufficient.

If you wish to learn more about creating new database users and database roles, see the official PostgreSQL documentation for this topic - http://www.postgresql.org/docs/8.3/static/user-manag.html

Creating Users and Databases - command line

To create a user or a database for PostgreSQL from the command line, you will need to connect to your Virtual Machine using SSH or the Console. See the User Guide: Connecting to your Virtual Machine (SSH and Console) - http://support.eapps.com/portal/ssh for more information.

createuser

The createuser command is used to create new database users. The command must be run as the postgres user.

The man page for createuser has information on all the available switches - http://www.postgresql.org/docs/8.3/static/app-createuser.html

The password that you choose must pass the cracklib verification, meaning that it must have a minimum of six (6) characters, and at least one special character such as !@#$%. This feature is mandatory, and will not be disabled.

createdb

The createdb command is used to create new databases. The command has to be run as the postgres user.

The man page for createdb has information on all the switches - http://www.postgresql.org/docs/8.3/static/app-createdb.html

This is just a very brief example of the createuser and createdb commands. If you are going to use these commands, you will need to read the official documentation so that you can use these commands correctly.

Creating Users and Databases - Control Panel

To create a user or database for PostgreSQL from the Control Panel, you will need to log in to the Control Panel, and then in the main System screen click on PostgreSQL Database Server

This takes you to the main PostgreSQL Database Server screen.

Creating a User

To create a PostgreSQL user, click on the PostgreSQL Users icon

This takes you to the PostgreSQL Users screen, which shows the existing users and their privileges.

Click on Create a new user to create the new PostgreSQL user.

This brings up the Create User screen.

PostgreSQL user details

• Username - enter a name for the database user, in lower case letters

• Password - select None for no password, or Set to and enter the password for this user. If you choose a password, it must contain a minimum of six (6) characters, and at least one special character such as !@#$%.

• Can create databases? - select Yes or No (default is No)

• Can create users? - select Yes or No (default is No)

• Valid until - the default is Forever or you can set a date for the user to expire

Once you have made your selections, click Create to create the new database user. This new user will now show in the PostgreSQL Users list. You can also delete users by clicking on the box next to their Username, and then on Delete Selected.

Creating a Database

To create a new database, click on Create a new database in the main PostgreSQL Database Server screen, in the PostgreSQL Databases section.

This brings up the Create Database screen.

• Database name - enter a name for the database, in lower case letters

• Owned by user - select Default for the postgres user, or select a user from the drop down menu

• Character set encoding - select Default to use the same CharSet as the server, or enter another Character Set here

• Database file path - select Default to use the system defined path for the database, or enter the absolute path to a location on the server

Once you have made your selections, click on Create to create the new database. This database will now show in the PostgreSQL Databases list. You can delete (drop) the database by clicking on the box next to the name of the database, and then on Drop Selected Databases.

Creating Users and Databases - phpPgAdmin

phpPgAdmin allows you to manage your PostgreSQL databases and users from a browser based interface.

phpPgAdmin is a very powerful application, and it is impossible to cover all aspects of it in this User Guide. If you need assistance with phpPgAdmin beyond creating a user or a database, you will need to refer to the official documentation, found on the phpPgAdmin home page - http://phppgadmin.sourceforge.net/

To log in to phpPgAdmin, you will need to set a password for the pgadmin user (the postgres user cannot log in to phpPgAdmin for security reasons). To set the password for pgadmin, click on PostgreSQL Users in the main PostgreSQL Database Server screen.

Click on the pgadmin user, and in the Password field, click on Set to and set a password for that user. Remember that the password must be a minimum of six (6) characters, and have at least one special character such as !@#$^. Click on Save.

Connecting to phpPgAdmin

You access phpPgAdmin from your browser, by either going to any of the domain names on your server, or by using the IP address. The application is located at /pgadmin, so the URL would be either http://eapps-example.com/pgadmin or http://IP_ADDRESS/pgadmin (substituting your domain name for eapps-example.com or your IP address for IP_ADDRESS).

To log in to phpPgAdmin, click on the PostgreSQL link on the left, with a red X next to it.

This brings up the login screen.

• Username - pgadmin

• Password - the password you set for the pgadmin user

Click on Login.

This takes you to the main phpPgAdmin screen, which shows an expandable tree view of the databases in the left pane, and by default the Roles tab with the database users in the right pane.

Creating a role (database user)

To create a new role, or database user, click on Create role.

This takes you to the screen where you can enter the Create role (database user) screen.

• Name - enter a name for the new role, in lower case letters

• Password - enter a password for this role. The password will have to contain a minimum of six (6) characters, and at least one special character such as @#$%^.

• Confirm - re-enter the password to confirm

• Superuser? - check the box if the role will have Superuser privileges

• Create DB? - check the box if the role will be able to create databases

• Can create role? - check the box if the role will be able to create another role

• Inherits privileges? - check the box if the new role inherits the privileges of the role being used to create it

• Can login? - check the box if the role will be allowed to log in to PostgreSQL

• Connection limit - enter the value for the concurrent connection limit (leave empty for no limit)

• Expires - if the role will expire after a certain date, enter that date here. Leave blank for no expiration date

• Member of - select the roles that this new role will also be a member of

• Members - select the roles that will be a member of this new role

• Admin Members - select the roles that will be able to grant membership to other roles into this role

Click Create to create the new role, or Cancel.

Creating a database

To create a new database, click on the Databases tab. This shows all the existing databases.

To create a new database, click on Create Database

This brings up the Create database screen.

• Name - enter the name of the database

• Encoding - the default is set by the locale of the operating system. If you need to use a different encoding, select that from the drop down menu

• Comment - enter an optional comment about the database

Click Create to create the database, or Cancel.

Starting and stopping PostgreSQL

PostgreSQL can be stopped or started from either the command line of the server, or from the Control Panel. There isn’t a way to stop or start PostgreSQL from phpPgAdmin.

Control Panel

To stop PostgreSQL from the Control Panel, click on Stop PostgreSQL Server from the main PostgreSQL Database Server screen.

To start PostgreSQL from the Control Panel, click on Start PostgreSQL Server. If the database server is stopped, this will be the only available option from the PostgreSQL Database Server screen.

Once PostgreSQL is restarted, the main PostgreSQL Database Server screen will return.

Command line

To stop, start or restart PostgreSQL from the command line, you will need to be at the command line of the Virtual Machine. Then, as the root user, use one of the following commands:

Check the status of PostgreSQL

Stopping PostgreSQL

Starting PostgreSQL

Restarting PostgreSQL

PostgreSQL configuration

There are many configuration options available for PostgreSQL, far too many to detail in this User Guide. If you need to make major configuration changes to PostgreSQL, it is assumed that you are a PostgreSQL expert, or have access to someone who is. The default configuration for PostgreSQL should work fine for the majority of users, and is the only configuration supported by eApps.

One common configuration change that some customers do make is optimize PostgreSQL for a large database. While not specifically supported by eApps, this is an easy change to make. This change is useful if you have a lot of large tables, or a lot of “ORDER BY” statements in your database.

This is done by increasing the memory available for PostgreSQL, which is 1 MB by default. The database can and will use more memory than that, but the 1 MB value is what the PostgreSQL server allocates for the databases to work in.

The file to change is the postgresql.conf file, located in /var/lib/pgsql/data. You can edit this file using a text editor from the command line of the VM or from the File Manager in the Control Panel.

Look for this line:

Which is in the RESOURCE USAGE (except WAL) section of the file.

Uncomment the line, and change the value to at least 8 MB, and save and exit the file.

Restart PostgreSQL for the changes to take effect.

Importing Content to a PostgreSQL database

To import data into a PostgreSQL database, you will need to use the command line psql tool. To use this, you will need to log in to the command line of the VM.

This is a very simple example to show basic command syntax. See the full documentation for psql for more information - http://www.postgresql.org/docs/8.3/static/app-psql.html

This command can be run as any system user. The -d switch is for the name of the database to import content into, and the -U switch specifies the PostgreSQL user to connect as. The dump.sql is the name of the file to be imported. You will be prompted for the password for the PostgreSQL user.

PostgreSQL Remote Access

By default, PostgreSQL only allows connections from localhost/127.0.0.1. If you need to enable remote connections to PostgreSQL, you will need to edit two files, either from the command line of the VM. You can also use the File Manager in the Control Panel.

The two files to edit are postgresql.conf and pg_hba.conf, both in the /var/lib/pgsql/data directory.

postgresql.conf

Change this line:

to this - replacing ‘localhost’ with ‘*’ (an asterisk)

and save and exit the file. You can also substitute an IP address or addresses if you only want to allow connections from those specific locations.

pg_hba.conf

Add this line at the very bottom of the file, under the existing connection entries:

and save and exit the file. You will need to restart PostgreSQL for the changes to take effect.

Backing up PostgreSQL databases - command line, phpPgAdmin, Control Panel

By default, there are no backups taken of your Virtual Machine. Please read the User Guide: Managing Virtual Machine Backups - http://support.eapps.com/portal/backups for more information. If you want to make backups of your VM that you can store on the storage array, you will need to purchase additional backup space.

You need to take responsibility for backing up your mission critical data! If the data is important to you or your business, making sure you have current backups needs to be one of your top priorities. Do not rely on eApps to have your data!

Command line

Using pg_dump to back up your databases

To back up your databases using pg_dump, you will need to connect to your VM and work from the command line

The pg_dump command will need to be run as the postgres user, not as the system root user. You can also specify a specific PostgreSQL user from the command line.

See the official documentation for pg_dump for more information - http://www.postgresql.org/docs/8.3/static/backup-dump.html

The man page for pg_dump has a reference to all the switches for the command - http://www.postgresql.org/docs/8.3/static/app-pgdump.html

There is also a pg_dumpall command to backup all databases. See the pg_dump documentation for more information.

phpPgAdmin

The phpPgAdmin program has an Export feature that can be used to backup your databases. You can backup either single databases, or all databases. To start, log in to phpPgAdmin.

All Databases

To back up all databases, click on the entry for the PostgreSQL server at the top of the directory tree in the left pane. Then click on the Export tab

Here you can choose the Format and the Format Options, and then choose to either Show or Download the databases. Show will print the databases to the screen, and Download will download the databases to your local computer in a file named dump.sql. Rename the file with a name relevant to you, but leave the file extension of .sql as is. Click on Export once you have made your choices.

Single Database

To backup a single database, click on the name of the database, and then in the next screen click on the Export tab

Here you can choose the Format and the Format Options, and then choose to either Show or Download the databases. Show will print the databases to the screen, and Download will download the databases to your local computer in a file named dump.sql. Rename the file with a name relevant to you, but leave the file extension of .sql as is. You can also choose to Download compressed with gzip. Click on Export once you have made your choices..

Control Panel

To backup PostgreSQL databases from the Control Panel, you will need to log in to the Control Panel, and then in the main System screen click on PostgreSQL Database Server

This takes you to the main PostgreSQL Database Server screen.

Backup all databases

To backup all databases, click on Backup Databases under the User, Groups and Permissions section.

This takes you to the Backup All Databases screen, where the backup options to backup all databases are configured.

Backup destination

• Backup files directory - this is the location where the database backup will be created. You can also enter or browse for the absolute path if you want to change the location.

• Create destination directory - Yes or No (the default). This will create the destination directory for the database backup if it does not already exist.

• Backup file format - choose the file format from the drop down menu. The default is Plain SQL text, which is a .sql file. You can also choose from tar archive or Custom Archive, which is PostgreSQL’s custom backup format which is compressed and supports large objects.

Other backup options

• Command to run before backup - enter the absolute path to a script or binary to run before the backup starts

• Command to run after backup - enter the absolute path to a script of binary to run after the backup completes

• When to run commands - choose when to run these commands: Before/after each database or Before/after all databases

Backup Schedule

You can schedule the backup to run at specific times using the variables available. If you have production databases, it is advisable to schedule backups of those databases for the safekeeping of your data.

Click Backup Now to immediately start a backup, or Save to save any scheduled backups you have configured.

Single Database

To backup a single database, click on the name of that database in the PostgreSQL Databases section of the main database screen.

In the list of options, click on Backup

This takes you to a screen with similar options to the Backup All Databases screen. Make your configuration choices, and either select Backup Now to immediately start a backup, or Save to save any scheduled backups you have configured.

Upgrading PostgreSQL

If there are upgrades available to PostgreSQL, they will be available in the Application section of the Control Panel. Click on the link to upgrade PostgreSQL.

Links to other information

PostgreSQL main site - http://www.postgresql.org/

PostgreSQL documentation - http://www.postgresql.org/docs/