Re: [COMMITTERS] pgsql-server/src backend/tcop/postgres.c backe ...

CVSROOT: /cvsroot
Module name: pgsql-server
Changes by: thomas(at)postgresql(dot)org 02/08/04 02:26:38

Modified files:
src/backend/tcop: postgres.c
src/backend/bootstrap: bootstrap.c
src/backend/postmaster: postmaster.c
src/bin/initdb : initdb.sh
src/bin/pg_ctl : pg_ctl.sh
src/include/access: xlog.h

Log message:
Implement WAL log location control using "-X" or PGXLOG.

Thomas Lockhart wrote:
> CVSROOT: /cvsroot
> Module name: pgsql-server
> Changes by: thomas(at)postgresql(dot)org 02/08/04 02:26:38
>
> Modified files:
> src/backend/tcop: postgres.c
> src/backend/bootstrap: bootstrap.c
> src/backend/postmaster: postmaster.c
> src/bin/initdb : initdb.sh
> src/bin/pg_ctl : pg_ctl.sh
> src/include/access: xlog.h
>
> Log message:
> Implement WAL log location control using "-X" or PGXLOG.

Woh, I didn't think we had agreement on this. This populates the 'X'
all over the system (postgres, postmaster, initdb, pg_ctl), while the
simple solution would be to add the flag only to initdb and use a
symlink from /data. I also think it is less error-prone because you
can't accidentally point to the wrong XLOG directory. In fact, you
almost have to use an environment variable unles you plan to specify -X
for all the commands. In my mind, PGDATA should take care of the whole
thing for pointing to your data.

If you want to do it this way, I request a vote.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Thomas Lockhart wrote:
>> Implement WAL log location control using "-X" or PGXLOG.

> Woh, I didn't think we had agreement on this. This populates the 'X'
> all over the system (postgres, postmaster, initdb, pg_ctl), while the
> simple solution would be to add the flag only to initdb and use a
> symlink from /data. I also think it is less error-prone because you
> can't accidentally point to the wrong XLOG directory. In fact, you
> almost have to use an environment variable unles you plan to specify -X
> for all the commands. In my mind, PGDATA should take care of the whole
> thing for pointing to your data.

> If you want to do it this way, I request a vote.

I have to vote a strong NO on this. What the patch essentially does is
to decouple specification of the data directory ($PGDATA or -D) from the
xlog directory ($PGXLOG or -X). This might seem nice and clean and
symmetrical, but in fact it has no conceivable use except for shooting
yourself in the foot in a particularly nasty manner. The xlog *cannot*
be decoupled from the data directory --- there are pointers in
pg_control and in every single data page that depend on the state of
xlog. Trying to make them look independent is just a recipe for
breaking your database by starting the postmaster with the wrong
combination of PGDATA and PGXLOG. And I'm not at all sure it'll be
possible to recover after you do that: if the postmaster notices the
discrepancy, it is likely to try to fix it by rolling forward from the
last checkpoint it can find in the mismatching xlog. Oops :-(

I think the existing mechanism of using a symlink in the data directory
when you want to move xlog is far safer and more reliable. I do not see
what functionality is added by this patch that can possibly justify the
hazards it creates.

regards, tom lane

Can I propose a compromise? Thomas, can we have only initdb honor
PGXLOG, just like it honors PGDATA? That way, admins can set PGXLOG and
initdb will take care of the symlinking for them, and we don't have to
push PGXLOG/-X down into all the other server parts. How do people like
that?

As far as tablespaces, we have three ways of storing directory path
info: environment variables, in the database, or in symlinks. If we
choose the first or last options, XLOG will have to follow that plan.
If we choose database, well there is no database during initdb so XLOG
would have a different system of specifying the location. I think
having only initdb honor PGXLOG and using a symlink will give us maximum
flexibility when we decide on table spaces.

Actually, as I remember, there was a strong vote that tablespaces where
going to use symlinks for storage, at least in some way.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Thomas Lockhart wrote:
> >> Implement WAL log location control using "-X" or PGXLOG.
>
> > Woh, I didn't think we had agreement on this. This populates the 'X'
> > all over the system (postgres, postmaster, initdb, pg_ctl), while the
> > simple solution would be to add the flag only to initdb and use a
> > symlink from /data. I also think it is less error-prone because you
> > can't accidentally point to the wrong XLOG directory. In fact, you
> > almost have to use an environment variable unles you plan to specify -X
> > for all the commands. In my mind, PGDATA should take care of the whole
> > thing for pointing to your data.
>
> > If you want to do it this way, I request a vote.
>
> I have to vote a strong NO on this. What the patch essentially does is
> to decouple specification of the data directory ($PGDATA or -D) from the
> xlog directory ($PGXLOG or -X). This might seem nice and clean and
> symmetrical, but in fact it has no conceivable use except for shooting
> yourself in the foot in a particularly nasty manner. The xlog *cannot*
> be decoupled from the data directory --- there are pointers in
> pg_control and in every single data page that depend on the state of
> xlog. Trying to make them look independent is just a recipe for
> breaking your database by starting the postmaster with the wrong
> combination of PGDATA and PGXLOG. And I'm not at all sure it'll be
> possible to recover after you do that: if the postmaster notices the
> discrepancy, it is likely to try to fix it by rolling forward from the
> last checkpoint it can find in the mismatching xlog. Oops :-(
>
> I think the existing mechanism of using a symlink in the data directory
> when you want to move xlog is far safer and more reliable. I do not see
> what functionality is added by this patch that can possibly justify the
> hazards it creates.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Can I propose a compromise? Thomas, can we have only initdb honor
> PGXLOG, just like it honors PGDATA? That way, admins can set PGXLOG and
> initdb will take care of the symlinking for them, and we don't have to
> push PGXLOG/-X down into all the other server parts.

That seems reasonable to me.

regards, tom lane

Thomas, have you commented on the objections to this patch? If so, I
didn't see it.

---------------------------------------------------------------------------

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > Thomas Lockhart wrote:
> >> Implement WAL log location control using "-X" or PGXLOG.
>
> > Woh, I didn't think we had agreement on this. This populates the 'X'
> > all over the system (postgres, postmaster, initdb, pg_ctl), while the
> > simple solution would be to add the flag only to initdb and use a
> > symlink from /data. I also think it is less error-prone because you
> > can't accidentally point to the wrong XLOG directory. In fact, you
> > almost have to use an environment variable unles you plan to specify -X
> > for all the commands. In my mind, PGDATA should take care of the whole
> > thing for pointing to your data.
>
> > If you want to do it this way, I request a vote.
>
> I have to vote a strong NO on this. What the patch essentially does is
> to decouple specification of the data directory ($PGDATA or -D) from the
> xlog directory ($PGXLOG or -X). This might seem nice and clean and
> symmetrical, but in fact it has no conceivable use except for shooting
> yourself in the foot in a particularly nasty manner. The xlog *cannot*
> be decoupled from the data directory --- there are pointers in
> pg_control and in every single data page that depend on the state of
> xlog. Trying to make them look independent is just a recipe for
> breaking your database by starting the postmaster with the wrong
> combination of PGDATA and PGXLOG. And I'm not at all sure it'll be
> possible to recover after you do that: if the postmaster notices the
> discrepancy, it is likely to try to fix it by rolling forward from the
> last checkpoint it can find in the mismatching xlog. Oops :-(
>
> I think the existing mechanism of using a symlink in the data directory
> when you want to move xlog is far safer and more reliable. I do not see
> what functionality is added by this patch that can possibly justify the
> hazards it creates.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> Thomas, have you commented on the objections to this patch? If so, I
> didn't see it.

Yes, there was quite a long thread on this.

- Thomas

Thomas, would you remind me of the concusions because I thought everyone
involved felt that it should be an initdb-only option, but I still see
it in CVS.

---------------------------------------------------------------------------

Thomas Lockhart wrote:
> > Thomas, have you commented on the objections to this patch? If so, I
> > didn't see it.
>
> Yes, there was quite a long thread on this.
>
> - Thomas
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> Thomas, would you remind me of the concusions because I thought everyone
> involved felt that it should be an initdb-only option, but I still see
> it in CVS.

?? "Concussions" as in brain bruises? ;)

I'm not sure I understand the question. I assume that we are talking
about the WAL log location feature I implemented recently. It is an
initdb-only option, and defaults to the current behavior *exactly*.

The new feature is to allow an argument to initdb to locate the WAL file
to another location. That location can be specified on the command line,
or through an environment variable. Neither form precludes use of the
other, and either form can be considered "best practice" depending on
your opinion of what that is.

The postmaster also recognizes the command line option and environment
variable. The only suggestion I got as an alternative involved soft
links, which is not portable, which is not robust, and which is not used
anywhere else in the system. If we moved toward relying on soft links
for distributing resources we will be moving in the wrong direction for
many reasons, some of which I've mentioned previously. GUC parameters
were also mentioned as a possibility, and the infrastructure does not
preclude that at any time.

I don't recall that there were very many folks "involved". There were
several opinions, though most were from folks who were not thinking of
implementing disk management features. Some opinions dealt with details,
and some seemed to deal with the wisdom of allowing anything other than
a "one partition" model of the database, which is nothing if not short
sighted. Current default behavior is as first implemented, and the new
feature allows locating the WAL logs in another area. For the current
state of the art, that seems competitive with features found in other
database products, and an essential step in teaching PostgreSQL to work
with very large databases.

I had thought to extend the capabilities to allow resource allocation
for individual tables and indices, which has *long* been identified as a
desired capability by folks who are managing large systems. It seemed
reasonable to have done in time for 7.3. I'm rethinking that, not
because it shouldn't happen, but because the process of discussing these
issues has become so argumentative, divisive, impolite, and unpleasant.
Which is a shame imho...

- Thomas

Thomas Lockhart wrote:
> > Thomas, would you remind me of the concusions because I thought everyone
> > involved felt that it should be an initdb-only option, but I still see
> > it in CVS.
>
> ?? "Concussions" as in brain bruises? ;)

Uh, conclusions. Sorry. New keyboard desk in new house. :-)

