How can I restore specific data in Postgresql by using “pg_restore”

In the context of SQL, here are the steps given:-

Create a new database

First, you would need to create the target database by using the “createdb” command.

Restoring the database by using “pg_restore”

Now you can use “pg_restore” command to restore the backup file into the newly or targeted database.

Verify the restoration

Now you can connect to the “companydb.restored” database by using the “psql” to verify whether the data, schema, and roles have been correctly restored or not.

Here is the combined script given which includes creating the new database and restoring the backup file including the steps for verifying the restoration:-

#!/bin/bash
# VariablesDB_NAME=”companydb_restored”
BACKUP_FILE=”/path/to/companydb_backup.tar”
DB_USER=”your_username”
DB_HOST=”your_host”
# Prompt for password
Echo “Enter password for PostgreSQL user $DB_USER:”
Read -s PGPASSWORD
Export PGPASSWORD
# Step 1: Create the new database
Createdb -U $DB_USER -h $DB_HOST $DB_NAME
# Step 2: Restore the database using pg

By using these above steps and script you can successfully and easily restore the “command_backup.tar” file into the “companydb_restored” database.

Here is also a Java-based program given which can automate the process of creating a new database. This program will also help you in restoring it from a backup file by using the Command “pg_restore”. This example is the “process builder” class to implement the shell command from within the Java programming language:-

Import java.io.BufferedReader;

Import java.io.InputStreamReader;

Public class PgRestoreUtility {    // Configuration
    Private static final String DB_NAME = “companydb_restored”;
    Private static final String BACKUP_FILE = “/path/to/companydb_backup.tar”;
    Private static final String DB_USER = “your_username”;
    Private static final String DB_HOST = “your_host”;
    Public static void main(String[] args) {
        Try {
            // Step 1: Create the new database
            createDatabase(DB_NAME, DB_USER, DB_HOST);
            // Step 2: Restore the database using pg_restore
            restoreDatabase(DB_NAME, BACKUP_FILE, DB_USER, DB_HOST);
            // Step 3: Verify the restoration
            verifyRestoration(DB_NAME, DB_USER, DB_HOST);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    Private static void createDatabase(String dbName, String dbUser, String dbHost) throws Exception {
        System.out.println(“Creating database: “ + dbName);
        ProcessBuilder processBuilder = new ProcessBuilder(
                “createdb”, 
                “-U”, dbUser, 
                “-h”, dbHost, 
                dbName
        );
        processBuilder.environment().put(“PGPASSWORD”, getPassword());
        executeCommand(processBuilder);
    }
    Private static void restoreDatabase(String dbName, String backupFile, String dbUser, String dbHost) throws Exception {
        System.out.println(“Restoring database from backup file: “ + backupFile);
        ProcessBuilder processBuilder = new ProcessBuilder(
                “pg_restore”, 
                “-d”, dbName, 
                “-U”, dbUser, 
                “-h”, dbHost, 
                “-v”, 
                backupFile
        );
        processBuilder.environment().put(“PGPASSWORD”, getPassword());
        executeCommand(processBuilder);
    }
    Private static void verifyRestoration(String dbName, String dbUser, String dbHost) throws Exception {
        System.out.println(“Verifying restoration for database: “ + dbName);
        ProcessBuilder processBuilder = new ProcessBuilder(
                “psql”, 
                “-d”, dbName, 
                “-U”, dbUser, 
                “-h”, dbHost, 
                “-c”, \dt
        );
        processBuilder.environment().put(“PGPASSWORD”, getPassword());
        executeCommand(processBuilder);
    }
    Private static void executeCommand(ProcessBuilder processBuilder) throws Exception {
        processBuilder.redirectErrorStream(true);
        Process process = processBuilder.start();
        Try (BufferedReader reader = new BufferedReader(new InputStreamReader(process.getInputStream()))) {
            String line;
            While ((line = reader.readLine()) != null) {
                System.out.println(line);
            }
        }
        Int exitCode = process.waitFor();
        If (exitCode != 0) {
            Throw new RuntimeException(“Command execution failed with exit code: “ + exitCode);
        }
    }
    Private static String getPassword() {
        // In a real-world scenario, you should securely fetch and handle the password
        // For simplicity, it is hard-coded here (Not recommended for production)
        Return “your_password”;
    }
}