> Expert > Backup and Restore Agents > Databases > PostgreSQL

FAQ

What additional mandatory packages are required to be installed along with PostgreSQL?

You need to install only the basic packages such as PostgreSQL-client and PostgreSQL-server. You need not install any additional packages along with the basic PostgreSQL packages.

When does a non-full backup automatically get converted to a full backup?

A non-full backup is automatically converted to a full backup in the following situations:

  • First backup of the subclient.

  • Promote a secondary storage policy copy that is not synchronized with a primary copy (for all the subclients of a storage policy).

  • If you switch from a IntelliSnap backup to a traditional backup or vice versa.

  • CommCell Migration operation.

  • When the Commvault software upgrades the indexing version of a client from version 1 to version 2. This happens if you enable the Use parallel jobs to dump the database option at the DumpBasedBackupSet properties level of the client.

How are the backups and restores performed in PostgreSQL?

This iDataAgent supports two types of backup:

  • Dump based backup

  • File System based backup

Dump Based Backup

Database dump is obtained in the form of a file. When you send this file back to the server, the database is recreated in the same state as it was existing at the time of dump. We use pg_dump and pg_restore utilities of the application. If you select the Backup global objects option, global objects metadata, such as roles and tablespace, will be included in the backup using pg_dumpall utility.

File System Based Backup

Commvault backs up the files used by PostgreSQL to store the data in the database. File system based backups will only work for a complete backup and restoration of an entire database cluster. Commvault uses pg_start_backup and pg_stop_backup processes of the application for the backup and restore operations. For PostgreSQL version 15 and more recent versions, pg_backup_start and pg_backup_stop processes are used.

What are all the files that get backed up during a full backup?

The data directory and archive log directory contents are backed up during a full backup.

Suppose, you run a log backup at time T1, and later you run a full backup (data and log) at time T2, then the transaction log files generated between T1 and T2 are also a part of the latest full backup.

Temporary .backup files created by PostgreSQL Server as part of log archiving are also backed up during a full backup operation.

What are all the files that get backed up during a log only backup?

Only the transaction log files that are relevant to the current backup cycle are backed up during a log only backup.

How does log file cleanup happen when Archive Delete option is enabled?

If the Archive Delete check box is selected, all the transaction log files that are backed up during the log backup are deleted. The transaction log files that were not deleted during earlier backups are also cleaned up. Temporary files created by PostgreSQL Server, that is .backup files are also pruned from the Log directory. Due to continuous archiving of transaction log files, you may see new transaction log files in the WAL directory after successful log backups. These log files are backed up during the next log backup.

If Archive Delete check box is not selected, the Commvault software does not prune the logs. Here, it is useful to have a method to clean up the archive log directory.

If the logs are not pruned from the archive log directory, all the logs will be backed up with full backups. If you do not want to back up the old cycle logs during a newer full backup cycle, set the value of additional setting nDoNotBackupAllLogs to 1.

How is the data retrieved during browse and restore operations?

When you browse or restore data, the browse function by default returns the requested data based on the latest backups available. This is usually the information that users are interested in. The browse function does this by using the current date and time as the effective date.

What is the job restart behavior for backup and restore jobs for this agent?

If a backup or restore job restarts for FSBasedBackupSet, it will not restart from the beginning, but will resume from the point of failure.

If a backup or restore job restarts for DumpBasedBackupSet, it will restart from the beginning of the database for which it was running pg_dump or pg_restore.

Why is the size of application for dumpbased backups sometimes greater than the actual size of the database?

Commvault performs dump based backup operation using the -Ft option which provides the dump output in tar format. In some cases, size of the tar dump could be greater than the actual size of the database.

You can reduce the size of the dump for dump based backups by enabling Commvault compression for the subclient. For more information on compression, see Data Compression.

Do we support compression for transaction logs?

You can achieve compression for transaction logs using gzip for archive command and gunzip for restore command.

For performing a restore operation, consider the following steps:

  • From the Restore Options dialog box, clear the Recover Database Server check box to prevent the PostgreSQL Server from starting automatically after the restore operation is complete.

  • After the restore operation is complete, edit the restore command as applicable. You can then start the PostgreSQL server manually. 

    restore_command = 'gunzip < /<path to archive dir>/%f > %p'

Can we protect multiple PostgreSQL instances in the same client, when each instance uses the same port but different IPs?

Yes, if you are using the same port number for two or more instances, in the Properties of PostgreSQL Instance dialog box, type the port number in the IP:PORT format.

For example: You can type the port number as 10.57.198.4:5432, if the IP for your Network Interface Card (NIC) is 10.57.198.4 and you are using port 5432. To use the same port number on a different NIC, you can type 10.57.198.5:5432.

FAQ - Do you support EDB Postgres Advanced Server Version 15 with Transparent Data Encryption feature enabled?

Yes, we support the EDB Postgres Advanced Server Version 15 enabled with Transparent Data Encryption feature.

Can we run both dump based backup operation and file system based backup operation simultaneously from an instance?

Yes, both dump based backup operation and file system based backup operation can be run simultaneously without any issues. Such backups are consistent and can be restored without any issue.

Can a dump restore operation be followed with a log restore operation from a file system backup?

No, we cannot restore dump from a dump based backup and apply logs on the server. Log only restore operation cannot be performed for PostgreSQL. A PostgreSQL file system based restore is always a full instance restore.

Can I run PostgreSQL backup and restore operations on EDB Postgres Advanced Server?

Yes, you can run PostgreSQL backup and restore operations on EDB Postgres Advanced Server.

Do we support backup and restore operations for PostgreSQL servers running in a docker container?

Yes, we support dumpbased backup and restore operations for PostgreSQL servers running in a docker container. The prerequisite is that PostgreSQL agent must be installed on the docker host and the PostgreSQL client side binaries. Major version same as server installed on the docker container must be present on the docker host.

We use psql, pg_dump and pg_restore binaries for backup and restore operations. Ensure that all these binaries reside in the PostgreSQL bin path that is used to create an instance. During PostgreSQL instance creation, provide the binary and library directories from the host machine. Ensure that the port is provided as localhost:port. For example, localhost:5432. You can run backups and restores only for a dumpbased backupset in a docker container.

Is database-level restore supported from EnterpriseDB to Community edition using Dumpbased backupset?

Yes, you can perform database-level restore from EnterpriseDB to Community edition using Dumpbased backupset. For more information, see Restore Entire Database to an Auxiliary Database.

Can we exclude tables from databases during PostgreSQL dump backups?

Yes, you can use the additional setting sPGExcludeTables to exclude tables across databases during dump backups. You can set the value as comma-separated strings that could consist multiple patterns for excluding tables. For example: db1."public"."table1", db2."public"."table1".

For instructions on adding additional settings from the CommCell Console, see Adding an Additional Setting from the CommCell Console.

Loading...