Hi,
we are using 9.3 with data checksums enabled. Now I am looking for a way
to check if all database blocks are still intact. First I tried
pg_filedump. In many cases it simply ignored tampered data blocks. It is
probably not made for this task.
Then I remembered about the pageinspect extension. The following select
is a bit too verbose but it seems to do the job for everything except
fsm files.
SELECT c.oid::regclass::text as rel,
f.fork,
ser.i as blocknr,
pg.*
FROM pg_class c
CROSS JOIN (values ('main'::text), ('vm'::text)) f(fork)
CROSS JOIN pg_relation_size(c.oid::regclass, f.fork) sz(sz)
CROSS JOIN generate_series(0,(sz.sz/8192)::int-1) ser(i)
CROSS JOIN page_header(get_raw_page(c.oid::regclass::text,
f.fork,
ser.i)) pg
WHERE sz.sz>0
Am I right?
The problem with the select above is that either page_header() or
get_raw_page() seems to allocate the memory for the page without freeing
it again. The process size grew to ~12.5 GB and the query returned
~1,500,000 rows. And 1.5E6 * 8 kB gives roughly 12 GB. Shared buffers is
~120 MB for this database.
I ran this query in a separate transaction. The memory was freed only
when the backend process exited.
Is there a way to work around this memory leak?
Is there a better way to do what I want? I also thought about pg_dump.
But that does not read indexes, AFAIK. Best if the solution would avoid
expensive locks. Then I could also use it in production. But currently I
need it only to verify a backup.
Thanks,
Torsten
