Postgresql Backup
Before taking database backup for your PostgreSQL database server, a lightweight agent needs to be installed on the database server, please refer to Agents for how to install agents on your database servers.
After the agents had been installed and licensed for database backup, please follow the below steps to create database backup job.
Configure Application
After the installation of Vinchin physical backup agent on PostgreSQL database server, users have to license the agent with database backup module. When done installation and licensing, please open Vinchin Backup Server web console and go to Resources > Agents page, find the target agent, click on Options and then select Application to configure application settings for database backup.
Click on Configure Application button to configure the application settings. In the Application Type dropdown list, please select PostgreSQL. The database instances of PostgreSQL will be listed in the Select Instance field. Select the database instance and click on Next button to get the instance authenticated for backup.
You need to specify the database bin file path and the database user credentials to get it authenticated. When PostgreSQL application is successfully configured, in the agents list, you should see the agent look like below.
Now you should be able to create backup jobs for the PostgreSQL database server
Before Backing Up PostgreSQL Database
DBA must check the below prerequisites before taking PostgreSQL database backups.
Ensure that the Connection setting listen_address parameter contains “localhost” in the PostgreSQL.conf file under the ProstgreSQL data directory
The database backup agent needs to use 2 service ports:23100 and 23101. On the database server firewall, these 2 ports need to be opened for Vinchin backup server.
Archivelog mode needs to be enabled with the database instance before taking backups.
The password-based authentication should be “md5” or “scram-sha-256” in the pg_hba.conf under the PostgreSQL data directory.
Create Backup Job
To create database backup jobs, please go to Physical Backup > Database Backup > Backup page. There are 4 steps to create a database backup job.
Step 1: Backup Source
First you need to select a target database server from the left column, then select PostgreSQL database instance you wish to backup, in the right column will show the instance you have selected.
Step 2: Backup Destination
A backup destination (backup storage) should be associated with this backup job.
In the Target Node dropdown list, you can select a backup node on which you want the backup data to be processed and stored.
In the Target Storage dropdown list, the storages belong to the selected backup node can be selected.
Step 3: Backup Strategies
In the General Strategy it including Schedule, Throttling Policy, Data Storage Policy and Retention Policy.
In the Schedule field, you can configure the time schedule of the backup job, you can configure the job as a Backup as Scheduled job or a Once-off Backup job.
For a once-off backup job, the job will only run for once, and only full backup will be performed. You only have to appoint a time of when to start the backup job, in the Start Time field.
If you want to setup a Backup as Scheduled job, you can schedule Full Backup and Archive Log Backup.
For PostgreSQL database, it is recommended to schedule weekly full backup with daily archive log backup.
Throttling Policy is optional. It can be used to limit the transmission speed during database backup if needed.
There are 2 options in Data Storage Policy section, Data Deduplication and Data Compression. By enabling these 2 options, the backup data will be deduplicated and compressed before saving into backup storage.
For the retention policy of the database backup, there are 2 retention modes, retain the database backups according to Number of Restore Points or Number of Days.
For the retention mode Number of Restore Points, the restore points will be counted by number of full restore points, including the archive log backups dependent on the corresponding full restore points.
For retention mode Number of Days, Vinchin Backup Server will save the restore points within the specified number of days.
When the retention policy is triggered, the outdated restore points will be purged to comply with the retention policy.
In the transmission Strategy, you can choose to enable Encrypted Transmission for data safety.
The backup data will be transferred through LAN by default.
Advanced Strategy allows you to configure archive log deletion and log space monitoring options.
Delete Archivelog: there are 3 options Delete backup up archive log, do not delete and Delete all archive log. It is recommended to use the Delete backed up archive log option to delete the archive log which had been backed up. Log Space Alert: if enabled, Vinchin backup server will monitoring on the archive log space usage, when exceeded the specified threshold you will receive alerts on the Vinchin web console.
Notice
If Delete Archivelog has been set to Do not delete, DBA must manually delete archivelog files regularly, otherwise, production database crash may occur once space is fulfilling with archive log files. It is recommended to set Delete Archivelog option to Delete backed up archive log.
Step 4: Review & Confirm
After completing the above-mentioned settings, you are able to review and confirm the settings in one screen.
A job name can be specified for identification of the database backup jobs, and by clicking on the Submit button to confirm the creation of the backup job.