Originally Posted by tariqf
I need to restore some data from yesterday into my postgres database. I only have a filesystem backup each night so I have the full contents of /var/lib/pgsql which contains data, base, global etc. etc.
How do I copy my backup database folder into my currently running or maybe a spare server so that I can query yesterdays data to export back into the current db?
You *never* copy your backup data directory into your currently running PostgreSQL directory. That's a perfect path to disaster. There is one and only one way that might (might) help you:
1. Stop the postgreSQL service
2. Make a complete backup of your *existing* postgreSQL data folder. Be very careful that this is a full backup.
3. Remove your existing data folder, and copy your backup folder back into place. This means your *complete* backup of your data directory, not just the particular location that you think might be your database (/mnt/buffalo/daily.0/var/lib/pgsql/data/base/156814)
4. Start PostgreSQL running again, and do a complete pg_dump of the database you are trying to rescue.*
5. Stop the PostgreSQL service again
6. Remove the older version of your data directory, and restore your current copy into place.
7. Start PostgreSQL and then begin trying to merge your pg_dump records with your current copy of the database.
*Note that I can't promise you the the backed up data directory has perfect data integrity. Simply copying your data folder while PostgreSQL is running is a bad practice and can result in corrupt backups. The only right way to backup PostgreSQL is to run pg_dump regularly.