CYBER WEEK SALE - 50% OFF ALL HOSTING PLANS
50% OFF ALL NEW PLANS
By visiting this website you accept its use of cookies. Manage your cookie preferences.

In this tutorial, we will show you how to configure a PostgreSQL database server on your account using the WebApps platform.

If you do not see a WebApps section in the Control Panel of your hosting account, this tutorial may not be suitable for your particular hosting environment. Please contact our support team for more information.

You need SSH access to the server in order to make the changes described in this tutorial. If you haven't set up SSH for your account yet, you should do this now.

Quick Setup

If you want to quickly set up a PostgreSQL server running on your account, you can download this PostgreSQL setup shell script to your account and run it. The script will set up the WebApps project and the PostgreSQL instance on your account and you will be able to start using them immediately.

In order to run the setup script, you have to change its permissions to 755 after you download it to your account. You will then be able to run it from the command line:

mv 4166.sh setup_postgresql.sh
chmod 755 setup_postgresql.sh
./setup_postgresql.sh postgres1

In that case, you can skip the "PostgreSQL Server Setup" and "Custom WebApps Project" sections of this article and go directly to the Creating Databases and Roles section.

PostgreSQL Server Setup

The PostgreSQL binaries are already installed on the server. You only have to set up a PostgreSQL instance to handle your databases using the WebApps platform.

To do that, you first have to create the directory structure to hold your data on the server. You can use the private/ directory that is available in each hosting account for this:

sureapp_project="postgres1"
pg_app_dir="/home/$USER/private/$sureapp_project"

mkdir -m 0700 "$pg_app_dir"
mkdir -m 0700 "$pg_app_dir/run"
mkdir -m 0700 "$pg_app_dir/sureapp"

touch "$pg_app_dir/.psql_history"
chmod 0600 "$pg_app_dir/.psql_history"

The next step is to initialize your database cluster and set up a superuser role. By default, the username of the superuser role is the same as the username of your hosting account ("example" in this case):

superuser_pass="$(pwgen 24 1)"
touch "$pg_app_dir/.superuser-pwfile"
chmod 0600 "$pg_app_dir/.superuser-pwfile"
echo "$superuser_pass" > "$pg_app_dir/.superuser-pwfile"

for pg_install_dir in $(find /usr/lib/postgresql -maxdepth 1 -type d -o -type l 2>/dev/null | sort -Vr)
do
    [ -x "$pg_install_dir/bin/initdb" ] && break
done
"$pg_install_dir/bin/initdb" -D "$pg_app_dir/pgdata" --auth=md5 --pwfile="$pg_app_dir/.superuser-pwfile" && rm -f "$pg_app_dir/.superuser-pwfile"

touch "$pg_app_dir/.pgpass"
chmod 0600 "$pg_app_dir/.pgpass"
printf "*:*:*:%s:%s\n" "$USER" "$superuser_pass" > "$pg_app_dir/.pgpass"

Now, you have to configure the PostgreSQL server to listen for requests using a UNIX socket at the correct location in your account:

sed -i "s~^#*unix_socket_directories = .*\(\s*#.*\)~unix_socket_directories = '$pg_app_dir/run' \1~g" "$pg_app_dir/pgdata/postgresql.conf"
sed -i "s~^#*unix_socket_permissions = .*\(\s*#.*\)~unix_socket_permissions = 0700 \1~g" "$pg_app_dir/pgdata/postgresql.conf"
sed -i "s~^#*listen_addresses = .*\(\s*#.*\)~listen_addresses = '' \1~g" "$pg_app_dir/pgdata/postgresql.conf"

To make starting the PostgreSQL service easier, the following commands will create a startup script:

printf '#!/bin/sh\nexec %s/bin/postgres -D %s/pgdata\n' "$pg_install_dir" "$pg_app_dir" > "$pg_app_dir/start.sh"
chmod 0700 "$pg_app_dir/start.sh"

In order to use various PostgreSQL management tools such as psql easily, you should also make some changes to your .bashrc file:

