Restoring a Postgresql database

Question

Last week one of my sites server crashed. I was not able to access the ssh server and could only use the basic linux console on digitalocean.com. Because I could not get into my ssh server I was not able to perform a pg_dump to backup my database. I was however able to use rsync to pull those files out onto my hard drive. The problem is that I have no idea how to put those files back into my server. I don't believe I can use:

>psql -U username -f backupfile.sql

to restore those files because the rsync command only got me a main dir with tons of db files in it. I so not see any .sql files in there so I have no idea what to do to get that database back into my site. Does anyone have any advice or know if this is even possible? It might be better to launch my site clean and manually rebuild the database. Thank you for checking this out.

Answer 1

What you have is a physical dump of your database, not a logical dump. So you can't reload it using psql.

What you would do is copy the files over to a new server (don't move them, keep a clean copy for future efforts) which has the same OS, architecture, and version of PostgreSQL, and try to start the database. If the database were down while you did the rsync backup (and if you rsynced all the necessary files, and whatever crashed the server didn't corrupt things in the process) then the database should come back up and automatically go through its internal recovery process, get to a consistent state, and reopen.

If the database were running during the rsync, then it is very likely that starting it up will fail, or that it will report success but the data will be corrupt, perhaps in hard to detect ways.

Rebuilding it from scratch might be the best option, as then you have a known-good state.