Diff for "DatabaseBackups"

Backing up the database

It is occasionally useful to backup the database content manually.

However, it is probably not useful to backup the entire Babase database. What is desired is to backup just the data stored in the babase schema, or perhaps another schema.

There is a way to backup the entire database from phpPgAdmin, using Export after choosing the database, but I don't believe there's a way to backup just one schema.

This describes how to backup a schema, or a database, by logging into papio.

• Login to papio

  $ ssh YOU@papio.biology.duke.edu
  YOU@papio.biology.duke.edu's password:
  Last login: Thu Jun 22 14:34:22 2006 from host.example.com
  [YOU@papio ~]$

• Backup the database

  [YOU@papio ~]$ pg_dump --file YOURBACKUP.custom --host=localhost --format=c --schema=babase --data-only --user YOU babase

This produces a file YOURBACKUP.custom in your current directory. You can transfer this file to your PC and burn it to a CD if desired.

Backup your schema

To backup a different schema, yours for example, alter the --schema argument. The following command will backup your schema.

[YOU@papio ~]$ pg_dump --file YOURBACKUP.custom --host=localhost --format=c --schema=YOU --data-only --user YOU babase

Backup the entire database

To backup the entire database, not just a schema, leave off the --schema argument.

[YOU@papio ~]$ pg_dump --file YOURBACKUP.custom --host=localhost --format=c --data-only --user YOU babase

Backup the test database

To backup a different database, change the name of the database that appears at the end of the command.

[YOU@papio ~]$ pg_dump --file YOURBACKUP.custom --host=localhost --format=c --data-only --user YOU babase_test

Backup for restoring into another database product

You can have the backup be a series of SQL commands that, when run, will restore the data. This is useful if you would like to put the data into a non-PostgreSQL database.

The following command backs up the entire database, including table definitions, and saves it as SQL.

[YOU@papio ~]$ pg_dump --file YOURBACKUP.sql --host=localhost --format=p --user YOU babase