> I'm not sure I understand the question. I assume that we are talking
> about the WAL log location feature I implemented recently. It is an
> initdb-only option, and defaults to the current behavior *exactly*.

Yep. What bothers me is the clutter to the other commands that allow
XLOG location specification when you would never really want to specify
it except as part of initdb. I just see those extra flags as
cruft/confusion.

Look at pg_ctl:

pg_ctl start [-w] [-D DATADIR] [-s] [-X PGXLOG] [-l FILENAME] [-o "OPTIONS"]

Which option doesn't make sense? -X. It is way beyond the
functionality of the command.

> The new feature is to allow an argument to initdb to locate the WAL file
> to another location. That location can be specified on the command line,
> or through an environment variable. Neither form precludes use of the
> other, and either form can be considered "best practice" depending on
> your opinion of what that is.
>
> The postmaster also recognizes the command line option and environment
> variable. The only suggestion I got as an alternative involved soft
> links, which is not portable, which is not robust, and which is not used
> anywhere else in the system. If we moved toward relying on soft links
> for distributing resources we will be moving in the wrong direction for
> many reasons, some of which I've mentioned previously. GUC parameters
> were also mentioned as a possibility, and the infrastructure does not
> preclude that at any time.

I don't think anyone agreed with your concerns about symlinks. If you
want to be careful, do the "ln -s" in initdb and exit on failure, and
tell them not to use -X on that platform, though we use symlinks for
postmaster/postgres identification, so I know the only OS that doesn't
support symlinks is Netware, only because Netware folks just sent in a
patch to add a -post flag to work around lack of symlinks. (I have
asked for clarification from them.)

I actually requested a vote, and got several people who wanted my
compromise (PGXLOG or initdb -X flag only), and I didn't see anyone who
liked the addition of -X into non-initdb commands. Should I have a
specific vote? OK, three options:

1) -X, PGXLOG in initdb, postmaster, postgres, pg_ctl
2) -X, PGXLOG in initdb only
3) nothing

I remember a number of people liking 2, but we can vote again.

> I don't recall that there were very many folks "involved". There were
> several opinions, though most were from folks who were not thinking of
> implementing disk management features. Some opinions dealt with details,
> and some seemed to deal with the wisdom of allowing anything other than
> a "one partition" model of the database, which is nothing if not short
> sighted. Current default behavior is as first implemented, and the new
> feature allows locating the WAL logs in another area. For the current
> state of the art, that seems competitive with features found in other
> database products, and an essential step in teaching PostgreSQL to work
> with very large databases.
>
> I had thought to extend the capabilities to allow resource allocation
> for individual tables and indices, which has *long* been identified as a
> desired capability by folks who are managing large systems. It seemed
> reasonable to have done in time for 7.3. I'm rethinking that, not
> because it shouldn't happen, but because the process of discussing these
> issues has become so argumentative, divisive, impolite, and unpleasant.
> Which is a shame imho...

I clearly want tablespaces, and it would be great for 7.3, and I don't
think it is a huge job. However, I think it will require symlinks to be
usable, and you probably do not, so it may be an issue.

As for the argumentativeness, we do have folks with some strong
opinions, and I guess on the PGXLOG issue, I am one of them. Maybe that
is bad? Are people expressing themselves badly? If so, I would like to
hear details either on list or privately so I can address them.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, Aug 09, 2002 at 06:54:44PM -0700, Thomas Lockhart wrote:

> I had thought to extend the capabilities to allow resource allocation
> for individual tables and indices, which has *long* been identified as a
> desired capability by folks who are managing large systems.

Without wishing to pour fuel on any fires, or advocate any
implementation, I can say for sure that this is very much a feature
we'd love to see.

A

--
----
Andrew Sullivan 87 Mowat Avenue
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M6K 3E3
+1 416 646 3304 x110

OK, seeing as no one voted, and only Tom and I objected originally, we
will keep the code as Thomas has applied it, namely that PGXLOG/-X is
recognized by initdb, postmaster, postgres, and pg_ctl. There is no
symlink from the /data directory to the WAL location.

Thomas, you mentioned implementing table spaces. Are you planning to
use environment variables there too? You mentioned that Ingres's use of
environment variables wasn't well implemented. How will you improve on
it?

---------------------------------------------------------------------------

