We will explore the process of performing a point-in-time recovery (PITR) in PostgreSQL. "Point-in-time recovery" allows you to restore a database to a specific moment in the past, making it an invaluable feature for disaster recovery and data restoration purposes.

Step 1: Initial Setup

Create a folder named "pgbasebackup" in "c:\Program Files\PostgreSQL\15" to store backup files. Inside "pgbasebackup," create two subfolders named "bck1" and "data.before_recovery." Create a folder named "wal_archive" in "c:\Program Files\PostgreSQL\15" and ensure it has Full Control security properties enabled.

1.   "C:\Program Files\PostgreSQL\15\pgbasebackup\bck1"
     "C:\Program Files\PostgreSQL\15\pgbasebackup\data.before_recovery"
   

Step 2: Configure PostgreSQL for Archiving

1. Access the PostgreSQL configuration file, "PostgreSQL.conf," usually located at "c:\Program Files\PostgreSQL\15\data\postgresql.conf."

2. Set the following parameters to enable archiving:

3. wal_level = replica

4. full_page_writes = on

5. archive_mode = on

    archive_command = 'copy "%p" "C:\Program Files\PostgreSQL\15\wal_archive\%f"'
   

6. archive_timeout = 300 sec.

Step 3: Allow Replication in pg_hba.conf

1. Open the "pg_hba.conf" file located at

    "C:\ProgramFiles\PostgreSQL\15\data\pg_hba.conf."
   

2. Copy the last two lines and comment out the last two lines.

3. Modify the newly copied lines to enable replication for the "Postgres" user:

1.  #host    replication     all             127.0.0.1/32            scram-sha-256
    #host    replication     all             ::1/128                 scram-sha-256
    host    replication     postgres         127.0.0.1/32               md5
    host    replication     postgres         ::1/128                    md5
   

Step 4: Restart PostgreSQL Service

1. Check the status of the PostgreSQL service using the command:

    pg_ctl status -D "c:\\Program Files\\PostgreSQL\\15\\data\\"
   

2. Restart the PostgreSQL service if it's running.

Step 5: Taking Full Backup

1. Open a command prompt and run the following command to take a base backup into the "bck1" folder:

    pg_basebackup --host=localhost --port=5432 --username=postgres --pgdata="C:\Program Files\PostgreSQL\15\pgbasebackup\bck1"
   

Step 6: Add Extra Data (Optional)

1. Perform additional data modifications in specific tables of your PostgreSQL database, if required.

Point-in-Time Recovery:

Step 1: Stop Database Server

1. Open a command prompt and stop the PostgreSQL server using the following command:

    pg_ctl stop -D "c:\\Program Files\\PostgreSQL\\15\\data\\"
   

Step 2: Backup Current Database Files

1. Create a backup of the current database files using the following command:

    xcopy /O/X/E/H/K "C:\\Program Files\\PostgreSQL\\15\\data" "C:\\Program Files\\PostgreSQL\\15\\pgbasebackup\\data.before_recovery"
   

Step 3: Remove Contents in Data Directory

1. Clear the contents of the data directory using the following command:

    rmdir /S/Q "c:\\Program Files\\PostgreSQL\\15\\data"
   

Step 4: Restore the Database to Point in Time

1. Create a new empty data directory for PostgreSQL:

    mkdir "c:\\Program Files\\PostgreSQL\\15\\data"
   

2. Copy the backup files from "bck1" to the data directory:

    xcopy /O/X/E/H/K "c:\\Program Files\\PostgreSQL\\15\\pgbasebackup\\bck1" "c:\\Program Files\\PostgreSQL\\15\\data"
   

Step 5: Cleanup Log Files

1. Delete the log files in the data directory:

    del /S/F/Q "C:\Program Files\PostgreSQL\\15\\data\\log"
   

2. Set Full Control permissions for the data directory.

Step 6: Create Recovery.conf File

1. Inside the new data directory, create a file named "recovery. conf".

2. Write the following command in the "recovery. conf" file:

    restore_command = 'copy "C:\\Program Files\\PostgreSQL\\15\\wal_archive\\%f" "%p"'
    recovery_target_time = '2023-07-24 12:00:00'
   

Step 7: Start the Server in Recovery Mode

1. Open a command prompt and start the PostgreSQL server in recovery mode:

    pg_ctl start -D "c:\\Program Files\\PostgreSQL\\15\\data\\"
   

Conclusion: In this blog post, we've learned how to set up point-in-time recovery for PostgreSQL. This powerful feature ensures data integrity and allows you to restore your database to a specific moment in time, minimizing the risk of data loss and providing better disaster recovery capabilities. By following the step-by-step guide, you can confidently perform point-in-time recovery in your PostgreSQL environment.