Backup, Transactions and Concurrency

Logical Backup With pg_dump

pg_dump -U app_user -d appdb > /backups/appdb.sql

Restoring

createdb -U app_user restored_db
psql -U app_user -d restored_db < /backups/appdb.sql

Physical Backup With pg_basebackup

sudo -u postgres pg_basebackup -D /backups/base -Fp -Xs -P

pg_basebackup copies the whole data directory and is the foundation of streaming replication.

Transactions

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- or ROLLBACK if anything looks wrong

MVCC Concurrency

PostgreSQL uses Multi-Version Concurrency Control. Readers see a consistent snapshot and never block writers, so many concurrent transactions proceed without locking each other out.

Key Points

• pg_dump creates consistent logical backups.
• pg_basebackup copies the live data directory.
• BEGIN / COMMIT / ROLLBACK manage transactions.
• MVCC lets readers and writers run concurrently.