PostgreSQL 9 and phpPgAdmin

  1. Portal Home
  2. Knowledgebase
  3. PostgreSQL 9 and phpPgAdmin

Applicable Plans - All Cloud Hosting Plans

PostgreSQL 9 and phpPgAdmin

Overview

Note This User Guide is designed to help you install PostgreSQL and phpPgAdmin on your Virtual Server, 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 available books about PostgreSQL if you need to come up to speed on how to actually use the database.

Installing PostgreSQL and related applications
    Installing PostgreSQL and related applications using the Control Panel
    Installing PostgreSQL and related applications using the command line

Creating databases and database users
    Creating databases and users using a Control Panel
    Creating databases and users using phpPgAdmin
    Creating databases and users using the command Line

Stopping and starting PostgreSQL
    Stopping and starting PostgreSQL using a Control Panel
    Stopping and starting PostgreSQL using the command line

PostgreSQL Configuration

Importing content to a PostgreSQL database
    Importing content using phpPgAdmin
    Importing content using the command line

PostgreSQL Remote Access
    Configuring remote access using a Control Panel
    Configuring remote access using the command line

Backing up PostgreSQL databases
    Backing up your databases using the Control Panel
    Backing up your databases using the command line
    Backing up your databases using phpPgAdmin

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.

If you are using the ISPmanager Control Panel, you can install PostgreSQL (and PHP and phpPgAdmin) from that Control Panel.

  • For ISPmanager 4, go to Server Settings > Applications. Install PostgreSQL, and make sure to install PHP before installing phpPgAdmin. More information on installing applications can be found here - Installing Server Applications. Note that in ISPmanager 4, phpPgAdmin is called PostgreSQL Management Tool.

  • For ISPmanager 5, go to Settings > Features. Install PostgreSQL, and make sure to install PHP before installing phpPgAdmin. More information on installing applications can be found here - Installing Features (Server Applications). Note that in ISPmanager 5, PHP is installed by clicking on Web-server (WWW), then on Edit in the upper left corner, and then select PHP and click on Apply changes. This will install PHP. After PHP is installed you can install phpPgAdmin.

Information on how to tell which version of ISPmanager you are using can be found here - ISPmanager versions

PostgreSQL and PHP can be installed from the command line of the Virtual Server using yum. To install applications using yum you will need to be able to connect to the Virtual Server using SSH and work as the root user.

  • PostgreSQL - install PostgreSQL using the yum install -y postgresql-server command

[root@eapps-example ~]# yum install -y postgresql-server

  • PHP - install PHP using the yum install -y php command

[root@eapps-example ~]# yum install -y php

  • phpPgAdmin - install phpPgAdmin after installing PHP using the yum install -y phpPgAdmin command (notice the capitalizations in phpPgAdmin)

[root@eapps-example ~]# yum install -y phpPgAdmin

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.

  • ISPmanager 4 - Users are created in Accounts Management > Users. More information can be found here - Creating Users.

  • ISPmanager 5 - Users are created in Accounts > Users. More information can be found here - Creating Users

Warning Make sure to understand that if you delete the user that is the database Owner all databases associated with that user will be deleted. This also deletes any WWW domains and E-Mail domains (and associated e-mail addresses) associated with that user. Proceed with caution.

Once you have created the User, you can create the actual database and the user that will access the database.

  • ISPmanager 4 - go to Management Tools > Databases. This is where you will create a database and a user. More information on creating a database and user can be found here - Creating Databases.

  • ISPmanager 5 - go to System > Databases. This is where you will create a database and a user. More information on creating a database and user can be found here - Creating Databases.

Note If you need to allow remote access to your database, you can configure that when creating the database from ISPmanager, or by editing two files from the command line after the database has been created.

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.

Note 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/

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

phpPgAdmin login link


This takes you to the Login to PostgreSQL screen.

