Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ian Lawrence Barwick barw...@gmail.com writes: 2013/5/1 Yang Zhang yanghates...@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)? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 yanghates...@gmail.com wrote: On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ian Lawrence Barwick barw...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
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 back together if the underlying storage of a tablespace disappears, but that should be thought of as disaster recovery, not as a oh, third time this week operation. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
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 database by recreating the directory in order for the database to be operational again. This isn't a documented PostgreSQL API; it's a disaster recovery procedure. It works now, it probably will work in the future, but I don't think there'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 something routine. That being said, you can make it work today if it is critical that it do so. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
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 of PostgreSQL'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 x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
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: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
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 dar...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
2013/5/1 Yang Zhang yanghates...@gmail.com: On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan dar...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
On Tue, Apr 30, 2013 at 7:14 PM, Ian Lawrence Barwick barw...@gmail.com wrote: 2013/5/1 Yang Zhang yanghates...@gmail.com: On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan dar...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
On 2013.04.30 7:14 PM, Ian Lawrence Barwick wrote: 2013/5/1 Yang Zhang yanghates...@gmail.com: On Tue, Apr 30, 2013 at 5:31 PM, Darren Duncan dar...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
Ian Lawrence Barwick barw...@gmail.com writes: 2013/5/1 Yang Zhang yanghates...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
On 01/05/13 12:36, Yang Zhang wrote: On Tue, Apr 30, 2013 at 7:21 PM, Julian Glass temp...@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) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] OK to put temp tablespace on volatile storage or to omit it from backups?
On Tue, Apr 30, 2013 at 8:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Ian Lawrence Barwick barw...@gmail.com writes: 2013/5/1 Yang Zhang yanghates...@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). -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
