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.

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.

  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:

  • 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

  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.

Following these steps will enable you to restore PostgreSQL.

Last updated

Was this helpful?