Re: Restoring a database - Mailing list pgsql-general

Chris Henderson wrote:
>> pg_dumpall archive.  If you look at the backup file, you'll find that
>> it's just straight SQL.  If you want to restore a particular database
>> out of it and not all of them, then you will need to edit the sql file
>> to include only what you want to restore.  Then you simply pass it
>> through psql like so:
>
> Thank you all for your help.
>
> Could anyone please share his or her backup and restore script? I want
> to backup all databases plus schemas in one server via crontab every
> night and restore one or all of the backed up databases to another
> server when I need to.
>
> Thanks again.
>

Here ya go.
[DEFAULT]
; List of files to tar
backup_files: /var/spool/mail /etc/ /home
pg_dump: /usr/bin/pg_dump
psql: /usr/bin/psql
pg_dump_user: postgres
pg_dumpall: /usr/bin/pg_dumpall
pg_port: 5432
svn: /usr/bin/svn
text_archive: /backups/full
binary_archive: /backups
; the path to the repos you want to backup
svn_repo_paths: /home/lacey/test/test1 /home/lacey/test/test2 /home/lacey/test/test3
; the owner and group that will own the archive files
archive_owner: root
archive_group: admin
; the location of the backup directory
backup_dir: /backups
; Notify messages
notify_critical:
notify_warning:
notify_ok:
#!/usr/bin/python

""" LICENSE

Copyright Command Prompt, Inc.

Permission to use, copy, modify, and distribute this software and its
documentation for any purpose, without fee, and without a written agreement
is hereby granted, provided that the above copyright notice and this
paragraph and the following two paragraphs appear in all copies.

IN NO EVENT SHALL THE COMMAND PROMPT, INC. BE LIABLE TO ANY PARTY FOR
DIRECT, INDIRECT, SPECIAL, INCIDENTAL, OR CONSEQUENTIAL DAMAGES, INCLUDING
LOST PROFITS, ARISING OUT OF THE USE OF THIS SOFTWARE AND ITS DOCUMENTATION,
EVEN IF THE COMMAND PROMPT, INC. HAS BEEN ADVISED OF THE POSSIBILITY OF
SUCH DAMAGE.

THE COMMAND PROMPT, INC. SPECIFICALLY DISCLAIMS ANY WARRANTIES,
INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
FITNESS FOR A PARTICULAR PURPOSE. THE SOFTWARE PROVIDED HEREUNDER IS ON AN
"AS IS" BASIS, AND THE COMMAND PROMPT, INC. HAS NO OBLIGATIONS TO
PROVIDE MAINTENANCE, SUPPORT, UPDATES, ENHANCEMENTS, OR MODIFICATIONS.

"""

import os
import sys
import pwd
import grp
import stat

from stat import *
from ConfigParser import *
from os import *
from sys import *
from optparse import OptionParser
from time import gmtime, strftime

# Initiate command line switches

usage = "usage: %prog [options] arg1 arg2"
parser = OptionParser(usage=usage)

parser.add_option("-B", "--backup", dest="backup", action="store_true", help="Whether or not to run a backup")
parser.add_option("-P", "--pgbackup", dest="pgbackup", action="store_true", help="Backup postgresql")
parser.add_option("-F", "--files", dest="files", action="store_true",  help="File system backup")
parser.add_option("-G", "--globals", dest="globals", action="store_true", help="PostgreSQL users and groups")
parser.add_option("-C", "--config", dest="configfile", action="store", help="Location of config file", metavar="FILE")
parser.add_option("-T", "--text", dest="text", action="store_true", help="Create a text pg_dumpall")
parser.add_option("-X", "--debug", dest="debug", action="store_true", help="Lots of Debug output")

(options, args) = parser.parse_args()

backup = options.backup
files = options.files
configfile = options.configfile
pgbackup = options.pgbackup
debug = options.debug
globals = options.globals
text = options.text

if not configfile:
   print "You must pass -C or --config"
   exit(1)

# initiate config parser
config = ConfigParser()
config.read(configfile)

# What day is it
day = strftime("%a")

# Set up our keys
backup_files = config.defaults()['backup_files']
pg_dump = config.defaults()['pg_dump']
pg_dump_user = config.defaults()['pg_dump_user']
psql = config.defaults()['psql']
pg_dumpall = config.defaults()['pg_dumpall']
text_archive = config.defaults()['text_archive']
binary_archive = config.defaults()['binary_archive']
archive_owner = config.defaults()['archive_owner']
archive_group = config.defaults()['archive_group']
backup_dir = config.defaults()['backup_dir']
pg_port = config.defaults()['pg_port']


def syntax_check_func():
   if not configfile:
      print "ERROR: --config is required"
      notify_critical_func()
      exit(1)

   if not backup:
      if not pgbackup:
         if not files:
            print "ERROR: You need one of --backup, --pgbackup or --files"
            notify_critical_func()
            exit(1)

