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.
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.
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"
To run the database server, you have to set up a PostgreSQL service. There are two ways to do this:
The following sections describe how to do this.
If you choose the Control Panel way, you should create a new app with the following settings:
After you create and enable the app, you will be able to use the new PostgreSQL database server.
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.
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.
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
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:
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.
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:
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>
All prices are in USD. No setup fees. Minimum contract period for shared hosting services - 12 months. Full prepayment for the contract period. 100-day money-back guarantee. No automatic renewal. Fees for domain registrations and SSL certificates cannot be refunded in case of an early contract termination.
ICDSoft 2001-2024 © All rights reserved
Terms of Use
|
Legal notice
|
Privacy
|
Reseller terms