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!
In questa pagina del sito puoi guardare il video online Chapter_17_Practical SQL: Maintaining Your Database. della durata di ore minuti seconda in buona qualità , che l'utente ha caricato Kabelo Xabendlini 22 maggio 2026, condividi il link con amici e conoscenti, su youtube questo video è già stato visto 10 volte e gli è piaciuto 0 spettatori. Buona visione!
