Обсуждение: 2,2gb of pg_xlog ??

Поиск
Список
Период
Сортировка

2,2gb of pg_xlog ??

От
Stefan.Schmidt@schinkel.de
Дата:
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 = off
debug_level = 0
log_connections = off
log_pid = on
log_timestamp = on
syslog = 1
# if syslog is 0, turn silent_mode off!
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 64
# shared_buffers must be at least twice max_connections, and not less than 16
shared_buffers = 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 = on
sort_mem = 512
            
 
------
 
cu
 

Re: 2,2gb of pg_xlog ??

От
"Andy Shellam"
Дата:

Hi Stefan,

 

These pg_xlog files are, as you said, 16MB files that store all the transactions made to your database – 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 = “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 –HUP 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 with:

 

Archive_command = “rm –f %p”

 

Or

 

Archive_command = “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 = off

debug_level = 0
log_connections = off
log_pid = on
log_timestamp = on
syslog = 1
# if syslog is 0, turn silent_mode off!
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 64
# shared_buffers must be at least twice max_connections, and not less than 16
shared_buffers = 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 = on
sort_mem = 512
            

 

------

 

cu

 

!DSPAM:14,449a31b6256849416340232!

Re: 2,2gb of pg_xlog ??

От
Alvaro Herrera
Дата:
Andy Shellam wrote:

> Archive_command = "mv %p /your/backup/directory/%f"

Don't do this.  Postgres itself will remove the file after the
archive_command has copied it elsewhere.

> 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 with:
>
> Archive_command = "rm -f %p"

Don't do this either.  If you're not using PITR, just set a blank
archive_command and Postgres will delete the file as soon as it's not
needed anymore.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: 2,2gb of pg_xlog ??

От
"Chris Hoover"
Дата:
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 and 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 <andy@andycc.net > wrote:

Hi Stefan,

 

These pg_xlog files are, as you said, 16MB files that store all the transactions made to your database – 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 = "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 –HUP 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 with:

 

Archive_command = "rm –f %p"

 

Or

 

Archive_command = "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 = off

debug_level = 0
log_connections = off
log_pid = on
log_timestamp = on
syslog = 1
# if syslog is 0, turn silent_mode off!
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 64
# shared_buffers must be at least twice max_connections, and not less than 16
shared_buffers = 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 = on
sort_mem = 512
            

 

------

 

cu

 

!DSPAM:14,449a31b6256849416340232!


Re: 2,2gb of pg_xlog ??

От
"Andy Shellam"
Дата:
> Don't do this.  Postgres itself will remove the file after the
> archive_command has copied it elsewhere.

In my 8.1.3 install, it didn't - when I specified a cp command, it left the
files there.  I had to define the command as "mv" to have them moved.

> Don't do this either.  If you're not using PITR, just set a blank
> archive_command and Postgres will delete the file as soon as it's not
> needed anymore.

Why not, what's the difference?  Surely Postgres just calls a similar "rm"
kernel call to remove the file anyway?

> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of Alvaro Herrera
> Sent: 22 June 2006 3:03 pm
> To: andy.shellam@mailnetwork.co.uk
> Cc: Stefan.Schmidt@schinkel.de; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] 2,2gb of pg_xlog ??
>
> Andy Shellam wrote:
>
> > Archive_command = "mv %p /your/backup/directory/%f"
>
> Don't do this.  Postgres itself will remove the file after the
> archive_command has copied it elsewhere.
>
> > 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
> with:
> >
> > Archive_command = "rm -f %p"
>
> Don't do this either.  If you're not using PITR, just set a blank
> archive_command and Postgres will delete the file as soon as it's not
> needed anymore.
>
> --
> Alvaro Herrera
> http://www.CommandPrompt.com/
> PostgreSQL Replication, Consulting, Custom Development, 24x7 support
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
> !DSPAM:14,449aa37b256841438716522!
>

Re: 2,2gb of pg_xlog ??

От
"Andy Shellam"
Дата:

