Re: Schemas, databases, and backups - Mailing list pgsql-novice
From | Martin Foster |
---|---|
Subject | Re: Schemas, databases, and backups |
Date | |
Msg-id | 4385F779.1010805@ethereal-realms.org Whole thread Raw |
In response to | Schemas, databases, and backups (Thomas Harold <tgh@tgharold.com>) |
List | 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 martin@ethereal-realms.org #!/bin/bash ####################################################################### # PG_BACKUP.SHL # # Created : Martin Foster # Modified : 24-Nov-2004 ####################################################################### # # Due to limitations with the PostgreSQL database backup tool not # accepting passwords on the command prompt to automate the process and # thus requiring a loose set of security. This script instead calls # the backup proceedure locally on the server and through SSH/RSH sends # the results back over the network to the appropriate workstation. # # Advantages to this system when making use of SSH is the encryption # and certificate based authentication allowing for security while # ease of use. This also runs the backup on the database server # allowing for a copy to be generated simultaneously if so desired. ################# # Data Members # # Site specific configuration BACKUP='/var/archives/postgres' # Backup path DB='ethereal' # Databases to backup CREDENTIALS='martin@io' # Username/host combination # # Date based hiearchy DAY=`date +%d` MONTH=`date +%m` YEAR=`date +%y` # # Location of utilities ARCHIVER="/usr/bin/bzip2" # Archive to compress stream DUMP="/usr/local/bin/pg_dump -cDU postgres" # Database dumping utility FILTER="/usr/bin/grep -v 'INSERT INTO param'" # Filter used with dump REMOTE="/usr/bin/ssh" # Remote access capability # # File settings PERM='0644' # Permissions to take on OWNER='root' # Owner of file GROUP='wheel' # Owner group EXT='.bz2' # Extension to use # # Verbosity VERBOSE='NO' # Display information to screen ################# # Program area # Explain what happens if [ "$VERBOSE" = "YES" ] then # Go change into the log dir if [ -d $BACKUP ] then # see if month dir exists in log dir - if not create one if [ ! -d "$BACKUP/$YEAR$MONTH" ] then # date dir does not exist in the log dir - create one /bin/mkdir "$BACKUP/$YEAR$MONTH" 2>&1 >/dev/null if [ $? -ne 0 ] then # could not create log date dir echo -e " Could not create: $BACKUP/$YEAR$MONTH - exiting!\n" exit 1 else # created dir echo -e " Created: $BACKUP/$YEAR$MONTH\n" fi fi # Change working directory cd "$BACKUP/$YEAR$MONTH" # Cycle through list for SINGLE in $DB do echo -e " Initiating backup of database: $SINGLE" $REMOTE $CREDENTIALS "$DUMP $SINGLE | $FILTER | $ARCHIVER" > $SINGLE-$MONTH$DAY$EXT echo -e " Setting permissions..." chmod $PERM $SINGLE-$MONTH$DAY$EXT echo -e " Setting permissions..." chown $OWNER $SINGLE-$MONTH$DAY$EXT echo -e " Setting permissions..." chgrp $GROUP $SINGLE-$MONTH$DAY$EXT # Verify file exists if [ -f "$SINGLE-$MONTH$DAY.bz2" ] then echo -e " Backup succesful!" else echo -e " Backup failed!" fi done else # Warning echo -e " Working directory not found: $BACKUP\n" fi # Lack of detail else # Go change into the log dir if [ -d $BACKUP ] then # see if month dir exists in log dir - if not create one if [ ! -d "$BACKUP/$YEAR$MONTH" ] then # date dir does not exist in the log dir - create one /bin/mkdir "$BACKUP/$YEAR$MONTH" 2>&1 >/dev/null if [ $? -ne 0 ] then # could not create log date dir echo -e "Could not create: $BACKUP/$YEAR$MONTH - exiting!\n" exit 1 fi fi # Change working directory cd "$BACKUP/$YEAR$MONTH" # Cycle through list for SINGLE in $DB do $REMOTE $CREDENTIALS "$DUMP $SINGLE | $FILTER | $ARCHIVER" > $SINGLE-$MONTH$DAY$EXT chmod $PERM $SINGLE-$MONTH$DAY$EXT chown $OWNER $SINGLE-$MONTH$DAY$EXT chgrp $GROUP $SINGLE-$MONTH$DAY$EXT # Verify file exists if [ ! -f "$SINGLE-$MONTH$DAY$EXT" ] then echo -e " Backup of $SINGLE failed!" fi done else # Warning echo -e " Working directory not found: $BACKUP\n" fi fi
pgsql-novice by date: