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


  • "and the only way to keep my postgres database was accessing to data with my folder browser (nautilus)". Out of interest: why? And why did you only copy a subdirectory? – Craig Ringer Aug 23 '14 at 14:55

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.