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.
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 |
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 |
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.
Check your Backups
Wait for a scheduled amount of time for the backup to be performed.
|
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.
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.
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.
Enter the credentials you’ve received via email after this PostgreSQL node has been added to your environment.
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.
Now you can be confident in your data’s safety, as it is periodically saved and can be restored and reused.
0
0