I can't speak to 1 but for 2, I don't know that your position should be pushing the business toward a specific RPO (recovery point objective). They may not be aware that they'd have to re-enter all the data for a day if things go belly up. Talk to them, find out how much data loss they're willing to tolerate. If they say 24 hours is too much, great, then that indicates the current approach is insufficient for their needs. If that requires hardware purchase to meet the RPO, then they will need to provide funding or accept their current max data loss. Finally, document the outcome in some public place and then test on your restores a recurring basis to ensure you are able to meet that RPO.
That said, there are plenty of other reasons to have data and log (and temp) on separate drives. Some of them documented on this question https://serverfault.com/questions/38511/ms-sql-layout-for-best-performance
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.
Best Answer
Yes, it's fairly simple. You need to copy the database from the old disk image to a directory in the new host then start it using the same major version of PostgreSQL compiled with the same options (integer datetimes on/off, etc).
You can start the DB with a command like
PGPORT=5433 pg_ctl -D /path/to/copy/of/old/database start
. You must use the same PostgreSQL major version that the DB was created with before. If you're not sure what that was, check thePG_VERSION
file in old data directory.Once you've started a server with the old database you can then
pg_dump
the databases(s) andpg_dumpall --globals-only
the users, etc, and load them into the new database.Do not try to start the database directly on the old disk image, make a copy and start the copy.
You haven't provided details about versions, operating systems, how you installed Pg on the old and new machines, etc, so it's hard to provide more detail.