Обсуждение: verifying database integrity - fsck for pg?

We are currently migrating a cluster between hosts. I'd like to
verify that the new database has been transferred reliably and
that the datafiles are in tact.

What's the recommended way to do this? We're using
pg_start/stop_backup so an md5 check is out of the question.

pg version 8.0

Thanks,

   - Rich Doughty

On 1/31/06, Rich Doughty <rich@opusvl.com> wrote:
> We are currently migrating a cluster between hosts. I'd like to
> verify that the new database has been transferred reliably and
> that the datafiles are in tact.
>
> What's the recommended way to do this? We're using
> pg_start/stop_backup so an md5 check is out of the question.
>
> pg version 8.0

I would doubt if it is a full test but in the past when I have had
corruptions in the database its been a full vacuum that has spotted
them.

--
Harry
http://www.hjackson.org
http://www.uklug.co.uk

Rich Doughty <rich@opusvl.com> writes:
> We are currently migrating a cluster between hosts. I'd like to
> verify that the new database has been transferred reliably and
> that the datafiles are in tact.

pg_dump both databases and diff the results, perhaps?

            regards, tom lane

Tom Lane wrote:
> Rich Doughty <rich@opusvl.com> writes:
>
>>We are currently migrating a cluster between hosts. I'd like to
>>verify that the new database has been transferred reliably and
>>that the datafiles are in tact.
>
>
> pg_dump both databases and diff the results, perhaps?

i had considered pg_dump. i was hoping there was a utility similar
to fsck that could check for corruption. i'd like to verify now that
the data is ok, rather than coming across errors in 6 months time.

i'm going to go a vacuum full, and a pg_dump. at least that should
mean all the data is accessible.

cheers


--

   - Rich Doughty

On 31.01.2006, at 16:00 Uhr, Rich Doughty wrote:

> i had considered pg_dump. i was hoping there was a utility similar
> to fsck that could check for corruption. i'd like to verify now that
> the data is ok, rather than coming across errors in 6 months time.

I have done this using JDBC (and the WebObjects frameworks) a few
months ago. It wasn't a big problem as I could crawl over the defined
tables (entities) in my database definition file (EOModel) and then
compare row by row. It was slow like hell as it had to go over every
row and over nearly every column, but it wasn't really complicate to
write.

Unfortunately I don't have the code for that anymore as I have
deleted the project by accident (I have made this with one of my test
projects).

But as far as I see, you can do the same with every tool that may
connect to two datasources, can iterate over a result set (rows and
columns) and can compare contents.

Perhaps you have to do some tricks to tell the script which tables or
columns should be equal and which are allowed to differ, but as far
as I can see, it shouldn't be that hard. At all, it took me about a
day to verify the db contents.

cug

--
PharmaLine Essen, GERMANY and
Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy
http://www.bignerdranch.com/classes/postgresql.shtml

Guido Neitzer wrote:
> Perhaps you have to do some tricks to tell the script which tables or
> columns should be equal and which are allowed to differ, but as far  as
> I can see, it shouldn't be that hard. At all, it took me about a  day to
> verify the db contents.

I'm not too fussed about a row-by-row comparison between the source and the
copy. It's rather a case of a tool to check the datafiles' integrity (such
as fsck, myisamchk, svnadmin verify etc).

If the fact that pg_dumpall returned successfully, then i would hope that
all the data is present and correct.

   - Rich Doughty

On 31.01.2006, at 17:38 Uhr, Rich Doughty wrote:

> I'm not too fussed about a row-by-row comparison between the source
> and the
> copy. It's rather a case of a tool to check the datafiles'
> integrity (such
> as fsck, myisamchk, svnadmin verify etc).
>
> If the fact that pg_dumpall returned successfully, then i would
> hope that
> all the data is present and correct.

As I moved my content from another DBMS to PostgreSQL that was no
option for us. We had to check table by table and row by row.

cug

--
PharmaLine Essen, GERMANY and
Big Nerd Ranch Europe - PostgreSQL Training, Feb. 2006, Rome, Italy
http://www.bignerdranch.com/classes/postgresql.shtml