1

I'm using Postgres 13 & recently dropped a table with cascade on to drop all indexes, etc.

It turns out we had an old view that was tied to that table, and we need to recover the view or at least recover the query in the view to use it on a new table we are going to create.

Is it possible to recover the SQL text or do a flashback like oracle has but in Postgres?

Improve this question
4
  • Get it from your source control system (e.g. git) where all your code is stored
    – user1822
    Commented Jan 12, 2023 at 18:34
  • So we do have a backup of the schema, but pg_dump doesn't include views, it looks like.
    – Jose Lopez
    Commented Jan 12, 2023 at 19:13
  • 1
    pg_dump includes view, unless you went out of your way to exclude them. Is that your only back up, or do you have others? You can restore a physical backup on a temp server, then dump the view definition out of that.
    – jjanes
    Commented Jan 13, 2023 at 3:35
  • If you have a plain format backup and know the name of the view, just use an editor like vim to find the CREATE VIEW statement.
    – Erwin Brandstetter
    Commented Jan 15, 2023 at 10:22

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.