Login to PostgreSQL

  • Username - by default, you can only log in to phpPgAdmin as the postgres user. If you need to be able to log in to phpPgAdmin as a different database user, see Configuring phpPgAdmin access.

  • Password - if you installed PostgreSQL from a Control Panel, the password for the postgres user is found there.

  • For ISPmanager 4, go to Server Settings > Database servers, click on the database server name (PostgreSQL), and then on Edit in the upper right. The password for the postgres user will be shown here.

  • For ISPmanager 5, go to Settings > Database servers, click on the database server name (PostgreSQL) and then on Edit in the upper left. The password for the postgres user will be shown here.

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.

pgAdmin - Create role


This takes you to the Create role screen. If you have any questions on creating a role, click on the question mark (?) next to Create role. This will take you to the official PostgreSQL 9 documentation for creating a role.

pgAdmin - Create role screen

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

  • Password - enter a password for this role.

  • 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 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.

pgAdmin - Create database


This brings up the Create database screen. If you have any questions on creating a database, click on question mark (?) next to Create database. This will take you to the official PostgreSQL 9 documentation for creating a 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.

pgAdmin - Create database screen

  • Name - the name of the database, in lowercase letters.

  • Template - select the template for the database from the drop down list.

  • Encoding - this is set to LATIN1 by default, you will need to choose the correct encoding from the drop down list.

  • Collation - this is how the objects in the database should be ordered. Enter the correct collation for your database.

  • Character Type - enter the correct character type here. This must work with your chosen Encoding.

  • Comment - enter a comment if you wish.

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.

template1 postgres password
host template1 postgres 0.0.0.0 0.0.0.0 password
local all postgres password
local template1 all password
local   test_db db_user password
host all all 127.0.0.1/32 md5

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) :

local postgres db_user password

so that the pg_hba.conf file will now look like this:

template1 postgres password
host template1 postgres 0.0.0.0 0.0.0.0 password
local all postgres password
local template1 all password
local   test_db db_user password
local postgres  db_user password
host all all 127.0.0.1/32 md5

Once you have made your changes, you will need to restart PostgreSQL.

Note You will only have the ability to manage the databases associated with the database user that you have logged in as. If you wish to be able to manage all databases, you will need to log in as the postgres user, which is the PostgreSQL super user.

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.

createuser

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 createuser -P command to create the user. Note the capital P in the command.

[root@eapps-example ~]# su - postgres
-bash-4.1$ createuser -P new_user
Enter password for new role:passwd
Enter it again:passwd
Shall the new role be a superuser (y/n) y
-bash-4.1$

 

createdb

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 createdb command to create the database. By default the database owner will be the user that created the database. You can specify a different owner with the -O switch. Note that this is a capital letter O.

[root@eapps-example ~]# su - postgres
-bash-4.1$ createdb new_database
-bash-4.1$ createdb -O new_user new_database
-bash-4.1$

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.

  • For ISPmanager 4, go to Management Tools > Services, and highlight the PostgreSQL service. Then click on Stop, Start, or Restart in the upper right corner. More information about managing service in ISPmanager 4 is available here - Managing Services

  • For ISPmanager 5, go to System > Services, and highlight the PostgreSQL service. Then click on Start, Stop, or Restart in the upper left corner. More information about managing services in ISPmanager 5 is available here - Managing Services

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

  • For CentOS 6, use the service postgresql status command:

    [root@eapps-example ~]# service postgresql status

  • For CentOS 7, use the systemctl status postgresql command:

    [root@eapps-example ~]# systemctl status postgresql

Stop PostgreSQL

  • For CentOS 6, use the service postgresql stop command:

    [root@eapps-example ~]# service postgresql stop

  • For CentOS 7, use the systemctl stop postgresql command:

    [root@eapps-example ~]# systemctl stop postgresql

Start PostgreSQL

  • For CentOS 6, use the service postgresql start command:

    [root@eapps-example ~]# service postgresql start

  • For CentOS 7, use the systemctl start postgresql command:

    [root@eapps-example ~]# systemctl start postgresql

Restart PostgreSQL

  • For CentOS 6, use the service postgresql restart command:

    [root@eapps-example ~]# service postgresql restart

  • For CentOS 7, use the systemctl restart postgresql command:

    [root@eapps-example ~]# systemctl restart postgresql

PostgreSQL Configuration

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 # RESOURCE USAGE (except WAL), which starts around line 104.

Look for this line, which is usually line 118:

