Backup and Recovery of Postgres

Backup

pg_dump --dbname=<database_name> \
--inserts \
--format=d \
--create \
--file=<directory with trailing slash> \
--username=<username> \
--host=localhost \
--port=5439

Here is an explanation of the parameters you mentioned for the pg_dump command:

--column-inserts: This parameter includes column names in the INSERT statements in the dump file. By default, pg_dump generates INSERT statements without column names, using the VALUES clause instead. Including column names can make the dump file more readable and can be useful when inserting data into a table with a different column order or when there are default values set for some columns.

--format=p: This parameter specifies the output file format as a plain-text SQL script. The p format is the default format for pg_dump, and it generates a script file containing SQL commands that can be used to recreate the database and its data.

--if-exists: This parameter instructs pg_dump to include commands to drop database objects (tables, views, etc.) only if they already exist in the target database. This is useful when restoring a backup to a database that may already contain some objects with the same names.

--create: This parameter tells pg_dump to include commands to create the database objects (tables, views, etc.) in the dump file. When the dump file is restored, these commands will recreate the database objects in the target database.

--clean: This parameter includes commands to drop (delete) the database objects before recreating them. This ensures a clean state for the database objects, removing any existing data or structures before restoring from the dump file.

--data-only: This parameter instructs pg_dump to dump only the data in the tables, excluding the schema (data definitions). This can be useful when you want to restore the data to an existing database without modifying the schema. Here's an example command that combines these parameters to create a backup with specific options:

example:

pg_dump --column-inserts --format=p --if-exists --create --clean --data-only -U postgres mydatabase > backup.sql

In this example:

--column-inserts includes column names in the INSERT statements. --format=p specifies the plain-text SQL script format. --if-exists drops objects only if they exist in the target database. --create includes commands to create the database objects. --clean drops the objects before recreating them. --data-only dumps only the data, excluding the schema. -U postgres specifies the user as postgres. mydatabase is the name of the database to back up. backup.sql is the output backup file path.

Remember to replace mydatabase with the actual name of your database and adjust the user as needed.

Backup file Per schema ( untested)

#!/bin/bash

# Get the list of schemas
schemas=$(psql -U username -d dbname -q -t -c "SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT LIKE 'pg_%' AND schema_name != 'information_schema';")

# Loop through each schema and create a dump file
for schema in $schemas; do
    pg_dump -U username -d dbname -n $schema -Ft -f $schema.dump
done

Restore

To restore the directory of backups created by pg_dump to your local PostgreSQL database safely, you can use the pg_restore command. Here's an example command to restore the backups:

pg_restore --dbname=your_database_name --username=your_username --host=localhost --port=5432 --jobs=4 --verbose /path/to/backup_directory

Let's break down the command:

--dbname: Specify the name of your local PostgreSQL database where you want to restore the backups.
--username: Specify the username to connect to the local PostgreSQL database.
--host: Specify the hostname or IP address of the local PostgreSQL server.
--port: Specify the port number on which the local PostgreSQL server is running.
--jobs: Specify the number of parallel jobs to use during the restore process. Adjust this value based on your system's capabilities.
--verbose: Display verbose output during the restore process.
/path/to/backup_directory: Specify the path to the directory containing the backup files created by pg_dump.

Make sure to replace your_database_name, your_username, and /path/to/backup_directory with the appropriate values for your setup.

Note that the pg_restore command will restore the database objects and data from the backup files in the specified directory. It will recreate the database objects and insert the data into the corresponding tables.

Before performing the restore, ensure that the target database is empty or has the same structure as the backup files. If necessary, you can drop and recreate the target database using the createdb command.

It's also recommended to take a backup of your local database before performing the restore, in case you need to revert any changes.

Sources:

psenger/README.md

Select an option