Bruce Momjian wrote:
> Thomas Lockhart wrote:
> > > Thomas, would you remind me of the concusions because I thought everyone
> > > involved felt that it should be an initdb-only option, but I still see
> > > it in CVS.
> >
> > ?? "Concussions" as in brain bruises? ;)
>
> Uh, conclusions. Sorry. New keyboard desk in new house. :-)
>
> > I'm not sure I understand the question. I assume that we are talking
> > about the WAL log location feature I implemented recently. It is an
> > initdb-only option, and defaults to the current behavior *exactly*.
>
> Yep. What bothers me is the clutter to the other commands that allow
> XLOG location specification when you would never really want to specify
> it except as part of initdb. I just see those extra flags as
> cruft/confusion.
>
> Look at pg_ctl:
>
> pg_ctl start [-w] [-D DATADIR] [-s] [-X PGXLOG] [-l FILENAME] [-o "OPTIONS"]
>
> Which option doesn't make sense? -X. It is way beyond the
> functionality of the command.
>
>
> > The new feature is to allow an argument to initdb to locate the WAL file
> > to another location. That location can be specified on the command line,
> > or through an environment variable. Neither form precludes use of the
> > other, and either form can be considered "best practice" depending on
> > your opinion of what that is.
> >
> > The postmaster also recognizes the command line option and environment
> > variable. The only suggestion I got as an alternative involved soft
> > links, which is not portable, which is not robust, and which is not used
> > anywhere else in the system. If we moved toward relying on soft links
> > for distributing resources we will be moving in the wrong direction for
> > many reasons, some of which I've mentioned previously. GUC parameters
> > were also mentioned as a possibility, and the infrastructure does not
> > preclude that at any time.
>
> I don't think anyone agreed with your concerns about symlinks. If you
> want to be careful, do the "ln -s" in initdb and exit on failure, and
> tell them not to use -X on that platform, though we use symlinks for
> postmaster/postgres identification, so I know the only OS that doesn't
> support symlinks is Netware, only because Netware folks just sent in a
> patch to add a -post flag to work around lack of symlinks. (I have
> asked for clarification from them.)
>
> I actually requested a vote, and got several people who wanted my
> compromise (PGXLOG or initdb -X flag only), and I didn't see anyone who
> liked the addition of -X into non-initdb commands. Should I have a
> specific vote? OK, three options:
>
> 1) -X, PGXLOG in initdb, postmaster, postgres, pg_ctl
> 2) -X, PGXLOG in initdb only
> 3) nothing
>
> I remember a number of people liking 2, but we can vote again.
>
> > I don't recall that there were very many folks "involved". There were
> > several opinions, though most were from folks who were not thinking of
> > implementing disk management features. Some opinions dealt with details,
> > and some seemed to deal with the wisdom of allowing anything other than
> > a "one partition" model of the database, which is nothing if not short
> > sighted. Current default behavior is as first implemented, and the new
> > feature allows locating the WAL logs in another area. For the current
> > state of the art, that seems competitive with features found in other
> > database products, and an essential step in teaching PostgreSQL to work
> > with very large databases.
> >
> > I had thought to extend the capabilities to allow resource allocation
> > for individual tables and indices, which has *long* been identified as a
> > desired capability by folks who are managing large systems. It seemed
> > reasonable to have done in time for 7.3. I'm rethinking that, not
> > because it shouldn't happen, but because the process of discussing these
> > issues has become so argumentative, divisive, impolite, and unpleasant.
> > Which is a shame imho...
>
> I clearly want tablespaces, and it would be great for 7.3, and I don't
> think it is a huge job. However, I think it will require symlinks to be
> usable, and you probably do not, so it may be an issue.
>
> As for the argumentativeness, we do have folks with some strong
> opinions, and I guess on the PGXLOG issue, I am one of them. Maybe that
> is bad? Are people expressing themselves badly? If so, I would like to
> hear details either on list or privately so I can address them.
>
> --
> Bruce Momjian | http://candle.pha.pa.us
> pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
> + If your life is a hard drive, | 13 Roberts Road
> + Christ can be your backup. | Newtown Square, Pennsylvania 19073
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, seeing as no one voted, and only Tom and I objected originally, we
> will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> recognized by initdb, postmaster, postgres, and pg_ctl.

We will? It looks to me like Thomas lost the vote 2-to-1.

Unless there are more votes, I'm going to *insist* that this code be
changed. It's dangerous and offers no offsetting benefit. XLOG
location should be settable at initdb, noplace later.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, seeing as no one voted, and only Tom and I objected originally, we
> > will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> > recognized by initdb, postmaster, postgres, and pg_ctl.
>
> We will? It looks to me like Thomas lost the vote 2-to-1.
>
> Unless there are more votes, I'm going to *insist* that this code be
> changed. It's dangerous and offers no offsetting benefit. XLOG
> location should be settable at initdb, noplace later.

Well, you didn't vote again in my follow up email, so I thought you
didn't care anymore, and Thomas didn't reply to my email either. I am
clearly concerned, as you are, but Thomas isn't, and no one else seems
to care.

Can two guys override another guy if he is doing the work? I usually
like to have a larger margin than that. I don't know what to do.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 13 Aug 2002, Tom Lane wrote:

> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, seeing as no one voted, and only Tom and I objected originally, we
> > will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> > recognized by initdb, postmaster, postgres, and pg_ctl.
>
> We will? It looks to me like Thomas lost the vote 2-to-1.
>
> Unless there are more votes, I'm going to *insist* that this code be
> changed. It's dangerous and offers no offsetting benefit. XLOG
> location should be settable at initdb, noplace later.

Okay, I'm going to pop up here and side with Thomas ... I think ... I may
have missed some issues here, but, quite frankly, I hate symlinks, as I've
seen it create more evil then good .. hardlinks is a different story ...

And for that reason, I will side with Thomas ...

initdb should allow you to specify a seperate location, which I don't
think anyone disagrees with ... but, what happens if, for some reason, I
have to move it to another location? I have to then dump/reload after
doing a new initdb?

One thought at the back of my mind is why not have something like a
'PG_VERSION' for XLOG? Maybe something so simple as a text file in both
the data and xlog directory that just contains a timestamp from the
initdb? then, when you startup postmaster with a -X option, it compares
the two files and makes sure that they belong to each other?

Bruce, if I'm missing something, could you post a summary/scorecard for
pros-cons on this issue?

On Mon, 2002-08-12 at 23:09, Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, seeing as no one voted, and only Tom and I objected originally, we
> > will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> > recognized by initdb, postmaster, postgres, and pg_ctl.
>
> We will? It looks to me like Thomas lost the vote 2-to-1.
>
> Unless there are more votes, I'm going to *insist* that this code be
> changed. It's dangerous and offers no offsetting benefit. XLOG
> location should be settable at initdb, noplace later.
>

I think Tom is on to something here. I meant to ask but never got
around to it. Why would anyone need to move the XLOG after you've
inited the db?

Greg

On Tue, 13 Aug 2002, Bruce Momjian wrote:

> Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > OK, seeing as no one voted, and only Tom and I objected originally, we
> > > will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> > > recognized by initdb, postmaster, postgres, and pg_ctl.
> >
> > We will? It looks to me like Thomas lost the vote 2-to-1.
> >
> > Unless there are more votes, I'm going to *insist* that this code be
> > changed. It's dangerous and offers no offsetting benefit. XLOG
> > location should be settable at initdb, noplace later.
>
> Well, you didn't vote again in my follow up email, so I thought you
> didn't care anymore, and Thomas didn't reply to my email either. I am
> clearly concerned, as you are, but Thomas isn't, and no one else seems
> to care.

