how to restore postgresql DB without backup

Question

Forgot to make a backup. Now I have harddrive with databases and new system with empty postgres. Can I somehow restore databases? by simple copy of files etc?

Answer 1

If you have the full data directory of your old postgresql system (and if it was the same version, or differing only in a revision number) you can just try to put it in place of your data directory in your new postgresql installation. (Of course, stop postgres server before doing this).

It's basically the same procedure used when upgrading postgresql, when there is no need to do backup-restore.

Edit: As pointed out in the comments, I assume not only same (or almost same) version, but same architecture (32 - 64 bits , Linux - Windows, etc)

Answer 2

In addition to the leonbloy's answer, you could try pg_migrator, especially if you need to upgrade from 8.3 to 8.4 (and 9.0 eventually).

Answer 3

In your case you have the files, but if you haven't, Maybe, only maybe, you can do something with the logs of the database, you can try to see the log of the statements in the database normally in /var/log/postgresql/postgresql.log, if it is there or close to it, and if log_statements = 'mod' or 'all' is set up before, you can recovery some of your data.

Table by table, by searching by insert into in this tables in all or recent history of database. You can cut text with some Unix tools to get only the statements and put a ";" at the end of each statement, and another important queries like delete, etc.

But you must to do it table by table, and data must be there, and database don't runned up too much time without backups.

In certain cases you just need the last operation or something like this to save the day.

This, however, its just for Apolo 13 disasters moment and never can replace a good backup.