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

Re: Schemas, databases, and backups

From: Martin Foster <martin(at)ethereal-realms(dot)org>
To: Thomas Harold <tgh(at)tgharold(dot)com>,PostgreSQL Novice List <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Schemas, databases, and backups
Date: 2005-11-24 17:25:13
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-novice
Thomas Harold wrote:
> 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?)
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

Its pretty trivial to code it in a bash script and I've done it for a 
while now.    Since these are primarily command line tools, you can 
easily pipe the output compress the files, sort it by date and make your 
life a bit easier overall.

I included a copy of mine, which was used to backup from another server. 
   It will also automatically store them in monthly folders with every 
day inside for ease of retrieval.   Its commented and I make use of 
variables to speed things along and to my knowledge is still setup to 
backup databases listed.

Please note however that this script has not been used in a while, the 
servers it was intended to work on are no longer operational.  So any 
passwords or addresses are of no consequence!

	Martin Foster
	Creator/Designer Ethereal Realms

Attachment: pg_backup.shl
Description: text/plain (3.9 KB)

In response to

pgsql-novice by date

Next:From: Thomas HaroldDate: 2005-11-24 18:47:35
Subject: Re: Schemas, databases, and backups
Previous:From: Jaime CasanovaDate: 2005-11-24 16:23:18
Subject: Re: Schemas, databases, and backups

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