InnoMarts logo
InnoMarts
Fast websites
← Back to course

Backups, Restore, and Basic Maintenance: Protecting Your PostgreSQL Data

Backups, Restore, and Basic Maintenance: Protecting Your PostgreSQL Data

Welcome back.

In the previous lesson, you learned how PostgreSQL connects to real applications.

You learned about:

Very good.

Now your database can work with real applications.

That is powerful.

But power creates responsibility.

And sometimes panic.

Because once your application stores real data, one question becomes very important:

What happens if something goes wrong?

A table is deleted.

A server crashes.

A bad migration runs.

A developer types DELETE without WHERE.

A laptop dies.

A disk fails.

Someone says:

I only changed one small thing.

Very dangerous sentence.

Today we will learn about backups, restore, and basic maintenance.

This is not the most glamorous part of databases.

Nobody opens a database course and says:

I cannot wait to learn backup strategy.

But backups are boring only until the day you need them.

Then they become beautiful.

Like a parachute.

Not exciting during normal walking.

Very important during falling.

What You Will Learn

In this lesson, you will learn:

By the end of this lesson, you will understand how to protect your PostgreSQL data.

Because creating data is good.

Using data is useful.

Losing data is character-building.

But let us avoid too much character-building.

Why Backups Matter

A backup is a copy of your database that you can use if something goes wrong.

Simple idea.

Very important.

Without backups, data loss can be permanent.

Examples of problems:

Someone deletes rows by mistake.
A migration breaks tables.
A server disk fails.
A database becomes corrupted.
A wrong command drops a table.
A deploy goes badly.
A production database is overwritten.

Some of these sound dramatic.

Some sound stupid.

In real life, both happen.

Computers fail.

Humans fail.

Developers fail creatively.

A backup gives you a way back.

It is not magic.

But it is hope in file form.

Backup Is Not Real Until Restore Works

Very important rule:

A backup you never tested is only a theory.

You may have a backup file.

But can you restore it?

Does it contain the data you expect?

Is it complete?

Is it too old?

Does it require a password you forgot?

Does it restore to a working database?

If you do not know, you do not really have a backup strategy.

You have a lucky charm.

And PostgreSQL is not impressed by lucky charms.

So always remember:

Backup is step one.
Restore test is step two.

Both matter.

Prepare a Demo Database

Open PostgreSQL:

sudo -iu postgres psql

Create a demo database:

CREATE DATABASE backup_demo_db;

Connect to it:

\c backup_demo_db

Create a table:

CREATE TABLE notes (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Insert some data:

INSERT INTO notes (title, content)
VALUES
  ('First Note', 'This is the first note.'),
  ('Backup Note', 'This row should survive backup and restore.'),
  ('PostgreSQL Note', 'Databases remember things. Sometimes too well.');

Check the data:

SELECT * FROM notes;

Now we have a small database to back up.

Not very important data.

But good for practice.

Practice with fake data.

Not with production.

Production is not a playground.

Production is where small mistakes wear expensive shoes.

What Is pg_dump?

pg_dump is a PostgreSQL tool that creates a backup of a database.

It can export:

table structure
data
indexes
constraints
sequences
database objects

It does not back up the entire PostgreSQL server by default.

It backs up one database.

Basic idea:

pg_dump reads a database and writes a backup file.

You run pg_dump from the terminal.

Not inside psql.

That is important.

Inside psql, you write SQL commands.

In the terminal, you run tools like:

pg_dump
psql
createdb
dropdb
pg_restore

Different places.

Different tools.

Same database adventure.

Create a Plain SQL Backup

Exit psql first:

\q

Now run this from your terminal:

pg_dump -U postgres -d backup_demo_db > backup_demo_db.sql

This creates a file:

backup_demo_db.sql

This is a plain SQL backup.

It contains SQL commands that can recreate the database structure and data.

You can inspect it:

less backup_demo_db.sql

You may see commands like:

CREATE TABLE
COPY
ALTER TABLE
CREATE SEQUENCE

A plain SQL backup is human-readable.

That is nice.

It can be restored with psql.

Simple.

Useful.

Very beginner-friendly.

Backup with Host and Port

Sometimes you need to specify host and port:

pg_dump -h localhost -p 5432 -U postgres -d backup_demo_db > backup_demo_db.sql

This means:

-h localhost     connect to localhost
-p 5432          use port 5432
-U postgres      connect as user postgres
-d backup_demo_db back up this database

If you use an application user, replace postgres with that user.

Example:

pg_dump -h localhost -p 5432 -U app_user -d app_db > app_db.sql

The user must have permission to read the database.

PostgreSQL cannot back up what the user cannot see.

Very fair.

Very strict.

Very PostgreSQL.

Restore a Plain SQL Backup

Now let us restore the backup into a new database.

Create a new database:

createdb -U postgres backup_demo_restore_db

Now restore:

psql -U postgres -d backup_demo_restore_db < backup_demo_db.sql

This reads the SQL file and runs it inside the new database.

Now connect:

psql -U postgres -d backup_demo_restore_db

Check the data:

SELECT * FROM notes;

You should see the rows.

Good.

You created a backup.

You restored it.

You tested it.

This is the correct path.

Backup without restore test is confidence theater.

Restore test is reality.

Reality matters.

Annoying but true.

Drop and Restore Example

Let us simulate a disaster.

Connect to the restored database:

psql -U postgres -d backup_demo_restore_db

Drop the table:

DROP TABLE notes;

Now check:

SELECT * FROM notes;

PostgreSQL will complain:

relation "notes" does not exist

Very sad.

Very educational.

Exit:

\q

Restore again:

psql -U postgres -d backup_demo_restore_db < backup_demo_db.sql

If the table already exists, you may get errors unless the backup includes drop commands.

For cleaner restore practice, you can recreate the database:

dropdb -U postgres backup_demo_restore_db
createdb -U postgres backup_demo_restore_db
psql -U postgres -d backup_demo_restore_db < backup_demo_db.sql

Then check again:

psql -U postgres -d backup_demo_restore_db

SELECT * FROM notes;

The data is back.

This is the magic feeling of backups.

Not actual magic.

Better.

Tested procedure.

Create a Backup with --clean

You can create a backup that includes commands to drop existing objects before recreating them.

Example:

pg_dump -U postgres --clean --if-exists -d backup_demo_db > backup_demo_clean.sql

Options:

--clean       include DROP commands
--if-exists   avoid errors if objects do not exist

This can make restoring over an existing database easier.

But be careful.

A backup with DROP commands can remove existing objects during restore.

That may be what you want.

Or it may be how your day becomes very exciting.

Always know what your backup file does.

SQL files are not decoration.

They execute.

PostgreSQL obeys.

No emotional support included.

Custom Format Backups

PostgreSQL also supports custom-format backups.

Create one with:

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

Here:

-F c              custom format
-f file_name      output file

This creates:

backup_demo_db.dump

This file is not plain readable SQL.

But it is flexible.

You restore it with pg_restore.

Custom format is useful because you can:

restore selected tables
restore with more options
use parallel restore in larger cases
list backup contents

For many real projects, custom format is a good choice.

Less readable.

More powerful.

Like a database toolbox in compressed form.

Restore a Custom Format Backup

Create a new database:

createdb -U postgres backup_demo_custom_restore_db

Restore with pg_restore:

pg_restore -U postgres -d backup_demo_custom_restore_db backup_demo_db.dump

Now connect:

psql -U postgres -d backup_demo_custom_restore_db

Check the data:

SELECT * FROM notes;

You should see your data.

Good.

Now you know both styles:

plain SQL backup -> restore with psql
custom backup    -> restore with pg_restore

Simple rule.

Very useful.

Plain SQL vs Custom Format

Plain SQL backup:

pg_dump -U postgres -d backup_demo_db > backup_demo_db.sql

Restore:

psql -U postgres -d new_db < backup_demo_db.sql

Good for:

simple backups
learning
human-readable files
small databases
easy inspection

Custom format backup:

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

Restore:

pg_restore -U postgres -d new_db backup_demo_db.dump

Good for:

larger databases
more flexible restore
selected object restore
production-style workflows

Both are useful.

Do not turn this into a religious war.

Databases already have enough drama.

List Contents of a Custom Backup

You can inspect a custom backup:

pg_restore -l backup_demo_db.dump

This lists what is inside the backup.

You may see tables, sequences, constraints, and data sections.

This is useful if you want to understand what the backup contains.

Because “I hope it has the table” is not a strategy.

Check.

Verify.

Then sleep better.

Maybe.

Backup File Names

Use clear file names.

Bad:

backup.sql
new_backup.sql
backup_final.sql
backup_final_REAL.sql
backup_please_work.sql

Good:

backup_demo_db_2026-05-03.sql
shop_db_2026-05-03_22-30.dump
portfolio_db_before_migration_2026-05-03.dump

Include:

database name
date
time if useful
reason if useful

Example:

pg_dump -U postgres -F c -d shop_db -f shop_db_2026-05-03.dump

Good names save brains.

Bad names create archaeology.

And nobody wants to excavate backup files at 2 AM.

Compress a Plain SQL Backup

Plain SQL backups can be large.

You can compress them:

pg_dump -U postgres -d backup_demo_db | gzip > backup_demo_db.sql.gz

Restore compressed backup:

gunzip -c backup_demo_db.sql.gz | psql -U postgres -d backup_demo_restore_db

This is useful for saving disk space.

But remember:

Compressed files should also be tested.

A compressed broken backup is still broken.

Just smaller.

Very efficient sadness.

Backup Before Dangerous Changes

Before doing risky operations, create a backup.

Examples of risky operations:

large migration
dropping columns
changing data types
mass update
mass delete
schema redesign
production deploy
importing external data

Example backup:

pg_dump -U postgres -F c -d shop_db -f shop_db_before_migration_2026-05-03.dump

Then do the dangerous change.

If something goes wrong, you have a restore point.

This is professional.

This is calm.

This is how you avoid making database history in a bad way.

The Dangerous DELETE Example

This is dangerous:

DELETE FROM customers;

It deletes all rows from customers.

Maybe you meant:

DELETE FROM customers
WHERE id = 5;

Small difference.

Huge result.

Before running dangerous commands, use a transaction when possible:

BEGIN;

Run your command:

DELETE FROM customers
WHERE id = 5;

Check:

SELECT * FROM customers;

If correct:

COMMIT;

If wrong:

ROLLBACK;

Transactions can save you from some mistakes.

Backups can save you from bigger mistakes.

Both are useful.

Like seatbelts and airbags.

Do not choose only one because you feel brave.

Basic Backup Strategy

A simple backup strategy should answer:

How often do we back up?
Where do we store backups?
How long do we keep backups?
Who can access backups?
How do we restore?
Have we tested restore?

For a small project, you might do:

daily backup
keep last 7 daily backups
keep weekly backup for 1 month
store backups outside the server
test restore regularly

For more serious projects, you need a stronger plan.

But even a simple tested plan is much better than:

I think the server provider has something.

Maybe they do.

Maybe they do not.

Maybe it costs extra.

Maybe it was never enabled.

Maybe it restores the whole server but not the one database you need.

Check.

Do not assume.

Assumption is the cousin of disaster.

Store Backups Somewhere Safe

If your database and backup are on the same server, and the server dies, both may be gone.

That is bad.

Better backup locations:

another server
external storage
secure cloud storage
encrypted backup storage
offline copy for important data

At minimum, avoid keeping the only backup next to the original database.

That is like keeping your spare house key inside the burning house.

Technically a spare.

Practically not helpful.

Protect Backup Files

Backups contain data.

Sometimes sensitive data.

That means backup files must be protected.

Rules:

do not expose backups publicly
limit access
use strong permissions
consider encryption
delete old backups safely
do not email database dumps casually

A database backup can be as sensitive as the live database.

Sometimes more sensitive.

Because people forget it exists.

Attackers do not.

Very rude of them.

But true.

Basic Maintenance: VACUUM

PostgreSQL uses a system where updated and deleted rows leave old row versions behind.

This is part of how PostgreSQL handles concurrency.

Very clever.

But it means PostgreSQL needs cleanup.

That cleanup is called:

VACUUM

Run:

VACUUM;

This cleans up dead rows in the current database.

Most PostgreSQL installations have autovacuum enabled.

Autovacuum runs automatically.

Good.

Because manually remembering everything is how humans lose.

Still, it is useful to know what VACUUM does.

It helps PostgreSQL keep tables healthy.

Like cleaning a workshop.

Not glamorous.

Very necessary.

VACUUM ANALYZE

You can run:

VACUUM ANALYZE;

This does two things:

VACUUM cleans dead row versions.
ANALYZE updates planner statistics.

Planner statistics help PostgreSQL choose good query plans.

If statistics are outdated, PostgreSQL may make bad choices.

Like using the wrong index.

Or scanning too much.

Or generally acting confused.

ANALYZE helps PostgreSQL understand the data better.

A database with fresh statistics is a happier database.

Probably.

PostgreSQL does not smile.

But performance can improve.

ANALYZE Alone

You can run:

ANALYZE;

This updates statistics without vacuuming.

You can also analyze one table:

ANALYZE products;

This is useful after large data changes.

Example:

imported many rows
deleted many rows
updated many rows
changed data distribution

PostgreSQL uses statistics to decide how to execute queries.

Bad statistics can produce bad plans.

Bad plans produce slow queries.

Slow queries produce sad developers.

The chain is clear.

REINDEX

Indexes can sometimes become bloated or need rebuilding.

PostgreSQL has:

REINDEX

Example:

REINDEX DATABASE backup_demo_db;

Or one index:

REINDEX INDEX index_name;

Or one table:

REINDEX TABLE table_name;

Do not run REINDEX randomly every five minutes.

This is not brushing your teeth.

Use it when needed.

For beginners, just know:

REINDEX rebuilds indexes.

In real maintenance, you should understand why you are doing it.

Blind maintenance is still blind.

Even if it wears a serious hat.

Check Database Size

You can check database size:

SELECT pg_size_pretty(pg_database_size('backup_demo_db'));

Check table sizes:

SELECT
  relname AS table_name,
  pg_size_pretty(pg_total_relation_size(relid)) AS total_size
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

This helps you understand what is taking space.

Sometimes the biggest table is expected.

Sometimes it is a log table that nobody cleaned for three years.

Databases collect history.

Sometimes too much history.

Like a drawer full of old cables.

Check Active Connections

You can see active connections:

SELECT
  pid,
  usename,
  datname,
  state,
  query
FROM pg_stat_activity;

This shows who is connected and what they are doing.

Useful for debugging.

Maybe an application has too many connections.

Maybe a query is stuck.

Maybe someone opened psql and went for lunch.

PostgreSQL knows.

PostgreSQL remembers.

PostgreSQL judges silently.

Find Slow Queries

PostgreSQL can log slow queries if configured.

A common setting is:

log_min_duration_statement

Example idea:

log queries slower than 1000 ms

This is usually configured in PostgreSQL configuration files.

For beginners, the important idea is:

Slow queries should be observed, not guessed.

Use tools like:

EXPLAIN ANALYZE
logs
monitoring
pg_stat_statements

pg_stat_statements is an extension that can help track query performance.

That is more advanced.

But good to know.

Performance work without observation is database astrology.

And we already do not trust database astrology.

Backup and Maintenance Checklist

A simple checklist:

Create regular backups.
Store backups outside the database server.
Protect backup files.
Test restore.
Back up before migrations.
Use app-specific users.
Keep PostgreSQL updated.
Monitor disk space.
Watch slow queries.
Let autovacuum run.
Run ANALYZE after big imports if needed.
Document restore steps.

Documentation matters.

A restore procedure should not live only in your head.

Especially if your head is tired.

Or on vacation.

Or drinking coffee while production burns.

Write the steps down.

Future you deserves mercy.

Common Backup Mistakes

Never Testing Restore

This is the biggest mistake.

A backup file is not enough.

You must know it restores.

Test restore on another database.

Example:

createdb -U postgres restore_test_db
pg_restore -U postgres -d restore_test_db shop_db_2026-05-03.dump

Then check the data.

Trust, but verify.

Actually, with backups:

Do not trust.
Verify.

Keeping Backups Only on the Same Server

If the server dies, you may lose database and backup together.

Bad.

Store copies elsewhere.

A backup should survive the failure of the original machine.

That is the point.

Backing Up Too Rarely

If you back up once per month, you may lose a month of data.

Maybe acceptable for a toy project.

Not acceptable for a real shop.

Choose backup frequency based on how much data you can afford to lose.

This question has a serious name:

Recovery Point Objective

But simple version:

How much data loss is acceptable?

If the answer is “almost none”, you need a serious strategy.

Not Knowing How Long Restore Takes

A backup may exist.

But restore may take time.

For small databases, maybe seconds.

For huge databases, maybe hours.

If your application is down, restore time matters.

This question has another serious name:

Recovery Time Objective

Simple version:

How long can the system be down?

Business people suddenly become very interested in databases here.

Funny how that works.

Backing Up the Wrong Database

Classic mistake.

You think you backed up production.

Actually, you backed up local development.

The backup contains three test users and a product called “banana”.

Check:

SELECT current_database();

Check backup file names.

Check restore result.

Do not assume.

Practice

Create a backup of backup_demo_db:

pg_dump -U postgres -d backup_demo_db > backup_demo_db.sql

Create a restore database:

createdb -U postgres backup_demo_restore_db

Restore:

psql -U postgres -d backup_demo_restore_db < backup_demo_db.sql

Check:

psql -U postgres -d backup_demo_restore_db

SELECT * FROM notes;

Create a custom backup:

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

Create another restore database:

createdb -U postgres backup_demo_custom_restore_db

Restore:

pg_restore -U postgres -d backup_demo_custom_restore_db backup_demo_db.dump

Check:

psql -U postgres -d backup_demo_custom_restore_db

SELECT * FROM notes;

Run maintenance:

VACUUM ANALYZE;

Check database size:

SELECT pg_size_pretty(pg_database_size(current_database()));

This is practical.

This is not glamorous.

This is useful.

Like a good screwdriver.

Again.

Databases love screwdrivers.

Metaphorically.

Mini Challenge

Create a backup plan for a small blog database.

Assume:

database name: blog_app_db
data changes every day
the blog is small
losing more than one day of data is bad
you deploy changes weekly

Write a simple backup plan:

Backup frequency:
Backup format:
Backup location:
Retention:
Restore test frequency:
Backup before migrations:
Who can access backups:

Example answer:

Backup frequency: daily
Backup format: custom format with pg_dump -F c
Backup location: external secure storage
Retention: keep daily backups for 7 days and weekly backups for 1 month
Restore test frequency: once per month
Backup before migrations: always
Who can access backups: only the administrator

Now create a backup command:

pg_dump -U postgres -F c -d blog_app_db -f blog_app_db_2026-05-03.dump

Create a restore test command:

createdb -U postgres blog_app_restore_test_db
pg_restore -U postgres -d blog_app_restore_test_db blog_app_db_2026-05-03.dump

Then write the check query:

SELECT COUNT(*) FROM posts;

This is how you start thinking like someone responsible for data.

A little scary.

Very useful.

Very adult.

Summary

Today you learned:

This lesson is extremely important.

Maybe not flashy.

But important.

A developer who can create tables is useful.

A developer who can query data is better.

A developer who can protect and restore data is much more trustworthy.

Because real projects are not only about building.

They are also about surviving mistakes.

And backups are one of the best ways to survive.

Course Summary

You have reached the end of this PostgreSQL course.

You learned:

That is a lot.

You did not just learn commands.

You learned how to think about data.

That matters.

Because applications come and go.

Frameworks change.

Frontend trends change every fifteen minutes.

But data stays important.

PostgreSQL is a serious tool.

Now you know enough to start using it seriously.

Carefully.

Practically.

And with backups.

Always with backups.

Final Words

PostgreSQL will not always be easy.

Sometimes it will complain.

Sometimes it will reject your query.

Sometimes it will say:

syntax error at or near...

and you will stare at one comma for ten minutes.

This is normal.

You are learning a real tool.

A powerful tool.

A tool used in real applications, real companies, real APIs, real dashboards, and real systems where data matters.

Keep practicing.

Create small projects.

Break things.

Restore them.

Write queries.

Use EXPLAIN ANALYZE.

Make backups.

Test backups.

Respect data.

And remember:

Browsers forget.
Databases remember.
Backups forgive.

Very PostgreSQL.

Very practical.

Very good.