PostgreSQL Restore Process

Recovering a PostgreSQL database involves two crucial stages. The first step is to recover the data files to the destination directory on the client. The second step is to perform point-in-time recovery of the database using PostgreSQL tools.

Note: During the restoration process, it's essential to follow these precautions:

1. Stop the PostgreSQL service before restoring files to the original location. After restoration, restart the PostgreSQL server.

2. As a precaution, copy the files in the database directory to a temporary location.

3. Ensure that all files and parent directories have correct read-write permissions and ownership as postgres:postgres.

Steps to recover the data files to the destination directory

1. Stop the PostgreSQL service: Before starting the restoration process, halt the PostgreSQL service.

2. Copy the PostgreSQL database directory: Move the PostgreSQL database directory to a temporary location by executing these commands.

# mkdir –p /var/lib/pgsql-restore/safeguard
# mv /var/lib/pgsql/14/data/ /var/lib/pgsql-restore/safeguard

1. Initiate restoration:

• Access the Restore tab and choose the backup set from the dropdown.

• Select the backup you wish to restore from the Restore What page and proceed to the next step.

1. Select destination directory: Choose the destination directory on the Restore Where page. By default, the temporary location is set to /tmp. If you wish to change this, toggle the option to Manual and specify the directory in the field provided.

Note:

• Ensure both the Destination Directory and Temporary Directory have sufficient space to accommodate the selected backup data.

• Avoid selecting the PostgreSQL data directory as the destination for your backup, especially if PostgreSQL is currently running and the files haven't been relocated elsewhere beforehand (see previous section for alternative locations). This could corrupt your data!

1. Restore data: Select file and directory conflict options on the Restore How page. Proceed to the Restore Now page and initiate the restore process.

1. Post restoration: After restoration, verify that the restore directory reflects the expected structure.

PostgreSQL Point-in-Time Recovery:

Note:

• During the restoration process on Debian machines, rename the 'data' folder to 'main' as mandatory for versions 5.1.4.

• Refer How to Get Your PostgreSQL Restore Ready for Zmanda 5.1 as mandatory for versions 5.1.3 and below. Version 5.1.4 does not require this step.

• The PostgreSQL server must be stopped during this process and restarted afterward.

• Move data: Move the data from the restore directory to the data directory of the Postgres server. Example: # mv /var/lib/pgsql/restore/data /var/lib/pgsql/14/

Follow these steps for Postgres version >= 12

1. Edit postgresql.conf: Define a restore_command in postgresql.conf with the full path of the cp utility and the restore archive directory path.

   eg: restore_command = ‘<path to cp> path/to/restore/archive/%f "%p"’

2. Create recovery.signal file: Create a blank recovery.signal file in the data directory to initiate recovery mode.

3. Start PostgreSQL service: Start the PostgreSQL service to begin recovering from archived WAL files. If recovery stops due to errors, restart the server to continue.

Follow these steps for Postgres version <= 11

1. Create recovery.conf File: Create the file recovery.conf in the data directory. Modify pg_hba.conf to prevent users from connecting until recovery is verified

Refer to the PostgreSQL documentation for detailed instructions: PostgreSQL Continuous Archiving and Point-in-Time Recovery Documentation.

1. Edit recovery.conf: Modify recovery.conf file located at /var/lib/pgsql/11/data/ to include the restore_command entry specifying the path to the temporary archive directory. Run the following restore command:

<path to cp> /path/to/restore/archive/%f "%p

1. Change ownership and permissions: Ensure that recovery.conf is owned by the database system user and has appropriate permissions. Change the ownership of the file to the database system user using the chown command:

# chown postgres:postgres /var/lib/pgsql/11/data/recovery.conf

Use chmod to limit file access to the database system user, allowing only read and write permissions using the following command.

# chmod 0600 /var/lib/pgsql/11/data/recovery.conf

1. Start PostgreSQL service: Initiate the PostgreSQL service to commence recovery from archived WAL files. If recovery halts due to errors, restart the server to resume.

Inspect the database to verify its point-in-time status. Check the log sub-directory under the Postgres data directory for any recovery errors. If necessary, repeat the recovery process. Once verified, restore pg_hba.conf to its production state to allow end-user access.

• For detailed information on PostgreSQL Point-in-Time Recovery, refer to the PostgreSQL Documentation: PostgreSQL: Documentation: 11: 25.3. Continuous Archiving and Point-in-Time Recovery (PITR)

• For Amanda client versions 5.1, 5.1.1, and 5.1.2, refer to the KB article for instructions on performing incremental backups after recovery. This workaround is not required for Amanda client versions 5.1.3 and subsequent versions. Upgrading to the latest Amanda client binaries is highly recommended to avoid this workaround and benefit from potential improvements and bug fixes.

Following these steps will enable you to restore PostgreSQL.