I agree about the upgrade, surely there was a way of archiving the transaction logs in 7.2, otherwise with very large databases you could have transaction logs backing up over tens of GBs, and no way to rid of them!

 

 

From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Chris Hoover
Sent: 22 June 2006 3:12 pm
To: andy.shellam@mailnetwork.co.uk
Cc: Stefan.Schmidt@schinkel.de; pgsql-admin@postgresql.org
Subject: Re: [ADMIN] 2,2gb of pg_xlog ??

 

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 and 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 <andy@andycc.net > wrote:

Hi Stefan,

 

These pg_xlog files are, as you said, 16MB files that store all the transactions made to your database – 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 = "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 –HUP 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 with:

 

Archive_command = "rm –f %p"

 

Or

 

Archive_command = "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 = off

debug_level = 0
log_connections = off
log_pid = on
log_timestamp = on
syslog = 1
# if syslog is 0, turn silent_mode off!
silent_mode = off
syslog_facility = LOCAL0
trace_notify = off
max_connections = 64
# shared_buffers must be at least twice max_connections, and not less than 16
shared_buffers = 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 = on
sort_mem = 512
            

 

------

 

cu

 


!DSPAM:14,449aa5c7256843821515230!

Re: 2,2gb of pg_xlog ??

От
Tom Lane
Дата:
"Andy Shellam" <andy@andycc.net> writes:
>> Don't do this.  Postgres itself will remove the file after the
>> archive_command has copied it elsewhere.

> In my 8.1.3 install, it didn't - when I specified a cp command, it left the
> files there.  I had to define the command as "mv" to have them moved.

If it left the files there, it had a reason to (probably, that it wanted
to rename them for use as new WAL segments).  It is NOT NOT NOT the job
of the archive_command to do anything except copy the data somewhere else.

Back to the OP's problem: failure to recycle WAL segments requires some
active looking into, not mindless removal of files.  The only
explanations I can think of are that he's got the checkpoint interval
settings at wacko values, or more likely something is preventing
checkpoints from completing; if so, it's desperately important to find
out what and fix it.  Manual file removal is addressing a symptom not
the problem.

            regards, tom lane

Re: 2,2gb of pg_xlog ??

От
"Andy Shellam"
Дата:
When I'm saying it left the files there, it left ALL the files there - over
3 weeks worth, and it kept producing new WAL files.

The archive command was succeeding as I was getting a copy in my backup
directory, and I was getting through an average of 3-4 files a day, so
surely if it wanted to recycle the original files it would have done by
then, rather than keep creating new files?  The checkpoint settings were all
at default values.

Thanks for clarifying the archive command's job - I hadn't realised WAL
segments are recycled, due to the above issue.


> -----Original Message-----
> From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-
> owner@postgresql.org] On Behalf Of Tom Lane
> Sent: 22 June 2006 3:22 pm
> To: andy.shellam@mailnetwork.co.uk
> Cc: 'Alvaro Herrera'; pgsql-admin@postgresql.org
> Subject: Re: [ADMIN] 2,2gb of pg_xlog ??
>
> "Andy Shellam" <andy@andycc.net> writes:
> >> Don't do this.  Postgres itself will remove the file after the
> >> archive_command has copied it elsewhere.
>
> > In my 8.1.3 install, it didn't - when I specified a cp command, it left
> the
> > files there.  I had to define the command as "mv" to have them moved.
>
> If it left the files there, it had a reason to (probably, that it wanted
> to rename them for use as new WAL segments).  It is NOT NOT NOT the job
> of the archive_command to do anything except copy the data somewhere else.
>
> Back to the OP's problem: failure to recycle WAL segments requires some
> active looking into, not mindless removal of files.  The only
> explanations I can think of are that he's got the checkpoint interval
> settings at wacko values, or more likely something is preventing
> checkpoints from completing; if so, it's desperately important to find
> out what and fix it.  Manual file removal is addressing a symptom not
> the problem.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
> !DSPAM:14,449aa805256841107475365!
>