Cloud Hosting Applications - PostgreSQL
Posted by Alan Bowman on 07 October 2010 09:34 PM
Applicable Plans - eApps Cloud Hosting Plans (eApps templates only)
User Guide - PostgreSQL and phpPgAdmin User Guide
“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/
See the Links to other information at the end of this document if you need detailed information about PostgreSQL.
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.
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.
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
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.
PostgreSQL user details
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.
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.
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.
Click on Login.
Creating a role (database user)
To create a new role, or database user, click on Create 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.
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.
To stop PostgreSQL from the Control Panel, click on Stop PostgreSQL Server from the main PostgreSQL Database Server screen.
Once PostgreSQL is restarted, the main PostgreSQL Database Server screen will return.
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
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
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 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.
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.
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.
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.
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.
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.
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..
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.
Other backup options
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.
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.
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/