Thread: Backup and Restore of PostgreSQL
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
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: >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.