Importing, Exporting, Backup, and Recovery

# CHAPTER 24

Importing, Exporting, Backup, and Recovery #

1. Introduction #

2. Learning Objectives #

• Use the COPY command to import and export CSV files.

• Understand the difference between logical and physical backups.

• Use pgdump to create a complete database backup.

• Use pgrestore to rebuild a database from a backup file.

• Understand the 3-2-1 Backup Strategy.

3. Importing and Exporting CSV Data #

Exporting a Table to CSV: Open psql (or the pgAdmin Query Tool) and run:

COPY users TO '/var/lib/postgresql/users_backup.csv' DELIMITER ',' CSV HEADER;

*(This takes the entire users table and writes it to a file. The HEADER keyword ensures the column names are printed on the first line).*

Importing a CSV into a Table: *Note: The table must already be created with the correct columns!*

COPY users FROM '/var/lib/postgresql/new_users.csv' DELIMITER ',' CSV HEADER;

4. Logical Backups: The pgdump Utility #

Creating a plain-text SQL backup:

pg_dump -U postgres -d university_db > backup_file.sql

*(This generates a massive text file containing every CREATE TABLE and INSERT statement needed to magically recreate your database from scratch).*

5. Creating a Custom Format Backup (Recommended) #

# -Fc means Format Custom. -f specifies the output file.
pg_dump -U postgres -Fc -d university_db -f university_backup.dump

6. Disaster Recovery: The pgrestore Utility #

First, you must create a blank database container:

createdb -U postgres university_db_new

Next, use the pg_restore terminal utility to unpack the backup into the empty container:

pg_restore -U postgres -d university_db_new -1 university_backup.dump

*(The -1 flag is brilliant: It wraps the entire restoration in a single Transaction. If the restore fails halfway through, it rolls back, preventing a corrupted partial restore!)*

7. Physical Backups and WAL (Advanced) #

#!/bin/bash
# A simple automated backup script
DATE=$(date +"%Y-%m-%d")
FILE_NAME="/backups/db_backup_$DATE.dump"

pg_dump -U postgres -Fc -d production_db -f $FILE_NAME

# Keep only the last 7 days of backups to save disk space
find /backups/ -type f -name "*.dump" -mtime +7 -delete

*(You would then configure the Linux Cron daemon to execute this script every night at 3:00 AM).*

9. Common Mistakes #

• Never Testing the Recovery: Generating backup files every night is useless if the files are corrupted. A backup does not officially exist until you have successfully executed pgrestore on a test server and verified the data is intact.

• Storing Backups on the Same Server: If you save your pgdump file to the same hard drive that the database lives on, and that hard drive catches fire, you lose both the database and the backup. Always transfer backups off-site immediately.

10. Best Practices #

• The 3-2-1 Backup Strategy:

• Keep 3 copies of your data (1 Primary, 2 Backups).

• Store them on 2 different types of media (e.g., Local Drive + Cloud Storage).

• Keep 1 copy completely off-site (e.g., in a different physical city or AWS region).

11. Exercises #

1. 1. What terminal utility is used to generate a complete logical backup of a PostgreSQL database?

1. 2. Why is the -1 flag highly recommended when executing pg_restore?

12. SQL Challenges #

COPY products TO 'C:\backups\products.csv' DELIMITER ',' CSV HEADER;

13. MCQ Quiz with Answers #

• Q: Describe the architectural differences between pgdump and the COPY command. When would you use one over the other?

• Q: Explain the concept of Write-Ahead Logging (WAL) in PostgreSQL. How does it facilitate Point-In-Time Recovery in enterprise disaster scenarios?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #