NetVault Plug-in for PostgreSQL 12.3.1

Reviewing the available backup methods Supported backup formats for the SQL Logical Data Dump backup method Available backup types for the SQL Logical Data Dump backup method Backup strategy considerations Examples of backup sequences

Performing a backup

Selecting data for a backup Setting backup options Finalizing and submitting the job

Backing up Global Objects only Backing up configuration files

Restoring data

Selecting data for a restore Setting restore options

Setting restore options for a File-Level Data Copy

Finalizing and submitting the job Performing advanced restore procedures

Restoring Global Objects Restoring configuration files Renaming a database during restore Restoring data to a different database cluster Restoring to a database cluster on an alternate server

Troubleshooting

Available backup types for the SQL Logical Data Dump backup method

The plug-in provides the following backup types:

•

Full Database Cluster: This option backs up all the databases within the cluster in the selected backup format. It also includes a backup of Global Objects, which are in the form of a Plain-text Script file, regardless of the backup format specified. The plug-in automatically includes the configuration files when the database cluster is selected; however, you can exclude it from the backup if applicable.

•

Individual Database/Table: This option backs up selected databases or tables in the selected backup format. You can use it to back up databases that are updated more frequently than others or use it as a source when creating test environments. This option does not include Global Objects when the databases or tables are backed up individually.

•

Global Objects Only: This option backs up only the Global Objects, which you can use to back up the updates made to the database users, groups, and access permissions.

•

Configuration Files Backup: You can back up the configuration files with any of the preceding backup types, or separately. The following files are included when you select this option:

•

postgresql.conf: This file is the primary server configuration file that contains all the parameter settings and directives.

•

pg_hba.conf: This file is the host-based authentication (HBA) or client authentication file.

•

pg_ident.conf: This file stores ident maps, excluding sameuser, and is used for ident-based authentication.

This backup type is similar to a file-system backup. You can use it to restore corrupt or lost configuration files, or to restore the cluster settings to a different server.

Backup strategy considerations

When defining the backup strategy, consider the following:

•

Database size.

•

Frequency of updates and changes.

•

Off-peak period for full backups.

•

Whether some databases are updated more frequently than others.

•

Whether changes are confined to a small or large number of tables in a database.

•

Available storage space and future expansion.

Examples of backup sequences

Following are a few sequences that might meet your PostgreSQL database protection requirements.

•

Full Database Cluster backups only: If update characteristics are similar across multiple databases, Full Database Cluster backups scheduled every night ensures data protection up to the previous day.

A Full Database Cluster backup consumes large amounts of storage space. However, restores are easier and quicker as only a single job is required to restore the entire cluster. Using the Custom Archive backup format can reduce the backup size.

•

Full Database Cluster backups and Individual Database/Table backups: If some databases are updated more frequently than others, regular Individual Database/Table backup coupled with periodic Full Database Cluster backup is an ideal strategy. For example, Full Database Cluster backups can be performed every Sunday night at 11:00 p.m., while Individual Database backups for the databases that are updated frequently can be performed Monday through Saturday at 11:00 p.m. This ensures data protection up to the previous day. You can increase backup frequency to reduce data loss, and use the Custom Archive (Linux only) format with a higher compression level for large databases to reduce the backup size.

Databases that are updated less frequently can be backed up two or three times a week or on demand after an update is made.

During restores, the databases that have not been updated since the last backup can be restored from the Full Database Cluster, while the others can be restored from their last individual backup. Even though the backups are quicker, the restores can take longer due to the additional intervention that is required to run multiple restore jobs.

•

Global Objects Only backup: Because the individual database backups exclude the global objects, you can use Global Objects Only after updates are made to the database users, groups, and access permissions. This option ensures that these recent backups can be used instead of Full Database Cluster backups.

•

Individual Database/Table backups and Global Objects Only backups: You could use a backup strategy of Individual Database/Table backups plus Global Objects Only backup where you back up the individual databases according to their update frequency. You might back up databases that updated less frequently on a weekly basis and back up the remaining databases daily. Also, you might run Global Objects Only backups weekly or on demand after an update to the database groups, users, and access permissions is made.