grep -q '^PGHOST=' "/home/$USER/.bashrc" || cat <<BASHRC >> "/home/$USER/.bashrc"

# $sureapp_project client configuration
PGHOST="$pg_app_dir/run"
export PGHOST
PGPASSFILE="$pg_app_dir/.pgpass"
export PGPASSFILE
PSQL_HISTORY="$pg_app_dir/.psql_history"
export PSQL_HISTORY
BASHRC
. "/home/$USER/.bashrc"

Custom WebApps Project

To run the database server, you have to set up a PostgreSQL service. There are two ways to do this:

  • You can create the project on the WebApps page of the Control Panel of your hosting account;
  • You can use the command line interface of the WebApps platform to create the project.

The following sections describe how to do this.

On the WebApps Page of the Control Panel

If you choose the Control Panel way, you should create a new app with the following settings:

  • Engine: Custom
  • Name: postgres1 - The name of your app.
  • Domain: example.com - You can choose any domain from the list. It will not affect the operation of the PostgreSQL server.
  • Subdomain: www - Again, you can choose any subdomain. It will have no effect because the PostgreSQL instance will not be listening on a TCP port.
  • Web access path: /Efu6dunox0vieyelahch2eaf - The web access path should simply be a dummy path that is unlikely to be used by your website. Here, we used "/Efu6dunox0vieyelahch2eaf" for the example, but you can use any random string. You should not leave the default '/' here because that would prevent your website from working correctly.
  • Deployment directory: /private/postgres1/sureapp - If you have changed the base data directory for PostgreSQL in the script above, you should enter the correct path here.
  • Start command: /home/example/private/postgres1/start.sh - Here, you have to replace "example" with the actual username of your hosting account.

create_app_form_postgres1.png

After you create and enable the app, you will be able to use the new PostgreSQL database server.

Using the Command Line Interface of the WebApps Platform

WebApps projects can be manipulated with the sureapp command-line client. To create and start the new PostgreSQL project, you should use the following commands:

sureapp project create \
    --engine "custom" \
    --engine-version "-" \
    --release-dir "$pg_app_dir/sureapp" \
    --start-cmd "$pg_app_dir/start.sh" \
    "$sureapp_project"
sureapp service manage --enable "$sureapp_project"
sureapp service manage --start "$sureapp_project"

After running these commands, the PostgreSQL server will be running and you will be able to use it.

Creating Databases and Roles

Once the server is running, you should set up a new user role and a database for your application. To do that, you can use the following commands over SSH:

. "/home/$USER/.bashrc"  # For $PGHOST
createuser -e -DEPRS "pgusername"
createdb -e -O "pgusername" "pgdatabase"

You should replace the username and the database name in the example with the actual details you are going to set up in your application.

Backups

We create automatic system backups every 12 hours so that you don't have to worry about data loss. These backups allow you to restore your data from backup if anything goes wrong with your site.

Database engines like PostgreSQL and MySQL store data in a special way on the server, so extra care must be taken in order to ensure backup archives are consistent and usable.

For MySQL, which is integrated with our hosting environment, this is already done automatically by the Control Panel. All MySQL data is backed up at regular intervals by our system and it can be easily restored using the "Restore" page of the Control Panel.

For PostgreSQL databases running on the WebApps platform, you have to set up a backup procedure separately. The following code will create two scripts in the WebApps project, backup.sh and restore.sh, that will take care of backups (note that if you have used our quick setup script, these files are already created for you):

mkdir -m 0700 "$pg_app_dir/backup"

cat <<BACKUP_SH > "$pg_app_dir/backup.sh"
#!/bin/sh
set -e
die() { printf "%s\n" "\$*" 1>&2 && exit 1; }
PGPASSFILE="$pg_app_dir/.pgpass"
export PGPASSFILE
"$pg_install_dir/bin/pg_dumpall" --clean --host "$pg_app_dir/run" | gzip --stdout -- > "$pg_app_dir/backup/pgdump.sql.gz" || die "PostgreSQL backup failed: $sureapp_project"
BACKUP_SH
chmod 700 "$pg_app_dir/backup.sh"

