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
>
>
