Re: OK to put temp tablespace on volatile storage or to omit it from backups?

On Tue, Apr 30, 2013 at 11:14 PM, Yang Zhang <yanghatespam(at)gmail(dot)com> wrote:
> On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Ian Lawrence Barwick <barwick(at)gmail(dot)com> writes:
>>> I think this is the post in question:
>>> http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/
>>
>> Appears to be sheer blather, or at least not tempered by any thoughts
>> of whether it'd work in special cases. The main reality underlying it,
>> I think, is that WAL replay will complain if files are missing. But
>> there will be no WAL log entries for temp tables.

Just thought of another thing - temp tables aren't the only thing temp
tablespaces are used for. Even if I were creating or updating a
non-temp table, if it involves (say) a large sort, the temp tablespace
is what's used for this. Is volatile storage of temp tablespaces safe
for these as well?

And does this mean it's also OK to omit temp tablespaces from base
backups as well?

>> Having said that, there's no substitute for testing ;-). I wouldn't be
>> surprised for instance if the DB won't restart until you create the
>> tablespace directories, and maybe even PG_VERSION files therein. But it
>> really shouldn't have an issue with the files underlying a temp table
>> not being there anymore; at worst you'd get some bleats in the log.
>
> Do you know what exactly I would need to create in place for this to work out?
>
> This isn't exactly the same test as what I should be running (pulling
> the cord), but I just tried:
>
> create tablespace ephemeral location '/mnt/eph0/pgtmp';
>
> Then reloading PG with temp_tablespaces = 'ephemeral' in postgresql.conf.
>
> At this point I (cleanly) stop PG, ran rm -rf /mnt/eph0/pgtmp/,
> started PG, and ran:
>
> create temp table foo (a int);
>
> which failed with:
>
> ERROR: could not create directory
> "pg_tblspc/16384/PG_9.1_201105231/11919": No such file or directory
>
> Once I did
>
> mkdir -p /mnt/eph0/pgtmp/PG_9.1_201105231/11919
>
> everything seems to be back to normal.
>
> Is this the extent of what I can expect, *always*, even if I had run
> the proper experiment involving pulling the cord (or at least kill
> -9)?

In other words, I guess, I'm asking because of Xof's comment on that blog post:

"That’s true if you recreate the PG_ directory in the tablespace;
otherwise, you get the error:
reindexdb: reindexing of database "test" failed: ERROR: could not
create directory "pg_tblspc/69944/PG_9.2_201204301/61884": No such
file or directory
However, that’s not a guaranteed feature of PostgreSQL, and I would
never rely on it."