Thread: 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.fujii@gmail.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:58 AM, Fujii Masao <masao.fujii@gmail.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.fujii@gmail.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
On Thu, Apr 30, 2009 at 3:50 PM, Jaime Casanova <jcasanov@systemguards.com.ec> wrote: > On Thu, Apr 30, 2009 at 2:58 AM, Fujii Masao <masao.fujii@gmail.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
Hi, On Fri, May 1, 2009 at 10:14 AM, Robert Haas <robertmhaas@gmail.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
<div style="font-family: Times New Roman; font-size: 12pt; color: #000000">----- "Fujii Masao" <masao.fujii@gmail.com>escreveu: <br />> IMO, it's not so dangerous that postgres creates the directories,<br />>which doesn't overwrite or remove any existing data. So, I thought<br />> that it's worth writing the patch to createa tablespace directory<br />> in recovery. Is this the right direction? Or, we should completely<br />> forbidthe auto-creation of directories outside $PGDATA?<br /><br />I don't think it's a good idea to create directories automaticallyat all.<br />Considering that the greatest benefit of using tablespaces is to move tables and indexes that havehigh concurrency to different disk partitions or controllers, creating directories (and mounting devices) for tablespacesshould be a manual process.<br /><br />Flavio<br /></div>
Fujii Masao <masao.fujii@gmail.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