Backup and Restore of PostgreSQL

[Tim Penhey]

Hi All,

I have a java web server (Tomcat) running a web application that uses PostgreSQL for all
of the data. All of the management of the application is done through the web application
and I would like to provide a backup and restore option for the user. Backup would
somehow get all of the data/schema (maybe not schema) from the DB and zip it and send it
to the customer through the web-app to allow them to save the backup on their local
machine. They could then select this backup file to restore the DB to the state that it
was when the backup was taken.

The backing up and restoring of the system is restricted to administrators of the
web-application (most likely only one person). Backup's would probably be done on a daily
basis with restores only needed upon FUBAR.

What would be the best way of handling this?
Platform is most likely windows, but I would like a system that would work cross platform,
as that is why the whole system uses Java and PostgreSQL as these are available for
Win/Linux/OSX which are the most common desktop OSs.

Ideas, comments or pointers most welcome.

Thanks,
Tim

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Richard Huxton]

Tim Penhey wrote:

Hi All,

I have a java web server (Tomcat) running a web application that uses PostgreSQL for all
of the data. All of the management of the application is done through the web application
and I would like to provide a backup and restore option for the user. Backup would
somehow get all of the data/schema (maybe not schema) from the DB and zip it and send it
to the customer through the web-app to allow them to save the backup on their local
machine. They could then select this backup file to restore the DB to the state that it
was when the backup was taken.

The pg_dump utility is the standard way to backup PostgreSQL and the
custom format is already compressed.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Richard Huxton]

Tim Penhey wrote:

Hi All,

I have a java web server (Tomcat) running a web application that uses PostgreSQL for all
of the data. All of the management of the application is done through the web application
and I would like to provide a backup and restore option for the user. Backup would
somehow get all of the data/schema (maybe not schema) from the DB and zip it and send it
to the customer through the web-app to allow them to save the backup on their local
machine. They could then select this backup file to restore the DB to the state that it
was when the backup was taken.

The pg_dump utility is the standard way to backup PostgreSQL and the
custom format is already compressed.

--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tim Penhey]

Richard Huxton mentioned:

Tim Penhey wrote:

Hi All,

I have a java web server (Tomcat) running a web application that uses

PostgreSQL for all

of the data. All of the management of the application is done through the

web application

and I would like to provide a backup and restore option for the user. Backup would
somehow get all of the data/schema (maybe not schema) from the DB and zip it

and send it

to the customer through the web-app to allow them to save the backup on their local
machine. They could then select this backup file to restore the DB to the

state that it

was when the backup was taken.

The pg_dump utility is the standard way to backup PostgreSQL and the
custom format is already compressed.

--
Richard Huxton
Archonet Ltd

The question though is how to call this from a java servlet / hopefully in a OS
independant way.

Tim

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

[Tim Penhey]

Richard Huxton mentioned:

Tim Penhey wrote:

Hi All,

I have a java web server (Tomcat) running a web application that uses

PostgreSQL for all

of the data. All of the management of the application is done through the

web application

and I would like to provide a backup and restore option for the user. Backup would
somehow get all of the data/schema (maybe not schema) from the DB and zip it

and send it

to the customer through the web-app to allow them to save the backup on their local
machine. They could then select this backup file to restore the DB to the

state that it

was when the backup was taken.

The pg_dump utility is the standard way to backup PostgreSQL and the
custom format is already compressed.

--
Richard Huxton
Archonet Ltd

The question though is how to call this from a java servlet / hopefully in a OS
independant way.

Tim

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

[Richard Huxton]

Tim Penhey wrote:

Richard Huxton mentioned:

The pg_dump utility is the standard way to backup PostgreSQL and the
custom format is already compressed.

The question though is how to call this from a java servlet / hopefully in a OS
independant way.

Well, obviously file-paths can be dealt with simply, and pg_dump will
output to a named file, so you're ok there.
As for safely calling external programs, that's probably more a question
for the servlet system (Tomcat I think you said) - it's not different to
calling any other external program.

--
Richard Huxton
Archonet Ltd

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

[Richard Huxton]

Tim Penhey wrote:

Richard Huxton mentioned:

The pg_dump utility is the standard way to backup PostgreSQL and the
custom format is already compressed.

The question though is how to call this from a java servlet / hopefully in a OS
independant way.

Well, obviously file-paths can be dealt with simply, and pg_dump will
output to a named file, so you're ok there.
As for safely calling external programs, that's probably more a question
for the servlet system (Tomcat I think you said) - it's not different to
calling any other external program.

--
Richard Huxton
Archonet Ltd

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