PostgreSQL Database Backups

Automatic PostgreSQL Backups

The instruction below shows how to configure automatic periodic backups of your application data, stored within the PostgreSQL database. This will ensure the safety of the information it contains in the case of unexpected server failure.

In order to eliminate the necessity to remember to backup and regularly perform the appropriate operations, we’ll use cron, a time-based job scheduler, and runner. It executes the necessary commands at the stated time/date, based on the previously specified scenario.

Thus, follow the step-by-step instruction below in order to get such an automated solution.

Create an Environment

1. Log into your BitssCloud account and click the Create environment button at the top dashboard panel.

2. In the appeared dialog window select the preferable version of the PostgreSQL database (8 or 9) and allocate the required amount of resources for it (use cloudlet sliders in the Vertical scaling wizard section). Then type your environment name (e.g. backup) and click the Create button.

3. Wait just a minute for your environment to be created.

Configure and Schedule Backups

Now let’s configure the backup itself.

1. Click the Config button for your PostgreSQL database.

2. You’ll see the configuration manager opened at the bottom of your dashboard with a list of accessible folders.

Navigate to the var/lib/pgsql/data/pg_hba.conf file and replace its content with the following lines: local   all             Postgres                              peer
local   all              all                                         md5
host   all             all             127.0.0.1/32        trust
host   all             all             ::1/128                md5

Tip: This file can be quickly accessed through the Favorites list in the file manager:

• for the PostgreSQL 9.6 and higher – click the direct pg_hba.conf shortcut to file
• for the PostgreSQL 9.5 and lower – use the conf redirect to the appropriate folder

Save the changes with the appropriate button above the editor.

3. Then, locate the Postgres file in the cron folder. Modify the next command based on your requirements and add it to this file (e.g. the example below will make the backup once per hour).

* */1 * * * pg_dump > /var/lib/jelastic/backup/backup.sql

In order to get more information on how to configure the desired frequency of reserve copying, see this article (Setting up cron event scheduler section).

4. Save the made changes and Restart your DB node for their appliance.

Check Your Backups

Now let’s check everything was configured properly.

1. Wait for a scheduled amount of time for the backup to be performed.

2. Open the configuration manager for your PostgreSQL server with the button, shown below.

3. The reserve data copy will appear in the backup folder.

Download your Backups

In order to get the ability to download the created backups, you’ll need to connect to your DB server via FTP protocol. Therefore, follow the next steps.

1. Install the FTP-addon to your database. For that, use the detailed instruction in the FTP/FTPS Support guide.

2. Once installation is finished, you receive your FTP credentials in a separate email. Use them in order to establish the connection to your PostgreSQL node through the preferred FTP client (e.g. FileZilla).

3. After you’ve accessed the container’s file system, navigate to the backup folder and download the backup.sql file with your stored data to your local machine.

Restore the Database

In case you need to restore your data from the existing backup file, please perform the following steps.

1. Open the phpPgAdmin panel for the DB server you would like to restore your database to.

2. Enter the credentials you’ve received via email after this PostgreSQL node has been added to your environment.

3. Select the database you want to import the data to (we’ll use the default Postgres one for this example) within the left-hand menu.

Then navigate to the SQL tab and upload the previously-stored backup.sql file with the help of the Choose File button. Click Execute.

As a result, you’ll see your database has been successfully restored.

That’s all! Now you can be confident in your data’s safety, as it is periodically saved and can be restored and reused.