PostgreSQL 9 and phpPgAdmin
Posted by on 02 December 2011 12:52 PM
Applicable Plans - All Cloud Hosting Plans
PostgreSQL 9 and phpPgAdmin
Installing PostgreSQL and related applications
To use PostgreSQL you will need to install the PostgreSQL database. You can also install phpPgAdmin to manage the database from a GUI application. If you are going to use phpPgAdmin, you will need to install PHP first, and then phpPgAdmin. If PHP is not installed, the installation of phpPgAdmin will fail.
If you are going to use PostgreSQL with an application like WildFly, JBoss, Tomcat, or GlassFish, you will also want to install the PostgreSQL JDBC Driver.
You can install PostgreSQL and any related applications from your Control Panel and also from the command line of the Virtual Server.
Installing PostgreSQL and related applications using the Control Panel
Installing PostgreSQL and related applications using the ISPmanager Control Panel
If you are using the ISPmanager Control Panel, you can install PostgreSQL (and PHP and phpPgAdmin) from that Control Panel.
Information on how to tell which version of ISPmanager you are using can be found here - ISPmanager versions
Installing PostgreSQL and related applications using the command line
PostgreSQL and PHP can be installed from the command line of the Virtual Server using
Creating databases and database users
PostgreSQL databases can be created from the Control Panel, phpPgAdmin, and the command line.
Creating databases and users using a Control Panel
Creating databases and users using the ISPmanager Control Panel
In ISPmanager, you need a User that will own the database. This user is not the same user who will access the database. This user is generally the same user that owns the website or web application that is connected to the database. The same user can own multiple databases.
If you do not already have a User created, you will need to do so.
Once you have created the User, you can create the actual database and the user that will access the database.
Creating databases and users using phpPgAdmin
phpPgAdmin allows you to manage many aspects of your PostgreSQL database and database users from a browser based interface.
Connecting to phpPgAdmin
To connect to phpPgAdmin, go to https://eapps-example.com/pgadmin/, substituting your domain name or server IP address for eapps-example.com.
To log in to phpPgAdmin, click on the PostgreSQL link in the left navigation pane, with the red X
If you installed PostgreSQL from the command line, you should know the password for the postgres user.
Once you have entered the Username and Password, click on Login.
Creating a Role (database user)
To create a Role (a database user), click on the Roles tab. This shows the current Roles associated with the PostgreSQL server. Click on Create role to create a new database user.
Click Create to create the new role, or Cancel.
Creating a Database
To create a Database, click on the Databases tab. This shows the current databases associated with the PostgreSQL server. Click on Create database to create a new database.
The fields for Name, Template, Encoding, Collation, and Character Type must be filled out correctly. The values will depend on the encoding and character set of the database you are trying to create.
Click Create to create the database, or Cancel.
Configuring phpPgAdmin access
By default, only the postgres user can log in to phpPgAdmin. If other users need to be able to log in to phpPgAdmin, you will need to make a change to the file that controls who can login to PostgreSQL.
Please note that eApps recommends that you leave things at the default, and only allow the postgres user to have access via phpPgAdmin. If you allow access for other users make sure that these users are skilled PostgreSQL admins.
To allow access for other users, you will need to make changes to the pg_hba.conf file, located at /var/lib/pgsql/data. These changes have to be made from either the command line or the File manager, as the root user. Once the changes are made, the PostgreSQL server must be restarted.
This example shows a pg_hba.conf file with a database user called db_user, managing a database called test_db. Look at line 5 in the file.
To allow the user to connect to phpPgAdmin, an additional line will have to be added to the pg_hba.conf file, just under the existing line for that user. The line to add will look like this (substitute your actual database user name) :
so that the pg_hba.conf file will now look like this:
Once you have made your changes, you will need to restart PostgreSQL.
Also be aware that some users will have more or less functionality than other users, depending on how they were created and what permissions the user has. For example, not all users will be able to create databases or create roles.
Creating databases and users using the command Line
To create a user or a database for PostgreSQL from the command line, you will need to connect to your Virtual Server using SSH. See the User Guide: Connecting to your Virtual Server (SSH) - http://support.eapps.com/ispmgr/ssh for more information.
The createuser command is used to create new database users. The command must be run as the postgres user.
The documentation for createuser has information on all the available switches - http://www.postgresql.org/docs/9.0/static/app-createuser.html
To create a user for PostgreSQL, you will need to switch users to the postgres user, and then use the
The createdb command is used to create new databases. The command must be run as the postgres user.
The man page for createdb has information on all the switches - http://www.postgresql.org/docs/9.0/static/app-createdb.html
To create a database in PostgreSQL, you will need to switch users to the postgres user, and then use the
Stopping and starting PostgreSQL
PostgreSQL can be stopped/started/restarted from the Control Panel or from the command line of the Virtual Server.
Stopping and starting PostgreSQL using a Control Panel
Using the ISPmanager Control Panel
The way to start, stop, and restart PostgreSQL from ISPmanager will depend on which version of ISPmanager you are using.
Stopping and starting PostgreSQL using the command line
You can stop, start, and restart PostgreSQL from the command line. To do this, you will need to connect to the Virtual Server using SSH, and be able to work as the root user.
Check the status of PostgreSQL
There are many configuration options available for PostgreSQL, far too many to detail in this User Guide. PostgreSQL has very good documentation, which anyone using the PostgreSQL database should be familiar with.
Generally, the default PostgreSQL configuration should work for most users, and is the only configuration that is supported. However, you have root access to your Virtual Server, and can make any changes you wish. It is assumed that if you are making these kinds of changes that you are a PostgreSQL expert, and will need no assistance from eApps.
If you do need to make configuration changes to PostgreSQL, but need assistance to do this, please contact eApps Support. Depending on the nature of the work, the configuration may be done for you. However, if the work required will take some time to accomplish, then the work may be contracted with eApps Support as a billable task, at the standard rate of $15 per 10 minutes ($90/hour).
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.
To do this, you will need to change a line in the postgresql.conf file, located in the /var/lib/pgsql/data directory. You can make this change using SSH.
The line is in the section starting with
Look for this line, which is usually line 118:
Uncomment the line, and change
Save and exit the file, and then restart PostgreSQL for the changes to take effect.
Importing content to a PostgreSQL database
You can import content into a PostgreSQL database from phpPgAdmin or from the command line. The content will need to be in the form of a PostgreSQL dump file.
This is usually done when you are installing an application that uses PostgreSQL as the database, and has a database file that has to be uploaded to create the correct tables and schema. The application instructions will tell you what the name of the database needs to be, or will create it for you.
Importing content using phpPgAdmin
To import content into a PostgreSQL database using phpPgAdmin, you will need to upload an SQL script (the sql dump file) from you local computer.
If your SQL file is larger than 2 MB, you will need to change the value for File minimum size in the PHP configuration to be at least 5 MB larger than your SQL file.
Log in to phpPgAdmin, and either create the new database, or select the database name from the left navigation pane.
Once you have created or selected the database, click on the SQL tab.
This will upload the PostreSQL dump file to the database. Once the file is imported, you will be taken to the Query Results screen, that will show the executed SQL commands.
Importing content using the command line
To import content into a PostgreSQL database from the command line of the Virtual Server, you will need to use the psql tool. The command must be run as the PostgreSQL user that owns the database where the content is being imported in to. The SQL file being imported must reside on the Virtual Server.
Connect to the Virtual Server using SSH, and run the following command:
where database is the name of the PostgreSQL database that the dump file is being imported into. An example of content being imported into a database owned by the postgres user would look like this:
PostgreSQL Remote Access
By default, PostgreSQL only allows access from localhost/127.0.0.1. If you need to enable remote connections to a PostgreSQL database, you can do this when the database is created from the Control Panel, or by editing two PostgreSQL configuration files from the command line.
Configuring remote access using a Control Panel
Remote access can be configured when the database is created, but if you need to allow remote access to an existing database, you will need to work from the command line.
Configuring remote access using the command line
If you need to allow remote connections to PostgreSQL, you can do this from the command line. Two files need to be edited to allow this to happen.
The two files to edit are postgresql.conf and pg_hba.conf, both in the /var/lib/pgsql/data directory
Change this line (usually line 59):
to this - uncomment the line, and replace "localhost" with an * (asterisk). If you only want to allow connections from a specific IP address or addresses, you can also use a comma separated list of IP addresses.
Save and exit the file.
At the very end of the file, add this line:
Save and exit the file, and restart PostgreSQL. Now the PostgreSQL server will allow remote connections.
Backing up PostgreSQL databases
PostgreSQL databases can be backed up from the command line, phpPgAdmin, or your Control Panel.
By default, there are no backups taken of your Virtual Server. eApps offers two backup services - a Basic backup and an Enterprise backup. More information about the backup services available through eApps can be found here - eApps Backup Services.
If you need help creating a custom backup solution that meets your needs, please contact eApps Sales for assistance.
Backing up your databases using the Control Panel
Backing up your databases using the ISPmanager Control Panel
ISPmanager has a rudimentary backup function that will do a manual database dump and download that to your local computer. No file or table locking is done, and if you have transactions being written to the database when the backup is running those transactions may not get backed up.
Remember - this is a manual process. If you want automated backups of your database, you will need to create a custom backup solution. If you need assistance with this, please contact eApps Sales for assistance.
Backing up your databases using the command line
PostgreSQL databases are backed up from the command line using the pg_dump or pg_dumpall commands. The pg_dump command backs up a single database, while pg_dumpall backs up all databases. You will need to connect to the VS using SSH.
The pg_dump command has to be run from the command line of the Virtual Server as the postgres user. The command is:
The pg_dump command is documented here: http://www.postgresql.org/docs/9.0/static/app-pgdump.html. There are many examples of how to use the command at the bottom of the page.
Make sure that you do not give the backup file the same name as the actual database.
The pg_dumpall command has to be run from the command line of the Virtual Server as the postgres user. The command is:
The pg_dumpall command is documented here: http://www.postgresql.org/docs/9.0/static/app-pg-dumpall.html.
Make sure that you do not give the backup file the same name as any existing database.
Backing up your databases using phpPgAdmin
phpPgAdmin has an Export feature that can be used to backup your databases. You can back up individual databases, or all databases.
To back up all databases, you will need to log in to phpPgAdmin as the postgres user, and click on the PostgreSQL server in the left hand navigation pane. Then click on the Export tab.
The command being run on the backend to export the databases is the pg_dump command (for which pg_dumpall) is a wrapper. Review the pg_dump documentation if you need to know more specifics about the options.
Once you have made your choices, click on Export.
To back up an individual database, log in to phpPgAdmin as either the postgres user or as the user for that specific database, and click on the name of the database in the left hand navigation pane. Then click on the Export tab.
The command being run on the backend to export the databases is the pg_dump command. Review the pg_dump documentation if you need to know more specifics about the options.
Once you have made your choices, click on Export.