k, why are you concerned? see my other message, but if the major concern
is the xlog directory for a postmaster, then put in a consistency check
for when starting ...

then again, how many out there are running multiple instances of
postmaster on their machine that would move xlog to a different location
without realizing they did? I know in my case, we're working at reducing
the # of instances on our servers to 2 (internal vs external), but, our
servers are all on a RAID5 array, so there is nowhere to really "move"
xlog to out then its default location ... but I can see the usefulness of
doing so ...

Myself, if I'm going to move something around, it will be after the server
has been running for a while and I've added in more drive space in order
to correct a problem i didn't anticipate (namely, disk I/O) ... at that
point, I really don't wan tto have to dump/re-initdb/load just to move the
xlog directory to that new drive ...

On 13 Aug 2002, Greg Copeland wrote:

> On Mon, 2002-08-12 at 23:09, Tom Lane wrote:
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > OK, seeing as no one voted, and only Tom and I objected originally, we
> > > will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> > > recognized by initdb, postmaster, postgres, and pg_ctl.
> >
> > We will? It looks to me like Thomas lost the vote 2-to-1.
> >
> > Unless there are more votes, I'm going to *insist* that this code be
> > changed. It's dangerous and offers no offsetting benefit. XLOG
> > location should be settable at initdb, noplace later.
> >
>
>
> I think Tom is on to something here. I meant to ask but never got
> around to it. Why would anyone need to move the XLOG after you've
> inited the db?

I just determined that disk I/O is terrible, so want to move the XLOG over
to a different file system that is currently totally idle ...

Marc G. Fournier wrote:
> On Tue, 13 Aug 2002, Tom Lane wrote:
>
> > Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > > OK, seeing as no one voted, and only Tom and I objected originally, we
> > > will keep the code as Thomas has applied it, namely that PGXLOG/-X is
> > > recognized by initdb, postmaster, postgres, and pg_ctl.
> >
> > We will? It looks to me like Thomas lost the vote 2-to-1.
> >
> > Unless there are more votes, I'm going to *insist* that this code be
> > changed. It's dangerous and offers no offsetting benefit. XLOG
> > location should be settable at initdb, noplace later.
>
> Okay, I'm going to pop up here and side with Thomas ... I think ... I may
> have missed some issues here, but, quite frankly, I hate symlinks, as I've
> seen it create more evil then good .. hardlinks is a different story ...

OK, that agrees with Thomas's aversion to symlinks.

> And for that reason, I will side with Thomas ...
>
> initdb should allow you to specify a seperate location, which I don't
> think anyone disagrees with ... but, what happens if, for some reason, I
> have to move it to another location? I have to then dump/reload after
> doing a new initdb?

If you move pg_xlog, you have to create a symlink in /data that points
to the new location. Initdb would do that automatically, but if you
move it after initdb, you would have to create the symlink yourself.
With Thomas's current code, you would add/change PGXLOG instead to point
to the new location, rather than modify the symlink.

> One thought at the back of my mind is why not have something like a
> 'PG_VERSION' for XLOG? Maybe something so simple as a text file in both
> the data and xlog directory that just contains a timestamp from the
> initdb? then, when you startup postmaster with a -X option, it compares
> the two files and makes sure that they belong to each other?

Uh, seems it could get messy, but, yea, that would work. It means
adding a file to pg_xlog and /data and somehow matching them. My
feeling was that the symlink was unambiguous and allowed for fewer
mistakes. I think that was Tom's opinion too.

> Bruce, if I'm missing something, could you post a summary/scorecard for
> pros-cons on this issue?

OK, I tried. :-)

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Marc G. Fournier wrote:
> > Well, you didn't vote again in my follow up email, so I thought you
> > didn't care anymore, and Thomas didn't reply to my email either. I am
> > clearly concerned, as you are, but Thomas isn't, and no one else seems
> > to care.
>
> k, why are you concerned? see my other message, but if the major concern
> is the xlog directory for a postmaster, then put in a consistency check
> for when starting ...

I really have two concerns; one, the ability to point to the wrong
place or to the default place accidentally if PGXLOG isn't set, and two,
the addition of PGXLOG/-X into postmaster, postgres, pg_ctl where it
really isn't adding any functionality and just adds bloat to the
commands arg list.

> then again, how many out there are running multiple instances of
> postmaster on their machine that would move xlog to a different location
> without realizing they did? I know in my case, we're working at reducing
> the # of instances on our servers to 2 (internal vs external), but, our
> servers are all on a RAID5 array, so there is nowhere to really "move"
> xlog to out then its default location ... but I can see the usefulness of
> doing so ...
>
> Myself, if I'm going to move something around, it will be after the server
> has been running for a while and I've added in more drive space in order
> to correct a problem i didn't anticipate (namely, disk I/O) ... at that
> point, I really don't wan tto have to dump/re-initdb/load just to move the
> xlog directory to that new drive ...

No dump/reload required, but the creation of a _dreaded_ symlink is
required.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Marc G. Fournier wrote:
> > I think Tom is on to something here. I meant to ask but never got
> > around to it. Why would anyone need to move the XLOG after you've
> > inited the db?
>
> I just determined that disk I/O is terrible, so want to move the XLOG over
> to a different file system that is currently totally idle ...

Yep, and you are going to do it using symlinks. Let us know how it
goes?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Tue, 2002-08-13 at 00:16, Marc G. Fournier wrote:
>
> Myself, if I'm going to move something around, it will be after the server
> has been running for a while and I've added in more drive space in order
> to correct a problem i didn't anticipate (namely, disk I/O) ... at that
> point, I really don't wan tto have to dump/re-initdb/load just to move the
> xlog directory to that new drive ...
>

Okay, fair enough. But do we really need to have environment variables
for this? Sounds like we need a stand alone utility which does the
associated magic in the database which moves the xlog and associated
internal pointers. Doing so would assuming that all "be"s for the
database have been shutdown or simply would not go into effect until
restarted. Is this feasible?

For something that would seemingly be infrequently used, creating
environment variables would seemingly be rather error prone.

Requiring soft links also doesn't strike me as a good portable idea
either...not to mention I've been bitten by them before too.

Sign,
Greg Copeland

> If you move pg_xlog, you have to create a symlink in /data that points
> to the new location. Initdb would do that automatically, but if you
> move it after initdb, you would have to create the symlink yourself.
> With Thomas's current code, you would add/change PGXLOG instead to point
> to the new location, rather than modify the symlink.

There is no "the symlink", but of course that tinkering is in no way
precluded by the new code. Although some seem to like symlinks, others
(including myself) see no good engineering practice in making them the
only foundation for distributing files across file systems.

The patches as-is follow existing PostgreSQL practice, have complete and
perfect backward compatibility, and do not preclude changes in
underlying implementation in the future if those who are objecting
choose to do a complete and thorough job of meeting my objections to the
current counter-suggestions. As an example, two lines of code in initdb
would add "the beloved symlink" to $PGDATA, eliminating one objection
though (of course) one I don't support.

