PostgreSQL Database signal 6 Error On Apple Mac

After I have done PostgreSQL upgrade through MacPort, the database refused to start and system generated crash report. The crash report generates by Apple doesn’t give much clue what was happening. Below is crash report I got from my machine;

shell> tail -F /var/log/system.log

Mar  3 10:50:33 takizo ReportCrash[74586]: Saved crashreport to /Library/Logs/CrashReporter/postgres_2009-03-03-105032_takizo.crash using uid: 0 gid: 0, euid: 0 egid: 0

Process:         postgres [74585]
Path:            /opt/local/lib/postgresql82/bin/postgres
Identifier:      postgres
Version:         ??? (???)
Code Type:       X86 (Native)
Parent Process:  postgres [74584]

Date/Time:       2009-03-03 10:50:32.277 +0800
OS Version:      Mac OS X 10.5.6 (9G55)
Report Version:  6

Exception Type:  EXC_CRASH (SIGABRT)
Exception Codes: 0x0000000000000000, 0x0000000000000000
Crashed Thread:  0

Thread 0 Crashed:
0   libSystem.B.dylib                   0x95ba1e42 __kill + 10
1   libSystem.B.dylib                   0x95c1423a raise + 26
2   libSystem.B.dylib                   0x95c20679 abort + 73
3   postgres                            0x002135ab errfinish + 587
4   postgres                            0x0003e2ee StartupXLOG + 7870
5   postgres                            0x00051d6d BootstrapMain + 1677
6   postgres                            0x001558e3 StartChildProcess + 211
7   postgres                            0x00158d6b PostmasterMain + 2779
8   postgres                            0x0010dc3b main + 1243
9   postgres                            0x00001bd6 start + 54

Thread 0 crashed with X86 Thread State (32-bit):
  eax: 0x00000000  ebx: 0x95c20639  ecx: 0xbfffc5fc  edx: 0x95ba1e42
  edi: 0x0030ddc0  esi: 0x00000000  ebp: 0xbfffc618  esp: 0xbfffc5fc
   ss: 0x0000001f  efl: 0x00000286  eip: 0x95ba1e42   cs: 0x00000007
   ds: 0x0000001f   es: 0x0000001f   fs: 0x00000000   gs: 0x00000037
  cr2: 0x00244158

Binary Images:
    0x1000 -   0x2c6ff3 +postgres ??? (???) /opt/local/lib/postgresql82/bin/postgres
  0x362000 -   0x39afeb +libssl.0.9.8.dylib ??? (???) <344b31c8167d85cb6c3f30ee35526d6f> /opt/local/lib/libssl.0.9.8.dylib
  0x3ae000 -   0x4b0fe7 +libcrypto.0.9.8.dylib ??? (???) <84f26a32852efdbc94bef9b6d8095d71> /opt/local/lib/libcrypto.0.9.8.dylib
  0x50e000 -   0x51effd +libz.1.dylib ??? (???) /opt/local/lib/libz.1.dylib
0x8fe00000 - 0x8fe2db43  dyld 97.1 (???) <100d362e03410f181a34e04e94189ae5> /usr/lib/dyld
0x92960000 - 0x92964fff  libmathCommon.A.dylib ??? (???) /usr/lib/system/libmathCommon.A.dylib
0x92965000 - 0x9296cfe9  libgcc_s.1.dylib ??? (???)  /usr/lib/libgcc_s.1.dylib
0x95b33000 - 0x95c9aff3  libSystem.B.dylib ??? (???)  /usr/lib/libSystem.B.dylib
0xffff0000 - 0xffff1780  libSystem.B.dylib ??? (???) /usr/lib/libSystem.B.dylib

No clue? Don’t think this log help much on troubleshooting, so let’s check on PostgreSQL logs;

shell> tail -F /opt/local/var/log/postgresql82/postgres.log 

LOG:  startup process (PID 74595) was terminated by signal 6
LOG:  aborting startup due to startup process failure
LOG:  database system was interrupted at 2008-11-28 12:21:59 MYT
LOG:  record with zero length at 0/95CD444
LOG:  invalid primary checkpoint record
LOG:  record with zero length at 0/95CD3FC
LOG:  invalid secondary checkpoint record
PANIC:  could not locate a valid checkpoint record
LOG:  startup process (PID 74780) was terminated by signal 6
LOG:  aborting startup due to startup process failure

Now I see the error, start up process was terminated by signal 6, what is signal 6? That doesn’t matter, the main problem is the error message stated

invalid primary checkpoint record

Something was screw up with the checkpoint record, to solve it, run a magic command call pg_resetxlog. Before you run pg_resetxlog, it’s advice to backup a copy of your database directory, because some unlucky fellow had problem after running pg_resetxlog, data was gone.

Play around the corrupted DB with pg_resetxlog;

shell> sudo su postgres
shell> /opt/local/lib/postgresql82/bin/pg_resetxlog -f /opt/local/var/db/postgresql82/defaultdb
shell> Transaction log reset

Now, start my Postgresql

shell> /opt/local/etc/LaunchDaemons/org.macports.postgresql82-server/postgresql82-server.wrapper start
shell> tail -F /opt/local/var/log/postgresql82/postgres.log 

LOG:  database system was shut down at 2009-03-03 11:15:02 MYT
LOG:  checkpoint record is at 0/B000020
LOG:  redo record is at 0/B000020; undo record is at 0/B000020; shutdown TRUE
LOG:  next transaction ID: 0/17667; next OID: 284360
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready

Alright, database is ready to roll again.