How do you perform point-in-time recovery (PITR) in PostgreSQL using WAL archives and base backups?

Performing point-in-time recovery (PITR) in PostgreSQL involves restoring a database to a specific point in time using a combination of base backups and Write-Ahead Log (WAL) archives. This process is useful in case data has been accidentally deleted or a database has become corrupt, and you need to restore it to a specific point in time, equal to or earlier than the last transaction committed to the database. To perform PITR in PostgreSQL, you will follow these steps:

1. Set up WAL archiving in the PostgreSQL configuration file. This will allow PostgreSQL to store transaction log files in a specific directory as they are being written. You can do this by editing the ‘postgresql.conf‘ file and setting the following parameters:

   wal_level = replica
   archive_mode = on
   archive_command = 'cp %p /path/to/wal/archive/%f'

This configuration will ensure that all WAL files are copied to the specified directory for archiving.

2. Create a base backup of the database. This is a complete backup of the database files, including all data and metadata at a specific point in time. To create a base backup, you can use the ‘pg_basebackup‘ utility, as shown in the following Java code:

   String backupDir = "/path/to/backup/dir";
   String dbHost = "localhost";
   String dbPort = "5432";
   String dbName = "mydb";
   String user = "myuser";
   String[] pgBaseBackupCmd = new String[] {
       "pg_basebackup",
       "-h", dbHost,
       "-p", dbPort,
       "-U", user,
       "-D", backupDir,
       "-Ft",
       "-z"
   };
   ProcessBuilder pb = new ProcessBuilder(pgBaseBackupCmd);
   Process p = pb.start();
   int exitCode = p.waitFor();

This code will create a base backup in the specified directory in a compressed tar archive format.

3. Create a recovery.conf file in the PostgreSQL data directory. This configuration file specifies the location of the WAL archives and the starting point for the recovery process. You can create a recovery.conf file with the following contents:

   restore_command = 'cp /path/to/wal/archive/%f %p'
   recovery_target_time = 'YYYY-MM-DD HH:MI:SS'

Replace ‘YYYY-MM-DD HH:MI:SS‘ with the specific date and time to which you want to restore the database.

4. Start the PostgreSQL server in recovery mode. This will initiate the recovery process and apply all WAL files up to the specified point in time. You can do this using the following Java code:

   String dataDir = "/path/to/postgres/data";
   String[] pgCtlCmd = new String[] {
       "pg_ctl",
       "-D", dataDir,
       "start",
       "-w",
       "-t", "600",
       "-o", "-c "recovery_target_timeline='latest'""
   };
   ProcessBuilder pb = new ProcessBuilder(pgCtlCmd);
   Process p = pb.start();
   int exitCode = p.waitFor();

This code will start the PostgreSQL server in recovery mode and apply all WAL files up to the specified time.

5. Verify that the recovery process was successful. You can connect to the PostgreSQL server using a database tool and execute queries to verify that the data has been restored to the specific point in time.

In conclusion, performing point-in-time recovery in PostgreSQL using WAL archives and base backups involves configuring WAL archiving, creating a base backup, creating a recovery.conf file, starting the PostgreSQL server in recovery mode, and verifying the recovery process.