> > One thought at the back of my mind is why not have something like a
> > 'PG_VERSION' for XLOG? Maybe something so simple as a text file in both
> > the data and xlog directory that just contains a timestamp from the
> > initdb? then, when you startup postmaster with a -X option, it compares
> > the two files and makes sure that they belong to each other?
> Uh, seems it could get messy, but, yea, that would work. It means
> adding a file to pg_xlog and /data and somehow matching them. My
> feeling was that the symlink was unambiguous and allowed for fewer
> mistakes. I think that was Tom's opinion too.

In the spirit of gratutious overstatement, I'll point out again:
symlinks are evil. Any sense of a job well done is misplaced if our
underpinnings rely on them for distributing files across file systems.
As an ad hoc hack to work around current limitations they may have some
utility.

Anyway, istm that this is way too much discussion for a small extension
of capability, and it has likely cost a table and index "with location"
implementation for the upcoming release just due to time wasted
discussing it. Hope it was worth it :/

- Thomas

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Tom Lane wrote:
>> We will? It looks to me like Thomas lost the vote 2-to-1.

> Well, you didn't vote again in my follow up email,

I thought my vote was obvious already ...

> Can two guys override another guy if he is doing the work? I usually
> like to have a larger margin than that. I don't know what to do.

I'm not pleased about it either; I'd have preferred to see a few more
opinions given (and I'm surprised that no one else bothered to weigh in;
lack of opinions is usually not a problem for pghackers ;-)).

But I really seriously feel that this feature is a bad idea as presently
implemented. If necessary, I'll volunteer to change it the way I think
it should be (viz, initdb can set up a symlink to a specified xlog
directory; no change from previous behavior anywhere else).

regards, tom lane

> But I really seriously feel that this feature is a bad idea as presently
> implemented. If necessary, I'll volunteer to change it the way I think
> it should be (viz, initdb can set up a symlink to a specified xlog
> directory; no change from previous behavior anywhere else).

Neither solution is a particularly good one.

Symlinks seem to break all over the place (windows, novell, os/2),
environment variables are clumsy, arguments are easily forgotten by a
new admin starting up the system manually without reading documentation
first, and postgresql.conf changes are implemented via HUP (which we
don't want -- has to be a full restart?).

I'm going to vote a postgresql.conf entry similar to the LC_ vars thats
initialized by initdb BUT is configurable with a big warning above it
describing what needs to be done when changing it.

"Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
>> I think Tom is on to something here. I meant to ask but never got
>> around to it. Why would anyone need to move the XLOG after you've
>> inited the db?

> I just determined that disk I/O is terrible, so want to move the XLOG over
> to a different file system that is currently totally idle ...

Sure, needing to manually move the xlog directory is a plausible thing,
but *you can already do it*. The current procedure is

1. shut down postmaster
2. cp -p -r xlog directory to new location
3. rm -rf old xlog directory
4. ln -s new xlog directory to $PGDATA/xlog
5. start postmaster

With the patch it's almost the same, but you can instead of (4) substitute

(4a) Change PGXLOG environment variable or -X argument in start script.

That is *not* materially easier than an "ln" in my book. And it's
fraught with all the risks we've come to know and not love over the
years: it's just way too easy to start a postmaster with the wrong set
of environment variables. (Hand start vs start from boot script, etc,
etc, etc.) But this time the penalty for getting it wrong is, very
possibly, irrecoverable corruption of your database.

I see a serious downside to doing it this way, and not enough upside
to justify taking the risk. We should continue to keep the "where's the
xlog" information in the database directory itself. While a symlink
isn't the only possible way to do that (a configuration-file item might
do instead), I just don't think it's a good idea to allow it to be
specified externally.

regards, tom lane

Thomas Lockhart <lockhart(at)fourpalms(dot)org> writes:
> In the spirit of gratutious overstatement, I'll point out again:
> symlinks are evil.

Please justify that claim. They work really nicely in my experience...
and I don't know of any modern Unix system that doesn't rely on them
*heavily*.

Possibly more to the point, I can assert "environment variables are
evil" with at least as much foundation. We have seen many many reports
of trouble from people who were bit by environment-variable problems
with Postgres. Do I need to trawl the archives for examples?

However, as I just commented to Marc the real issue in my mind is that
the xlog needs to be solidly tied to the data directory, because we
can't risk starting a postmaster with the wrong combination. I do not
think that external specification of the xlog as a separate env-var or
postmaster command-line arg gives the appropriate amount of safety.
But there's more than one way to record the xlog location in the data
directory. If you don't like a symlink, what of putting it in
postgresql.conf as a postmaster-start-time-only config option?

regards, tom lane

"Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> One thought at the back of my mind is why not have something like a
> 'PG_VERSION' for XLOG? Maybe something so simple as a text file in both
> the data and xlog directory that just contains a timestamp from the
> initdb? then, when you startup postmaster with a -X option, it compares
> the two files and makes sure that they belong to each other?

While that isn't a bad idea, it seems to me that it's adding mechanism
to get around a problem that we don't need to have in the first place.
The only reason this risk exists is that the patch changes a monolithic
postmaster option (-D) into two independent options (-D/-X) that in
reality should never be independent.

Essentially, you're proposing Kevlar shoes as a solution for the problem
that you want to walk around carrying a loaded gun aimed at your foot.
The shoes might be a good idea anyway, but the primary problem is
elsewhere...

regards, tom lane

On Tue, 2002-08-13 at 08:15, Tom Lane wrote:
> "Marc G. Fournier" <scrappy(at)hub(dot)org> writes:
> >> I think Tom is on to something here. I meant to ask but never got
> >> around to it. Why would anyone need to move the XLOG after you've
> >> inited the db?
>
> > I just determined that disk I/O is terrible, so want to move the XLOG over
> > to a different file system that is currently totally idle ...
>
> Sure, needing to manually move the xlog directory is a plausible thing,
> but *you can already do it*. The current procedure is
>
> 1. shut down postmaster
> 2. cp -p -r xlog directory to new location
> 3. rm -rf old xlog directory
> 4. ln -s new xlog directory to $PGDATA/xlog
> 5. start postmaster
>
> With the patch it's almost the same, but you can instead of (4) substitute

Why not simply create a script which does this? Creation of "movexlog"
or some such beast which anally checked everything it did. As options,
you could simply pass it the src and dest and let it take care of the
rest.

Greg

On Tue, 2002-08-13 at 14:15, Tom Lane wrote:
> 4. ln -s new xlog directory to $PGDATA/xlog

> With the patch it's almost the same, but you can instead of (4) substitute
>
> (4a) Change PGXLOG environment variable or -X argument in start script.
>
> That is *not* materially easier than an "ln" in my book. And it's
> fraught with all the risks we've come to know and not love over the
> years: it's just way too easy to start a postmaster with the wrong set
> of environment variables. (Hand start vs start from boot script, etc,
> etc, etc.) But this time the penalty for getting it wrong is, very
> possibly, irrecoverable corruption of your database.
>
> I see a serious downside to doing it this way, and not enough upside
> to justify taking the risk. We should continue to keep the "where's the
> xlog" information in the database directory itself. While a symlink
> isn't the only possible way to do that (a configuration-file item might
> do instead), I just don't think it's a good idea to allow it to be
> specified externally.

Since the xlog is so closely linked with the database, I would be
unhappy for its location to be determined by a parameter in a file that
could be edited by an ignorant or careless administrator. Thomas does
not like symlinks. Equally I don't like the idea of an environment
variable, which is even more vulnerable to misuse.

