Postgresql – Opening a .backup file for PostgreSQL

backupdumppostgresqlrestore

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.)

Best Answer

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.

Backup without password

If you run the script as OS user postgres it should not request a password in a standard setup, because password-less peer access (or ident on older versions) is enabled in pg_hba.conf for the postgres user.

So make it a cronjob of postgres if you can. Or enable password-less access for the OS user running the job. You can do that in pg_hba.conf but, as @Richard already pointed out, the password file .pgpass file may be a more elegant solution.

More on how to connect without password in this related question.

Restore in pgAdmin

I quote the pgAdmin3 FAQ:

pgAdmin III uses PostgreSQL's pg_restore tool, which supports only the COMRESS and TAR options of pg_dump which is used for backup creation. The PLAIN format can't be interpreted by pgAdmin III and pg_restore (it can be edited manually, and executed with psql and pgAdmin III's query tool in many cases), and thus isn't accepted as valid file.

We recommend using the COMPRESS format for daily backup tasks. The PLAIN format is for advanced manual processing before executing as SQL script, and has some restrictions (no blobs) which makes it less usable for standard backup tasks.

To restore a plain SQL backup execute it with psql similar to this:

psql mydb -p 5432 -U myuser -f backup.sql

For a single database backup you have to connect to the right database. For a cluster-backup with pg_dumpall, you might as well connect to the maintenance db "postgres".

You could also load and run such a backup with the pgAdmin query tool. It's plain SQL.

Postgresql – Questions about PostgreSQL backup/restores

2) pg_dumpall is what you're looking for if you want all objects (globals, roles, etc.).

1) You noted that it is subjective. On my dev laptop (Win7 x64 .. i7 4 @ 2.2 ... 12 GB RAM ... normal, spinny disk @ 5400) I backup a 4GB pg cluster in about 15 seconds. I don't know if it scales linearly.

Finally, depending on your OS, I'd verify that it's not actually being backed up. I've seen pg on linux being backed up at the file system level using rsync. Without getting into a debate about the relative merits of these differing methods, I'd verify that this isn't already implemented.

Best Answer

Related Solutions

PostgreSQL Scheduled Database Backup

Backup without password

Restore in pgAdmin

Postgresql – Questions about PostgreSQL backup/restores

Related Question