Re: PGXLOG variable worthwhile? - Mailing list pgsql-hackers
From | scott.marlowe |
---|---|
Subject | Re: PGXLOG variable worthwhile? |
Date | |
Msg-id | Pine.LNX.4.33.0209250839010.22525-100000@css120.ihs.com Whole thread Raw |
In response to | Re: PGXLOG variable worthwhile? (Curt Sampson <cjs@cynic.net>) |
Responses |
Re: PGXLOG variable worthwhile?
(Bruce Momjian <pgman@candle.pha.pa.us>)
|
List | pgsql-hackers |
On Wed, 25 Sep 2002, Curt Sampson wrote: > On Tue, 24 Sep 2002, Jan Wieck wrote: > > > And AFAICS it is scary only because screwing that up will simply corrupt > > your database. Thus, a simple random number (okay, and a timestamp of > > initdb) in two files, one in $PGDATA and one in $PGXLOG would be a > > totally sufficient safety mechanism to prevent starting with the wrong > > XLOG directory. > > But still, why set up a situation where your database might not > start? Why not set it up so that if you get just *one* environment > or command-line variable right, you can't set another inconsistently > and screw up your start anyway? Why store configuration information > outside of the database data directory in a form that's not easily > backed up, and not easily found by other utilities? > > It's almost like people *don't* want to put this in the config file > or something.... Curt, did you see my post about this earlier? I'll repeat it now, just in case anyone else missed it. Problem: - People need to move the pg_xlog directory around on heavily loaded systems to improve performance Constraints: - Windows can't reliably use links to do this. - If the pg_xlog directory is moved wrong or referenced incorrectly, data corruption may occur. This makes using a switch or environmental var dangerous I consider using a GUC in the postgresql.conf file to be better than any other option listed so far, but it is still a dangerous place for it to be. So, the way I think that would work best would be: If there's a directory called pg_xlog in the $PGDATA directory, then use that. If there's a file called pg_xlog in the $PGDATA directory, then it will contain the path to the real pg_xlog directory. If you want to move the pg_xlog directory, you called a custom script called "mvpgxlog" or something like it that: 1: Checks to make sure the database is shut down 2: Checks to make sure the destination path has enough free space for the xlogs 3: If these are both true (and whatever logic we need here for safety) then copy the current pg_xlog directory contents to the new pg_xlog (even if we are already using an alternative location, this should work), set proper permissions, rename / move the pg_xlog file / directorry, then edit/create the $PGDATA/pg_xlog file to point to the new directory. This method has several advantages, and no real disadvantages I can think of. The advantages are: - It makes it easy to move the pg_xlog directory. - It works equally well for Windows and Unix. - Gets rid of another GUC setting people can scram their database with. - It is easy to backup your pg_xlog setting. - If painted green it should not rust. How's that sound for a general theory of operation?
pgsql-hackers by date: