Integrity check of postgresql database

[jmoore]

1) ... I faced two issues: first, the fk constaints still issue error and second, I have no idea how to force a general test that the data fullfill all the contraint after having adding all the data. Any suggestion ?

You could try `SET CONSTRAINTS ALL DEFERRED` (see http://www.postgresql.org/docs/9.3/static/sql-set-constraints.html). If that doesn't work, you're likely into ordering your inserts into the required order.

2) ...I sense that I have the issue you mention even if I am not clear with the use of these tables in omero. Here are my output

Code: Select all

...
omero20141103=# select id from eventlog order by id desc limit 1;
    id   
----------
41570621
(1 row)
...


And I have little idea how to fix that. Should I edit seq_eventlog with to have last_value matching the max value (or row count) of eventlog table ?

Yes. `ALTER SEQUENCE seq_eventlog RESTART WITH 41570621;` This may be necessary for other sequences as well, and depending on what occurred during the split-window, it may have to be `max(backup, current)` that you set the sequence to.

This mean that my omero was hanged / stopped in the middle of something ? and I will loose the log of some events that should have occured to finish the action ?

Not really a concern. I would assume that in one of the two DBs you would find eventlogs higher than 41555274. You don't need to necessarily find 41555274 itself, though, since sequence increases are non-transactional, i.e. even if a transaction is rolled back, the sequence will stay incremented.

Is it a concern if after this editing, I extract the inserts to be put in a Fresh DB + inserts from backup + ...

Yes. I'd assume you should adjust the sequences after all other DB manipulations.

3)

I would be easier if you could send me the 2 separate schemas.

They are attached.

I've attached a runnable diff. There are in fact a number of discrepancies that I wouldn't have expected.

Cheers,
~Josh.

[jacques2020]

Hi,

1) I tried to add the inserts of the backup on top a freshly created database.

You could try `SET CONSTRAINTS ALL DEFERRED` (see http://www.postgresql.org/docs/9.3/stat ... aints.html). If that doesn't work, you're likely into ordering your inserts into the required order.

I tried to deferre the constraints but get many error. More precisely, made the inserts a single transaction preceeded by your instruction

Code: Select all

root@cedre-5a:/tmp# head omero_inserts_20141025_constraints_deferred.dump
BEGIN;
SET CONSTRAINTS ALL DEFERRED;
--
-- PostgreSQL database dump
--

SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;


and at the end

Code: Select all

root@cedre-5a:/tmp# tail omero_inserts_20141025_constraints_deferred.dump
-- Data for Name: wellsampleannotationlink; Type: TABLE DATA; Schema: public; Owner: omero
--



--
-- PostgreSQL database dump complete
--

COMMIT;


Then running

Code: Select all

psql -h localhost -U omero omero_from_scratch < /tmp/omero_inserts_20141025_constraints_deferred.dump 1>/tmp/adding_inserts_20141025_constraints_deferred.log 2>>/tmp/adding_inserts_20141025_constraints_deferred.err

trigerred ten of thousands of errors like

Code: Select all

head adding_inserts_20141025_constraints_deferred.err
ERROR:  duplicate key value violates unique constraint "_lock_ids_pkey"
DETAIL:  Key (id)=(1) already exists.
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ERROR:  current transaction is aborted, commands ignored until end of transaction block

So I stopped the process. I am not at all an expert so I might did it wrong.

2) My second attemps was for the alternative, add the missing constraint on the actual database. Indeed, it seems that all that missing fk saved my day allowing the merging that would have normally not be possible with a fully featured schema.

Code: Select all

su postgres -c "psql omero_20141106_1454 < diff.sql" 1>/root/apply_diff_table_shcema.log 2>/root/apply_diff_table_shcema.err

I got the following output on stderr (below the message). Error with datasetimagelink can be easily fixed by editing the parent or deleting the link that has no parent nor child existing. Same for imageannotationlink and pixels. But I don't know what to with

Code: Select all

ERROR:  insert or update on table "event" violates foreign key constraint "fkevent_session_session"
DETAIL:  Key (session)=(230321) is not present in table "session".


Because the role of these table are unclear to me.

Right now, I guess I do prefer the solution 2 and propose to abandon the solution 1. Luckily, the missing constraints allowed me to do the merge of the data. And adding the missing constraints in solution 2 ensures integrity likely enough.

3)

Yes. `ALTER SEQUENCE seq_eventlog RESTART WITH 41570621;` This may be necessary for other sequences as well, and depending on what occurred during the split-window, it may have to be `max(backup, current)` that you set the sequence to.

I guess I will likely remain on the version restored from the evening before the bug. We still have the file imported in the interval and will simply re-import them. No other editing was performed.

But if you find it interesting to know the outcome, I can briefly test what you propose and see if it allows the server to start. But this is a production server and I cannot stop it easily. I will test that at the same time as I will add missing constraints for which I imagine I should stop the omero server.

Many thanks.

Cheers

Jacques



---------------------------------apply_diff_table_shcema.err------------------------------

Code: Select all

NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "annotationannotationlink_parent_child_owner_id_key" for table "annotationannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "channel_pixels_pixels_index_key" for table "channel"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "channelannotationlink_parent_child_owner_id_key" for table "channelannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "channelbinding_renderingdef_renderingdef_index_key" for table "channelbinding"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "codomainmapcontext_renderingdef_renderingdef_index_key" for table "codomainmapcontext"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "datasetannotationlink_parent_child_owner_id_key" for table "datasetannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "datasetimagelink_parent_child_owner_id_key" for table "datasetimagelink"
ERROR:  insert or update on table "datasetimagelink" violates foreign key constraint "fkdatasetimagelink_child_image"
DETAIL:  Key (child)=(197285) is not present in table "image".
ERROR:  insert or update on table "datasetimagelink" violates foreign key constraint "fkdatasetimagelink_parent_dataset"
DETAIL:  Key (parent)=(2225) is not present in table "dataset".
ERROR:  insert or update on table "event" violates foreign key constraint "fkevent_session_session"
DETAIL:  Key (session)=(230321) is not present in table "session".
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "experimenterannotationlink_parent_child_owner_id_key" for table "experimenterannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "experimentergroupannotationlink_parent_child_owner_id_key" for table "experimentergroupannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "filtersetemissionfilterlink_parent_child_owner_id_key" for table "filtersetemissionfilterlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "filtersetexcitationfilterlink_parent_child_owner_id_key" for table "filtersetexcitationfilterlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "groupexperimentermap_child_child_index_key" for table "groupexperimentermap"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "groupexperimentermap_parent_child_key" for table "groupexperimentermap"
ERROR:  insert or update on table "imageannotationlink" violates foreign key constraint "fkimageannotationlink_parent_image"
DETAIL:  Key (parent)=(197285) is not present in table "image".
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "imageannotationlink_parent_child_owner_id_key" for table "imageannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "joboriginalfilelink_parent_child_owner_id_key" for table "joboriginalfilelink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "lightpathemissionfilterlink_parent_child_owner_id_key" for table "lightpathemissionfilterlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "lightpathexcitationfilterlink_parent_child_owner_id_key" for table "lightpathexcitationfilterlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "lightpathexcitationfilterlink_parent_parent_index_key" for table "lightpathexcitationfilterlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "namespaceannotationlink_parent_child_owner_id_key" for table "namespaceannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "nodeannotationlink_parent_child_owner_id_key" for table "nodeannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "originalfileannotationlink_parent_child_owner_id_key" for table "originalfileannotationlink"
ERROR:  insert or update on table "pixels" violates foreign key constraint "fkpixels_image_image"
DETAIL:  Key (image)=(197285) is not present in table "image".
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "pixels_image_image_index_key" for table "pixels"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "pixelsannotationlink_parent_child_owner_id_key" for table "pixelsannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "pixelsoriginalfilemap_parent_child_owner_id_key" for table "pixelsoriginalfilemap"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "planeinfoannotationlink_parent_child_owner_id_key" for table "planeinfoannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "plateacquisitionannotationlink_parent_child_owner_id_key" for table "plateacquisitionannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "plateannotationlink_parent_child_owner_id_key" for table "plateannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "projectannotationlink_parent_child_owner_id_key" for table "projectannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "projectdatasetlink_parent_child_owner_id_key" for table "projectdatasetlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "reagentannotationlink_parent_child_owner_id_key" for table "reagentannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "roiannotationlink_parent_child_owner_id_key" for table "roiannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "screenannotationlink_parent_child_owner_id_key" for table "screenannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "screenplatelink_parent_child_owner_id_key" for table "screenplatelink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "sessionannotationlink_parent_child_owner_id_key" for table "sessionannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "shape_roi_roi_index_key" for table "shape"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "sharemember_parent_child_key" for table "sharemember"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "wellannotationlink_parent_child_owner_id_key" for table "wellannotationlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "wellreagentlink_parent_child_owner_id_key" for table "wellreagentlink"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "wellsample_well_well_index_key" for table "wellsample"
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index "wellsampleannotationlink_parent_child_owner_id_key" for table "wellsampleannotationlink"


[jmoore]

..

2) ...
Because the role of these table are unclear to me.

The session, event, and eventlog tables are all there to record who did what when. If the failed to insert but nothing else fails to insert, then that means it was an empty event, which is fine. The only thing you'll lose in that case is a record of a user attempting to do something.

Right now, I guess I do prefer the solution 2 and propose to abandon the solution 1. Luckily, the missing constraints allowed me to do the merge of the data. And adding the missing constraints in solution 2 ensures integrity likely enough.

All makes sense.

3) ...

But if you find it interesting to know the outcome, I can briefly test what you propose and see if it allows the server to start. But this is a production server and I cannot stop it easily. I will test that at the same time as I will add missing constraints for which I imagine I should stop the omero server.

Cheers
Jacques

The record of all you've done is certainly useful for the community at large. In general, I don't think there can be a set recipe for recovery from a catastrophe, but any tips are certainly appreciated.

All the best,
~Josh

[jacques2020]

Hi Josh,

Yes. `ALTER SEQUENCE seq_eventlog RESTART WITH 41570621;` This may be necessary for other sequences as well, and depending on what occurred during the split-window, it may have to be `max(backup, current)` that you set the sequence to.

I tried that and can still not restart the server on that version of the DB. I attach the log below. In anyway, I will stop investigating this issue.

I kept on working on adding the missing constraint and after a little bit more cleaning of the database similar to previously posted in this topic (fixing the not fullfilled constraints), I can make a "cleaning script" applied on a freshly restored DB (I backup up my DB before the cleaning "experiments") and then apply the runnable_diff you kindly made for me without any error. So, I guess everything is fine and the "new" constraints ensure quite well the integrity of the database. Since I restored all the file deleted in the omero data folder and the cleanse script don't try to delete any file, all files are referenced in the DB. I see now nothing else I can easily do to check DB integrity and I am likely at an acceptable level of verification.

Many thanks for your help thorough all these tests and attempts.

Cheers

Jacques