Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 0781C63311F for ; Mon, 23 Aug 2010 19:16:32 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 76461-03 for ; Mon, 23 Aug 2010 22:16:23 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc2-s18.bay0.hotmail.com (bay0-omc2-s18.bay0.hotmail.com [65.54.190.93]) by mail.postgresql.org (Postfix) with ESMTP id 54CF5632CD5 for ; Mon, 23 Aug 2010 19:16:24 -0300 (ADT) Received: from BAY149-W32 ([65.54.190.125]) by bay0-omc2-s18.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Mon, 23 Aug 2010 15:16:22 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_a9f36063-c2b6-48cf-bf65-db79862bb3bf_" X-Originating-IP: [94.8.170.116] From: Kieren Scott To: , Subject: Re: WAL and archive disks full Date: Mon, 23 Aug 2010 22:16:22 +0000 Importance: Normal In-Reply-To: <4C72A63D0200002500034A80@gw.wicourts.gov> References: , <4C72A63D0200002500034A80@gw.wicourts.gov> MIME-Version: 1.0 X-OriginalArrivalTime: 23 Aug 2010 22:16:22.0427 (UTC) FILETIME=[D18AE6B0:01CB4310] X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.909 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001, RCVD_IN_DNSWL_NONE=-0.0001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201008/157 X-Sequence-Number: 35406 --_a9f36063-c2b6-48cf-bf65-db79862bb3bf_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Apologies for the hypothetical scenario=2C I was trying to gain a greater understanding of what actions postgres would require in order to get the in= stance started without any errors (such as archiver errors because wal files had b= een=20 wrongly manually deleted in order to free up space). I'd be happy with a sitution which lets us start over again with a new base= backup. We have separate mount points for wal=2C and archived wal filesystems. Noth= ing else apart from wal files are written to the filesystems. I noticed a situation recently whereby our backup scripts had been failing= =2C and the script had subsequently not been clearing down the archive wal filesysytem after a= successful backup. The wal filesystem was almost full because the archive_command couldn't cop= y wal files=20 to the archive filesystem. Sorry it's a bit of a what-if scenario. I can envisage encountering a situa= tion in the future whereby we hit this problem=2C and I was trying to put a plan in place for = how to deal with it. Thanks in advance. > Date: Mon=2C 23 Aug 2010 16:47:57 -0500 > From: Kevin.Grittner@wicourts.gov > To: kierenscott@hotmail.com=3B pgsql-admin@postgresql.org > Subject: Re: [ADMIN] WAL and archive disks full >=20 > Kieren Scott wrote: > =20 > > What would be the best course of action for resolving a situation > > whereby your postgres instance had crashed due to the wal disk and > > archive wal disk becoming 100% full? Say your backups have been > > failing and your 'monitoring' had not reported it correctly. > >=20 > > You can't start the instance because it needs to write to the WAL > > disk (which is full)=2C but if you manually move WAL files off the > > WAL disk=2C the archiver will fail because it can't find WAL files > > it needs to archive. The instance may also still be in backup > > mode=2C because the backups had not completed due to the disk full > > situation. > >=20 > > Being new to postgres=2C im trying to understand what actions need > > to be taken to get the instance back up and running without > > compromising recoverability...? > =20 > You will get more detailed advice if you avoid hypotheticals and say > exactly what's going on and what your priorities are. For starters=2C > are you OK with a situation which gets your primary database running > again and lets you start over with a new base backup=2C or is it > critical that you continue your backup stream without having to take > a new base backup? My advice would depend on that answer to that. > =20 > Also=2C it would be helpful to have an idea what your various mount > points are=2C how big they are=2C and what's on them. (If there's > something else *also* on the same mount point as the WAL files=2C that > might make a difference. What do you mean=2C exactly=2C when you say > your wal disk and archive wal disk are 100% full? (Are those > separate mount points? Did the archive fail to restore=2C thereby > building up to where the archive later began to fail=2C or is it a > shared drive?) > =20 > -Kevin >=20 > --=20 > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin = --_a9f36063-c2b6-48cf-bf65-db79862bb3bf_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Apologies for the hypothetical scenario=2C I was trying to gain a greaterunderstanding of what actions postgres would require in order to get the = instance
started without any errors (such as archiver errors because wal= files had been
wrongly manually deleted in order to free up space).
I'd be happy with a sitution which lets us start over again with a new= base backup.

We have separate mount points for wal=2C and archived = wal filesystems. Nothing
else apart from wal files are written to the fi= lesystems.

I noticed a situation recently whereby our backup scripts= had been failing=2C and the script
had subsequently not been clearing d= own the archive wal filesysytem after a successful backup.
The wal files= ystem was almost full because the archive_command couldn't copy wal files <= br>to the archive filesystem.

Sorry it's a bit of a what-if scenario= . I can envisage encountering a situation in the future
whereby we hit t= his problem=2C and I was trying to put a plan in place for how to deal with= it.

Thanks in advance.



>=3B Date: Mon=2C 23 Aug 20= 10 16:47:57 -0500
>=3B From: Kevin.Grittner@wicourts.gov
>=3B To:= kierenscott@hotmail.com=3B pgsql-admin@postgresql.org
>=3B Subject: R= e: [ADMIN] WAL and archive disks full
>=3B
>=3B Kieren Scott <= =3Bkierenscott@hotmail.com>=3B wrote:
>=3B
>=3B >=3B What w= ould be the best course of action for resolving a situation
>=3B >= =3B whereby your postgres instance had crashed due to the wal disk and
&= gt=3B >=3B archive wal disk becoming 100% full? Say your backups have bee= n
>=3B >=3B failing and your 'monitoring' had not reported it correc= tly.
>=3B >=3B
>=3B >=3B You can't start the instance becaus= e it needs to write to the WAL
>=3B >=3B disk (which is full)=2C but= if you manually move WAL files off the
>=3B >=3B WAL disk=2C the ar= chiver will fail because it can't find WAL files
>=3B >=3B it needs = to archive. The instance may also still be in backup
>=3B >=3B mode= =2C because the backups had not completed due to the disk full
>=3B &g= t=3B situation.
>=3B >=3B
>=3B >=3B Being new to postgres=2C= im trying to understand what actions need
>=3B >=3B to be taken to = get the instance back up and running without
>=3B >=3B compromising = recoverability...?
>=3B
>=3B You will get more detailed advice = if you avoid hypotheticals and say
>=3B exactly what's going on and wh= at your priorities are. For starters=2C
>=3B are you OK with a situat= ion which gets your primary database running
>=3B again and lets you s= tart over with a new base backup=2C or is it
>=3B critical that you co= ntinue your backup stream without having to take
>=3B a new base backu= p? My advice would depend on that answer to that.
>=3B
>=3B Al= so=2C it would be helpful to have an idea what your various mount
>=3B= points are=2C how big they are=2C and what's on them. (If there's
>= =3B something else *also* on the same mount point as the WAL files=2C that<= br>>=3B might make a difference. What do you mean=2C exactly=2C when you= say
>=3B your wal disk and archive wal disk are 100% full? (Are thos= e
>=3B separate mount points? Did the archive fail to restore=2C ther= eby
>=3B building up to where the archive later began to fail=2C or is= it a
>=3B shared drive?)
>=3B
>=3B -Kevin
>=3B
&= gt=3B --
>=3B Sent via pgsql-admin mailing list (pgsql-admin@postgres= ql.org)
>=3B To make changes to your subscription:
>=3B http://ww= w.postgresql.org/mailpref/pgsql-admin
= --_a9f36063-c2b6-48cf-bf65-db79862bb3bf_--