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.

Configure and Schedule Backups

Now let’s configure the backup itself.

Click the Config button for your PostgreSQL database.

publicpreview

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:

local   all             postgres                              peer
local   all             all                                       md5
host   all             all             127.0.0.1/32        trust
host   all             all             ::1/128                md5

 

In case you’ve chosen PostgreSQL 8, use this code:

local   all         all                                        md5
host    all         all         127.0.0.1/32            ident

host    all         all         ::1/128                    trust
host    all         all         0.0.0.0/0                 md5

 

deployment manager

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

For PostgreSQL 8 please use the line of the following format:

* */1 * * * pg_dump -h localhost > /var/lib/jelastic/backup/backup.sql

deployment manager2

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

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

restart

Check your Backups

Now let’s check everything was configured properly.

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

Open the configuration manager for your PostgreSQL server with the button, shown below.
config manager
The reserve data copy will appear in the backup folder.
backup folder

Note: that each newly created backup will override the existing one, i.e. you’ll always see just one file with the latest data.

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.

Install the FTP-addon to your database. For that, use the instructions here.

ftp

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 your preferred FTP client (e.g. FileZilla).

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.

backup

Restore the Database

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

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

phpPgAdmin

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

phpPgAdmin2

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.

phpPgAdmin3

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

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

in Jelastic

0

0

Related Articles