Thread: OK to put temp tablespace on volatile storage or to omit it from backups?
I would intuit that it's fine, but I just want to make sure there are no gotchas from a recovery point of view: If I were to lose my temp tablespace upon system crash, would this prevent proper crash recovery? Also, if I were to omit the temp tablespace from the base backup, would that prevent proper backup recovery? Thanks.
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Darren Duncan
Date:
On 2013.04.30 4:55 PM, Yang Zhang wrote: > I would intuit that it's fine, but I just want to make sure there are > no gotchas from a recovery point of view: > > If I were to lose my temp tablespace upon system crash, would this > prevent proper crash recovery? > > Also, if I were to omit the temp tablespace from the base backup, > would that prevent proper backup recovery? Although it would be nice if what you said would work, I read in a recent blog post that losing any tablespace would prevent the database server from starting, even if it was only for temporary things. -- Darren Duncan
That is unfortunate. Good thing I asked, I guess. Do you have a pointer to said blog post? On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan <darren@darrenduncan.net> wrote: > On 2013.04.30 4:55 PM, Yang Zhang wrote: >> >> I would intuit that it's fine, but I just want to make sure there are >> no gotchas from a recovery point of view: >> >> If I were to lose my temp tablespace upon system crash, would this >> prevent proper crash recovery? >> >> Also, if I were to omit the temp tablespace from the base backup, >> would that prevent proper backup recovery? > > > Although it would be nice if what you said would work, I read in a recent > blog post that losing any tablespace would prevent the database server from > starting, even if it was only for temporary things. -- Darren Duncan > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Ian Lawrence Barwick
Date:
2013/5/1 Yang Zhang <yanghatespam@gmail.com>: > On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan <darren@darrenduncan.net> wrote: >> On 2013.04.30 4:55 PM, Yang Zhang wrote: >>> >>> I would intuit that it's fine, but I just want to make sure there are >>> no gotchas from a recovery point of view: >>> >>> If I were to lose my temp tablespace upon system crash, would this >>> prevent proper crash recovery? >>> >>> Also, if I were to omit the temp tablespace from the base backup, >>> would that prevent proper backup recovery? >> >> >> Although it would be nice if what you said would work, I read in a recent >> blog post that losing any tablespace would prevent the database server from >> starting, even if it was only for temporary things. -- Darren Duncan > > That is unfortunate. Good thing I asked, I guess. Do you have a > pointer to said blog post? I think this is the post in question: http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ (BTW please try not to top-post :) ) Regards Ian Barwick
On Tue, Apr 30, 2013 at 7:14 PM, Ian Lawrence Barwick <barwick@gmail.com> wrote: > 2013/5/1 Yang Zhang <yanghatespam@gmail.com>: > >> On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan <darren@darrenduncan.net> wrote: >>> On 2013.04.30 4:55 PM, Yang Zhang wrote: >>>> >>>> I would intuit that it's fine, but I just want to make sure there are >>>> no gotchas from a recovery point of view: >>>> >>>> If I were to lose my temp tablespace upon system crash, would this >>>> prevent proper crash recovery? >>>> >>>> Also, if I were to omit the temp tablespace from the base backup, >>>> would that prevent proper backup recovery? >>> >>> >>> Although it would be nice if what you said would work, I read in a recent >>> blog post that losing any tablespace would prevent the database server from >>> starting, even if it was only for temporary things. -- Darren Duncan >> >> That is unfortunate. Good thing I asked, I guess. Do you have a >> pointer to said blog post? > > I think this is the post in question: > > http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ > > (BTW please try not to top-post :) ) Argh, trying not to. Old habits die hard. The comments suggest recovery from a lost tablespace is possible. If anyone has any details or even pointers, I'd love to dig deeper, since this would yield some nice and economical performance gains for us on EC2.
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Darren Duncan
Date:
On 2013.04.30 7:14 PM, Ian Lawrence Barwick wrote: > 2013/5/1 Yang Zhang <yanghatespam@gmail.com>: > >> On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan <darren@darrenduncan.net> wrote: >>> On 2013.04.30 4:55 PM, Yang Zhang wrote: >>>> >>>> I would intuit that it's fine, but I just want to make sure there are >>>> no gotchas from a recovery point of view: >>>> >>>> If I were to lose my temp tablespace upon system crash, would this >>>> prevent proper crash recovery? >>>> >>>> Also, if I were to omit the temp tablespace from the base backup, >>>> would that prevent proper backup recovery? >>> >>> >>> Although it would be nice if what you said would work, I read in a recent >>> blog post that losing any tablespace would prevent the database server from >>> starting, even if it was only for temporary things. -- Darren Duncan >> >> That is unfortunate. Good thing I asked, I guess. Do you have a >> pointer to said blog post? > > I think this is the post in question: > > http://thebuild.com/blog/2013/03/10/you-cannot-recover-from-the-loss-of-a-tablespace/ Yes, that looks like it. -- Darren Duncan
Ian Lawrence Barwick <barwick@gmail.com> writes: > 2013/5/1 Yang Zhang <yanghatespam@gmail.com>: >> That is unfortunate. Good thing I asked, I guess. Do you have a >> pointer to said blog post? > 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. The main concern I'd have about Yang's idea is that just because *he* thinks a tablespace is "temp" doesn't mean the system knows it is, so there would be no protection against accidentally creating a regular table there; whereupon he's at risk of replay failures. 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. regards, tom lane
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Julian Glass
Date:
On 01/05/13 12:36, Yang Zhang wrote: > On Tue, Apr 30, 2013 at 7:21 PM, Julian Glass <tempura@internode.on.net> wrote: >> On 01/05/13 09:55, Yang Zhang wrote: >>> I would intuit that it's fine, but I just want to make sure there are >>> no gotchas from a recovery point of view: >>> >>> If I were to lose my temp tablespace upon system crash, would this >>> prevent proper crash recovery? >>> >>> Also, if I were to omit the temp tablespace from the base backup, >>> would that prevent proper backup recovery? >>> >>> Thanks. >>> >>> >> Yes I find this interesting. I wonder if you can snapshot the tablespace >> filesystem upon initiation, then mount the snapshot backup before >> restarting the server. >> >> Worth testing. > This strikes me as the sort of thing that is dangerous to attempt to > validate using strictly black-box testing, esp. by someone such as > myself who has little knowledge of PG internals - just because it > works for certain test cases of mine doesn't yield generalizable > guarantees. System disk failure is pretty serious, but I'm not sure if shutting down the server would be required. I have no experience yet with a tablespace failure on temporary objects (tablespaces + MV is a future interest). Is there a reason why dropping the temp objects, including the tablespace and resetting temp_tablespaces to DEFAULT would not work? Its not clear how you utilize this temp tablespace. (Assuming you are using temp_tablespaces and not defining it withing the table definition.) Regards, Julians (Sorry about missing the ML previously)
On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ian Lawrence Barwick <barwick@gmail.com> writes: >> 2013/5/1 Yang Zhang <yanghatespam@gmail.com>: >>> That is unfortunate. Good thing I asked, I guess. Do you have a >>> pointer to said blog post? > >> 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. > > The main concern I'd have about Yang's idea is that just because *he* > thinks a tablespace is "temp" doesn't mean the system knows it is, > so there would be no protection against accidentally creating a regular > table there; whereupon he's at risk of replay failures. > > 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. I mentioned this in my response to Julian but I would not trust black-box tests as strong evidence. E.g. I can imagine a system implementation wherein everything just happens to work out for my own experiments (e.g. certain temp files are not created), but not work in other circumstances (e.g. where those temp files are created and then subsequently expected).
On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Ian Lawrence Barwick <barwick@gmail.com> writes: >> 2013/5/1 Yang Zhang <yanghatespam@gmail.com>: >>> That is unfortunate. Good thing I asked, I guess. Do you have a >>> pointer to said blog post? > >> 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. > > The main concern I'd have about Yang's idea is that just because *he* > thinks a tablespace is "temp" doesn't mean the system knows it is, > so there would be no protection against accidentally creating a regular > table there; whereupon he's at risk of replay failures. So this is interesting: if it's OK to put the temp tablespace on volatile storage, is it OK to put indexes for non-temp tables into the same temp tablespace (and everything works)? > > 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)?
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Darren Duncan
Date:
You know what I think Postgres needs? Its an official way to declare a tablespace in a special way that marks the whole tablespace as being temporary/volatile, so that whenever the server starts/recovers, it assumes this tablespace doesn't exist or may not exist and can reinitialize it without trouble. There would also be restrictions then, that certain things may not be declared in such a tablespace, such as anything but temporary tables, or maybe some other things. There is such a reasonable use case for this. A feature for 9.4 perhaps? -- Darren Duncan
On Tue, Apr 30, 2013 at 11:14 PM, Yang Zhang <yanghatespam@gmail.com> wrote: > On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> Ian Lawrence Barwick <barwick@gmail.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."
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Christophe Pettus
Date:
On Apr 30, 2013, at 8:13 PM, Tom Lane wrote: > Appears to be sheer blather, or at least not tempered by any thoughts > of whether it'd work in special cases. As the author of that blog post, I'd go with option (b). :) And that lack of tempering was actually the point. The point there was that having the tablespace directories disappear shouldn't be considered a normal operational model. (Like, for example, putting a tablespace in a RAM disk.) There's no question that you can patch the database backtogether if the underlying storage of a tablespace disappears, but that should be thought of as disaster recovery, notas a "oh, third time this week" operation. -- -- Christophe Pettus xof@thebuild.com
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Christophe Pettus
Date:
On Apr 30, 2013, at 11:34 PM, Yang Zhang wrote: > 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." Well, here's the core situation: If the underlying storage for a tablespace disappears, you have to patch up the databaseby recreating the directory in order for the database to be operational again. This isn't a documented PostgreSQLAPI; it's a disaster recovery procedure. It works now, it probably will work in the future, but I don't thinkthere's any guarantee that the procedure that works today will work tomorrow. Thus, I really don't recommend making an operational decision that the lost of a tablespace's storage is considered somethingroutine. That being said, you can make it work today if it is critical that it do so. -- -- Christophe Pettus xof@thebuild.com
Re: OK to put temp tablespace on volatile storage or to omit it from backups?
From
Christophe Pettus
Date:
On Apr 30, 2013, at 11:14 PM, Yang Zhang wrote: > 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)? I would not count on it. And if it works 100% reliably now, it might not on a future version of PostgreSQL. As Josh Berkus pointed out to my off-list, there are two competing definitions of the term "recover" in use here: 1. In my blog post, the definition of "recover" was "bring up the database without having unusually extensive knowledge ofPostgreSQL's internals." 2. For Tom, the definition of "recover" is "bring up the database if you have appropriate knowledge of PostgreSQL's internals." You can't recover from the lost of a tablespace per definition #1. You can per definition #2. I'd strongly suggest that relying on definition #2, while absolutely correct, is a poor operational decision for most users. -- -- Christophe Pettus xof@thebuild.com