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

1. Log into your Jelastic 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.

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 conf directory, find the pg_hba.conf file in it, and replace its content with the following lines:

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.

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.

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.

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.