X-Original-To: pgsql-admin-postgresql.org@postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2C3839F9FE3 for ; Thu, 22 Jun 2006 11:12:21 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 84270-02 for ; Thu, 22 Jun 2006 11:12:07 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wx-out-0102.google.com (wx-out-0102.google.com [66.249.82.201]) by postgresql.org (Postfix) with ESMTP id B47B49F9ED6 for ; Thu, 22 Jun 2006 11:12:07 -0300 (ADT) Received: by wx-out-0102.google.com with SMTP id s13so319782wxc for ; Thu, 22 Jun 2006 07:12:05 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=T2denvFhnsvDE7fIuQk2t/qTqbhXa3gJLc4NNcTrwGOIy6Q/xKH46jqAnth+LRl2dKjXXeIMNeaT3kohuT1NZA2npj/+6ypW4oDGkNxWwMcTUVHu8gByvDhCepnSgFzasuDCiu5O3TR7Op9AXNtGgZPr9qwHeeZlm6NKJ1vv0QM= Received: by 10.70.30.10 with SMTP id d10mr3182738wxd; Thu, 22 Jun 2006 07:12:05 -0700 (PDT) Received: by 10.70.73.15 with HTTP; Thu, 22 Jun 2006 07:12:04 -0700 (PDT) Message-ID: <1d219a6f0606220712k3be5d72fra35a3af8d10ea072@mail.gmail.com> Date: Thu, 22 Jun 2006 10:12:04 -0400 From: "Chris Hoover" To: andy.shellam@mailnetwork.co.uk Subject: Re: 2,2gb of pg_xlog ?? Cc: Stefan.Schmidt@schinkel.de, pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_11295_31375200.1150985524823" References: <0000EA81.4497EA95@192.168.3.13> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/196 X-Sequence-Number: 22158 ------=_Part_11295_31375200.1150985524823 Content-Type: text/plain; charset=WINDOWS-1252; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline None of this will work since he is using PG 7.2. To start off with, and urgent upgrade is needed to a newer version since there are serious bugs an= d data loss issues in these earlier versions. As far as having the 2.2 GB of xlogs, someone more knowledgeable of this would need to answer that. On 6/22/06, Andy Shellam wrote: > > Hi Stefan, > > > > These pg_xlog files are, as you said, 16MB files that store all the > transactions made to your database =96 if you're doing regular SQL dump > backups, and you know you won't need Point-In-Time (PITR) recovery, you > won't need these files. > > > > However, you will need to arrange for old files to be automatically > archived into a separate folder outside of your pg_xlog directory, by > setting an archive_command in the WAL configuration in postgresql.conf. = A > dead simple command looks like this: > > > > Archive_command =3D "mv %p /your/backup/directory/%f" > > > > This tells PGSQL to move ("mv") the file no longer in use "%p" to the > directory "/your/backup/directory/" with the filename "%f". %f is the > original filename of the file being archived. > > > > Once you've put that in, killall =96HUP postmaster, and you should notice > your files being moved out. Note: the postgresql Unix user (from > /etc/passwd) will need write access to /your/backup/directory. > > > > Tip: if you're absolutely sure you will never need these transaction logs= , > you can use the following command to delete them when they're finished wi= th: > > > > Archive_command =3D "rm =96f %p" > > > > Or > > > > Archive_command =3D "mv %p /dev/null" > > > > This is for PostgreSQL 8.0 above, I believe it should be the same for 7.2= . > > > > Hope this helps! > > > > Andy > > > > > ------------------------------ > > *From:* pgsql-admin-owner@postgresql.org [mailto: > pgsql-admin-owner@postgresql.org] *On Behalf Of * > Stefan.Schmidt@schinkel.de > *Sent:* 20 June 2006 11:31 am > *To:* pgsql-admin@postgresql.org > *Subject:* [ADMIN] 2,2gb of pg_xlog ?? > > > > Hi, > > > > I'm new to pgsql, so please be kind ;-) > > > > ok here my problem: > > > > I have a linux box with Debian Woody and pgsql 7.2. > > > > The problem is in the pg_xlog hold about 137 files with 16 MB each, the > oldest file is from 2005. > > > > I read the WAL docu but as far as I'm understandig it, it should be auto > cleand as long as isn't configured otherwise. > > But it is not, all options mentioned in the WAL doku are not set.. > > > > Any suggestions? > > > > here is my postgres.conf > > > > ------ > > > fsync =3D off > > debug_level =3D 0 > log_connections =3D off > log_pid =3D on > log_timestamp =3D on > syslog =3D 1 > # if syslog is 0, turn silent_mode off! > silent_mode =3D off > syslog_facility =3D LOCAL0 > trace_notify =3D off > max_connections =3D 64 > # shared_buffers must be at least twice max_connections, and not less tha= n > 16 > shared_buffers =3D 128 > # TCP/IP access is allowed by default, but the default access given in > # pg_hba.conf will permit it only from localhost, not other machines. > tcpip_socket =3D on > sort_mem =3D 512 > > > > > ------ > > > > cu > > > > !DSPAM:14,449a31b6256849416340232! > ------=_Part_11295_31375200.1150985524823 Content-Type: text/html; charset=WINDOWS-1252 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline None of this will work since he is using PG 7.2.  To start off with, a= nd urgent upgrade is needed to a newer version since there are serious bugs= and data loss issues in these earlier versions. 

