Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TsKMN-0003yB-M5 for pgsql-admin@arkaria.postgresql.org; Mon, 07 Jan 2013 21:35:55 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.72) (envelope-from ) id 1TsKMN-0001Mf-7c for pgsql-admin@arkaria.postgresql.org; Mon, 07 Jan 2013 21:35:55 +0000 Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TsKML-0001Ma-Uo for pgsql-admin@postgresql.org; Mon, 07 Jan 2013 21:35:54 +0000 Received: from [64.207.29.137] (helo=mail.illumen.com) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TsKMI-0001v3-90 for pgsql-admin@postgresql.org; Mon, 07 Jan 2013 21:35:53 +0000 X-MimeOLE: Produced By Microsoft Exchange V6.5 Content-class: urn:content-classes:message MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01CDED1E.F6192590" Subject: Re: Possible database corruption - urgent Date: Mon, 7 Jan 2013 14:35:48 -0700 Message-ID: X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Possible database corruption - urgent Thread-Index: Ac3tHNaAnSiwoU9uSlew79m2stv9kAAAQLOgAAAqdvA= From: "Benjamin Krajmalnik" To: X-Host-Lookup-Failed: Reverse DNS lookup failed for 64.207.29.137 (failed) X-Pg-Spam-Score: 0.3 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-admin Precedence: bulk Sender: pgsql-admin-owner@postgresql.org This is a multi-part message in MIME format. ------_=_NextPart_001_01CDED1E.F6192590 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Sorry for the cut and paste error. This is the log entry when the pg_xlog partition ran out of space: =20 2013-01-07 20:50:22 GMT [local]PANIC: could not write to file "pg_xlog/xlogtemp.49680": No space left on device 2013-01-07 20:50:22 GMT [local]STATEMENT: INSERT INTO tbltmptests (testhash, testtime, statusid, replytxt, replyval, groupid) V 2013-01-07 20:50:23 GMT LOG: server process (PID 49680) was terminated by signal 6: Abort trap 2013-01-07 20:50:23 GMT LOG: terminating any other active server processes 2013-01-07 20:50:23 GMT [local]WARNING: terminating connection because of crash of another server process 2013-01-07 20:50:23 GMT [local]DETAIL: The postmaster has commanded this server process to roll back the current transaction an 2013-01-07 20:50:23 GMT [local]HINT: In a moment you should be able to reconnect to the database and repeat your command. . . . 2013-01-07 20:50:23 GMT [local]FATAL: the database system is in recovery mode 2013-01-07 20:50:23 GMT LOG: all server processes terminated; reinitializing 2013-01-07 20:50:24 GMT LOG: database system was interrupted; last known up at 2013-01-07 00:31:02 GMT 2013-01-07 20:50:24 GMT LOG: database system was not properly shut down; automatic recovery in progress 2013-01-07 20:50:24 GMT LOG: consistent recovery state reached at 52F/8CE57490 2013-01-07 20:50:24 GMT LOG: redo starts at 52F/7BABC118 2013-01-07 20:50:38 GMT [local]FATAL: the database system is in recovery mode 2013-01-07 20:50:53 GMT [local]FATAL: the database system is in recovery mode 2013-01-07 20:51:08 GMT [local]FATAL: the database system is in recovery mode 2013-01-07 20:51:24 GMT [local]FATAL: the database system is in recovery mode 2013-01-07 20:51:39 GMT [local]FATAL: the database system is in recovery mode 2013-01-07 20:51:54 GMT [local]FATAL: the database system is in recovery mode =20 From: Benjamin Krajmalnik=20 Sent: Monday, January 07, 2013 2:31 PM To: Benjamin Krajmalnik; pgsql-admin@postgresql.org Subject: RE: [ADMIN] Possible database corruption - urgent =20 I forgot to mention - PostgreSQL 9.0 - my apologies. Can I just recreate the file using touch so it exists and then restart potgresql? The system coredumped and was attempting to go intorecovery mode =20 =20 2013-01-07 01:49:12 GMT ERROR: could not open file "base/16748/181979366_fsm": No such file or directory 2013-01-07 01:49:12 GMT CONTEXT: writing block 1 of relation base/16748/181979366_fsm 2013-01-07 01:49:12 GMT WARNING: could not write block 1 of base/16748/181979366_fsm . . . =20 2013-01-07 01:49:12 GMT ERROR: could not open file "base/16748/181979366_fsm": No such file or directory 2013-01-07 01:49:12 GMT CONTEXT: writing block 1 of relation base/16748/181979366_fsm 2013-01-07 01:49:12 GMT WARNING: could not write block 1 of base/16748/181979366_fsm =20 =20 From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Benjamin Krajmalnik Sent: Monday, January 07, 2013 2:22 PM To: pgsql-admin@postgresql.org Subject: [ADMIN] Possible database corruption - urgent =20 I have a situation where pg_xlog started growing until it filled up the disk drive. I got alerted to the error and started investigating. Checked the logs and I am seeing the following entry repeatedly: =20 2013-01-07 01:49:12 GMT ERROR: could not open file "base/16748/181979366_fsm": No such file or directory 2013-01-07 01:49:12 GMT CONTEXT: writing block 1 of relation base/16748/181979366_fsm 2013-01-07 01:49:12 GMT WARNING: could not write block 1 of base/16748/181979366_fsm =20 I checked the actual file system, and that file is indeed missing. 181979366 exists. Is there a way to get the system back up and running? I stopped the postmaster and am moving the pg_xlog directory to a partition which has room left in it, but I need to resolve this missing file problem =20 =20 ------_=_NextPart_001_01CDED1E.F6192590 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Sorry for the cut and paste = error.