Could you not store the location of the xlog directory as an entry in
$PGDATA/global/pg_control? The xlog is as closely tied in with the
database as is its locale, which is already stored in pg_control.

To let the directory be moved, there should then be a standalone program
that would shut down the server, copy the xlog directory to the new
location and set its access permissions; on a successful copy, change
the control entry, delete the old xlog directory and finally restart the
server. Use of such a program would protect against other possible
errors, such as pointing two different databases to the same xlog.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Watch ye therefore, and pray always, that ye may be
accounted worthy to escape all these things that shall
come to pass, and to stand before the Son of man."
Luke 21:36

Rod Taylor <rbt(at)zort(dot)ca> writes:
> Symlinks seem to break all over the place (windows, novell, os/2),

The portability argument carries little weight with me. Recent versions
of Windows have symlinks. If anyone wants to run a PG installation on
a symlink-less platform, okay; they just won't have the option to move
the xlog directory. That's probably not the only functionality they
lose by using such an inadequate filesystem...

regards, tom lane

On Tue, 2002-08-13 at 14:24, Tom Lane wrote:
...
> But there's more than one way to record the xlog location in the data
> directory. If you don't like a symlink, what of putting it in
> postgresql.conf as a postmaster-start-time-only config option?

Please don't!

The Debian package at least provides a default postgresql.conf and it
will be all too easy for someone installing an updated package to let
the default file overwrite the existing configuration. That could be
disastrous.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Watch ye therefore, and pray always, that ye may be
accounted worthy to escape all these things that shall
come to pass, and to stand before the Son of man."
Luke 21:36

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> Could you not store the location of the xlog directory as an entry in
> $PGDATA/global/pg_control?

We could do that *only* if we were to produce an xlog-moving program
immediately; otherwise we've regressed in functionality compared to
prior releases.

I do not think it's necessary to be quite that anal about tying the two
directories together --- the manual symlinking procedure I described has
been around for two releases now, and while doubtless not that many
people have actually done it, we've not heard any reports of failures.
The thing is that if the DBA has to do this himself, he is very well
aware that he's performing a critical procedure, and he's not likely
to muck it up.

I think that from a safety point of view either a symlink or a
config-file entry are perfectly acceptable, and in general I prefer
plain-text config files to those which are not. (Right now, pg_control
is *not* a config file: there is not anything in it that you might want
to edit in normal system maintenance. It should stay that way.)

Marc's idea of matching signature files would be a better
safety-checking mechanism than just making the data directory's xlog
link hard to get at.

regards, tom lane

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> On Tue, 2002-08-13 at 14:24, Tom Lane wrote:
>> But there's more than one way to record the xlog location in the data
>> directory. If you don't like a symlink, what of putting it in
>> postgresql.conf as a postmaster-start-time-only config option?

> Please don't!

> The Debian package at least provides a default postgresql.conf and it
> will be all too easy for someone installing an updated package to let
> the default file overwrite the existing configuration. That could be
> disastrous.

Ouch. That's a mighty good point ... although if we were to implement
Marc's idea of matching signature files, we'd certainly catch the error.

If we didn't, we'd need to use a separate, one-purpose config file that
just records the xlog location. Curiously enough, that seems to me to
be exactly what a symlink does, except that the symlink is OS-level code
rather than something we have to write for ourselves. So I'm back to
thinking that a symlink is a perfectly respectable answer.

regards, tom lane

On Tue, 2002-08-13 at 15:00, Tom Lane wrote:
> Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> > Could you not store the location of the xlog directory as an entry in
> > $PGDATA/global/pg_control?
>
> We could do that *only* if we were to produce an xlog-moving program
> immediately; otherwise we've regressed in functionality compared to
> prior releases.

If it doesn't have to edit pg_control (accepting your point below) it
can be a shell script - half an hour to write and test it. I'll do it
tonight if you choose to go this way....

...
> I think that from a safety point of view either a symlink or a
> config-file entry are perfectly acceptable, and in general I prefer
> plain-text config files to those which are not. (Right now, pg_control
> is *not* a config file: there is not anything in it that you might want
> to edit in normal system maintenance. It should stay that way.)

I suggested pg_control because it's already there. It could just as well
be a *private* configuration file containing the pathname. Just don't
put it in with postgresql.conf. As a producer of a binary distribution,
I don't want to deal with the consequences of people ignorantly changing
it. I'm sure you remember those mails from people who said, "I wanted
to save space so I deleted this log file..."

> Marc's idea of matching signature files would be a better
> safety-checking mechanism than just making the data directory's xlog
> link hard to get at.

When dealing with unknown numbers of package users, some of whom have
only just converted from being Windows users, I want to be defensive. I
cannot afford to assume that administrators know what they are doing! I
have to try to pick up the pieces after those that don't.

I would like to have Marc's safeguards as well.

--
Oliver Elphick Oliver(dot)Elphick(at)lfix(dot)co(dot)uk
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
========================================
"Watch ye therefore, and pray always, that ye may be
accounted worthy to escape all these things that shall
come to pass, and to stand before the Son of man."
Luke 21:36

Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
>> Marc's idea of matching signature files would be a better
>> safety-checking mechanism than just making the data directory's xlog
>> link hard to get at.

> I would like to have Marc's safeguards as well.

Yeah, I was lukewarm about that at first, but the more I think about it
the better it seems.

That does not change my opinion about the -X/PGXLOG switch though ---
having a backup safety check is not an excuse for having a fundamentally
insecure set of startup options.

regards, tom lane

Yea, the problem with postgresql.conf is that we don't have any
automatic modifications of that file, and I don't think we want to start
just to avoid symlinks.

I personally like symlinks too. I use them all the time. What is the
problem with them, exactly? Can someone show me some commands that
cause problems?

And the problem with a separate file is that when the move pg_xlog, it
isn't going to be obvious what they need to change to find the new
directory. Of course, they could just create a symlink and leave the
file unchanged.

Aside from the arg bloat problem, the real danger is that someone is
going to forget PGDATA and PGXLOG, try to start the postmaster, add -D
for PGDATA, then when they see that they need PGXLOG, they may just
create data/pg_xlog as an empty directory and start the postmaster.
That is a very real possibility. I just tried it and it does complain
about the missing checkpoint records so maybe it isn't as bad as I
thought, but still, it opens a place for error where none existed
before.

---------------------------------------------------------------------------

Tom Lane wrote:
> Oliver Elphick <olly(at)lfix(dot)co(dot)uk> writes:
> > On Tue, 2002-08-13 at 14:24, Tom Lane wrote:
> >> But there's more than one way to record the xlog location in the data
> >> directory. If you don't like a symlink, what of putting it in
> >> postgresql.conf as a postmaster-start-time-only config option?
>
> > Please don't!
>
> > The Debian package at least provides a default postgresql.conf and it
> > will be all too easy for someone installing an updated package to let
> > the default file overwrite the existing configuration. That could be
> > disastrous.
>
> Ouch. That's a mighty good point ... although if we were to implement
> Marc's idea of matching signature files, we'd certainly catch the error.
>
> If we didn't, we'd need to use a separate, one-purpose config file that
> just records the xlog location. Curiously enough, that seems to me to
> be exactly what a symlink does, except that the symlink is OS-level code
> rather than something we have to write for ourselves. So I'm back to
> thinking that a symlink is a perfectly respectable answer.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo(at)postgresql(dot)org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Mon, 12 Aug 2002, Thomas Lockhart wrote:

