Postgresql – Import binary database file into postgres

importpostgresql

I have a quite newbie question that I do not know if is possible or not:

I had to format my computer, and the only way to keep my postgres database was accessing to data with my folder browser (nautilus)

So now I have the data folder with a lot of binary data inside, from /var/lib/postgresql/9.3/main/base

My question is:

Is there any form to import theese data to my (now) new postgres database?

I think copy paste is not going to work.

Maybe with some import? Or have I permanentely lost all my data?

I run postgres 9.3 in Ubuntu 14.04

Thanks

Best Answer

Assuming you took a copy of the whole data directory including pg_xlog, pg_clog, global, base, etc, then you can simply ensure that PostgreSQL 9.3 is installed and:

postgres -D /path/to/data/directory -c "port=5433"

then in another terminal use pg_dump to dump the database(s) from the database running on port 5433 with (eg) pg_dump "dbname=mydb port=5433".

This process is well-covered elsewhere; it's just starting a new PostgreSQL server and dumping the database. So I won't go into detail and cover all the possible errors, etc.

However, if you only copied the base directory, you're pretty much stuffed. The data is sort-of there, but it's missing all the vital information that tells the system what databases there are, handles crash-safe updates, keeps track of rolled back vs committed transactions, and lots more.

If you only have base, recovery might be possible but would probably involve paying an expert for a fair bit of time to try to extract the data. Even then, it'd likely be significantly corrupted - duplicates in primary key columns, deleted data reappearing, old versions of updated rows reappearing, recently committed transactions only half-complete, etc.

So I hope you kept backups.

Related Question