This is the log entry when the pg_xlog partition = ran out of space:

 

2013-01-07 20:50:22 GMT = [local]PANIC:  could not write to file = "pg_xlog/xlogtemp.49680": No space left on = device

2013-01-07 20:50:22 GMT [local]STATEMENT:  = INSERT INTO tbltmptests (testhash, testtime, statusid, replytxt, = replyval, groupid) V

2013-01-07 20:50:23 GMT LOG:  server = process (PID 49680) was terminated by signal 6: Abort = trap

2013-01-07 20:50:23 GMT LOG:  terminating = any other active server processes

2013-01-07 20:50:23 GMT = [local]WARNING:  terminating connection because of crash of another = server process

2013-01-07 20:50:23 GMT [local]DETAIL:  The = postmaster has commanded this server process to roll back the current = transaction an

2013-01-07 20:50:23 GMT [local]HINT:  In a = moment you should be able to reconnect to the database and repeat your = command.

.

.

.

2013-01-07 20:50:23 GMT = [local]FATAL:  the database system is in recovery = mode

2013-01-07 20:50:23 GMT LOG:  all server = processes terminated; reinitializing

2013-01-07 20:50:24 GMT = LOG:  database system was interrupted; last known up at 2013-01-07 = 00:31:02 GMT

2013-01-07 20:50:24 GMT LOG:  database = system was not properly shut down; automatic recovery in = progress

2013-01-07 20:50:24 GMT LOG:  consistent = recovery state reached at 52F/8CE57490

2013-01-07 20:50:24 GMT = LOG:  redo starts at 52F/7BABC118

2013-01-07 20:50:38 GMT = [local]FATAL:  the database system is in recovery = mode

2013-01-07 20:50:53 GMT [local]FATAL:  the = database system is in recovery mode

2013-01-07 20:51:08 GMT = [local]FATAL:  the database system is in recovery = mode

2013-01-07 20:51:24 GMT [local]FATAL:  the = database system is in recovery mode

2013-01-07 20:51:39 GMT = [local]FATAL:  the database system is in recovery = mode

2013-01-07 20:51:54 GMT [local]FATAL:  the = database system is in recovery mode

 

From:= = Benjamin Krajmalnik
Sent: Monday, January 07, 2013 2:31 = PM
To: Benjamin Krajmalnik; = pgsql-admin@postgresql.org
Subject: RE: [ADMIN] Possible = database corruption - urgent

 

I forgot to mention – PostgreSQL 9.0 = – my apologies.

Can I just recreate the file using touch so it = exists and then restart potgresql?

The system coredumped = and was attempting to go intorecovery mode

 

 

2013-01-07 01:49:12 GMT = ERROR:  could not open file "base/16748/181979366_fsm": = No such file or directory

2013-01-07 01:49:12 GMT = CONTEXT:  writing block 1 of relation = base/16748/181979366_fsm

2013-01-07 01:49:12 GMT WARNING:  could not = write block 1 of base/16748/181979366_fsm

.

.

.

 

2013-01-07 01:49:12 GMT = ERROR:  could not open file "base/16748/181979366_fsm": = No such file or directory

2013-01-07 01:49:12 GMT = CONTEXT:  writing block 1 of relation = base/16748/181979366_fsm

2013-01-07 01:49:12 GMT WARNING:  could not = write block 1 of base/16748/181979366_fsm

 

 

From:= = pgsql-admin-owner@postgr= esql.org [mailto:pgsql-admin-owner= @postgresql.org] On Behalf Of Benjamin = Krajmalnik
Sent: Monday, January 07, 2013 2:22 = PM
To: pgsql-admin@postgresql.org=
Subject: [ADMIN] Possible database corruption - = urgent

 

I have a = situation where pg_xlog started growing until it filled up the disk = drive.

I got alerted to the error and = started investigating.

Checked the = logs and I am seeing the following entry repeatedly:

 

2013-01-07 = 01:49:12 GMT ERROR:  could not open file = "base/16748/181979366_fsm": No such file or = directory

2013-01-07 01:49:12 GMT = CONTEXT:  writing block 1 of relation = base/16748/181979366_fsm

2013-01-07 = 01:49:12 GMT WARNING:  could not write block 1 of = base/16748/181979366_fsm

 

I checked = the actual file system, and that file is indeed missing.  181979366 = exists.

Is there a way to get the = system back up and running?

I stopped = the postmaster and am moving the pg_xlog directory to a partition which = has room left in it, but I need to resolve this missing file = problem

 

 

------_=_NextPart_001_01CDED1E.F6192590--