PDA

View Full Version : PostgreSQL crashes during purge



jurrr
10-24-2009, 05:08 AM
This is not likely a HM bug, probably a PostgreSQL bug but perhaps you can give some advice.

PostgreSQL crashes (as in service is no longer available) during purge.

Event log shows:

2009-10-24 09:39:57 BSTERROR: table "temppurge" does not exist
2009-10-24 09:39:57 BSTSTATEMENT: DROP TABLE temppurge

2009-10-24 09:59:32 BSTPANIC: could not open file "pg_xlog/000000010000000D0000003A" (log file 13, segment 58): Permission denied
2009-10-24 09:59:32 BSTSTATEMENT: select pokerhand_id into TempPurge *snipped remainder of SQL statement*

and then 4 instances of:

2009-10-24 09:59:39 BSTWARNING: terminating connection because of crash of another server process
2009-10-24 09:59:39 BSTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2009-10-24 09:59:39 BSTHINT: In a moment you should be able to reconnect to the database and repeat your command.



Do I need to open a bug for PostgreSQL? Any other suggestions?

fozzy71
10-24-2009, 09:09 AM
I have forwarded this thread to my support manager for a reply.

fabio
10-25-2009, 09:39 AM
The permission denied makes me think firewall / anti virus or potentially a corrupt DB. Doing a full vacuum through pgadmin should determine if the last option is right

jurrr
10-25-2009, 02:33 PM
The permission denied makes me think firewall / anti virus or potentially a corrupt DB. Doing a full vacuum through pgadmin should determine if the last option is right

Firewall shouldn't apply, it is a local disk. I also only have the default Windows one (my router is the other but that is at the edge of the network).
Anti-virus is AVG9 and I don't see it blocking anything.
Corrupt DB is unlikely as it is a fresh install with a fresh restore from backup.
Full vacuum worked and finished fine, no crashes.

The noteworthy things are that this is on:
Retail Win7 Home Premium
Postgres 8.4
Intel X25-M G2 SSD

Which are all 3 fairly recent and maybe not the "standard" setup".

fozzy71
10-25-2009, 02:40 PM
....
Corrupt DB is unlikely as it is a fresh install with a fresh restore from backup.
........

If you did a restore from a .backup file, a corrupt DB is a very likely possibility.

I personally never use .backup/restore, because of all the horror stories I see about corrupt DB's after using the restore process. I have rebuilt my DB countless times, and have only done a restore 1 time, just as an educational experience.

jurrr
10-26-2009, 02:30 AM
By restore I meant I backed up the DB, dropped it, created a new database with the same name and then restored the backup to it. Is that the proper way? If not, what is? I guess you thought I restored it to my old database on top of it and I can see why, but I didn't.

Do you now agree a corrupt DB isn't that likely?

In HM if you do the export hands - reimport hands, what happens with observed hands which are removed from DB but stats for which are still present? Do I lose those stats? I could try that I guess if all else fails.

gsus
10-26-2009, 05:02 AM
I also have this issue. I run Windows 7 64 bit and this began to happen after the beta 40 update and the latest Widows Updates releases last week.

PostgreSQL crashes after a couple of hands been imported and the same happen after a little while when doing a vacum in the database.

I think this is some problem with PostgreSQL and Windows 7 latest security updates, but I am not sure.

jurrr
10-26-2009, 05:59 AM
gsus, yes I'm also on Win7 64bit (release version).

What's in your Event log at the time of the crash? Same thing as for me?

gsus
10-26-2009, 06:30 AM
Get tons of errors in the log. It ends with:

2009-10-26 03:26:55 PDTWARNING: relation "postflopactions_month" page 21 is uninitialized --- fixing
2009-10-26 03:27:01 PDTPANIC: could not lock semaphore: error code 0


This application has requested the Runtime to terminate it in an unusual way.
Please contact the application's support team for more information.

2009-10-26 03:27:01 PDTLOG: WAL writer process (PID 908) exited with exit code 3
2009-10-26 03:27:01 PDTLOG: terminating any other active server processes
2009-10-26 03:27:01 PDTWARNING: terminating connection because of crash of another server process
2009-10-26 03:27:01 PDTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2009-10-26 03:27:01 PDTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2009-10-26 03:27:01 PDTWARNING: terminating connection because of crash of another server process
2009-10-26 03:27:01 PDTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2009-10-26 03:27:01 PDTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2009-10-26 03:27:02 PDTWARNING: terminating connection because of crash of another server process
2009-10-26 03:27:02 PDTDETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2009-10-26 03:27:02 PDTHINT: In a moment you should be able to reconnect to the database and repeat your command.
2009-10-26 03:27:02 PDTLOG: all server processes terminated; reinitializing
2009-10-26 03:27:12 PDTFATAL: pre-existing shared memory block is still in use
2009-10-26 03:27:12 PDTHINT: Check if there are any old server processes still running, and terminate them.

I have also tried setting up a new database and it still crashes. Not sure if some of the handhistory formats are not compatible. They are from PokerStars. How does it work when they change their handhistory format and HM updates to support it. Are HM then still supporting the old format aswell?

jurrr
10-26-2009, 06:41 AM
The errors are different than mine. It might be the same thing, might be a different thing.

Generally HM should support the old formats too imo, but that's just my opinion.

netsrak
10-26-2009, 09:06 AM
You both should try a new database and if that doesnt help a fresh installation of postgresql.

There should be no problem with old Stars hands and the logs seem to be more of a internal database problem than a problem with handhistories.

fozzy71
10-26-2009, 11:29 AM
By restore I meant I backed up the DB, dropped it, created a new database with the same name and then restored the backup to it. Is that the proper way? If not, what is? I guess you thought I restored it to my old database on top of it and I can see why, but I didn't.

Do you now agree a corrupt DB isn't that likely?

That is what I meant. That process often causes corrupt DBs which is why I never use it and dont recommend it. If you have a multi-million hand DB then it might be worth the effort, instead of importing for days.

This is why it is so important to compress/archive any original, processed, datamined hands. If anything happens to your DB, or your exported text files from the DB, you can import the original hands to rebuild.


In HM if you do the export hands - reimport hands, what happens with observed hands which are removed from DB but stats for which are still present? Do I lose those stats? I could try that I guess if all else fails.

I had many old datamined hands from FTP and would export/import and they would return. Eventually I purged them all, and exported my hands again. Now when they get reimported next time, to a enw DB, the mined hands and stats are gone.

jurrr
10-27-2009, 02:29 AM
You both should try a new database and if that doesnt help a fresh installation of postgresql.

Yes, well it started happening on a new install and a new DB. If it only happens during purge I can live with it. I really think the odds of a corrupt DB are like 10% (primarily because it doesn't happen on reports/etc).

Oh, well, if all else fails I can reimport my hands and hope I have all of them saved :(.


See the thing is, fozzy, most software don't offer a way to backup-restore outside Postgres, like HM (partially) does with the import-export function. If the backup-restore was really buggy like you say for huge databases, don't you think many people who use Postgres for things other than HM (like 99.9% of their users) would be up in their arms about it?