Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm going to warn you that whoever explains this needs to talk to me like I'm five years old, because this is something way, way beyond my level of expertise.

In short, I have what I think is a PostgreSQL dump file that someone sent me, and I need to be able to open it up to extract the information. I was expecting a simple tabular format (such as an Excel spreadsheet), but I got something way more complicated (a .backup file). I know how to run some basic SQL queries, but I think whoever sent this to me strongly overestimated my knowledge of database programs. Could someone give me a very, very, VERY simple, completely 110% spoon-fed explanation on how to open this?

(As an aside, I know the person who sent this to me and I know the nature of the file, so I know it's not something completely different.)

share|improve this question

migrated from Mar 12 '12 at 12:26

This question came from our site for professional and enthusiast programmers.

You cannot just "open" a database backup. You have to restore the database. For this, you first need to install PostgreSQL. The latest version should be capable of taking backups from older versions, too. But older version may have problems with newer features in the backup. So your version should be at least as recent as the source.

How you install PostgreSQL depends on your OS that you cunningly kept a secret. In Debian it's basically a matter of:

apt-get install postgresql-8.4

(Or you take postgresql-9.1 from the backports.)

Depending on the format (there are various) you can then restore the backup. If the backup holds a complete database cluster (produced with pg_dumpall - ask your friend!), you can simply:

psql postgres -f /path/tp/file.sql

To view the data you would best use a GUI like pgAdmin. Or you learn how to use SQL. :)

For more advise you would need to provide more information. Like a short(!) sample of the backup and how it was produced.

For more about PostgreSQL start by reading the manual or one of the thousand pages on the web.

But really, your friend should just export the relevant data do a CSV file that you can open in Open Office Calc or some other spreadsheet application. Or even any text editor.

share|improve this answer
I know that I had to install PostgreSQL. I did that, but I didn't have the slightest clue what to do after that. I read the help files, but I just kept spinning my wheels, because I really had no idea what I was doing. My OS is Windows 7. As for version, I think the thing I'm trying to restore is 8.4, but I was using 9.1. Does that matter? Should I just download an older version? As for a sample of the backup, I can't do that. The information is confidential. And, I wouldn't really call this person a friend. He's a co-worker, and it's not even my job to know how to do this! – Yikes Mar 12 '12 at 12:54
@Yikes: PostgreSQL 9.1 should be fine for a backup from 8.4. – Erwin Brandstetter Mar 12 '12 at 12:58

Since you don't show the content of the file I'm going to assume that it was created with the pg_dump command. If that's the case, you will see a lot of SQL-commands in it, such as CREATE TABLE, INSERT INTO and such (open it in any text-editor of your choice to find out).

If it's in this format, restoring it is simple. In short you will need to load the file into a database, something like psql -a < backup_file and then dump it again in a better format for you, like tabular data.

But since this is just plain guesswork (we don't know what the backup file really is), I will stop here unwil you provide more information.

share|improve this answer

It could be most useful to start by using the pg_restore command to check the format of the file that's been sent. If your file was named "mydata.backup", the command would be:

pg_restore --list mydata.backup

(On Windows this would be pg_restore.exe)

This will output some text to show the format of the file, and the data tables that are in the file. This will confirm that you've actually received a PostgreSQL backup file, and not some other type of file.

Then if you run:

pg_restore mydata.backup

This will start displaying the text in the backup file to the screen (standard output). There are three different possible formats but pg_restore will detect which one was used, and start outputting the text.

If you were really desperate to get the data from this file, and don't have access to a more convenient format, then you may discover that some of it is quite neatly arranged into space-separated columns within the backup file. There'd be some possibility of pasting these into a text editor, saving to disk, then loading into a spreadsheet. It's certainly not ideal though.

A PostgreSQL backup file is not really a data distribution format though. It should just be for backups.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

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