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

Re: Question about backing up PostgreSQL databases

From: Sai Hertz And Control Systems <sank89(at)sancharnet(dot)in>
To: "Van L(dot) Loggins" <vloggins(at)turbocorp(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Question about backing up PostgreSQL databases
Date: 2003-12-17 17:03:27
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Dear Van L. Loggins ,

>What methods are available to easily backup the contents of a PostgreSQL database?
PostgreSQL  both file system backup option and SQL data in form of 
insert statement  :
Note: I have done this thing with  > PostgreSQL 7.3
This is what I do
1. Take a file system backup of  PostgreSQL "data"   folder  preferably 
with DUMP
2.  Take a back up of   data  with
        pg_dump --disable-triggers -U <USER NAME>  -a -d -b -D -Fc -Z 9  
-f    filename.tar.gz   <DATABASE NAME>
       a. This will take care of blobs (Though 7.2 does not have it)
       b. Make sure that your data be less than 8 GB as tar could not 
handle  more than that
3.  Yes Schema is also required to be backed up with above command and 
having switch as

        pg_dump --disable-triggers -U <USER NAME>  -s Fp  -f    
schema.sql   <DATABASE NAME>
4. Now why Schema is dumped separately to that of  data
    Because :
    a. pg_dump will dump the schema as per its own wish and not as per 
the  database requirement so
       if a required user defined function is not  in the secema dump as 
early as it is required  your pg_restore will
       abort with Xmas bells
    b. In this case you can rearrange the creation of  Schema as
          Create functions > then table >  then sequences > then views
5.  Now after all stuff you have done install your new OS
    initdb  a new database in the same directory location as it was in 
the old one (i.e in the older OS)
6. Now try restore the Schema  if succeeds  then go to 7 else
    rearrange your sql statements to satisfy the monster
7. Restore Data with
    pg_restore  --disable-triggers -U <USER NAME>  -d   <DATABASE NAME>  
8. Now if 6 and  7 fails you have a chance reinstall the older version 
of postgresql and also its DUMP file to regain the data and schema again 
and start again from point 2 to 8
This is what I do  to restore / migrate and have got 100 % results till date

Any one having a better and more reliable method please pass it on to  
Anjan Dave and me

We would be grateful to you

V Kashyap

In response to


pgsql-admin by date

Next:From: Sai Hertz And Control SystemsDate: 2003-12-17 17:24:37
Subject: Re: Question about backing up PostgreSQL databases
Previous:From: Naomi WalkerDate: 2003-12-17 16:36:04
Subject: Re: comparing with oracle

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