PostgreSQL Server - Linux

Everyone's favorite Elephant-inspired database is covered by Zmanda

PostgreSQL Server Backup

This guide outlines the steps involved in configuring Zmanda for PostgreSQL server backups and adding a PostgreSQL source in the Zmanda Management Console (ZMC).

Note: When you configure and license Zmanda EBR for PostgreSQL backup, Zmanda Management Console (ZMC) allows you to configure backups for your PostgreSQL server. These backups can be either full or incremental, depending on your needs.

• Full backups capture both data and archive log files (PostgreSQL WALs).

• Incremental backups encompass only the Write Ahead Logs (WALs).

• Incremental backups contain WALs written to the log directory, excluding those not yet written to a WAL file.

Requirements

Before proceeding with PostgreSQL server backup and restore using Zmanda EBR, ensure the following prerequisites are met. These instructions assume you have already installed and licensed the Zmanda EBR server and the PostgreSQL server intended for backup. Additional requirements include:

Pre-configuration Checks

• Before initiating the configuration process, ensure the following pre-configuration checks are performed:

  • PostgreSQL version compatibility: Confirm that the version of PostgreSQL on the client aligns with the tested versions listed on the Zmanda Network supported platforms page under Compatible Databases.

  • GNU tar version: Verify that the system tar utility, typically located at /bin/tar, is GNU tar version 1.26 or later on the client running the PostgreSQL server.

Zmanda Client Configuration (Do these on the client)

• Create WAL directory: Create a new directory for storing Write Ahead Logs (WALs) during backup. The directory should be owned by the user amandabackup and belong to the group postgres, with permissions set to 770.

Before configuring Zmanda for PostgreSQL backup, ensure the following steps are taken:

should be owned by user: amandabackup and group: postgres, with permissions set to 770.

• PostgreSQL configuration changes: Make the following changes in the PostgreSQL configuration file (postgresql.conf), restarting the postgresql service is necessary for any modifications made to this file to be implemented:

a. Enable WAL: Enable WAL for full and incremental backups by setting wal_level to replica, or logical.

b. Configure WAL archiving: Set archive_mode to 'on' and define an archive_command full path of the cp utility (find it using which cp) and the WAL directory path, e.g., archive_command = '<path_of_cp> %p /path_to_archive_dir/%f'.

c. Allow Zmanda server access: Ensure the listen_address includes the IP address or hostname of the Zmanda server.

d. Restart PostgreSQL: After making changes, restart the PostgreSQL service for them to take effect.

• Database user permissions:

Modify the pg_hba.conf file to permit the amandabackup and root users to access all databases from the PostgreSQL server.

• Amanda-client configuration:

Edit the amanda-client.conf file located in /etc/amanda directory, incorporating the following changes:

property "PG-DATADIR" "Path_to_PSQL_Data_Dir"
property "PSQL-PATH" "Path_to_PSQL_Binary"
property "PG-ARCHIVEDIR" "Path_to_WAL directory"
property "PG-CLEANUPWAL" "Whether_to_clean_up_WAL_Yes_or_No"
property "PG-USER" "PostgreSQL_username"
property "PG-PASSWORD" "PSQL_Password"

Sample of amanda-client.conf properties

1. Avoid data import during backup: Refrain from performing PostgreSQL data import (loading data from a data file) during the backup process to prevent backup failures.

2. Restart the PostgreSQL service to ensure all applied changes take effect.

To locate data directory and PSQL path:

These steps are crucial when configuring Zmanda for PostgreSQL backups.

• Identify the PostgreSQL data directory by logging in as the postgres user and running the command show data_directory.

• Determine the path of the PostgreSQL binaries (PSQL_PATH) by running the command which psql.

Adding a PostgreSQL source in ZMC

1. Navigate to the Sources tab in ZMC.

2. Click the Add Source option.

3. Choose the source type as Database.

4. Select PostgreSQL from the dropdown.

5. Provide the necessary client details, including the IP address of the PostgreSQL server.

6. Enter the path to the data directory associated with the PostgreSQL server.

7. Click Save to add the source to your backup sets..

Following these steps ensures the proper configuration of a PostgreSQL source within ZMC for backup management.