def stat_backup_dir_func():
      exists = os.path.exists(str(binary_archive))
      if not exists:
         print "WARNING: Directory doesn't exist: %s" % (str(binary_archive))
         print "INFO: Attempting to create backup directory"
         notify_warning_func()
         try:
            os.mkdir("%s" % (str(binary_archive)))
            if text_archive:
               try:
                  os.mkdir("%s" % (str(text_archive)))
               except OSError, e:
                  print "ERROR: Could not create text archive location"
                  print "EXCEPTION: %s" % (str(e))
                  notify_critical_func()
                  exit(1)
               try:
                  # os.chown requires the numeric uid of the owner
                  # and the numeric guid of the group.
                  # So we retrieve the numbers by name.
                  if debug:
                      print "Owner", pwd.getpwnam(str(archive_owner))
                      print "Group", grp.getgrnam(str(archive_group))
                  owner = pwd.getpwnam(str(archive_owner))[2]
                  group = grp.getgrnam(str(archive_group))[2]
                  os.chown(str(binary_archive),owner,group)
               except OSError, e:
                  print "ERROR: Unable to set ownership of backup files"
                  print "EXCEPTION: %s" % (str(e))
                  notify_critical_func()
                  exit(1)
               try:
                  # Equivalent to 6770 in the shell.
                  perm = S_ISUID | S_ISGID | S_IRWXU | S_IRWXG
                  os.chmod(str(binary_archive),perm)
               except OSError, e:
                  print
                  print "ERROR: Unable to set permissions for binary archive"
                  print "EXCEPTION: %s" % (str(e))
                  notify_critical_func()
                  exit(1)
         except OSError, e:
            print "ERROR: I can go no farther"
            print "EXCEPTION: %s" % (str(e))
            notify_critical_func()
            exit(1)

def files_backup_func():
   retval = system("""/bin/tar -czPf %s/%s.files.tar.gz %s""" % (str(backup_dir),str(day),str(backup_files)))
   if retval:
      print "ERROR: files_backup_func(): I was not able to backup your files"
      print "Continuing..."
      notify_critical_func()
   try:
      # Equivalent to 0770 in the shell.
      perm = S_IRWXU | S_IRWXG
      # Allows permissions to be set on all files in the directory, regardless of depth.
      for root, dir, files in os.walk(str(backup_dir)):
         for file in files:
             fullname = os.path.join('%s', '%s') % (str(root), str(file))
             os.chmod(str(fullname),perm)
   except OSError, e:
      print "ERROR: files_backup_func(): I was unable to change the backup files permissions"
      print "EXCEPTION: %s" % (str(e))
      print "Continuing..."
      notify_critical_func()

def get_datnames_func():
   datnames = os.popen("""%s -U %s -p %s -A -t -c "SELECT datname FROM pg_database WHERE datistemplate = 'false'" """ %
(str(psql),str(pg_dump_user),str(pg_port)))
   return datnames.readlines()


def pg_dump_func():
   data = get_datnames_func()
   for row in data:
      row = row.rstrip('\n')
      if debug:
         print """DEBUG: %s -U %s -p %s -Fc -f %s/%s.%s.sqlc %s""" %
(str(pg_dump),str(pg_dump_user),str(pg_port),str(backup_dir),str(day),str(row),str(row)) 
      retval = system("""%s -U %s -p %s -Fc -f %s/%s.%s.sqlc %s""" %
(str(pg_dump),str(pg_dump_user),str(pg_port),str(backup_dir),str(day),str(row),str(row)))
      if retval:
         print "ERROR: pg_dump_func(): I was not able to backup the database"
         print "Continuing..."
         notify_critical_func()
   try:
      # Equivalent to 0770 in the shell.
      perm = S_IRWXU | S_IRWXG
      os.chmod(str(binary_archive),perm)
   except OSError, e:
      print "ERROR: pg_dump_func(): I was unable to change the backup permissions"
      print "EXCEPTION: %s" % (str(e))
      print "Continuing..."
      notify_critical_func()

def pg_dump_text_func():
   data = get_datnames_func()
   for row in data:
      row = row.rstrip('\n')
      if debug:
         print "DEBUG: %s -U %s -p %s > %s/%s.sql" % (str(pg_dumpall),
str(pg_dump_user),str(pg_port),str(text_archive),str(row)) 
      retval = system("%s -U %s -p %s > %s/%s.sql" % (str(pg_dumpall),
str(pg_dump_user),str(pg_port),str(text_archive),str(row))) 
      if retval:
         print "ERROR: pg_dump_text_func(): I was not able to backup the database"
         print "Continuing..."
         notify_critical_func()
   try:
      # Equivalent to 0770 in the shell.
      perm = S_IRWXU | S_IRWXG
      os.chmod(str(binary_archive),perm)
   except OSError, e:
      print "ERROR: pg_dump_text_func(): I was unable to change the backup permissions"
      print "EXCEPTION: %s" % (str(e))
      print "Continuing..."
      notify_critical_func()

def get_globals_func():
    retval = system("""%s -U %s -p %s -g > %s/globals.%s.sql""" %
(str(pg_dumpall),str(pg_dump_user),str(pg_port),str(backup_dir),str(day)))
    if retval:
       print "ERROR: get_globals_func(): Backing up globals failed!"
       print "Continuing..."
       notify_critical_func()

# Skeleton Notifications for Nagios

def notify_ok_func():
   if config.defaults()['notify_ok']:
      notify_ok = config.defaults()['notify_ok']
   else:
      notify_ok = None

def notify_warning_func():
   if config.defaults()['notify_warning']:
      notify_warning = config.defaults()['notify_warning']
   else:
      notify_warning = None

def notify_critical_func():
   if config.defaults()['notify_critical']:
      notify_critical = config.defaults()['notify_critical']
   else:
      notify_critical = None

if backup:
   stat_backup_dir_func()
   if files:
      files_backup_func()
   if pgbackup:
      pg_dump_func()
   if globals:
      get_globals_func()
   if text:
      pg_dump_text_func()

else:
   print "ERROR: You must select something to do"
   notify_critical_func()
   exit(1)

notify_ok_func()
exit(0)