0

I have a table in Postgresql and a column with hstore type. This requires hstore extension. By accident I've removed this addon from Postgresql and the column disappeared. I've reinstalled the addon but the column is still missing. Is it possible to retrieve the missing data?

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
freakish
  • 54,167
  • 9
  • 132
  • 169
  • How did you "remove this addon from PostgreSQL" exactly? Show the exact series of commands if possible. – Craig Ringer Jul 01 '14 at 11:50
  • @CraigRinger I've fired `DROP SCHEMA public` (wanted fast cleaning) which apparently caused that. The data I'm talking about was in different schema. – freakish Jul 01 '14 at 12:16
  • 1
    If the hstore extension was in the `public` schema and was used from other schemas that should've failed with an error indicating that other objects depend on it (and listing them). Did you use `CASCADE`? – Craig Ringer Jul 01 '14 at 12:29
  • @CraigRinger Ah, yes, I've used `CASCADE`. Sorry, I've forgotten to mention. I've used it since it wouldn't let me delete nonempty tables otherwise. Was stupid idea. I finally understand now that I've actually called `DROP COLUMN` implicitely. I guess that it can't be undone? – freakish Jul 01 '14 at 12:35
  • 2
    Just a tip for the future to prevent such a thing: that's one of the reasons I install extension into a different schema (`create extension hstore with schema hstore;`). I also always make them owned by the superuser, so a regular user cannot drop them. And finally if I want to cleanup stuff for a single user I use `drop owned by my_user` –  Jul 01 '14 at 13:27
  • @a_horse_with_no_name Yes, that's a great tip. I will do that from now on, thanks. – freakish Jul 01 '14 at 13:28

1 Answers1

2

You used DROP SCHEMA public CASCADE.

This drops all objects that depend on the schema.

hstore was installed in the public schema. When it was dropped, so were columns of hstore type that depended on it. PostgreSQL emitted a message for each object it dropped, including those columns.

(If you don't use CASCADE, Pg tells you what it'll drop if you do use CASCADE).

Stop the database now, and make an immediate file-system level backup while the DB is stopped.

If you haven't updated the table contents since the data is probably still there and can possibly recovered with some surgery to PostgreSQL's system catalogs.

Then you can (maybe) hack the column in pg_attribute to point to the new oid of the hstore type and clear attisdropped to get the column back. Maybe. Don't try anything like this on your main copy, you must do it on a copy of the backup you made that you start just for this purpose, in case it just makes things worse.

Any rows updated since you dropped the column are probably just gone forever. It's possible they could still be on disk and just have xmax set, in which case pg_dirtyread can maybe see them. If they got VACUUMed and overwritten then they're just not coming back, though.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
  • Wow, sounds like some kind of black magic TBH. :) Luckily the data is not sensitive and I can recreate it at any time (it's just quite time consuming). I will try your ideas as well. Thanks! – freakish Jul 01 '14 at 13:27