| From: | Sönmez Başar <6prensgold1(at)gmail(dot)com> |
|---|---|
| To: | "pgsql-hackers(at)lists(dot)postgresql(dot)org" <pgsql-hackers(at)lists(dot)postgresql(dot)org> |
| Subject: | Proposal: per-table row counts in pg_dump/pg_restore, with restore-time verification |
| Date: | 2026-11-19 22:18:22 |
| Message-ID: | MM0P280MB2039ABA7C8BED9ECC42D7FEDFA792@MM0P280MB2039.SWEP280.PROD.OUTLOOK.COM |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-hackers |
Hi hackers,
We are testing major-version upgrades (15 to 16) with pg_dumpall
dump/restore. Coming from Oracle Data Pump, the thing our DBA team
misses most is the per-table row count in the log. expdp/impdp write
one line per table, e.g.
. . exported "APP"."ORDERS" 1.234 MB 10000 rows
. . imported "APP"."ORDERS" 1.234 MB 10000 rows
Operators use these lines as cheap, built-in evidence that every table
was dumped and restored completely, without scanning the source or the
target again.
Today the verbose output carries no counts:
pg_dump: dumping contents of table "public.orders"
pg_restore: processing data for table "public.orders"
When a plain dump is restored with psql, the "COPY 10000" tag has the
count but not the table name, so we ended up pairing "psql -e" echo
lines with COPY tags, and counting the lines of each COPY block in the
dump file with awk. The same request was posted once before [1] and got
no follow-up.
Proposal, in three independent steps:
1. pg_dump/pg_dumpall --verbose: after each table's data, report the
rows and bytes written, e.g.
pg_dump: dumped 10000 rows (1.2 MB) of table "public.orders"
dumpTableData_copy() already receives one row per PQgetCopyData()
call, and the final PGresult of COPY ... TO STDOUT carries the row
count in its command tag. dumpTableData_insert() knows how many
rows it fetched.
2. pg_restore --verbose, when restoring into a database: report the
rows loaded per table, e.g.
pg_restore: restored 10000 rows of table "public.orders"
EndDBCopyMode() already holds the PGresult of the COPY, so
PQcmdTuples() gives the count at no extra cost.
3. Verification: keep the dumped row count in the archive (e.g. in the
TABLE DATA TOC entry), show it in "pg_restore -l", and let
pg_restore warn when the number of rows restored differs from the
number dumped. For plain format, a comment after the COPY
terminator, e.g. "-- 10000 rows", would let tools check a dump file
without a database. This step changes the archive format and needs
design work (the TOC is not always written after the data, e.g.
custom format to a pipe), so it could come later.
Steps 1 and 2 are logging only and could go in first. Would patches
along these lines be acceptable? I am happy to test and review them.
Regards,
Sönmez
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Geoghegan | 2026-11-19 22:54:10 | Re: auto_explain crashes on EXPLAIN (BUFFERS) of an index scan (19, master) |
| Previous Message | Zsolt Parragi | 2026-11-19 22:04:13 | Re: Block various aggregate component functions from being called directly |