Finding and exporting your PostgreSQL data

What it holds

PostgreSQL stores relational business records such as accounts, transactions and operational events. Keys and timestamps link activity across tables; retained history depends on the application and its deletion policies.

Where PostgreSQL shows the size

1. Connect using psql or another SQL client. For database totals, run SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database WHERE datistemplate = false ORDER BY pg_database_size(datname) DESC;
2. For table detail, run SELECT relname, pg_size_pretty(pg_total_relation_size(relid)) AS total, pg_size_pretty(pg_table_size(relid)) AS without_indexes FROM pg_statio_user_tables ORDER BY pg_total_relation_size(relid) DESC LIMIT 20;
3. Estimate rows with SELECT relname, n_live_tup FROM pg_stat_user_tables; use SELECT count(*) FROM orders; when an exact count is necessary.

Plan and admin role. Database sizing requires CONNECT on the database or pg_read_all_stats membership. Dumping also requires permission to read the selected objects; managed-host dashboards require separate cloud access.

Exporting the full history

1. Prefer a read replica or quiet period to limit primary read load. Run pg_dump -Fd -j 4 -f /backup/mydb_dir -d mydb for a parallel directory dump.
2. For a single compressed archive, run pg_dump -Fc -f mydb.dump -d mydb; restore custom or directory output with pg_restore.
3. If cluster objects are required, separately run pg_dumpall --globals-only -f globals.sql. Use pg_dumpall -f cluster.sql for a plain SQL cluster dump.
4. Cloud SQL can offload an export with gcloud sql export sql INSTANCE_NAME gs://BUCKET_NAME/mydb.gz --database=mydb --offload.

Database disk usage includes indexes, TOAST and unreclaimed dead rows; dumps contain live rows and index definitions, so their sizes differ. pg_size_pretty uses binary units.

pg_dump provides a consistent snapshot without blocking ordinary reads or writes, but adds read load. Only directory format supports parallel dumping; standby parallel dumps need PostgreSQL 10 or later, and standby dumps omit unlogged-table data.

Cloud SQL offloaded exports cost extra; the service account needs storage.objectAdmin on the destination bucket. Allow sufficient instance free space; at least 25% of database size is recommended.

You don't need to run an export to talk to us, and there's no need to clean it first — removing personal data is part of our work.