As far as ha= ving the=20 2.2 GB of xlogs, someone more knowledgeable of this would need to answer th= at.

On 6/22/06, Andy Shellam <andy@and= ycc.net > wrote:

Hi Stefan,

 

These pg_xlog files are, as you sa= id, 16MB files that store all the transactions made to your database =96 if you're doing regular SQL dump backups, and you know you won't need Point-In-Time (PITR) recovery, you won't need these files. 

 

However, you will need to arrange = for old files to be automatically archived into a separate folder outside of your pg_xlog directory, by setting an archive_command in the WAL configuration i= n postgresql.conf.  A dead simple command looks like this:

 

Archive_command =3D "mv %p /your/backup/directory/%f"

 

This tells PGSQL to move ("mv") the file no longer in use "%p" to the directory "/your/backup/directory/" with the filename "%f".  %f is the original filename of the file being archived.

 

Once you've put that in, killall = =96HUP postmaster, and you should notice your files being moved out.  Note: t= he postgresql Unix user (from /etc/passwd) will need write access to /your/backup/directory.

 

Tip: if you're absolutely sure you will never need these transaction logs, you can use the following command t= o delete them when they're finished with:

 

Archive_command =3D "rm =96f %p"

 

Or

 

Archive_command =3D "mv %p /dev/nu= ll"

 

This is for PostgreSQL 8.0 above, = I believe it should be the same for 7.2.

 

Hope this helps!

 

Andy

 

 


From: pgsql-admin-owner@postg= resql.org [mailto: pgsql-admin-owner@postgresql.org] On Behalf Of Stef= an.Schmidt@schinkel.de
Sent: 20 June 2006 11:31 a= m
To: pgsql-admin@postgresql.org
Subject: [ADMIN] 2,2gb of = pg_xlog ??

 

Hi,

 

I'm new to pgsql, so please be k= ind ;-)

 

ok here my problem:

 

I have a linux box with Debian W= oody and pgsql 7.2.

 

The problem is in the pg_xlog ho= ld about 137 files with 16 MB each, the oldest file is from 2005.

 

I read the WAL docu but as far a= s I'm understandig it, it should be auto cleand as long as isn't configured otherwise.

But it is not, all options menti= oned in the WAL doku are not set..

 

Any suggestions?

 

here is my postgres.conf<= /font>

 

------


fsync =3D off

debug_level =3D 0
log_connections =3D off
log_pid =3D on
log_timestamp =3D on
syslog =3D 1
# if syslog is 0, turn silent_mode off!
silent_mode =3D off
syslog_facility =3D LOCAL0
trace_notify =3D off
max_connections =3D 64
# shared_buffers must be at least twice max_connections, and not less than = 16
shared_buffers =3D 128
# TCP/IP access is allowed by default, but the default access given in
# pg_hba.conf will permit it only from localhost, not other machines.
tcpip_socket =3D on
sort_mem =3D 512
            

 

------

 

cu

 

!DSPAM:14,449a31b625684941634023= 2!


------=_Part_11295_31375200.1150985524823--