Postgresql – Restoring a database using Postgres data files instead of .sql files

Without knowing your specific errors, I dont know what the easiest approach to recovering your database is. For example, there could be minor problems like bad settings that cause PostgreSQL to be unable to start. I would recommend asking a new question about getting PostgreSQL to start before looking at how to recover your data.

However, I can answer what are some aspects of your question about recovering data. I have actually never seen data corruption in PostgreSQL, and so all my experience with it is second-hand (email list threads and the like). It is very rare, and usually related to failing hardware. If you do have data corruption, my recommendation is to restore from backup onto a new server while you run diagnostics and determine the problem on your existing server.

When you talk about recovering from the files in pg_xlog you are talking about using write logs as incremental backups. You still need a compatible starting point, which means effectively you are going with a base backup plus all pg_xlog segments created from that point onward.

Now, if you have no good backups, and you have a lot of important data, it may be possible to hire an expert for significant expense to recover some or all of your data. This is not cheap.

I think your immediate steps are to step back and ask why PostgreSQL is not starting and see what you can do about that first before assuming you need to recover your data. If you need to recover your data, you hopefully have backups.

You can't just copy base from the old cluster to the new one. It doesn't work like that. You'll be missing the transaction logs, transaction commit statuses, global catalogs, and all the other components that make those databases make any sense.

Instead, pg_createcluster in the new location, then replace the entire data directory with the one from your old system. That's the directory with base, pg_xlog, global, etc in it.