Thread: How to reduce WAL file Size
By setting postgres.conf params...log files are generated of 16 MB each. How can I reduce the size of this files? & can I force the log files to switch with any command as we do in mysql like flush log? where we force the new log creation ? -- View this message in context: http://www.nabble.com/How-to-reduce-WAL-file-Size-tp25799623p25799623.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
> By setting postgres.conf params...log files are generated of 16 MB each. How
> can I reduce the size of this files? & can I force the log files to switch
> with any command as we do in mysql like flush log? where we force the new
> log creation ?
pg_switch_xlog() is the switch function.
The option
--with-wal-segsize=SEGSIZE
set WAL segment size in MB [16]
when you compile postgresql allow you to change the size of these files (but you
can't do it only restarting :S )
--
Emanuel Calvo Franco
DBA at: www.siu.edu.ar
www.emanuelcalvofranco.com.ar
On 8 Oct 2009, at 9:35, Mitesh51 wrote: > By setting postgres.conf params...log files are generated of 16 MB > each. How > can I reduce the size of this files? & can I force the log files to > switch > with any command as we do in mysql like flush log? where we force > the new > log creation ? Why do you want to reduce the WAL file size? What problem are you trying to solve? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4ace7bd211681560221775!
I am dealing with backup of databases. Now eventhough there are not many changes in the DB, it creates WAL file of 16 mb. Now because I am working on a application which takes full & inc backup of db after specific intervals..if for small changes it creates 16 mb files then storing them will eat up lot many space. As I am not having DB knowledge I am just assuming that it will create problems in terms of storage space. That is my concern. If you will explain this thing then it will help me in a good way :) Thank you. Alban Hertroys-3 wrote: > > On 8 Oct 2009, at 9:35, Mitesh51 wrote: > >> By setting postgres.conf params...log files are generated of 16 MB >> each. How >> can I reduce the size of this files? & can I force the log files to >> switch >> with any command as we do in mysql like flush log? where we force >> the new >> log creation ? > > > Why do you want to reduce the WAL file size? What problem are you > trying to solve? > > Alban Hertroys > > -- > If you can't see the forest for the trees, > cut the trees and you'll see there is no forest. > > > !DSPAM:737,4ace7bd211681560221775! > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/How-to-reduce-WAL-file-Size-tp25799623p25816266.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On 9 Oct 2009, at 8:48, Mitesh51 wrote: > I am dealing with backup of databases. Now eventhough there are not > many > changes in the DB, it creates WAL file of 16 mb. Now because I am > working on > a application which takes full & inc backup of db after specific > intervals..if for small changes it creates 16 mb files then storing > them > will eat up lot many space. As I am not having DB knowledge I am just > assuming that it will create problems in terms of storage space. > That is my > concern. > > If you will explain this thing then it will help me in a good way :) I thought as much, you can't just make a file-system backup of the WAL files and expect you'll be able to recover your database from that. Unless you take special measures your chances are minimal and the method is inferior to the other possible approaches. Backups are explained at length in the documentation, see http://www.postgresql.org/docs/8.4/static/backup.html If your backup system can handle block- or byte-level incremental backups it's probably easiest to just do a database dump to a fixed file at regular intervals. The backup software will just backup the differences since last backup after all. Another common strategy is to use PITR (point in time recovery) for your backups. P.S. Please try to avoid top-posting. > Alban Hertroys-3 wrote: What's this? Did my name change suddenly? Or is yahoo-mail a bad mail client? >> On 8 Oct 2009, at 9:35, Mitesh51 wrote: >> >>> By setting postgres.conf params...log files are generated of 16 MB >>> each. How >>> can I reduce the size of this files? & can I force the log files to >>> switch >>> with any command as we do in mysql like flush log? where we force >>> the new >>> log creation ? >> >> >> Why do you want to reduce the WAL file size? What problem are you >> trying to solve? Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll see there is no forest. !DSPAM:737,4acf013d11687582812586!
On Thu, Oct 08, 2009 at 11:48:29PM -0700, Mitesh51 wrote: > I am dealing with backup of databases. Now eventhough there are not many > changes in the DB, it creates WAL file of 16 mb. Now because I am working on > a application which takes full & inc backup of db after specific > intervals..if for small changes it creates 16 mb files then storing them > will eat up lot many space. I think the following utility would help you here: http://pgfoundry.org/projects/clearxlogtail/ Also I'd recommend reading the articles Alban pointed to, it can be somewhat tricky to backup PG correctly. -- Sam http://samason.me.uk/
Alban Hertroys wrote: > On 9 Oct 2009, at 8:48, Mitesh51 wrote: > >Alban Hertroys-3 wrote: > > What's this? Did my name change suddenly? Or is yahoo-mail a bad > mail client? Mystifying eh? It's nabble.com's way of identifying unique posters. You have probably posted from other three different email addresses. Following the "see this post in context" link at the bottom would show you more, if you care about that sort of stuff. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support