> > If you move pg_xlog, you have to create a symlink in /data that points
> > to the new location. Initdb would do that automatically, but if you
> > move it after initdb, you would have to create the symlink yourself.
> > With Thomas's current code, you would add/change PGXLOG instead to point
> > to the new location, rather than modify the symlink.
>
> There is no "the symlink", but of course that tinkering is in no way
> precluded by the new code. Although some seem to like symlinks, others
> (including myself) see no good engineering practice in making them the
> only foundation for distributing files across file systems.

Why? You often say you don't like them, but I have yet to see you say why
you don't like them.

> The patches as-is follow existing PostgreSQL practice,

using environmental variables is a practice we should discontinue if
possible, and use as little as possible. They ARE a security hole waiting
to happen.

> have complete and
> perfect backward compatibility, and do not preclude changes in
> underlying implementation in the future if those who are objecting
> choose to do a complete and thorough job of meeting my objections to the
> current counter-suggestions. As an example, two lines of code in initdb
> would add "the beloved symlink" to $PGDATA, eliminating one objection
> though (of course) one I don't support.
>
> > > One thought at the back of my mind is why not have something like a
> > > 'PG_VERSION' for XLOG? Maybe something so simple as a text file in both
> > > the data and xlog directory that just contains a timestamp from the
> > > initdb? then, when you startup postmaster with a -X option, it compares
> > > the two files and makes sure that they belong to each other?
> > Uh, seems it could get messy, but, yea, that would work. It means
> > adding a file to pg_xlog and /data and somehow matching them. My
> > feeling was that the symlink was unambiguous and allowed for fewer
> > mistakes. I think that was Tom's opinion too.
>
> In the spirit of gratutious overstatement, I'll point out again:
> symlinks are evil. Any sense of a job well done is misplaced if our
> underpinnings rely on them for distributing files across file systems.
> As an ad hoc hack to work around current limitations they may have some
> utility.

Why are symlinks evil? They exist on every major OS I know of, and they
work. They allow the user to quickly point the postgresql engine in
different places, and they are simple and easy to use. I found the use of
environmental variables far more confusing when I first started using
postgresql than symlinks.

In particular, which operating systems does Postgresql run don't have
symlink capability?

> Anyway, istm that this is way too much discussion for a small extension
> of capability, and it has likely cost a table and index "with location"
> implementation for the upcoming release just due to time wasted
> discussing it. Hope it was worth it :/

Well, if it averts a security problem, or makes the database easier to use
in the long run, then it probably was. It may seem like too much
discussion for such a simple topic, but it's not.

My non-coding vote goes with Tom Lane on this. initdb can set pg_xlog,
and if you need to change it, use symlinks. They're safe, secure, and
they just plain work. The only argument I can possibly think of against
the symlink boogie is if there is an os we run on that can't do symlinks.
And then I'd still think it would belong in postgresql.conf, be set by
initdb, and not be an environmental variable.

Of course that's just my opinion, I could be wrong (with apologies to
Dennis Miller)

Scott Marlowe

On Tue, 13 Aug 2002, Bruce Momjian wrote:

> Marc G. Fournier wrote:
> > > I think Tom is on to something here. I meant to ask but never got
> > > around to it. Why would anyone need to move the XLOG after you've
> > > inited the db?
> >
> > I just determined that disk I/O is terrible, so want to move the XLOG over
> > to a different file system that is currently totally idle ...
>
> Yep, and you are going to do it using symlinks. Let us know how it
> goes?

This was purely an fictional example ...

Sounds good to me, but I have proven very unreliable in guessing others
opinions on this issue.

---------------------------------------------------------------------------

Peter Eisentraut wrote:
> Tom Lane writes:
>
> > That does not change my opinion about the -X/PGXLOG switch though ---
> > having a backup safety check is not an excuse for having a fundamentally
> > insecure set of startup options.
>
> OK, so:
>
> 1. Leave -X option in initdb. Remove all other -X options.
>
> 2. Remove all uses of PGXLOG.
>
> 3. Symlink from PGDATA to desired location.
>
> 4. Implement pg_mvxlog to move xlog if server is shut down. (So no one
> needs to know about 3.)
>
> In the future:
>
> Combine pg_mvxlog, pg_controldata, pg_resetxlog into pg_srvadm.
>
> Sounds good.
>
> --
> Peter Eisentraut peter_e(at)gmx(dot)net
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Tom Lane writes:

> That does not change my opinion about the -X/PGXLOG switch though ---
> having a backup safety check is not an excuse for having a fundamentally
> insecure set of startup options.

OK, so:

1. Leave -X option in initdb. Remove all other -X options.

2. Remove all uses of PGXLOG.

3. Symlink from PGDATA to desired location.

4. Implement pg_mvxlog to move xlog if server is shut down. (So no one
needs to know about 3.)

In the future:

Combine pg_mvxlog, pg_controldata, pg_resetxlog into pg_srvadm.

Sounds good.

--
Peter Eisentraut peter_e(at)gmx(dot)net

On Tue, 13 Aug 2002, Bruce Momjian wrote:

> If you move pg_xlog, you have to create a symlink in /data that points
> to the new location. Initdb would do that automatically, but if you
> move it after initdb, you would have to create the symlink yourself.
> With Thomas's current code, you would add/change PGXLOG instead to point
> to the new location, rather than modify the symlink.

Right, but if the use of PGXLOG/-X it make sense then tryin got document
"hey, if you move pg_xlog, go into this directory and change the symlink
to point to hte new locaiton" ... as I said, I do not like symlinks ...

> Uh, seems it could get messy, but, yea, that would work. It means
> adding a file to pg_xlog and /data and somehow matching them. My
> feeling was that the symlink was unambiguous and allowed for fewer
> mistakes. I think that was Tom's opinion too.

Hrmmm ... how about some sort of 'tag' that gets written to the xlog
file(s) themselves instead?

On Tue, 13 Aug 2002, scott.marlowe wrote:

> My non-coding vote goes with Tom Lane on this. initdb can set pg_xlog,
> and if you need to change it, use symlinks.

I've not been following this thread, and thus I suppose I missed
my opportunity to vote, but just for the record I'm with the "don't
use an environment variable" crowd here, too. It's way, way to easy
to start up with the wrong setting in your environment.

The log is part of the database. Therefore you should store the
information on its location along with the rest of the database
information. The idea is, you pass *one* piece of information to your
program when you start it (in this case the database data directory
location), and all of the rest of the information comes from there. Then
you have guaranteed consistency.

How the log location is stored within that area, I'm not so fussy
about. If a symlink is so terrible, why not put this information
in the database config file?

Oh, and yes, it does need to be changable after an initdb. Say you
start out with only one disk on your system, but add a second disk
later, and want to move the log to that?

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Curt Sampson <cjs(at)cynic(dot)net> writes:
> ... just for the record I'm with the "don't
> use an environment variable" crowd here, too. It's way, way to easy
> to start up with the wrong setting in your environment.

What he said ...

> Oh, and yes, it does need to be changable after an initdb. Say you
> start out with only one disk on your system, but add a second disk
> later, and want to move the log to that?

