How can I restore specific data in Postgresql by using “pg_restore”
I am a database administrator for a particular mid-sized company that uses PostgreSQL for its critical application. Recently, I have been tasked with restoring a specific database from a backup file that was created by using the “pg_dump”. The backup file, “company_backup.tar” is stored on my local machine. My requirement is restoring should be performed on a new database called “company_restored” considering the server should not be affected by the process of restoring.
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 pgBy 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”;
}
}