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://www.postgresql.org/message-id/CAMdzBEkhEzr9-1x8pX_YR%3DHnHdtOVoWHbb5_xC1rVkgS0wg2LA%40mail.gmail.com

Regards,
Sönmez


Reply via email to