Proposal: per-table row counts in pg_dump/pg_restore, with restore-time verification

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.

[1] https://stream.boxwatch.ru/message-id/CAMdzBEkhEzr9-1x8pX_YR%3DHnHdtOVoWHbb5_xC1rVkgS0wg2LA%40mail.gmail.com

Regards,
Sönmez

Browse pgsql-hackers by date

  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