#work_mem = 1MB                         # min 64kB

Uncomment the line, and change work_mem to 8 MB:

work_mem = 8MB                          # min 64kB

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.

pgAdmin - SQL tab


Click on Choose File. This will open a file browser, and allow you to choose the file on your local computer to upload. Once you have chosen the correct file, click on Execute.

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.

pgAdmin - Query Results


Expand the database in the left navigation pane, and click on Tables. This will show the content that was imported.

pgAdmin - Database Tables


Once you have imported your database content, you can manage your database as needed.

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:

psql -d database < dump.sql

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:

[root@eapps-example ~]# su - postgres
-bash-4.1$ psql -d database  < /path/to/file.sql
[content being imported .... ]
-bash-4.1$

Note This is one small example of using the psql command. See the psql documentation for more information: http://www.postgresql.org/docs/current/interactive/app-psql.html

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

postgresql.conf

Change this line (usually line 59):

#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        # (change requires restart)

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.

listen_addresses = '*'          # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost', '*' = all
                                        #(change requires restart)

Save and exit the file.

pg_hba.conf

At the very end of the file, add this line:

host    all             all             0.0.0.0/0               md5

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.

Warning 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!

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.

  • For ISPmanager 4, go to Management Tools > Databases, and click on the database you want to back up. Then click on Download in the upper right. This will download the database to your local computer as a .sql file.

  • For ISPmanager 5, go to System > Databases, and click on the database you want to back up. Then click on Download in the upper left. This will download the database to your local computer as a .sql file.

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.

pg_dump

The pg_dump command has to be run from the command line of the Virtual Server as the postgres user. The command is:
pg_dump name_of_database > name_of_backup

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.

[root@eapps-example ~]# su - postgres
-bash-4.1$ pg_dump postgres > postgres_bck.sql

pg_dumpall

The pg_dumpall command has to be run from the command line of the Virtual Server as the postgres user. The command is:
pg_dumpall > name_of_backup

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.

[root@eapps-example ~]# su - postgres
-bash-4.1$ pg_dumpall > all_db.sql

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.

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.

pgAdmin - PostgreSQL Export


This shows the export options for all the databases on the PostgreSQL server.

pgAdmin - Export options

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.

Format

  • Data only - this only exports the data from the database, not any of the structure (the schema)

  • Structure only - this only exports the database structure (the schema), not any of the data

  • Structure and data - this exports both the data and the structure (the schema)


Format Options

  • Format - choose either COPY or SQL. The COPY option is the default, and makes a plain text dump file.

  • OIDs - this will dump the Object Identifiers as part of the data for every table. Read the pg_dump documentation for the --oids switch before enabling this.

  • Drop - this cleans the database objects. Read the pg_dump documentation for the --clean switch before enabling this.


Options

  • Show - this shows the dump file, but doesn't save it (locally or remotely)

  • Download - this will download the dump file to your local computer, in a file named dump.sql. You will want to rename this file into something relevant for your database.

Once you have made your choices, click on Export.

 

Individual Databases

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.

pgAdmin - Database Export


This shows the export options for an individual database on the PostgreSQL server.

pgAdmin - Export Options

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.

Format

  • Data only - this only exports the data from the database, not any of the structure (the schema)

  • Structure only - this only exports the database structure (the schema), not any of the data

  • Structure and data - this exports both the data and the structure (the schema)


Format Options

  • Format - choose either COPY or SQL. The COPY option is the default, and makes a plain text dump file.

  • OIDs - this will dump the Object Identifiers as part of the data for every table. Read the pg_dump documentation for the --oids switch before enabling this.

  • Drop - this cleans the database objects. Read the pg_dump documentation for the --clean switch before enabling this.


Options

  • Show - this shows the dump file, but doesn't save it (locally or remotely)

  • Download - this will download the dump file to your local computer, in a file named dump.sql. You will want to rename this file into something relevant to your database.

  • Download compressed with gzip - this will download the dump.sql file to your local computer, but will compress it with gzip. Be aware that some browsers, such as Safari on Mac OS X, may unzip the file while downloading it.

Once you have made your choices, click on Export.

 

  Print

Comments

Please login to comment