Re: Tablespaces in the data directory

Someone just reported a problem when they had created a new tablespace
inside the old data directory. I'm sure there can be other issues
caused by this as well, but this is mainly a confusing scenario for
people now.

As there isn't (as far as I know at least) any actual *point* in
creating a tablespace inside the main data directory, should we
perhaps disallow this in CREATE TABLESPACE? Or at least throw a
WARNING if one does it?

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 1 December 2012 13:45, Magnus Hagander <magnus(at)hagander(dot)net> wrote:
> Someone just reported a problem when they had created a new tablespace
> inside the old data directory. I'm sure there can be other issues
> caused by this as well, but this is mainly a confusing scenario for
> people now.
>
> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

+1

--
Simon Riggs http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> Someone just reported a problem when they had created a new tablespace
> inside the old data directory. I'm sure there can be other issues
> caused by this as well, but this is mainly a confusing scenario for
> people now.

> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

It could be pretty hard to detect that in general (think symlinks
and such). I guess if we're just trying to print a helpful warning,
we don't have to worry about extreme corner cases. But what exactly
do you have in mind --- complain about any relative path? Complain
about absolute paths that have a prefix matching the DataDir?

regards, tom lane

On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> Someone just reported a problem when they had created a new tablespace
>> inside the old data directory. I'm sure there can be other issues
>> caused by this as well, but this is mainly a confusing scenario for
>> people now.
>
>> As there isn't (as far as I know at least) any actual *point* in
>> creating a tablespace inside the main data directory, should we
>> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
>> WARNING if one does it?
>
> It could be pretty hard to detect that in general (think symlinks
> and such). I guess if we're just trying to print a helpful warning,
> we don't have to worry about extreme corner cases. But what exactly
> do you have in mind --- complain about any relative path? Complain
> about absolute paths that have a prefix matching the DataDir?

Oh, I hadn't thought quite so far as the implementation :) Was looking
to see if there were going to be some major objections before I even
started thinking about that.

But for the implementation, I'd say any absolute path that have a
prefix matching DataDir. Tablespaces cannot be created using relative
paths, so we don't have to deal with that.

--
Magnus Hagander
Me: http://www.hagander.net/
Work: http://www.redpill-linpro.com/

On 12/02/2012 07:50 PM, Magnus Hagander wrote:
> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>> Someone just reported a problem when they had created a new tablespace
>>> inside the old data directory. I'm sure there can be other issues
>>> caused by this as well, but this is mainly a confusing scenario for
>>> people now.
>>> As there isn't (as far as I know at least) any actual *point* in
>>> creating a tablespace inside the main data directory, should we
>>> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
>>> WARNING if one does it?
>> It could be pretty hard to detect that in general (think symlinks
>> and such). I guess if we're just trying to print a helpful warning,
>> we don't have to worry about extreme corner cases. But what exactly
>> do you have in mind --- complain about any relative path? Complain
>> about absolute paths that have a prefix matching the DataDir?
> Oh, I hadn't thought quite so far as the implementation :) Was looking
> to see if there were going to be some major objections before I even
> started thinking about that.
>
> But for the implementation, I'd say any absolute path that have a
> prefix matching DataDir. Tablespaces cannot be created using relative
> paths, so we don't have to deal with that.
>

I have been known to symlink a tablespace on a replica back to a
directory in the datadir, while on the primary it points elsewhere. What
exactly is the problem?

cheers

andrew

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160

> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

Sure there is a point - emulating some other system. Could be
replication, QA box, disaster recovery, etc. I'd be
cool with a warning, but do not think we should disallow it.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation http://www.endpoint.com/
PGP Key: 0x14964AC8 201212022133
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAlC8D7kACgkQvJuQZxSWSsj+5gCgsmi6NXue+Hp0gycVOL/JEGUT
anYAoIqwo24JeLfliRHLvwPbdK4F4TXa
=EwgC
-----END PGP SIGNATURE-----

On Dec 3, 2012 2:55 AM, "Andrew Dunstan" <andrew(at)dunslane(dot)net> wrote:
>
>
> On 12/02/2012 07:50 PM, Magnus Hagander wrote:
>>
>> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>
>>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>>>
>>>> Someone just reported a problem when they had created a new tablespace
>>>> inside the old data directory. I'm sure there can be other issues
>>>> caused by this as well, but this is mainly a confusing scenario for
>>>> people now.
>>>> As there isn't (as far as I know at least) any actual *point* in
>>>> creating a tablespace inside the main data directory, should we
>>>> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
>>>> WARNING if one does it?
>>>
>>> It could be pretty hard to detect that in general (think symlinks
>>> and such). I guess if we're just trying to print a helpful warning,
>>> we don't have to worry about extreme corner cases. But what exactly
>>> do you have in mind --- complain about any relative path? Complain
>>> about absolute paths that have a prefix matching the DataDir?
>>
>> Oh, I hadn't thought quite so far as the implementation :) Was looking
>> to see if there were going to be some major objections before I even
>> started thinking about that.
>>
>> But for the implementation, I'd say any absolute path that have a
>> prefix matching DataDir. Tablespaces cannot be created using relative
>> paths, so we don't have to deal with that.
>>
>
> I have been known to symlink a tablespace on a replica back to a
directory in the datadir, while on the primary it points elsewhere. What
exactly is the problem?

That wouldn't be affected by this though, since it would only warn at
create tablespace.

I'd still consider it a bad idea in general to do that, since you're
basically messing with the internal structure of the data directory. Why
not just link it to some place outside the data directory?

One obvious problem with it atm is that pg_basebackup breaks, in that it
backs up your data twice, and throws warnings about things that aren't
links if you actually out it inside pg_tblspc.

/Magnus

On 12/03/2012 12:33 PM, Magnus Hagander wrote:
>
>
> On Dec 3, 2012 2:55 AM, "Andrew Dunstan" <andrew(at)dunslane(dot)net
> <mailto:andrew(at)dunslane(dot)net>> wrote:
> >
> >
> > On 12/02/2012 07:50 PM, Magnus Hagander wrote:
> >>
> >> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us
> <mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us>> wrote:
> >>>
> >>> Magnus Hagander <magnus(at)hagander(dot)net <mailto:magnus(at)hagander(dot)net>>
> writes:
> >>>>
> >>>> Someone just reported a problem when they had created a new
> tablespace
> >>>> inside the old data directory. I'm sure there can be other issues
> >>>> caused by this as well, but this is mainly a confusing scenario for
> >>>> people now.
> >>>> As there isn't (as far as I know at least) any actual *point* in
> >>>> creating a tablespace inside the main data directory, should we
> >>>> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> >>>> WARNING if one does it?
> >>>
> >>> It could be pretty hard to detect that in general (think symlinks
> >>> and such). I guess if we're just trying to print a helpful warning,
> >>> we don't have to worry about extreme corner cases. But what exactly
> >>> do you have in mind --- complain about any relative path? Complain
> >>> about absolute paths that have a prefix matching the DataDir?
> >>
> >> Oh, I hadn't thought quite so far as the implementation :) Was looking
> >> to see if there were going to be some major objections before I even
> >> started thinking about that.
> >>
> >> But for the implementation, I'd say any absolute path that have a
> >> prefix matching DataDir. Tablespaces cannot be created using relative
> >> paths, so we don't have to deal with that.
> >>
> >
> > I have been known to symlink a tablespace on a replica back to a
> directory in the datadir, while on the primary it points elsewhere.
> What exactly is the problem?
>
> That wouldn't be affected by this though, since it would only warn at
> create tablespace.
>
> I'd still consider it a bad idea in general to do that, since you're
> basically messing with the internal structure of the data directory.
> Why not just link it to some place outside the data directory?
>
> One obvious problem with it atm is that pg_basebackup breaks, in that
> it backs up your data twice, and throws warnings about things that
> aren't links if you actually out it inside pg_tblspc.
>
>

Well, when I last did it I don't think there was such a thing as
pg_basebackup :-)

I think it would be reasonable for it to complain if it came across a
PG_VERSION file in an unexpected location.

cheers

andrew

On Mon, Dec 3, 2012 at 02:38:20AM -0000, Greg Sabino Mullane wrote:
>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: RIPEMD160
>
>
> > As there isn't (as far as I know at least) any actual *point* in
> > creating a tablespace inside the main data directory, should we
> > perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> > WARNING if one does it?
>
> Sure there is a point - emulating some other system. Could be
> replication, QA box, disaster recovery, etc. I'd be
> cool with a warning, but do not think we should disallow it.

FYI, someone put their new cluster inside an existing old tablespace,
and when they ran the script to delete their old install, their new
install was deleted too. My answer was, "Don't do that".

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Dec 3, 2012, at 12:33, Magnus Hagander wrote:

> On Dec 3, 2012 2:55 AM, "Andrew Dunstan" <andrew(at)dunslane(dot)net> wrote:
>>
>>
>> On 12/02/2012 07:50 PM, Magnus Hagander wrote:
>>>
>>> On Sat, Dec 1, 2012 at 6:56 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>>>
>>>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>>>>>
>>>>> Someone just reported a problem when they had created a new tablespace
>>>>> inside the old data directory. I'm sure there can be other issues
>>>>> caused by this as well, but this is mainly a confusing scenario for
>>>>> people now.
>>>>> As there isn't (as far as I know at least) any actual *point* in
>>>>> creating a tablespace inside the main data directory, should we
>>>>> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
>>>>> WARNING if one does it?
>>>>
>>>> It could be pretty hard to detect that in general (think symlinks
>>>> and such). I guess if we're just trying to print a helpful warning,
>>>> we don't have to worry about extreme corner cases. But what exactly
>>>> do you have in mind --- complain about any relative path? Complain
>>>> about absolute paths that have a prefix matching the DataDir?
>>>
>>> Oh, I hadn't thought quite so far as the implementation :) Was looking
>>> to see if there were going to be some major objections before I even
>>> started thinking about that.
>>>
>>> But for the implementation, I'd say any absolute path that have a
>>> prefix matching DataDir. Tablespaces cannot be created using relative
>>> paths, so we don't have to deal with that.
>>>
>>
>> I have been known to symlink a tablespace on a replica back to a
> directory in the datadir, while on the primary it points elsewhere. What
> exactly is the problem?
>
> That wouldn't be affected by this though, since it would only warn at
> create tablespace.
>
> I'd still consider it a bad idea in general to do that, since you're
> basically messing with the internal structure of the data directory. Why
> not just link it to some place outside the data directory?

One reason is that subsequent copies of the data directory then also includes the tablespace data. Saves one step when setting up a standby.

Michael Glaesemann
grzm seespotcode net

On Mon, Dec 3, 2012 at 10:06 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> FYI, someone put their new cluster inside an existing old tablespace,
> and when they ran the script to delete their old install, their new
> install was deleted too. My answer was, "Don't do that".

Uh, wow. I feel bad for that person, but it does seem like a bit of a
self-inflicted injury.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Tue, Dec 4, 2012 at 09:37:46AM -0500, Robert Haas wrote:
> On Mon, Dec 3, 2012 at 10:06 PM, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > FYI, someone put their new cluster inside an existing old tablespace,
> > and when they ran the script to delete their old install, their new
> > install was deleted too. My answer was, "Don't do that".
>
> Uh, wow. I feel bad for that person, but it does seem like a bit of a
> self-inflicted injury.

They wanted pg_upgrade to guard against it, and I said that was
possible, but it would require pg_upgrade to know which files to remove,
and that would make pg_upgrade more fragile.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On 2012-12-01 14:45, Magnus Hagander wrote:
> Someone just reported a problem when they had created a new tablespace
> inside the old data directory. I'm sure there can be other issues
> caused by this as well, but this is mainly a confusing scenario for
> people now.
>
> As there isn't (as far as I know at least) any actual *point* in
> creating a tablespace inside the main data directory, should we
> perhaps disallow this in CREATE TABLESPACE? Or at least throw a
> WARNING if one does it?

Does this apply when creating a tablespace in another tablespace too? If
the problem is that pg_basebackup copies that data twice it sounds like
it should.

--
Andreas Karlsson

On Mon, Dec 03, 2012 at 01:14:30PM -0500, Andrew Dunstan wrote:
> I think it would be reasonable for it to complain if it came across a
> PG_VERSION file in an unexpected location.

That sounds like a reliable approach to detecting the hazard. Pseudocode:

chdir(proposed_tablespace_path)
do {
if (stat("PG_VERSION"))
ereport(WARNING, ...)
} while (chdir(".."))