How do you perform a backup and restore of a PostgreSQL database using pg_dump and pg_restore?

Performing a backup and restore of a PostgreSQL database can be a straightforward process when using the ‘pg_dump‘ and ‘pg_restore‘ utilities. The following steps detail the process:

1. **Perform a backup with pg_dump:** The ‘pg_dump‘ utility is used to create a backup of the PostgreSQL database. A database can be backed up in several formats, including plain SQL, custom or directory format. The recommended option is the custom format which provides better compression and can be faster to restore. The following code snippet shows how to perform a backup with ‘pg_dump‘:

public static void performBackup() throws IOException {
    ProcessBuilder pb = new ProcessBuilder("/usr/bin/pg_dump", "-Fc", "-f", "/path/to/backup_file.backup", "database_name");
    pb.environment().put("PGPASSWORD", "database_password");
    pb.start();
    System.out.println("Backup completed successfully.");
}

In this example, the command ‘pg_dump -Fc -f /path/to/backup_file.backup database_name‘ is executed using ‘ProcessBuilder‘. The ‘-Fc‘ option indicates that the backup will be in custom format, ‘-f /path/to/backup_file.backup‘ specifies the path and file name for the backup, and ‘database_name‘ is the name of the database to be backed up. The ‘PGPASSWORD‘ environment variable is set to provide the password for the database.

2. **Perform a restore with pg_restore:** The ‘pg_restore‘ utility is used to restore a database from a backup file. The following code snippet shows how to restore a database with ‘pg_restore‘:

public static void performRestore() throws IOException {
    ProcessBuilder pb = new ProcessBuilder("/usr/bin/pg_restore", "-c", "-O", "-d", "database_name", "/path/to/backup_file.backup");
    pb.environment().put("PGPASSWORD", "database_password");
    pb.start();
    System.out.println("Restore completed successfully.");
}

In this example, the command ‘pg_restore -c -O -d database_name /path/to/backup_file.backup‘ is executed using ‘ProcessBuilder‘. The ‘-c‘ option drops the database objects before recreating them, ‘-O‘ skips restoring the ownership of the objects, and ‘-d database_name‘ specifies the destination database for restoring. The ‘PGPASSWORD‘ environment variable is set to provide the password for the database.

These are basic examples to illustrate the process; you may need to adjust the details depending on your specific environment and requirements. It is also worth noting that the ‘pg_dump‘ and ‘pg_restore‘ utilities can be used with various options and flags that can provide greater flexibility and control over the backup and restore process. Full details can be found in the PostgreSQL documentation.