Re: PGXLOG variable worthwhile?

I do.

The problem is that if you change the location of pg_xlog and do one thing
wrong, poof, your database is now corrupt. Like Tom said earlier, imagine
a command like switch called "please-dont-scram-my-database" and if you
ever forgot it then your data is gone.

Is it better to move such a switch into the postgresql.conf file? Imagine
a GUC setting called "butter-and-bread" that when set would delete all
your data. That's what the equivalent here is, if you make a single
mistake.

Having a FILE called pg_xlog isn't the fix here, it's the result of the
fix, which is to take all the steps of moving the pg_xlog directory and
put them into one script file the user doesn't need to understand to do it
right. I.e. idiot proof the system as much as possible.

We could do it much simpler, if everyone was on Unix. We could just write
a script that would do everything the same but instead of using a file
called pg_xlog, would make a link. the reason for the file is to make it
more transportable to brain damaged OSes like Windows.

Do you really think the GUC variable is a safe way of referencing the
pg_xlog directory all by itself? I can see MANY posts to the lists that
will go like this:

I just installed Postgresql 7.4 and it's been working fine. I needed more
speed, so I looked up the GUC for the pg_xlog and set it to /vol/vol3/ on
my machine. Now my database won't come up. I set it back but it still
won't come up. What can I do to fix that?

Here's the email we'd get from my solution:

Hey, I just tried to move my pg_xlog directory with the mvpgxlog script,
and it gave an error of "permission denied on destination". What does that
mean?

The choice is yours.

On Wed, 25 Sep 2002, Bruce Momjian wrote:

>
> I don't see the gain of having a file called pg_xlog vs. using GUC.
>
> ---------------------------------------------------------------------------
>
> scott.marlowe wrote:
> > 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?
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>