Re: Creating a tablespace directory in recovery

Hi,

Does anyone know why a tablespace directory isn't automatically
created in recovery? I think that it's troublesome to create all the
directories before recovery.

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

On Thu, Apr 30, 2009 at 2:58 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> Hi,
>
> Does anyone know why a tablespace directory isn't automatically
> created in recovery? I think that it's troublesome to create all the
> directories before recovery.
>

i guess it's because you need an absolute path to create tablespaces
and the appropiate path could not exist in the other machine (assuming
you are using recovery for warm standby or because you are recreating
a database after reinstall your server).

don't know if there are better reasons...

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Thu, Apr 30, 2009 at 3:50 PM, Jaime Casanova
<jcasanov(at)systemguards(dot)com(dot)ec> wrote:
> On Thu, Apr 30, 2009 at 2:58 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
>> Hi,
>>
>> Does anyone know why a tablespace directory isn't automatically
>> created in recovery? I think that it's troublesome to create all the
>> directories before recovery.
>>
>
> i guess it's because you need an absolute path to create tablespaces
> and the appropiate path could not exist in the other machine (assuming
> you are using recovery for warm standby or because you are recreating
> a database after reinstall your server).
>

From the caveats section in
http://www.postgresql.org/docs/8.3/static/continuous-archiving.html#BACKUP-PITR-RECOVERY:
"""
* CREATE TABLESPACE commands are WAL-logged with the literal
absolute path, and will therefore be replayed as tablespace creations
with the same absolute path. This might be undesirable if the log is
being replayed on a different machine. It can be dangerous even if the
log is being replayed on the same machine, but into a new data
directory: the replay will still overwrite the contents of the
original tablespace. To avoid potential gotchas of this sort, the best
practice is to take a new base backup after creating or dropping
tablespaces.
"""
--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

On Thu, Apr 30, 2009 at 3:58 AM, Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:
> Does anyone know why a tablespace directory isn't automatically
> created in recovery? I think that it's troublesome to create all the
> directories before recovery.

Well, there's some chance that if the directories don't exist, it's
because you haven't troubled to mount the correct partitions. And in
that case just blindly creating the directories will possibly lead to
a series of unfortunate events.

I generally think that it's not a good idea for PG to create
directories outside $PGDATA. I didn't much like the recent change
where (AIUI) we follow the symlink and create a directory in the right
place to be the target thereof. Those kinds of things strike me as
footguns waiting to go off. If something is not right, I would rather
have the system complain to me and stop than just do something that
might not be right. It's easier to fix my init scripts if PG is too
dumb than to work around PG trying to be too smart.

...Robert

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> wrote:

> Does anyone know why a tablespace directory isn't automatically
> created in recovery? I think that it's troublesome to create all the
> directories before recovery.

I guess that's because postgres (OS user) needs a write privilege
of *upper* directory of the tablespace directory. So, makeing
directory might fail with permission denied error.

However, it might be as well to create tablespace directories
for less-restricted installations.

Regards,
---
ITAGAKI Takahiro
NTT Open Source Software Center

Hi,

On Fri, May 1, 2009 at 10:14 AM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Well, there's some chance that if the directories don't exist, it's
> because you haven't troubled to mount the correct partitions.  And in
> that case just blindly creating the directories will possibly lead to
> a series of unfortunate events.

I understand. Thanks!

> I generally think that it's not a good idea for PG to create
> directories outside $PGDATA.  I didn't much like the recent change
> where (AIUI) we follow the symlink and create a directory in the right
> place to be the target thereof.  Those kinds of things strike me as
> footguns waiting to go off.  If something is not right, I would rather
> have the system complain to me and stop than just do something that
> might not be right.  It's easier to fix my init scripts if PG is too
> dumb than to work around PG trying to be too smart.

Yeah, there are trade-offs between the risks and conveniences
of letting postgres create a directory. This issue is related also to
the following patch.
http://archives.postgresql.org//pgsql-hackers/2009-04/msg01057.php

ISTM that there is currently no unified policy of whether to
automatically create the directory specified by an user.
For example, though log_directory is created by postgres,
stats_temp_directory is not. So, I think that we need to consider
that policy to get things going.

IMO, it's not so dangerous that postgres creates the directories,
which doesn't overwrite or remove any existing data. So, I thought
that it's worth writing the patch to create a tablespace directory
in recovery. Is this the right direction? Or, we should completely
forbid the auto-creation of directories outside $PGDATA?

Regards,

--
Fujii Masao
NIPPON TELEGRAPH AND TELEPHONE CORPORATION
NTT Open Source Software Center

----- "Fujii Masao" <masao(dot)fujii(at)gmail(dot)com> escreveu:
> IMO, it's not so dangerous that postgres creates the directories,
> which doesn't overwrite or remove any existing data. So, I thought
> that it's worth writing the patch to create a tablespace directory
> in recovery. Is this the right direction? Or, we should completely
> forbid the auto-creation of directories outside $PGDATA?

I don't think it's a good idea to create directories automatically at all.
Considering that the greatest benefit of using tablespaces is to move tables and indexes that have high concurrency to different disk partitions or controllers, creating directories (and mounting devices) for tablespaces should be a manual process.

Flavio

Fujii Masao <masao(dot)fujii(at)gmail(dot)com> writes:
> ISTM that there is currently no unified policy of whether to
> automatically create the directory specified by an user.

I think it's reasonably consistent: we don't automatically recreate
directories that are likely to be symlinks to someplace outside PGDATA.
We don't know what special properties (like mount points) they're
supposed to have, and very likely we don't have the necessary
permissions to make them anyway.

> IMO, it's not so dangerous that postgres creates the directories,
> which doesn't overwrite or remove any existing data.

You might want to reflect on this old horror story ...
http://archives.postgresql.org/pgsql-hackers/2004-12/msg00628.php
http://archives.postgresql.org//pgsql-hackers/2004-12/msg00851.php

Creating a directory where there "should be one", and you don't know
why it's missing, can make things very seriously worse indeed. Joe
was lucky to get anything back out of that fiasco.

regards, tom lane