Question about backing up PostgreSQL databases

[Van L. Loggins]

What methods are available to easily backup the contents of a PostgreSQL database?

We are in the process of converting over to it from Progress, and we need to figure
out what our best option is to have a complete backup in case of disaster.


Thanks for the input,

Van
--
Van Loggins ***@turbocorp.com
Assistant System Administrator - ESC Dept
Linux User #316727
1-770-532-2239 Extension 9404
Turbo Logistics
http://www.turbocorp.com


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Sai Hertz And Control Systems]

Dear Van L. Loggins ,

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

[Sai Hertz And Control Systems]

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

Post by Sai Hertz And Control Systems
Dear Van L. Loggins ,

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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[David C. Brown]

Well, we currently use pg_dumpall and pipe it to a tar file. It backups
up all data and the database schema. So if a database goes down, I can
just pipe that file to pgsql and it will reload everything including all
tables, users, grant rights, etc.

There is also a company called Bakbone software that has a module for
their software that will backup PostgreSQL databases. You can see it at
their website. http://www.bakbone.com

Good luck,
Dave

Post by Van L. Loggins
What methods are available to easily backup the contents of a PostgreSQL database?
We are in the process of converting over to it from Progress, and we need to figure
out what our best option is to have a complete backup in case of disaster.
Thanks for the input,
Van

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ***@postgresql.org)