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_backup_pre.sh: This script puts the database into backup mode.
• postgresql_snapshot_post.sh: This script removes the database from backup mode.
• postgresql_settings: This is a configuration file where PostgreSQL variables might need to be updated.
• postgresql_backup_post.sh: This script updates log about the backup status.

Pre-requisites

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 LinuxPostgres.zip which contains the following four files: postgresql_backup_pre.sh, postgresql_snapshot_post.sh, postgresql_settings, postgresql_backup_post.sh
2. Copy the files from pre/post backup/snapshot to the following path on the Linux backup server /opt/Arcserve/d2dserver/usr/prepost.
3. Copy the postgresql_settings to the source path /root/backup.
4. Make sure to check the postgresql_settings for all values set against the variables and make modifications for any required changes as per your environment.
5. Configure the plan from UDP Console and select the PostgreSQL node as source.
6. 
6. Confirm the backup status. To know the status of PostgreSQL backup, check the arcserve_postgresql_backup_${DATE}.log file. This log file gets created under the directory, which is set by the user. For more information about configuring the directory, refer to the postgresql_settings file.

Restore PostgreSQL database

Follow these steps:

1. Stop the database server.
2. To restore the original location, do the following:
1. Delete files and directories from the current /data folder.
2. Perform a restore of the entire /data folder.
3. Delete the files from the following folders after the 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

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 configured for WAL Archiving, and then 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.

Limitations

The above scripts will not help to perform backup if PostgreSQL database is configured with non-default port. The scripts work only with the default port number 5432.

As a workaround, use the following recommendations to manually modify the postgresql_backup_pre.sh and postgresql_snapshot_post.sh scripts:

• postgresql_backup_pre.sh:
• Original: sudo -u ${USERNAME} -H -- psql -c "SELECT pg_start_backup('Arcserve UDP backup - ${DATE} $(timestamp)', true)" >> ${LOG} 2>&1
• Modified: sudo -u ${USERNAME} -H -- psql -p 5432 -c "SELECT pg_start_backup('Arcserve UDP backup - ${DATE} $(timestamp)', true)" >> ${LOG} 2>&1
• postgresql_snapshot_post.sh:
• Original: sudo -u ${USERNAME} -H -- psql -c "SELECT pg_stop_backup()" >> ${LOG} 2>&1
• Modified: sudo -u ${USERNAME} -H -- psql -p 5432 -c "SELECT pg_stop_backup()" >> ${LOG} 2>&1