Thread: truncate transaction log

Hi.

Is there any way to truncate WAL log in postgres?
We want to use full-backup strategy where we stop the server and copy
the data directory, however WAL log is taking dozens gigabytes of
spaces.
Is there any way to remove it while keeping the database operational/
restore-able by copying it back?

On 8/5/07, Sergei Shelukhin <realgeek@gmail.com> wrote:
> Hi.
>
> Is there any way to truncate WAL log in postgres?
> We want to use full-backup strategy where we stop the server and copy
> the data directory, however WAL log is taking dozens gigabytes of
> spaces.
> Is there any way to remove it while keeping the database operational/
> restore-able by copying it back?

This sounds unusually high.  What non-default settings are there in
your postgresql.conf file?  Are you using PITR or something like that?

On Sun, 2007-08-05 at 03:45 -0700, Sergei Shelukhin wrote:

> Is there any way to truncate WAL log in postgres?
> We want to use full-backup strategy where we stop the server and copy
> the data directory, however WAL log is taking dozens gigabytes of
> spaces.
> Is there any way to remove it while keeping the database operational/
> restore-able by copying it back?

Reduce the setting of checkpoint_segments to something more realistic.

--
  Simon Riggs
  EnterpriseDB  http://www.enterprisedb.com

On Aug 7, 9:57 pm, si...@2ndquadrant.com ("Simon Riggs") wrote:
> On Sun, 2007-08-05 at 03:45 -0700, Sergei Shelukhin wrote:
> > Is there any way to truncate WAL log in postgres?
> > We want to use full-backup strategy where we stop the server and copy
> > the data directory, however WAL log is taking dozens gigabytes of
> > spaces.
> > Is there any way to remove it while keeping the database operational/
> > restore-able by copying it back?
>
> Reduce the setting of checkpoint_segments to something more realistic.
>

Hmm. Reduced to 60, during the db conversion from MySQL Postgres used
to complain about this setting so I increased it to the sky. Thanks :)