Re: archived WALL files question

[Frederiko Costa <frederiko@xxxxxxxxx>]

I have seen new 16 MB segments files created in pg_xlog directory as time goes on. Honestly, I did not understand why it got created, because I was running it on a VM and there was no activity. I got several new segments.

If I run the command you asked, this is the output. Neither segment is copied, nor a new segment file gets created:

 select pg_switch_xlog();
 pg_switch_xlog
----------------
 0/81000088
(1 row)

Wasn't this command supposed to create a new segment file and copy the last non-copied segments - using command specified at archive_command - to the destination you defined?

I wanted to understand why the newly created wal segment files (with 16 MB each) did not copied automatically to the new place. What's the criteria? They only get copied when I carry out a base backup.

Thanks for the help!

On 04/19/2010 09:41 AM, Kevin Grittner wrote:

Frederiko Costa <frederiko@xxxxxxxxx> wrote:
 
  

I noticed the new logs files don't get copied to the directory
specified on the archive_command. It's only copied when I do the
pg_start_backup()/pg_stop_backup() base backup. Is this behaviour
only achieved if I set archive_timeout? I did not want to do that,
because I thought as soon as the 16MB WAL segment file got created
it would be copied to the exported directory.
    

 
Have you actually been filling up any 16MB WAL file segments?  How
have you determined that?  What happens when you run (as a database
superuser) this command?:
 
SELECT pg_switch_xlog();
 
If the reason you don't want to set archive_timeout is that you
don't want 16MB files piling up when there is little or no activity,
you might want to consider using pglesslog or pg_clearxlogtail. 
These can eliminate nearly all the cost of the more frequent logs.
 
-Kevin