Thread: turning of pg_xlog
Hi, I have installes Postgres 8.0.15 on a embedded Linux and have only 130 MB for Postgres. Is it possible to turn off the comlete (Wal) pg_xlog? Because this is the biggest part. I found an Entry how to minimize the count of stored xlogs. Is it possible to store it to /dev/null or something else? Something like ln -s /dev/null pg_xlog/ (I know this does not work :-) .... regards, Jonny
On Mon, Sep 29, 2008 at 01:00:41PM +0200, Jonny wrote: > Is it possible to turn off the comlete (Wal) pg_xlog? No. A -- Andrew Sullivan ajs@commandprompt.com +1 503 667 4564 x104 http://www.commandprompt.com/
Jonny wrote: > Hi, > > I have installes Postgres 8.0.15 on a embedded Linux and have only 130 > MB for Postgres. > Is it possible to turn off the comlete (Wal) pg_xlog? Because this is > the biggest part. > I found an Entry how to minimize the count of stored xlogs. > Is it possible to store it to /dev/null or something else? > Something like ln -s /dev/null pg_xlog/ (I know this does not work > :-) .... You should also understand that if you set checkpoint_segments to a small number, its still possible that PostgreSQL might use more than that number for a large transaction. As such, be careful about limiting the space available to your xlogs, lest it cause transactions to fail due to insufficient space for the xlogs. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com
Chander Ganesan <chander@otg-nc.com> writes: > You should also understand that if you set checkpoint_segments to a > small number, its still possible that PostgreSQL might use more than > that number for a large transaction. "Large transactions" have nothing to do with this. You are confusing Postgres' implementation with Oracle's. regards, tom lane
Tom Lane wrote: > Chander Ganesan <chander@otg-nc.com> writes: > >> You should also understand that if you set checkpoint_segments to a >> small number, its still possible that PostgreSQL might use more than >> that number for a large transaction. >> > > "Large transactions" have nothing to do with this. You are confusing > Postgres' implementation with Oracle's. > > regards, tom lane Okay. I guess my use of the word "transaction" here was erroneous - probably comes from my oracle background :-( . However, the fact remains that its possible to exceed checkpoint_segments in certain scenarios (I think the docs refer to it as a peak in log creation or something like that), which - if it fills up the disk that the WALs are on, can result in issues... And one should be wary of that when trying to limit the number of WALs in order to meet limited disk space requirements. -- Chander Ganesan Open Technology Group, Inc. One Copley Parkway, Suite 210 Morrisville, NC 27560 919-463-0999/877-258-8987 http://www.otg-nc.com
I might have to disagree here. I personally experienced a scenario where I had 500 MB of file space defined for my WAL log files and then attempted (not thinking it through thoroughly) to perform a COPY of a very large (1.8 GB) dataset and kept adding WAL files until my file system filled up and then the instance crashed. -----Original Message----- From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Tom Lane Sent: Tuesday, September 30, 2008 1:45 PM To: Chander Ganesan Cc: Jonny; pgsql-admin@postgresql.org Subject: Re: [ADMIN] turning of pg_xlog Chander Ganesan <chander@otg-nc.com> writes: > You should also understand that if you set checkpoint_segments to a > small number, its still possible that PostgreSQL might use more than > that number for a large transaction. "Large transactions" have nothing to do with this. You are confusing Postgres' implementation with Oracle's. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin