Thread: Massive insert created 975 WAL segments.. what?

We were doing a test and inserted 1.5 million rows. In doing so, postgres created 974 WAL segments of 16MB apiece. The relevant configuration from my master's postgresql.conf:

On Sep 11, 2012, at 8:10 PM, Wells Oliver wrote:

> So, now I have 16GB of WAL segments, 974 of them. Will postgresql clean this up? Will it remove these files? Will it
createmore with big inserts? 
>
> What is the best way to manage this?

http://www.postgresql.org/docs/9.2/static/pgarchivecleanup.html

you can use this in conjunction with your backup facility/scipts.  Don't want to clean them up until you have a backup,
right?

On 12 September 2012 01:10, Wells Oliver <wellsoliver@gmail.com> wrote:
> We were doing a test and inserted 1.5 million rows. In doing so, postgres
> created 974 WAL segments of 16MB apiece. The relevant configuration from my
> master's postgresql.conf:
>
> wal_level         = hot_standby
> archive_mode      = on
> archive_command   = 'test ! -f /mnt/postgresql-logs/%f && cp %p
> /mnt/postgresql-logs/%f'
> max_wal_senders   = 3
> wal_keep_segments = 16
>
> So, now I have 16GB of WAL segments, 974 of them. Will postgresql clean this
> up? Will it remove these files? Will it create more with big inserts?
>
> What is the best way to manage this?

These are transaction log files, created by database writes. They
protect you if you crash and also allow replication/backup.

These will be rotated every checkpoint, which by default is 5 minutes.
So they'll be long gone by now.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services