5 1 0:PostgreSQL Backup

From SEPsesam


Welcome to the latest SEP sesam documentation version 5.1.0 Apollon. For previous documentation version(s), check documentation archive.


Overview


The SEP sesam extension for PostgreSQL allows an uninterruptible backup of PostgreSQL databases. For details on PostgreSQL restore configuration, see PostgreSQL Restore.

SEP sesam PostgreSQL module supports two different methods for performing PostgreSQL database backup:

  • Using pg_dump utility, which enables performing single database backup and restore operations. Only backup level COPY is supported with this method. FULL/INCR backup levels are supported only for complete database instance backup (backup source All).
  • File-level backup involving backing up both the PostgreSQL database data files and the Write Ahead Log (WAL) files. SEP sesam can perform FULL and DIFF level backups for complete database instances when the PostgreSQL database runs in WAL mode. However, in this case single database backup or restore is not supported.

Prerequisites

To enable DIFF backups, the PostgreSQL database must run in WAL mode. Activating WAL mode requires restarting the database service.

Granting access to the database

In order for the SEP sesam PostgreSQL Backup module to work correctly, the root user must have access to the database instance.
On modern PostgreSQL setups access can be granted by using the following command:

root@database-system# su - postgres -c "createuser -s root"

This can be verified by listing the available databases on the command line:

root@database-system:~# psql -l

Configuring PostgreSQL database backup

The PostgreSQL database backup process includes FULL backups, which save all DB data files, and DIFF backups, capturing files that were changed since the last FULL backup. Internally, the INCR backup level is reserved and used for saving the Write Ahead Log (WAL) files.

While the SEP sesam PostgreSQL module now offers DIFF/INCR backups, it only supports complete database instance backup and restore. Single database backup or restore is not available with this functionality.

To configure regular PostgreSQL database backup, perform the following steps:

  1. From Main Selection -> Tasks -> By Clients, select a PostgreSQL client and click New Backup Task. The New Backup Task window opens.
  2. Select PostgreSQL task type.
  3. In the Source field enter all, or use the browse button to select what you want to back up.
  4. Note
    To perform single database backup, use the browse button to specify the Source and select the individual database you want to back up. It is recommended to create a separate backup task for each PostgreSQL database. In this case you can add the tasks to a task group and create a backup event for this task group.
  5. Specify the user name (usually postgres instead of root) to access the database. Click the tab Options and enter the following in the Backup options and Restore options fields:
  6.    -a osuser=<user_name>

    In the Backup options field, you can also specify a different port than the default port (5432) with the switch -a port=<port_number>.

  7. Configure all other options as required and click OK to save the backup task. For more information refer to Creating a Backup Task.

Screenshots

You can start the backup task immediately with the option Immediate start, or you can create a backup event and associate the task with a schedule for regular or repeated execution. You can select an existing schedule or create a new one. For instructions, see Creating a Schedule.

To create a backup event, navigate to Main Selection -> Scheduling -> Schedules, right-click the appropriate schedule and click New Backup Event. Define the settings and options as required. For more information on creating a backup event, see Creating a Backup Event.

You can monitor the status of your backup jobs. In Web UI navigate to Monitoring -> Last Backup State or Backups and review the job results. The backup status overview provides information about the last run of backup jobs, including the task name, start and stop time of the last backup, backup level, data size, throughput, assigned media pool, etc. You can also open and review details of the backup job or backup task.


See also

PostgreSQL RestoreStandard Backup Procedure

Copyright © SEP AG 1999-2024. All rights reserved.
Any form of reproduction of the contents or parts of this manual is allowed only with the express written permission from SEP AG. When compiling and designing user documentation SEP AG uses great diligence and attempts to deliver accurate and correct information. However, SEP AG cannot issue a guarantee for the contents of this manual.