Backup and Restore of PostgreSQL

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

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

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

Easiest thing to do is to call pgsql from your servlet, have it dump the
file somewhere in your web apps directory, and then either send the link
to that file to the client or automatically forward the client there.

Option 2 would be to use JDBC to view the meta data of the table and use
it to write "CREATE" statements, and then build inserts from this data,
but this would be very time consuming and difficult to do.

Thanks!

~ Troy Campano ~

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: Monday, June 07, 2004 9:57 AM
To: Tim Penhey
Cc: pgsql-general
Subject: Re: [GENERAL] Backup and Restore of PostgreSQL

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

Well you might want to make sure the servlet only does one pg_dump at a
time to a particular file in a secured directory (making sure you don't
have any funny symlink problems) and all that sort of boring security,
reliability and predictability stuff. Don't forget the UI. The dump could
take a while.

At 11:27 AM 6/7/2004 -0400, Campano, Troy wrote: