Re: Use of non-restart-safe storage by temp_tablespaces

Bruce Momjian <bruce(at)momjian(dot)us> writes:

> On Wed, May 31, 2017 at 08:28:51AM -0700, Mark Dilger wrote:
>
>> > Uh, I thought only the sessions that created the temporary objects could
>> > see them, and since they are not in WAL and autovacuum can't see them,
>> > their non-existence in a temporary tablespace would not be a problem.
>>
>> You are correct. I was thinking about an extension to allow unlogged
>> tablespaces on temporary filesystems, but got the words "unlogged" and
>> "temporary" mixed up in my thinking and in what I wrote. I should have
>> written that unlogged tablespaces would only host unlogged tables and
>> unlogged indexes, such that users are not surprised to find their data
>> missing.

Very late to the discussion here...

At my site, we have an NVME disk as temp_tablespace.

It's used not only for disk-spilling temp operations. There are temp
tables, UNLOGGEd persistent tables.

And you guessed it. In spite of our warnings, users have created LOGGEd
tables in there.

This has made on a few ocasions our SAN snapshots, started on a
different system fail apparently due to something in the crash recovery
data trying to update pages on one of the real tables :-)

The SAN snaps capture the entire pgdata and WAL pg_xlog area but there
is no attempt to copy the NVME device when the snaps are made.

There's an event trigger plus batch job now running tou avoid this risk.

We realize too that there are implications here if a backup is
instantiated and PITR is done.

Just FYI that there could be others running like this ignorant of the
potential gotchas.

>>
>> On reflection, I think both features are worthwhile, and not at all exclusive
>> of each other, though unlogged tablespaces is probably considerably more
>> work to implement.
>
> TODO item added:
>
> Allow tablespaces on RAM-based partitions for temporary objects
>
> and I wrote a blog entry about this:
>
> https://momjian.us/main/blogs/pgblog/2017.html#June_2_2017
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + As you are, so once was I. As I am, so you will be. +
> + Ancient Roman grave inscription +

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net
p: 312.241.7800