From: Uwe Schroeder <uwe@oss4u.com>
Organization: OSS4U, LLC
To: pgsql-admin@postgresql.org
Subject: Re: error log, tablespace, wal files
Date: Wed, 9 Nov 2011 23:36:24 -0800
User-Agent: KMail/1.13.5 (Linux/2.6.35-25-paeoss; KDE/4.5.1; i686; ; )
References: 
 <CAC-Lqbpzumfr4zW4+4eca90z5-kEmEaR_cJ0e-s7a=a_E=n0WA@mail.gmail.com>
In-Reply-To: 
 <CAC-Lqbpzumfr4zW4+4eca90z5-kEmEaR_cJ0e-s7a=a_E=n0WA@mail.gmail.com>
MIME-Version: 1.0
Content-Type: Text/Plain;
  charset="us-ascii"
Content-Transfer-Encoding: 7bit
Message-Id: <201111092336.24858.uwe@oss4u.com>


> Hello,
> 
>      1) what is tablespace in postgres?
> 
>      2) one more issue is that, I try to rename or delete some file in
> /opt/PostgresPlus/9.0/data/base/16428/ directory.  when I restart postgres
> server, it start normally.  but I thing this is wrong.  I rename or delete
> file into above directory, that means my database(having 16428 oid) is
> corrupted.  So How can my postgres server is started normally?? I want to
> see the log file for that database corruption. Can I see it?? and where???
> and please explain me that what's going on in above case??????
> 
>      3) I want know about pg_xlog(wal) file creation. That means, size of
> wal files and how to create that files and time stamp between creation of
> two different wal files, that means when one file is created on 11:53AM, so
> how much time  after the second file is created?? can we set that time
> stamp???

There are people here better qualified to answer this, but I'll take a shot 
anyways.

1) tablespaces allow you to physically store tables in different directories 
(meaning different disks). So if you have a table that contains gazillions of 
records and needs a lot of space, you could add a disk for that, mount it to 
some directory on your server and create a tablespace on the disk. Then you'd 
tell postgresql to store the large table(s) in that tablespace. 

2) I think - and I certainly may be wrong here - that you will encounter the  
error only when postgresql tries to access data that should be in those files. 
It's kind of like misplacing your car keys - you'll only notice when you need 
them.

3) This depends a lot on your settings in postgresql.conf. To my understanding 
WAL files are majorly depending on the amount of data written to them and not 
based on a time period. If your database does mostly reads, no WAL log will be 
needed, thus the files allocated don't fill up quickly. If you have a lot of 
write operations, the files will fill quickly and once full the next file (up to 
number of WAL files configured) will be created. This one I'm even more fuzzy on 
than #2 - so someone here with more knowledge of the internals may set me 
right.