Fixing Greenplum 'unresolved in-doubt transaction' errors
We had an issue with a database server running Greenplum (commercial Postgresql for large-scale datawarehousing). Greenplum was starting, but attempts to do anything in the database were resulting in the following errors:
INFO: Crash recovery broadcast of the distributed transaction 'Commit Prepared' broadcast succeeded for gid = 1265880453-0032866370.
INFO: Crash recovery broadcast of the distributed transaction 'Abort Prepared' broadcast succeeded for gid = 1265880453-0032866371��C
psql: FATAL: DTM Log recovery failed. There are still unresolved in-doubt transactions on some of the segment databaes that were not able to be resolved for an unknown reason. (cdbtm.c:2829)
DETAIL: Here is a list of in-doubt transactions in the system: List of In-doubt transactions remaining across the segdbs: ("1265880453-0032866371��C" , )
HINT: Try restarting the Greenplum Database array. If the problem persists an Administrator will need to resolve these transactions manually.
Of course, manuals/forums/Google did not provide any useful ideas as regards to how 'to resolve these transactions manually'. Morever there was no backup handy (and the db was huge). I didn't care about two lost transactions, I just wanted to start the database. After an hour of attempts, eventually I succeeded. The trick was to delete files from pg_twophase/ subdirectories.
I'm blogging this in the hope that when somebody else faces this problem, he would be able to find this post through Google, saving his nerves. :-)
INFO: Crash recovery broadcast of the distributed transaction 'Commit Prepared' broadcast succeeded for gid = 1265880453-0032866370.
INFO: Crash recovery broadcast of the distributed transaction 'Abort Prepared' broadcast succeeded for gid = 1265880453-0032866371��C
psql: FATAL: DTM Log recovery failed. There are still unresolved in-doubt transactions on some of the segment databaes that were not able to be resolved for an unknown reason. (cdbtm.c:2829)
DETAIL: Here is a list of in-doubt transactions in the system: List of In-doubt transactions remaining across the segdbs: ("1265880453-0032866371��C" , )
HINT: Try restarting the Greenplum Database array. If the problem persists an Administrator will need to resolve these transactions manually.
Of course, manuals/forums/Google did not provide any useful ideas as regards to how 'to resolve these transactions manually'. Morever there was no backup handy (and the db was huge). I didn't care about two lost transactions, I just wanted to start the database. After an hour of attempts, eventually I succeeded. The trick was to delete files from pg_twophase/ subdirectories.
I'm blogging this in the hope that when somebody else faces this problem, he would be able to find this post through Google, saving his nerves. :-)