Thread: 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
Attachment
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