 |
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
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.
yum install -y postgresql-server command[root@eapps-example ~]# yum install -y postgresql-server
yum install -y php command[root@eapps-example ~]# yum install -y php
yum install -y phpPgAdmin command (notice the capitalizations in phpPgAdmin)[root@eapps-example ~]# yum install -y phpPgAdmin
PostgreSQL databases can be created from the Control Panel, phpPgAdmin, and the command line.
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
 |
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.
 |
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. |
phpPgAdmin allows you to manage many aspects of your PostgreSQL database and database 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 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
This takes you to the Login to PostgreSQL screen.
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.
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.
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.
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.
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.
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.
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.
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.
|
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.
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 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$
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$
PostgreSQL can be stopped/started/restarted from the Control Panel or from the command line of the Virtual Server.
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
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
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.
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.
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.
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.
Expand the database in the left navigation pane, and click on Tables. This will show the content that was imported.
Once you have imported your database content, you can manage your database as needed.
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$
|
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 |
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.
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.
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.
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.
 |
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.
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.
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:
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
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
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.
This shows the export options for all the databases on the PostgreSQL server.
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.
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.
This shows the export options for an individual database on the PostgreSQL server.
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.