Chapter_17_Practical SQL: Maintaining Your Database.
PostgreSQL Backup & Restore Tutorial — PowerShell Compatible (gis_analysis)
In this video we walk through a complete PostgreSQL backup and restore workflow, fully rewritten to work in PowerShell on Windows. Whether you are protecting a production database or just learning how pg_dump and pg_restore work, this tutorial covers every step from creating a compressed backup all the way through restoring individual tables — with clean, working PowerShell syntax throughout.
---
WHAT YOU'LL LEARN
By the end of this video, you'll know how to:
Set up reusable PowerShell variables for your PostgreSQL bin path and credentials
Avoid password prompts using the $env:PGPASSWORD environment variable
Create a full compressed database backup using pg_dump
Verify a backup file exists and check its size using Get-Item
Back up a single table instead of the whole database
Drop a database safely after confirming a backup exists
Restore a full database from a compressed backup using pg_restore
Restore only a specific table into an existing database
Generate a plain-text human-readable SQL backup for version control
Use optional flags like --clean and -v for cleaner restores and verbose output
---
TOOLS & SETUP
PostgreSQL 18 installed on Windows
PowerShell (built into Windows — no extra install needed)
pg_dump and pg_restore located at: C:\Program Files\PostgreSQL8in
psql for running SQL commands like DROP DATABASE and size checks
NOTE: SQL commands such as DROP DATABASE and SELECT pg_size_pretty must be run inside psql, not in PowerShell. The video clearly marks which steps run where.
---
STEP-BY-STEP BREAKDOWN
Setup — Variables and Password
We start by defining four reusable variables at the top of the script: $pgBin for the PostgreSQL bin folder path, $pgUser for the username, $dbName for the target database, and $backupDir for where backup files are saved. We also set $env:PGPASSWORD to avoid being prompted for a password on every command.
Step 1 — Full Database Backup
Using the & call operator to run pg_dump from PowerShell, we create a full compressed backup of gis_analysis with the -Fc flag. This produces a .dump file that is optimised for use with pg_restore.
Step 2 — Verify the Backup
Get-Item is used to confirm the backup file was created and to display its full path and file size — a quick sanity check before doing anything destructive.
Step 3 — Check Database Size
Run SELECT pg_size_pretty inside psql to check the size of a table. This is a SQL command, not a PowerShell one, so we note it must be run inside the psql terminal.
Step 4 — Backup a Single Table
Using the -t flag with pg_dump, we back up only the farmers_markets table into its own separate .dump file, leaving the rest of the database untouched.
Step 5 — Drop the Database
A reminder that DROP DATABASE is a destructive SQL command run inside psql — only after confirming a valid backup exists. Always double-check before running this.
Step 6 — Restore the Full Database
Using pg_restore with the -C flag, PostgreSQL automatically recreates the gis_analysis database before restoring all its contents from the backup file.
Step 7 — Restore a Single Table
Using the -t flag with pg_restore, we restore only the farmers_markets table into an existing database.
Step 8 — Human-Readable SQL Backup
Dropping the -Fc flag produces a plain .sql text file instead of a compressed binary, ideal for reviewing or storing in version control.
Optional — Clean Backup and Verbose Restore
Adding --clean inserts DROP statements before each CREATE, making restores cleaner. Using -v with pg_restore prints detailed output so you can follow the restore and catch errors early.
---
KEY CONCEPTS
$env:PGPASSWORD — PowerShell way to pass a password to PostgreSQL tools without an interactive prompt.
& operator — Required in PowerShell to execute an external program stored in a variable path.
-Fc flag — Custom compressed format, smaller file size and faster to restore than plain SQL.
-C flag — Tells pg_restore to recreate the target database automatically before restoring.
-t flag — Scopes pg_dump or pg_restore to a single named table instead of the whole database.
---
RESOURCES
PostgreSQL Downloads: postgresql.org/download
pg_dump Documentation: postgresql.org/docs/current/app-pgdump.html
pg_restore Documentation: postgresql.org/docs/current/app-pgrestore.html
---
TIMESTAMPS
0:00 — Introduction and script overview
1:00 — Setting variables and PGPASSWORD
2:30 — Full database backup with pg_dump
4:00 — Verifying the backup with Get-Item
5:00 — Checking database size in psql
6:00 — Backing up a single table
7:30 — Dropping the database safely
9:00 — Restoring the full database
11:00 — Restoring a single table
13:00 — Plain-text SQL backup for version control
15:00 — Optional clean and verbose flags
---
If this helped you, like and subscribe for more PostgreSQL and PowerShell tutorials! Leave a comment if you hit errors — we are happy to help!
On this page of the site you can watch the video online Chapter_17_Practical SQL: Maintaining Your Database. with a duration of hours minute second in good quality, which was uploaded by the user Kabelo Xabendlini 22 May 2026, share the link with friends and acquaintances, this video has already been watched 10 times on youtube and it was liked by 0 viewers. Enjoy your viewing!