Sure, there should be *a* way to do that. It does not have to be as
easy as "change an environment variable". And in fact the primary
objection to this patch is exactly that it is *not* as easy as "change
an environment variable" --- what you get if you just change your
environment variable is not a moved xlog, but a broken database.
Possibly an irredeemably broken database.

regards, tom lane

I would like to know how to move this item forward.

---------------------------------------------------------------------------

Tom Lane wrote:
> Curt Sampson <cjs(at)cynic(dot)net> writes:
> > ... just for the record I'm with the "don't
> > use an environment variable" crowd here, too. It's way, way to easy
> > to start up with the wrong setting in your environment.
>
> What he said ...
>
> > Oh, and yes, it does need to be changable after an initdb. Say you
> > start out with only one disk on your system, but add a second disk
> > later, and want to move the log to that?
>
> Sure, there should be *a* way to do that. It does not have to be as
> easy as "change an environment variable". And in fact the primary
> objection to this patch is exactly that it is *not* as easy as "change
> an environment variable" --- what you get if you just change your
> environment variable is not a moved xlog, but a broken database.
> Possibly an irredeemably broken database.
>
> regards, tom lane
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Thu, 15 Aug 2002, Bruce Momjian wrote:

> I would like to know how to move this item forward.

Right now (i.e., in 7.2), the only two options we have for moving the
log file to a different spindle are mounting it on pg_xlog and using a
symlink. I doubt many people do the the former, and if they do they do
not need an option to init_db to move the logfile away from its default
location.

So I propose we just continue to use the symlink method for the moment,
until we agree on another way to store the log file location within the
data directory, and at that time we implement the code to do that.

Note that if we don't move forward at all, we're still left in the symlink
situation, with the exception that you init_db, move the log directory and
create the symlink by hand, and then start up the database. So this partial
move forward makes no difference to the symlink argument.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

Curt Sampson wrote:
> On Thu, 15 Aug 2002, Bruce Momjian wrote:
>
> > I would like to know how to move this item forward.
>
> Right now (i.e., in 7.2), the only two options we have for moving the
> log file to a different spindle are mounting it on pg_xlog and using a
> symlink. I doubt many people do the the former, and if they do they do
> not need an option to init_db to move the logfile away from its default
> location.
>
> So I propose we just continue to use the symlink method for the moment,
> until we agree on another way to store the log file location within the
> data directory, and at that time we implement the code to do that.
>
> Note that if we don't move forward at all, we're still left in the symlink
> situation, with the exception that you init_db, move the log directory and
> create the symlink by hand, and then start up the database. So this partial
> move forward makes no difference to the symlink argument.

Part of the reason we can't "just continue to use the symlink method" is
that the PGXLOG environment variable situation is currently in CVS
beyond initdb and in postmaster, postgres, and pg_ctl, so we do have to
do something before 7.3 or we will have new environment variable
handling in all those commands, and I don't think we have agreement on
that.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

On Fri, 16 Aug 2002, Bruce Momjian wrote:

> Part of the reason we can't "just continue to use the symlink method" is
> that the PGXLOG environment variable situation is currently in CVS
> beyond initdb and in postmaster, postgres, and pg_ctl, so we do have to
> do something before 7.3 or we will have new environment variable
> handling in all those commands, and I don't think we have agreement on
> that.

Well, let's take it out, then, and use the symlink instead. It may
be in CVS now, but it's never been in a release, so there should
be no problem with removing it.

I think we've got some pretty strong opinions here that distributing
configuration information amongst multiple environment variables
is a Bad Idea.

cjs
--
Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light. --XTC

OK, with two people now asking to have the patch removed, and with no
comment from Thomas, I have removed the patch. This removes XLogDir
environment variable, and -X postmaster/postgres/initdb/pg_ctl flag.

I have also removed the code that dynamically sized xlogdir.

I will post the patch to patches, and keep the patch here in case it is
needed later.

---------------------------------------------------------------------------

Curt Sampson wrote:
> On Fri, 16 Aug 2002, Bruce Momjian wrote:
>
> > Part of the reason we can't "just continue to use the symlink method" is
> > that the PGXLOG environment variable situation is currently in CVS
> > beyond initdb and in postmaster, postgres, and pg_ctl, so we do have to
> > do something before 7.3 or we will have new environment variable
> > handling in all those commands, and I don't think we have agreement on
> > that.
>
> Well, let's take it out, then, and use the symlink instead. It may
> be in CVS now, but it's never been in a release, so there should
> be no problem with removing it.
>
> I think we've got some pretty strong opinions here that distributing
> configuration information amongst multiple environment variables
> is a Bad Idea.
>
> cjs
> --
> Curt Sampson <cjs(at)cynic(dot)net> +81 90 7737 2974 http://www.netbsd.org
> Don't you know, in this new Dark Age, we're all light. --XTC
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> OK, with two people now asking to have the patch removed, and with no
> comment from Thomas, I have removed the patch. This removes XLogDir
> environment variable, and -X postmaster/postgres/initdb/pg_ctl flag.

I thought we intended to keep the -X switch for initdb (only), and have
it make a symlink.

regards, tom lane

Tom Lane wrote:
> Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> > OK, with two people now asking to have the patch removed, and with no
> > comment from Thomas, I have removed the patch. This removes XLogDir
> > environment variable, and -X postmaster/postgres/initdb/pg_ctl flag.
>
> I thought we intended to keep the -X switch for initdb (only), and have
> it make a symlink.

The majority of the patch wasn't needed, so rather than muck it up, I
just backed it all out. If we want that, and I think we do, someone
should implent it as a separate patch that people can review. All the
work is going to be done in initdb anyway.

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

> OK, with two people now asking to have the patch removed, and with no
> comment from Thomas, I have removed the patch. This removes XLogDir
> environment variable, and -X postmaster/postgres/initdb/pg_ctl flag.
> I have also removed the code that dynamically sized xlogdir.

... Back in town...

Sorry to hear that this is the way it turned out. It is a bad precedent
imho, and I see no way forward on my interest in this area. Hopefully
someone else will pick it up; perhaps one of those so vehemently against
the details of this?

- Thomas

Yes, perhaps a bad precedent. I have very rarely done this. I do have
the patch here if anyone wants to use it. My guess is if someone
implements it, it will be done only in initdb, and use symlinks, which
you and Marc don't like, so we may be best leaving it undone for 7.3 and
returning with a clear slate in 7.4.

---------------------------------------------------------------------------

Thomas Lockhart wrote:
> > OK, with two people now asking to have the patch removed, and with no
> > comment from Thomas, I have removed the patch. This removes XLogDir
> > environment variable, and -X postmaster/postgres/initdb/pg_ctl flag.
> > I have also removed the code that dynamically sized xlogdir.
>
> ... Back in town...
>
> Sorry to hear that this is the way it turned out. It is a bad precedent
> imho, and I see no way forward on my interest in this area. Hopefully
> someone else will pick it up; perhaps one of those so vehemently against
> the details of this?
>
> - Thomas
>

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073

Thomas Lockhart <lockhart(at)fourpalms(dot)org> writes:
> Sorry to hear that this is the way it turned out. It is a bad precedent
> imho, and I see no way forward on my interest in this area. Hopefully
> someone else will pick it up; perhaps one of those so vehemently against
> the details of this?

I said I would be willing to make initdb create a symlink given a -X
switch; if you don't want to pick it up then I will do that.

regards, tom lane