cat <<RESTORE_SH > "$pg_app_dir/restore.sh"
#!/bin/sh
set -e
die() { printf "%s\n" "\$*" 1>&2 && exit 1; }
PGPASSFILE="$pg_app_dir/.pgpass"
export PGPASSFILE
archive="$pg_app_dir/backup/pgdump.sql.gz"
[ -f "\$archive" ] || die "Backup file does not exist: \$archive"
zgrep -m1 "Dumped" "\$archive" || die "Backup file does not contain PostgreSQL data: \$archive"
gzip --decompress --stdout "\$archive" | "$pg_install_dir/bin/psql" --quiet --host "$pg_app_dir/run" postgres
RESTORE_SH
chmod 700 "$pg_app_dir/restore.sh"

After the scripts are created, you can set up a cron job that runs the backup.sh script every 12 hours. This can be done on the "Cron Jobs" page of the Control Panel.

This cron job will overwrite the pgdump.sql.gz backup archive every time. This way, each new system backup will contain the newest copy of the file.

Hopefully, you won't have to use the restore.sh script. If you do, however, it will drop the current data in your PostgreSQL instance and will restore the data from the pgdump.sql.gz archive.

To restore from a particular backup, you can restore the pgdump.sql.gz file from one of the system backups using the "Restore" page of the Control Panel. After that, running restore.sh on the command line will restore the data from the archive into your PostgreSQL instance:

./restore.sh

PHP Configuration

If you are going to use a PHP application with PostgreSQL, you have to update the php.ini configuration file. You can skip this step if you are not going to use PHP.

PHP can use two libraries to connect to PostgreSQL. They are the PostgreSQL database extension and the more abstract PDO driver for PostgreSQL.

You have to enable the correct PHP extension depending on the application you want to use via the  Control Panel -> PHP Settings section by following these steps:

  1. Click on the Manage button next to the domain/subdomain where you want to use PosgtreSQL.
  2. Check the box next to the PostgreSQL or PDO_PGSQL extension from the Extensions section.
  3. Click on the Save changes button.

The extensions can also be activated by manually adding the respective line listed below to a PHP configuration file (php.ini):

PostgreSQL extension:

extension = pgsql.so

PDO driver for PostgreSQL:

extension = pdo_pgsql.so


Instructions on how to change other PHP settings for your account are available in our Changing PHP settings article.

Enabling Remote Connections

By default, PostgreSQL is configured to listen only to local connections. There is a way to enable remote connections if you wish to connect to your PostgreSQL server from a remote server or a graphical user interface (GUI) administration tool for PostgreSQL, such as pgAdmin. In order to enable remote connections, you need to update two configuration files:

  • pgdata/postgresql.conf
  • pgdata/pg_hba.conf

 In the pgdata/postgresql.conf file, you need to edit the following lines:

listen_addresses = '' # what IP address(es) to listen on;
port = 5432                             # (change requires restart)

You need to set the "listen_address" variable to "*", and the "port" variable to the port of your WebApp. You can find the port of the WebApp in the "WebApps" section of the hosting Control Panel.

In the pgdata/pg_hba.conf file, you need to add the following line at the end:

hostallall0.0.0.0/0md5

This code will configure the PostgreSQL server to accept connections from any remote location given that they provide a valid username and password. If you wish, you can configure the server to accept connections only from a specific IP address. For instance, to allow connections from the 123.123.123.123 IP address, you need to use the following line instead:

host all all 123.123.123.123/32 md5

You should then restart the WebApp through the "WebApps" section of the hosting Control Panel, and you will be able to connect to the server from a remote location. 

Note: Updating the port in the configuration file of PostgreSQL will break the functionality of applications, such as psql, createuser, and createdb. You will still be able to use them by defining the new port in the following way:
psql -p <the WebApp port>