How to restore postgresql database without backup

Question

Forgot to backup. Now I have a hard drive with databases and a new system with empty postgres. Can I restore databases somehow? a 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 differed only in the revision number), you can just try to put it instead of your data directory in a new postgresql installation, (Of course, stop the postgres server before doing so).

Basically, the same procedure is used when upgrading postgresql, when there is no need to do a backup restore.

Edit: as stated in the comments, I assume not only the same (or almost the same) version, but the same architecture (32 - 64 bit, Linux - Windows, etc.)

Answer 2

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

Answer 3

In your case, you have the files, but if you haven’t done this, it’s possible that only you can do something with the database logs, you can try to look at the statement log in the database, usually in / var / log / postgresql / postgresql.log, if it is or is close to it, and if log_statements = 'mod' or 'all' is configured earlier, you can restore some of your data.

Table by table, by searching by inserting into these tables in the entire or recent history of the database. You can cut text with some Unix tools to get only instructions and put ";" at the end of each statement and other important requests, such as deletion, etc.

But you have to do it according to the table, and the data should be there, and the database did not work too much time without backups.

In some cases, you need a final operation or something like this to save the day.

This, however, is just for the time of the Apolo 13 disaster and can never replace a good backup.