Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-novice by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group