Use Scripts to Backup and Restore PostgreSQL Database

The following scripts are available to perform backup of the PostgreSQL database. When running the scripts, you do not have to stop your database to perform a backup.

• postgresql_pre_script.bat: This script puts the database into backup mode.
• postgresql_post_snapshot_script.bat: This script removes the database from backup mode.
• postgresql_pre_post_conf.bat: This is a configuration file where PostgreSQL variables might need to be updated.

Prerequisites

Before you begin the backup, make sure to do the following:

• WAL level is set to archive (or hot_standby)
• archive_mode is set to on
• archive_command has to be set to specify the archive location

Note: To apply the settings, reboot the server after configuring these settings in the postgresql.conf file.

The following commands help to check the status of the archive mode after reboot:

• show archive_mode
• show archive_command
• show WAL level

Apply Scripts

Follow these steps:

1. Extract the PostgreSQL_UDP_Windows_Scripts.zip, which contains the following three files: postgresql_pre_script.bat, postgresql_post_snapshot_script.bat, postgresql_pre_post_conf.bat.
2. Create a folder on a node where PostgreSQL database is running, for example: C:\PostgresBKP, and then copy all three files.
3. Make sure to check the postgresql_pre_post_conf.bat for all values set against the variables and make modifications for any required changes as per your environment.
4. Configure the plan from UDP Console and select the PostgreSQL node as source.
5. 
5. Confirm the backup status. To know the status of PostgreSQL backup, check for the postgresql_pre_post_backup.txt file, which gets created under the directory ‘C:\Program Files\Arcserve\Unified Data Protection\Engine\Logs’.

Restore PostgreSQL Database

1. Stop the database server.
2. To restore to the original location, do the following:
1. Delete files and directories from the current /data folder.
2. Perform a restore of entire /data folder.
3. Delete the files from the following folders after completion of restore from /data folder:
• pg_dynshmem/
• pg_notify/
• pg_serial/
• pg_snapshots/
• pg_stat_tmp/
• pg_subtrans/
• pg_internal.init
4. Go to the folder, which is configured for WAL Archiving, and do the following:
1. Delete the files present in the restored pg_wal directory which contains the information related to transactions performed during the backup.
2. Now, copy files from the user defined archived location to the pg_wal folder, for data consistency and point-intime recovery.
5. Start the Database server.

Restore to Alternate location on the Same Server:

Follow these steps:

1. Stop the database server.
2. Run PGDATA configuring it to the” new_data_directory_path”.
3. Initialize the newly created DB using `Initdb` cmd.
4. Delete files and directories from the current /data folder.
5. Perform a restore of entire /data folder.
6. Delete the files from the following folders after completion of restore from /data folder:
• pg_dynshmem/
• pg_notify/
• pg_serial/
• pg_snapshots/
• pg_stat_tmp/
• pg_subtrans/
• pg_internal.init
7. Go to the folder, which is configured for WAL Archiving, and do the following:
1. Delete the files present in the restored pg_wal directory, which contains the information related to transactions performed during the backup.
2. Now, copy files from the user defined archived location to the pg_wal folder, for data consistency and point-intime recovery.
8. Start the Database server.
9. Note: Make sure that the database startup is performed in the session where the PGDATA gets updated.

Sample postgresql_pre_post_conf.bat

PG_BIN_PATH=C:\"Program Files"\PostgreSQL\12\bin\

PG_DATA_DIR=C:\"Program Files"\PostgreSQL\12\data\

PG_USERNAME=postgres

PGPASSWORD=postgres

Set PG_PORT=5432