Schemas, databases, and backups

From: Thomas Harold <tgh(at)tgharold(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Schemas, databases, and backups
Date: 2005-11-24 12:51:03
Message-ID: 4385B737.3070304@tgharold.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

According to my reading of the pgsql documentation, the two basic backup
scripts are pg_dump and pg_dumpall. pg_dump allows you to dump a single
database to a file, while pg_dumpall dumps all of the databases to a
single file.

Currently, we use MSSQL's built-in backup facility. That allows us,
with a single command, to dump every database to separate files on a
daily basis (and we keep 14-days online). That makes recovering from a
glitch in one of the databases very easy, and it's rather simple to go
back to a particular day.

Also, schemas are new to us, so I'm still thinking about how they will
affect our processes and databases.

(I'm betting that the ultimate answer is going to be to look for some
3rd party tool in pgFoundry.)

So, now for the questions:

1) Is there a tool (or is this easily scripted in bash?) that would
iterate through the databases in pgsql and dump them to individual
files? I'm guessing that we would query pg_databases and dump the
database names to a file (how?) and then parse that to feed to pg_dump
(I can figure this bit out myself).

2) What if I wanted to dump individual schemas? Is this dangerous / not
recommended? (Probably not... if I can have relationships between
tables in different schemas?)

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Peter George 2005-11-24 15:53:32 8.0.2 Install Problems on Win XP
Previous Message dawizz 2005-11-24 06:46:52 Re: Linux DB designer tools