Re: Question about backing up PostgreSQL databases

I forgot to add one thing  :
For file system backup and restore PostgreSQL must be stoped
Regards
V Kashyap

> 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>  ./filename.tar.gz
> 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
>
> Regrads,
> V Kashyap
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>