How to Backup and Restore a PostgreSQL Database in Linux​

• Ashiwini
• 23 May, 2024
• 0 Comments
• 3 Mins Read

How to Backup and Restore a PostgreSQL Database in Linux​

 

PostgreSQL Backup and Recovery using pg_dump, pg_restore, and psql 

1. Introduction 

Backing up and restoring PostgreSQL databases is crucial for data safety and disaster recovery. PostgreSQL provides several tools for backup and restore operations, primarily pg_dump, pg_restore, and psql. 

2. Backup Formats 

2.1 Plain Text Format (-Fp) 

Command Example: 

pg_dump -U postgres -d db1 -F p > db1.sql 

Characteristics & Importance: 

– Plain SQL script. 

– Restored using: psql -U postgres -d db1 -f db1.sql 

– Human-readable; editable in a text editor. 

– Can modify table names, schema, or filter objects before restore. 

– Best for small databases. 

– Limitation: No built-in compression; restoring large databases is slow. 

 

Use Case: 

– Quick migration of small databases. 

– Making schema changes while restoring. 

2.2 Custom Format (-Fc) 

Command Example: 

pg_dump -U postgres -d db1 -F c -f db1.dump  

Characteristics & Importance: 

– Binary format; not human-readable. 

– Restored using pg_restore: pg_restore -U postgres -d db1 db1.dump 

– Supports parallel restore (-j option) — useful for large databases. 

– Selectively restore tables or schemas. 

– Supports built-in compression. 

 

Use Case: 

– Large databases with parallel restore. 

– Partial restoration requirements. 

– Production backups. 

2.3 Directory Format (-Fd) 

Command Example: 

pg_dump -U postgres -d db1 -F d -j 4 -f /backup/db1_dir 

 

Characteristics & Importance: 

– Dumps database into a directory; each table as a separate file. 

– Supports parallel dumps and restores. 

– Selective restoration by table or schema. 

 

Use Case: 

– Huge production databases (hundreds of GBs/TBs). 

– Backup automation with parallel processing. 

– Separate files for selective restoration. 

3. Backup Scenarios 

3.1 Backup Only Table Structure 

Command: 

pg_dump -U postgres -d db1 -t employee --schema-only > employee_structure.sql 

Result: 

– Only the CREATE TABLE statement (indexes, constraints included) for the employee table. 

3.2 Backup Entire Schema (Schema + Data) 

Command: 

pg_dump -U postgres -d db1 -n public > public_full.sql 

3.3 Restore Table Structure 

Command: 

psql -U postgres -d db1 -f employee_structure.sql 

4. Include/Exclude Objects 

4.1 Include Multiple Tables 

pg_dump -U postgres -d db1 -t employee -t department > selected_tables.sql 

4.2 Exclude Multiple Tables 

pg_dump -U postgres -d db1 \ 

  --exclude-table=employee \ 

  --exclude-table=department \ 

  > db1_without_emp_dept.sql 

4.3 Include/Exclude Schema 

Include Schema: 

pg_dump -U postgres -d db1 -n public > public_schema.sql 

Exclude Schema: 

pg_dump -U postgres -d db1 --exclude-schema=public > db1_without_public.sql 

5. Full Database Backup 

Command: 

pg_dump -U [username] [database_name] > [backup_file].sql 

Custom Format Backup: 

pg_dump -U [username] -F c [database_name] -f [backup_file].dump 

Other Formats: 

– Directory: -F d 

– Tar: -F t 

Backup All Databases: 

pg_dumpall > all_pg_dbs.sql 

6. Restore PostgreSQL Database 

6.1 Plain Text Restore 

psql -U postgres [database_name] < [backup_file].sql 

6.2 Custom / Directory / Tar Restore 

pg_restore -d [database_name] [backup_file].dump 

pg_restore -d [database_name] [backup_file].tar 

pg_restore -d [database_name] [backup_dir] 

7. Compressed Backup 

pg_dump db1 | gzip > db1.gz 

pg_dump -F d -j 5 -f dir1 

8. Backup Remote Database 

pg_dump -h [remote_host] -p [port] -U [username] [database_name] > [backup_file].sql 

pg_dump -h example.com -p 5432 -U postgres mydatabase > mydatabase_backup.sql 

9. Automating Backups with Cron 

Steps: 

1. mkdir -p /srv/backups/databases

2. crontab -e

3. Add a cron job to run daily at midnight:

   0 0 * * * pg_dump -U postgres db1 > /srv/backups/postgres/db1.sql

– Runs daily at midnight. 

– Cron handles automated execution without restart. 

10. Example Scenario: Backup and Restore 

Backup Employee Table Structure: 

pg_dump -U postgres -d db1 -t employee --schema-only > employee_structure.sql 

Backup Entire Schema: 

pg_dump -U postgres -d db1 -n public > public_full.sql 

Restore Employee Table Structure: 

psql -U postgres -d db1 -f employee_structure.sql 

Restore Full Schema (Plain Text): 

psql -U postgres -d db1 -f public_full.sql 

Restore Full Schema (Custom Format): 

pg_restore -U postgres -d db1 public_full.dump 

*Document Prepared by Learnomate*