Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 8416C63244B for ; Mon, 23 Aug 2010 20:05:01 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 69290-04 for ; Mon, 23 Aug 2010 23:04:52 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from bay0-omc2-s20.bay0.hotmail.com (bay0-omc2-s20.bay0.hotmail.com [65.54.190.95]) by mail.postgresql.org (Postfix) with ESMTP id 9ADF0632919 for ; Mon, 23 Aug 2010 20:04:53 -0300 (ADT) Received: from BAY149-W45 ([65.54.190.125]) by bay0-omc2-s20.bay0.hotmail.com with Microsoft SMTPSVC(6.0.3790.4675); Mon, 23 Aug 2010 16:04:49 -0700 Message-ID: Content-Type: multipart/alternative; boundary="_2c013622-cac6-4ff6-8dd8-4cc775aa7388_" X-Originating-IP: [94.8.170.116] From: Kieren Scott To: , Subject: Re: WAL and archive disks full Date: Mon, 23 Aug 2010 23:04:49 +0000 Importance: Normal In-Reply-To: <4C72B2E70200002500034A85@gw.wicourts.gov> References: , <4C72A63D0200002500034A80@gw.wicourts.gov>, , <4C72B2E70200002500034A85@gw.wicourts.gov> MIME-Version: 1.0 X-OriginalArrivalTime: 23 Aug 2010 23:04:49.0916 (UTC) FILETIME=[968A83C0:01CB4317] 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/160 X-Sequence-Number: 35409 --_2c013622-cac6-4ff6-8dd8-4cc775aa7388_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks Kevin. So if the wal filesystem is 100% full=2C can you actually startup postgres = in archiving mode (so the archive process can resume copying)? Presumably p= ostgres will try to write to the wal filesystem when you start it=2C and fa= il due to the filesystem full and then just shutdown/abort? Wouldnt you hav= e to free some space in the wal filesystem in order to get postgres up and = running? Thanks for you help. > Date: Mon=2C 23 Aug 2010 17:41:59 -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 > > Sorry it's a bit of a what-if scenario. I can envisage > > encountering a situation 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. > =20 > Oh=2C OK. I was afraid you had actually *hit* this situation and were > being coy. No need to apologize for contingency planning! :-) > =20 > Hypothetically=2C in the situation where the stall originated with the > application of files from the archive=2C fixing that end would and > clearing files from the archive directory (or moving or deleting old > ones if they were applying cleanly and just sitting there after > application)=2C would allow the archive process to resume copying and > cleaning up files on the source database. > =20 > If someone panicked and deleted files from the pg_xlog directory=2C > well=2C the first thing is to try to make sure nobody does that. You > might be able to turn off archiving and get the server to come up.=20 > If not=2C start by making a complete copy of your data directory and > all of its subdirectories while PostgreSQL is stopped=2C because you > may wander into trouble and want to try again. If you can't start > with archiving turned off=2C you might want to look at this: > =20 > http://www.postgresql.org/docs/current/interactive/app-pgresetxlog.html > =20 > Of course=2C you want to monitor closely to ensure your backups are > running correctly so you never need any of the above advice. =3B-) > =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 = --_2c013622-cac6-4ff6-8dd8-4cc775aa7388_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Thanks Kevin.

So if the wal filesystem is 100% full=2C can you actua= lly startup postgres in archiving mode (so the archive process can resume c= opying)? Presumably postgres will try to write to the wal filesystem when y= ou start it=2C and fail due to the filesystem full and then just shutdown/a= bort? Wouldnt you have to free some space in the wal filesystem in order to= get postgres up and running?

Thanks for you help.

>=3B Dat= e: Mon=2C 23 Aug 2010 17:41:59 -0500
>=3B From: Kevin.Grittner@wicourt= s.gov
>=3B To: kierenscott@hotmail.com=3B pgsql-admin@postgresql.org>=3B Subject: Re: [ADMIN] WAL and archive disks full
>=3B
>= =3B Kieren Scott <=3Bkierenscott@hotmail.com>=3B wrote:
>=3B
= >=3B >=3B Sorry it's a bit of a what-if scenario. I can envisage
>= =3B >=3B encountering a situation in the future whereby we hit this
&g= t=3B >=3B problem=2C and I was trying to put a plan in place for how to d= eal
>=3B >=3B with it.
>=3B
>=3B Oh=2C OK. I was afraid= you had actually *hit* this situation and were
>=3B being coy. No ne= ed to apologize for contingency planning! :-)
>=3B
>=3B Hypoth= etically=2C in the situation where the stall originated with the
>=3B = application of files from the archive=2C fixing that end would and
>= =3B clearing files from the archive directory (or moving or deleting old>=3B ones if they were applying cleanly and just sitting there after
= >=3B application)=2C would allow the archive process to resume copying an= d
>=3B cleaning up files on the source database.
>=3B
>=3B= If someone panicked and deleted files from the pg_xlog directory=2C
>= =3B well=2C the first thing is to try to make sure nobody does that. You>=3B might be able to turn off archiving and get the server to come up.=
>=3B If not=2C start by making a complete copy of your data director= y and
>=3B all of its subdirectories while PostgreSQL is stopped=2C be= cause you
>=3B may wander into trouble and want to try again. If you = can't start
>=3B with archiving turned off=2C you might want to look a= t this:
>=3B
>=3B http://www.postgresql.org/docs/current/intera= ctive/app-pgresetxlog.html
>=3B
>=3B Of course=2C you want to m= onitor closely to ensure your backups are
>=3B running correctly so yo= u never need any of the above advice. =3B-)
>=3B
>=3B -Kevin>=3B
>=3B --
>=3B Sent via pgsql-admin mailing list (pgsql-= admin@postgresql.org)
>=3B To make changes to your subscription:
&g= t=3B http://www.postgresql.org/mailpref/pgsql-admin
= --_2c013622-cac6-4ff6-8dd8-4cc775aa7388_--