Thread: Restoring a database
I backup all my databases by using pg_dumpall - pg_dumpall > /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze, postgres, template0 and template1 I guess this backs up the schemas as well. Now I want to restore one of the databases and schema from this backup dump file onto a different server. The databases is call "analyze". Does anyone know how to do that with pg_restore? Thanks.
On Wed, Oct 15, 2008 at 3:19 PM, Chris Henderson <henders254@gmail.com> wrote: > I backup all my databases by using pg_dumpall - pg_dumpall > > /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze, > postgres, template0 and template1 > I guess this backs up the schemas as well. > > Now I want to restore one of the databases and schema from this backup > dump file onto a different server. The databases is call "analyze". > Does anyone know how to do that with pg_restore? Thanks. This is a common mistake. Regular pg_dump / pg_dumpall without custom format are just plain text dumps and are designed to be restored with psql. You can take a text editor (or tail / head etc) to the text file and chop out the parts you want, or you can make a compressed backup and use pg_restore to do things like what you want. I generally do a pg_dumpall --globals, and then pg_dump for each database into a separate file.
On 15/10/2008 22:19, Chris Henderson wrote: > I backup all my databases by using pg_dumpall - pg_dumpall > > /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze, > postgres, template0 and template1 > I guess this backs up the schemas as well. > > Now I want to restore one of the databases and schema from this backup > dump file onto a different server. The databases is call "analyze". > Does anyone know how to do that with pg_restore? Thanks. Off the top of my head, I don't think you can do this with pg_restore. pg_dumpall produces a plain-text dump, which means that you need to use it as input to psql to restore from it; pg_restore uses the non-text formats. However, if the only other databases in the dump are the built-in ones, they will add very little time to the restore process. In any case, there's no need to back up databases postgres, template0 and template1, unless you've added custom stuff to template1 that you need to keep. Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
Chris Henderson wrote: > I backup all my databases by using pg_dumpall - pg_dumpall > > /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze, > postgres, template0 and template1 > I guess this backs up the schemas as well. > > Now I want to restore one of the databases and schema from this backup > dump file onto a different server. The databases is call "analyze". > Does anyone know how to do that with pg_restore? Thanks. > Chris, pg_restore is used to restore backups that were saved in the custom format by pg_dump (option -Fc). You don't need pg_restore to restore a 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: psql -f /tmp/postgres.backup.`hostname` postgres Hint: the above will require that the CREATE DATABASE and \connect commands for the database you want to restore are still in the file. -- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
Jeff Frost wrote:
Whoops, that should say, in the custom or tar format (options -Fc or -Ft).Chris Henderson wrote:I backup all my databases by using pg_dumpall - pg_dumpall > /tmp/postgres.backup.`hostname`. It should backup four DBs: analyze, postgres, template0 and template1 I guess this backs up the schemas as well. Now I want to restore one of the databases and schema from this backup dump file onto a different server. The databases is call "analyze". Does anyone know how to do that with pg_restore? Thanks.Chris, pg_restore is used to restore backups that were saved in the custom format by pg_dump (option -Fc). You don't need pg_restore to restore a
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: psql -f /tmp/postgres.backup.`hostname` postgres Hint: the above will require that the CREATE DATABASE and \connect commands for the database you want to restore are still in the file.
-- Jeff Frost, Owner <jeff@frostconsultingllc.com> Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 916-647-6411 FAX: 916-405-4032
> 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.
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)