Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I goofed and destroyed my postgresql 9.2/postgis 2.1 installation under freebsd 9.2 by trying to install mapnik which, for my inconvenience, overwrote it with 9.0.

I have, on another spindle, a tablespace and 2 databases declared. One of the databases is filled with openstreetmap data that took me 15 hours to create, so I'd rather preserve it when I reinstall 9.2, if I can. Is that possible? Under mariadb/mysql it would be trivially easy, but the pgsql docs don't seem to have much to say about the problem (that I could find).

Many thanks in advance for any appropriate advice.

share|improve this question
If you don't have the main database - pg_control, pg_xlog, etc - then those tablespaces are pretty much useless. – Craig Ringer Jan 20 '14 at 2:22
[sigh] I was afraid that's the case. Thanks for confirming it. – MMacD Jan 21 '14 at 12:01
It's probably possible to do some recovery if it's critical data there's no other way to ever get back - but you're taking serious expertise and serious time, thus serious $. It'd be nice if PostgreSQL added a larger table header as a "recovery block", and was more resilient to lost tablespaces, but where do you stop? Make UPDATE and DELETE undo-able until undo records are purged, and even then ask three times first? There comes a time when "don't do that" or "have backups, test them" becomes the answer. – Craig Ringer Jan 21 '14 at 12:45
It's really not that simple, it's a matter of compromises and priorities. All the dynamic, user-definable types and extensions in PostgreSQL mean that the system needs the catalogs to interpret the tables. It might be nice to save some recovery information alongside tables, but so far, nobody has done that. If you want it, you need to push for it, because it's not going to happen unless somebody wants it badly enough to make noise, learn the system, and come up with a concrete proposal on how to do it. – Craig Ringer Jan 23 '14 at 12:09
If it was easy to make tables recoverable as simple files, it'd be done. It isn't simple at all, though. One of the big challenges is transactional DDL, and how that affects the system. – Craig Ringer Jan 23 '14 at 12:10

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.