Subject: PATCH: Warn users about tablespace abuse data loss risk

From: Craig Ringer <[email-masked]>
Date: Wed, 12 Feb 2014 11:52:10 +0800

Hi all

I've just seen another case of data loss due to misuse of /
misunderstanding of tablespaces:

http://dba.stackexchange.com/questions/58704/how-do-i-access-a-old-saved-tablespace-after-reinstalling-postgres

and it's prompted me to write some docs amendments to make it more
obvious that *you shouldn't do that*.

Not that it'll stop people, but it'll at least mean they can't say we
didn't warn them.

This is actually quite important, because many users are used to MySQL's
MyISAM, where each table contains its own metadata and is readable by
simply copying the table into a different MySQL install's data
directory. It doesn't even have to be the same version! Users are
clearly surprised that PostgreSQL tablespaces don't have the same
properties.

Thoughts?

-- 
 Craig Ringer                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services
>From 6d0abd7bcaca596ac2298d8f7ac408e465ec1aef Mon Sep 17 00:00:00 2001
From: Craig Ringer <[email-masked]>
Date: Wed, 12 Feb 2014 11:48:23 +0800
Subject: [PATCH] Warn about risks of tablespace misuse

---
 doc/src/sgml/manage-ag.sgml             | 17 +++++++++++++++++
 doc/src/sgml/ref/create_tablespace.sgml | 10 ++++++++++
 2 files changed, 27 insertions(+)

diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml
index b44d521..dd43e19 100644
--- a/doc/src/sgml/manage-ag.sgml
+++ b/doc/src/sgml/manage-ag.sgml
@@ -379,6 +379,23 @@ dropdb <replaceable class="parameter">dbname</replaceable>
    expensive, slower disk system.
   </para>
 
+  <warning>
+   <para>
+    Tablespaces are <emphasis>not</emphasis> suitable for backup/redundancy.
+    If the main database cluster is lost, tablespaces cannot simply be reattached
+    to a different database cluster, the metadata from the original is required
+    to read the tablespace. Similarly, if you lose a tablespace (deletion,
+    disk failure, etc) the main database may become unreadable or fail to start.
+   </para>
+
+   <para>
+    Do not attempt to use tablespaces to transfer tables between database
+    clusters, for backup and restore, or to partition your data based on
+    the reliability of the storage it is on. <emphasis>Never put a tablespace
+    on a ramdisk or temporary file system</emphasis>.
+   </para>
+  </warning>
+
   <para>
    To define a tablespace, use the <xref
    linkend="sql-createtablespace">
diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml
index 04c5fb8..83edd88 100644
--- a/doc/src/sgml/ref/create_tablespace.sgml
+++ b/doc/src/sgml/ref/create_tablespace.sgml
@@ -50,6 +50,16 @@ CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
    <command>CREATE INDEX</> or <command>ADD CONSTRAINT</> to have the data
    files for these objects stored within the specified tablespace.
   </para>
+
+  <warning>
+   <para>
+    You can't use the contents of a tablespace without the database it's part of,
+    nor can you use the main database if a tablespace is missing.  Don't try to
+    use tablespaces for backup or redundancy.
+    See <xref linkend="manage-ag-tablespaces" />.
+   </para>
+  </warning>
+
  </refsect1>
 
  <refsect1>
-- 
1.8.3.1

-- 
Sent via pgsql-docs mailing list ([email-masked])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-docs

---

From: Ian Lawrence Barwick <[email-masked]>
Date: Wed, 12 Feb 2014 13:44:03 +0900

> clearly surprised that PostgreSQL tablespaces don't have the same

People still use MyISAM!?

I had a similar issue pop up at work a while back, having something
explicit to point to is definitely a good idea.

Suggestion for the first paragraph of the patch (sorry I can't provide it in
patch form right now):

  Even if they are located outside the main PostgreSQL data directory,
tablespaces
  are an integral part of the database cluster and
<emphasis>cannot</emphasis> be
  treated as an autonomous collection of data files. They rely on
metadata contained
  in the main data directory, without which they are useless. In
particular, tablespaces
  cannot be reattached to a different database cluster, and backing up
individual
  tablespaces makes no sense as a backup/redundancy method. Similarly,
if you lose a
  tablespace (file deletion, disk failure, etc) the main database may
become unreadable
  or fail to start.

Regards

Ian Barwick

---

From: David Johnston <[email-masked]>
Date: Tue, 11 Feb 2014 21:06:33 -0800 (PST)

Ian Lawrence Barwick wrote
> 2014-02-12 12:52 GMT+09:00 Craig Ringer &lt;

> craig@

> &gt;:
> <emphasis>
> cannot
> </emphasis>
>  be

While providing additional warnings is good and necessary it may also help
to be more descriptive as to in what situations tablespaces are appropriate
and/or necessary so that people leave with a better understanding of why the
feature exists and not just trying to know what not to use it for.  It goes
against the more prescriptive tone of the documentation generally but both
approaches work well together to tackle the knowledge/understanding gap some
users seem to have.

David J.

--
View this message in context: http://postgresql.1045698.n5.nabble.com/PATCH-Warn-users-about-tablespace-abuse-data-loss-risk-tp5791537p5791542.html
Sent from the PostgreSQL - docs mailing list archive at Nabble.com.

---

From: Ian Lawrence Barwick <[email-masked]>
Date: Wed, 12 Feb 2014 15:16:58 +0900

2014-02-12 14:06 GMT+09:00 David Johnston <[email-masked]>:
>> 2014-02-12 12:52 GMT+09:00 Craig Ringer <
>
>> craig@
>
>> >:
>>> Hi all
>>>

> approaches work well together to tackle the knowledge/understanding gap some

The warning would appear on this page:

  http://www.postgresql.org/docs/current/static/manage-ag-tablespaces.html

which describes what tablespaces *can* do, but unless you're familiar with the
structure of the PostgreSQL data directories, it's not obvious what you *can't*
do. I recall reading a blog post a while back about tablespaces being "archived"
to the cloud with disastrous results, and a quick search pulls up
stuff like this:

  http://stackoverflow.com/questions/3534415/moving-postgres-tablespaces-and-tables-across-ec2-instance

so it's definitely not a niche issue. Something "official" to link to
would be very
useful in this kind of situation. That doesn't preclude the general
documentation
being improved of course.

Regards

Ian Barwick

---

From: Ian Lawrence Barwick <[email-masked]>
Date: Wed, 12 Feb 2014 15:35:09 +0900

2014-02-12 15:16 GMT+09:00 Ian Lawrence Barwick <[email-masked]>:
> would be very useful in this kind of situation. That doesn't preclude the general
> documentation being improved of course.

And taking a look at the page in question I see this prominent example:

  CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

As '/mnt' is usually a temporary mount point for detachable media and
all that [*]
maybe it's not the best impression to give for a suitable tablespace location.

[*] http://www.pathname.com/fhs/pub/fhs-2.3.html#MNTMOUNTPOINTFORATEMPORARILYMOUNT
"This directory is provided so that the system administrator may
temporarily mount a
filesystem as needed. "

Ian Barwick

---

From: Craig Ringer <[email-masked]>
Date: Thu, 13 Feb 2014 08:15:59 +0800

>   CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';

Here's an updated patch, adopting your wording for the first par and
tweaking the example.

>From a754f61f5228e0d066edc10ae903282b36f9a256 Mon Sep 17 00:00:00 2001

With rewording by Ian Barwick
---
 doc/src/sgml/manage-ag.sgml             | 28 +++++++++++++++++++++++++++-
 doc/src/sgml/ref/create_tablespace.sgml | 10 ++++++++++
 2 files changed, 37 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml
index b44d521..efd080f 100644
--- a/doc/src/sgml/manage-ag.sgml
+++ b/doc/src/sgml/manage-ag.sgml
@@ -379,12 +379,33 @@ dropdb <replaceable class="parameter">dbname</replaceable>
    expensive, slower disk system.
   </para>
 
+  <warning>
+   <para>
+     Even if they are located outside the main PostgreSQL data directory,
+     tablespaces are an integral part of the database cluster and
+     <emphasis>cannot</emphasis> be treated as an autonomous collection of data
+     files. They rely on metadata contained in the main data directory, without
+     which they are useless. In particular, tablespaces cannot be reattached to
+     a different database cluster, and backing up individual tablespaces makes
+     no sense as a backup/redundancy method. Similarly, if you lose a
+     tablespace (file deletion, disk failure, etc) the main database may become
+     unreadable or fail to start.
+   </para>
+
+   <para>
+    Do not attempt to use tablespaces to transfer tables between database
+    clusters, for backup and restore, or to partition your data based on
+    the reliability of the storage it is on. <emphasis>Never put a tablespace
+    on a ramdisk or temporary file system</emphasis>.
+   </para>
+  </warning>
+
   <para>
    To define a tablespace, use the <xref
    linkend="sql-createtablespace">
    command, for example:<indexterm><primary>CREATE TABLESPACE</></>:
 <programlisting>
-CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
+CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
 </programlisting>
    The location must be an existing, empty directory that is owned by
    the <productname>PostgreSQL</> operating system user.  All objects subsequently
@@ -392,6 +413,11 @@ CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
    directory.
   </para>
 
+  <para>
+   The location must not be on removable or transient storage, as the database
+   will not work if the tablespace is missing or lost.
+  </para>
+
   <note>
    <para>
     There is usually not much point in making more than one
diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml
index 04c5fb8..83edd88 100644
--- a/doc/src/sgml/ref/create_tablespace.sgml
+++ b/doc/src/sgml/ref/create_tablespace.sgml
@@ -50,6 +50,16 @@ CREATE TABLESPACE <replaceable class="parameter">tablespace_name</replaceable>
    <command>CREATE INDEX</> or <command>ADD CONSTRAINT</> to have the data
    files for these objects stored within the specified tablespace.
   </para>
+
+  <warning>
+   <para>
+    You can't use the contents of a tablespace without the database it's part of,
+    nor can you use the main database if a tablespace is missing.  Don't try to
+    use tablespaces for backup or redundancy.
+    See <xref linkend="manage-ag-tablespaces" />.
+   </para>
+  </warning>
+
  </refsect1>
 
  <refsect1>
-- 
1.8.3.1

---

From: Bruce Momjian <[email-masked]>
Date: Sat, 19 Apr 2014 10:53:29 -0400

> Here's an updated patch, adopting your wording for the first par and

I have applied an adjusted version of this patch that is more compact. 
Thanks.

-- 
  Bruce Momjian  <[email-masked]>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + Everyone has their own god. +
diff --git a/doc/src/sgml/manage-ag.sgml b/doc/src/sgml/manage-ag.sgml
new file mode 100644
index b44d521..78ec509
*** a/doc/src/sgml/manage-ag.sgml
--- b/doc/src/sgml/manage-ag.sgml
*************** dropdb <replaceable class="parameter">db
*** 379,395 ****
     expensive, slower disk system.
    </para>
  
    <para>
     To define a tablespace, use the <xref
     linkend="sql-createtablespace">
     command, for example:<indexterm><primary>CREATE TABLESPACE</></>:
  <programlisting>
! CREATE TABLESPACE fastspace LOCATION '/mnt/sda1/postgresql/data';
  </programlisting>
     The location must be an existing, empty directory that is owned by
     the <productname>PostgreSQL</> operating system user.  All objects subsequently
     created within the tablespace will be stored in files underneath this
!    directory.
    </para>
  
    <note>
--- 379,412 ----
  
+   <warning>
+    <para>
+      Even though located outside the main PostgreSQL data directory,
+      tablespaces are an integral part of the database cluster and
+      <emphasis>cannot</emphasis> be treated as an autonomous collection
+      of data files. They are dependent on metadata contained in the main
+      data directory, and therefore cannot be attached to a different
+      database cluster or backed up individually.  Similarly, if you lose
+      a tablespace (file deletion, disk failure, etc), the database cluster
+      might become unreadable or unable to start.  Placing a tablespace
+      on a temporary file system like a ramdisk risks the reliability of
+      the entire cluster.
+    </para>
+   </warning>
+ 
    <para>
     To define a tablespace, use the <xref
     linkend="sql-createtablespace">
     command, for example:<indexterm><primary>CREATE TABLESPACE</></>:
  <programlisting>
! CREATE TABLESPACE fastspace LOCATION '/ssd1/postgresql/data';
  </programlisting>
     The location must be an existing, empty directory that is owned by
     the <productname>PostgreSQL</> operating system user.  All objects subsequently
     created within the tablespace will be stored in files underneath this
!    directory.  The location must not be on removable or transient storage,
!    as the cluster might fail to function if the tablespace is missing
!    or lost.
    </para>
  
    <note>
diff --git a/doc/src/sgml/ref/create_tablespace.sgml b/doc/src/sgml/ref/create_tablespace.sgml
new file mode 100644
index b643565..cf6215e
*** a/doc/src/sgml/ref/create_tablespace.sgml
--- b/doc/src/sgml/ref/create_tablespace.sgml
*************** CREATE TABLESPACE <replaceable class="pa
*** 50,55 ****
--- 50,63 ----
     <command>CREATE INDEX</> or <command>ADD CONSTRAINT</> to have the data
     files for these objects stored within the specified tablespace.
    </para>
+ 
+   <warning>
+    <para>
+     A tablespace cannot be used independently of the cluster in which it
+     is defined;  see <xref linkend="manage-ag-tablespaces"/>.
+    </para>
+   </warning>
+ 
   </refsect1>
  
   <refsect1>