Обсуждение: Backup with Java

Hello,
I have a question about how I can make a database backup
with Java. I have made a class Connection.java that connect
my application with Postgresql Database. I would make a
function in this class for backup the state of the database.
Anyone can help me?

Thanks!
Dr. Tommaso Taruffi

Hi, tomaso,

tommaso.taruffi@ricesoft.it wrote:

> I have a question about how I can make a database backup
> with Java. I have made a class Connection.java that connect
> my application with Postgresql Database. I would make a
> function in this class for backup the state of the database.

If you want to make a complete, generic backup procedure, you'll have to
extract the necessary knowledge from the source of pg_dump / pg_dumpall.
AFAICT, there's no other place where this knowledge is documented.

In case something application-specific is enough, it's best to use a
domain specific data format for output.

HTH,
Markus
--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf.     | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

Read and understand the file 'src/bin/pg_dump/pg_dump.c' in the postgresql
core server code base. This is the only solution of your problem, you are going
to rewrite all the C code in JAVA.

--Imad
www.EnterpriseDB.com


On 11/27/06, tommaso.taruffi@ricesoft.it <tommaso.taruffi@ricesoft.it> wrote:
> Hello,
> I have a question about how I can make a database backup
> with Java. I have made a class Connection.java that connect
> my application with Postgresql Database. I would make a
> function in this class for backup the state of the database.
> Anyone can help me?
>
> Thanks!
> Dr. Tommaso Taruffi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

It just loads the respective utility from the database engine and use it. Does
not do anything itself.


--Imad
www.EnterpriseDB.com


On 11/28/06, Michael Schmidt <michaelmschmidt@msn.com> wrote:
>
>
>
> Mr. Taruffi,
> The PostgreSQL backup, restore, vacuum, and reindex utilities are all
> included in Komo, a basic Java (Eclipse rcp) GUI.  You can look at the code
> and adapt it to your application.  Komo can be accessed and downloaded at
> pgFoundry
>
> Michael Schmidt
>
> ----- Original Message -----
> From: imad
> To: tommaso.taruffi@ricesoft.it
> Cc: pgsql-jdbc@postgresql.org
> Sent: Monday, November 27, 2006 11:20 AM
> Subject: Re: [JDBC] Backup with Java
>
> Read and understand the file 'src/bin/pg_dump/pg_dump.c' in the postgresql
> core server code base. This is the only solution of your problem, you are
> going
> to rewrite all the C code in JAVA.
>
> --Imad
> www.EnterpriseDB.com
>
>
> On 11/27/06, tommaso.taruffi@ricesoft.it <tommaso.taruffi@ricesoft.it>
> wrote:
> > Hello,
> > I have a question about how I can make a database backup
> > with Java. I have made a class Connection.java that connect
> > my application with Postgresql Database. I would make a
> > function in this class for backup the state of the database.
> > Anyone can help me?
> >
> > Thanks!
> > Dr. Tommaso Taruffi
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 7: You can help support the PostgreSQL project by donating at
> >
> >                 http://www.postgresql.org/about/donate
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

2006/11/27, John S.Abraham <john.abraham@sysbuild.net>:
>
> Tommaso:
>
> Why not try pg_dumpall, instead?
>
> The file that this command creates allows a complete regeneration of the
> database cluster, including users and user permissions. Even better, its
> already written and you can schedule it using the UNIX/LINUX command 'cron'.

Because, for instance, calling pg_dumpall has a number of shortcomings:

1.) access rights
You have to have a shell/ftp/other account on the machine or make the
network admin give you some other sort of network access (samba, nfs)
so that you can work with backup files.
You also have to be allowed to define cron tasks and have a
potentially large disk space quota.
2.) event based dump
cron is a nice way to make sure a backup gets done, but sometimes
backups make sense in relation to critical events in the system: e.g.
before occasional massive data imports or updates.
In those circumstances, having, say, a special stored procedure which
backs up the whole database and returns the content to the caller over
the network would enable a programmer building an application in *any*
language to add a manual backup feature which the users could then use
at will.
3.) Misc. nuisances
Aside for backup/restore functionality, a user communicates with the
database exclusively with the connection string. Making sure that your
application (even one which isn't multiplatform) will find the pg_*
tools is an additional issue you have to think of and resolve, instead
of sending a request directly to the database server (calling a backup
procedure), like you do generally when you need something from the
RDBMS server.

Cheers,
t.n.a.

On Nov 27, 2006, at 12:20 PM, imad wrote:

> Read and understand the file 'src/bin/pg_dump/pg_dump.c' in the
> postgresql
> core server code base. This is the only solution of your problem,
> you are going
> to rewrite all the C code in JAVA.

BTW, if you're going to go to that much trouble, you should ask on -
hackers about putting some of the pg_dump functionality into
functions, since this isn't the first time people have asked for a
non-pg_dump backup solution. It would make sense to abstract things
that were reasonable out of pg_dump and into the database itself if
there's really that much demand for this.

Another option would be providing some means to call pg_dump(all)
from a database connection.
--
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)

I would also like to use such a backup function by JDBC (for backing up
only one named database)

tom

Jim Nasby schrieb:
> On Nov 27, 2006, at 12:20 PM, imad wrote:
>
>> Read and understand the file 'src/bin/pg_dump/pg_dump.c' in the
>> postgresql
>> core server code base. This is the only solution of your problem, you
>> are going
>> to rewrite all the C code in JAVA.
>
> BTW, if you're going to go to that much trouble, you should ask on
> -hackers about putting some of the pg_dump functionality into
> functions, since this isn't the first time people have asked for a
> non-pg_dump backup solution. It would make sense to abstract things
> that were reasonable out of pg_dump and into the database itself if
> there's really that much demand for this.
>
> Another option would be providing some means to call pg_dump(all) from
> a database connection.
> --
> Jim Nasby                                            jim@nasby.net
> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

I'd strongly suggest you exec pg_dump from the command line using
System.exec and connect to the remote database. This is certainly re-
inventing a non-trivial wheel.

Dave
On 29-Nov-06, at 6:21 PM, tom12167 wrote:

> I would also like to use such a backup function by JDBC (for
> backing up only one named database)
>
> tom
>
> Jim Nasby schrieb:
>> On Nov 27, 2006, at 12:20 PM, imad wrote:
>>
>>> Read and understand the file 'src/bin/pg_dump/pg_dump.c' in the
>>> postgresql
>>> core server code base. This is the only solution of your problem,
>>> you are going
>>> to rewrite all the C code in JAVA.
>>
>> BTW, if you're going to go to that much trouble, you should ask on
>> -hackers about putting some of the pg_dump functionality into
>> functions, since this isn't the first time people have asked for a
>> non-pg_dump backup solution. It would make sense to abstract
>> things that were reasonable out of pg_dump and into the database
>> itself if there's really that much demand for this.
>>
>> Another option would be providing some means to call pg_dump(all)
>> from a database connection.
>> --
>> Jim Nasby                                            jim@nasby.net
>> EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)
>>
>>
>>
>> ---------------------------(end of
>> broadcast)---------------------------
>> TIP 5: don't forget to increase your free space map settings
>>
>>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>               http://archives.postgresql.org
>

Dave Cramer <pg@fastcrypt.com> writes:

> I'd strongly suggest you exec pg_dump from the command line using
> System.exec and connect to the remote database. This is certainly re-
> inventing a non-trivial wheel.

We have implemented this, with a couple of additional checks


<https://forge.continuent.org/plugins/scmcvs/cvsweb.php/sequoia/src/org/continuent/sequoia/controller/backup/?cvsroot=sequoia>

This code is generic, designed to support not only PostgreSQL.

We could try to externalize it into an independent project if a
significant number of people are interested and... provided it's not
too hard to detach it from sequoia (no promise here).

We also interested by alternatives approaches.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Marc,

Marc Herbert schrieb am 30.11.2006 12:03:
> Dave Cramer <pg@fastcrypt.com> writes:
>
>> I'd strongly suggest you exec pg_dump from the command line using
>> System.exec and connect to the remote database. This is certainly re-
>> inventing a non-trivial wheel.
>
> We have implemented this, with a couple of additional checks
>
>
<https://forge.continuent.org/plugins/scmcvs/cvsweb.php/sequoia/src/org/continuent/sequoia/controller/backup/?cvsroot=sequoia>
>
> This code is generic, designed to support not only PostgreSQL.
>
> We could try to externalize it into an independent project if a
> significant number of people are interested and... provided it's not
> too hard to detach it from sequoia (no promise here).
>
> We also interested by alternatives approaches.

I'm following the discussion for quite a while already... very
interesting topic.
But I just checked the link above, and it's still native. It's just a
wrapper around pg_dump and even psql for single SQL statements...

- --
Mit freundlichen Gruessen / Regards
Patric Bechtel, IPCON Informationssysteme OHG
Kontakt: http://www.ipcon.de/kontakt.php
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: GnuPT 2.5.2

iD8DBQFFbtCOfGgGu8y7ypARAm4lAKDT1uKP5Md4E2dyOENZ6IEG2TJugQCg5vzp
+Qnr7dpMsQffBbNp7Q8dQ4g=
=nV7z
-----END PGP SIGNATURE-----

On 30-Nov-06, at 7:37 AM, Patric Bechtel wrote:

> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Hi Marc,
>
> Marc Herbert schrieb am 30.11.2006 12:03:
>> Dave Cramer <pg@fastcrypt.com> writes:
>>
>>> I'd strongly suggest you exec pg_dump from the command line using
>>> System.exec and connect to the remote database. This is certainly
>>> re-
>>> inventing a non-trivial wheel.
>>
>> We have implemented this, with a couple of additional checks
>>
>> <https://forge.continuent.org/plugins/scmcvs/cvsweb.php/sequoia/
>> src/org/continuent/sequoia/controller/backup/?cvsroot=sequoia>
>>
>> This code is generic, designed to support not only PostgreSQL.
>>
>> We could try to externalize it into an independent project if a
>> significant number of people are interested and... provided it's not
>> too hard to detach it from sequoia (no promise here).
>>
>> We also interested by alternatives approaches.
>
> I'm following the discussion for quite a while already... very
> interesting topic.
> But I just checked the link above, and it's still native. It's just a
> wrapper around pg_dump and even psql for single SQL statements...
>
Yes, and given the complexity of this plus the requirement to keep up
with each new server version;
unless this facility is built in to the server I would suggest this
is the only sane way to go.
> - --
> Mit freundlichen Gruessen / Regards
> Patric Bechtel, IPCON Informationssysteme OHG
> Kontakt: http://www.ipcon.de/kontakt.php
> -----BEGIN PGP SIGNATURE-----
> Version: GnuPG v1.4.3 (GNU/Linux)
> Comment: GnuPT 2.5.2
>
> iD8DBQFFbtCOfGgGu8y7ypARAm4lAKDT1uKP5Md4E2dyOENZ6IEG2TJugQCg5vzp
> +Qnr7dpMsQffBbNp7Q8dQ4g=
> =nV7z
> -----END PGP SIGNATURE-----
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

Patric Bechtel <bechtel@ipcon.de> writes:

> Marc Herbert schrieb am 30.11.2006 12:03:
>> Dave Cramer <pg@fastcrypt.com> writes:
>>
>>> I'd strongly suggest you exec pg_dump from the command line using
>>> System.exec and connect to the remote database. This is certainly re-
>>> inventing a non-trivial wheel.
>>
>> We have implemented this, with a couple of additional checks
>>
>> We also interested by alternatives approaches.


> But I just checked the link above, and it's still native.

Yes, that's why I tried to say by quoting.

Sorry if you misunderstood it; I probably should have removed the
"reinventing" sentence.


By the way a (the?) major issue with programming against pg_dump is
that it may prompt for a password in an unpredictable way. There is no
--never-prompt-but-IMMEDIATLY-fail-instead option.

On 30-Nov-06, at 10:54 AM, Marc Herbert wrote:

> Patric Bechtel <bechtel@ipcon.de> writes:
>
>> Marc Herbert schrieb am 30.11.2006 12:03:
>>> Dave Cramer <pg@fastcrypt.com> writes:
>>>
>>>> I'd strongly suggest you exec pg_dump from the command line using
>>>> System.exec and connect to the remote database. This is
>>>> certainly re-
>>>> inventing a non-trivial wheel.
>>>
>>> We have implemented this, with a couple of additional checks
>>>
>>> We also interested by alternatives approaches.
>
>
>> But I just checked the link above, and it's still native.
>
> Yes, that's why I tried to say by quoting.
>
> Sorry if you misunderstood it; I probably should have removed the
> "reinventing" sentence.
>
>
> By the way a (the?) major issue with programming against pg_dump is
> that it may prompt for a password in an unpredictable way. There is no
> --never-prompt-but-IMMEDIATLY-fail-instead option.
That could certainly be changed much easier than duplicating the
entire pg_dump functionality
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

2006/11/30, Dave Cramer <pg@fastcrypt.com>:

> That could certainly be changed much easier than duplicating the
> entire pg_dump functionality

Yeah, but the way this have been architected, it's impossible to do a
backup over the network, i.e. with only the 5432 port at your
disposal. And that's just one of the _obvious_ drawbacks...the way I
see it, the way to do it would be to move the dump code into the
database server. Let _the_server_ do all the hard work and let the
application on the other end do whatever it wants with the (supposedly
plain text) dump. That's flexibility, and it wouldn't require
rewriting the dump utility - just a move into the server itself.

t.n.a.

> Yeah, but the way this have been architected, it's impossible to do a
> backup over the network, i.e. with only the 5432 port at your
> disposal. And that's just one of the _obvious_ drawbacks...the way I
> see it, the way to do it would be to move the dump code into the
> database server. Let _the_server_ do all the hard work and let the
> application on the other end do whatever it wants with the (supposedly
> plain text) dump. That's flexibility, and it wouldn't require
> rewriting the dump utility - just a move into the server itself.

The main reason it's not done so is because pg_dump is working across
server versions. You will want to do the dump using the newer pg_dump,
because it can create SQL which compensates for postgres internal
changes like user/group/role creation syntax and the like.

If you tie the dump software to the server, how will you do a dump of an
old server using the new dump client ?

Cheers,
Csaba.

2006/11/30, Csaba Nagy <nagy@ecircle-ag.com>:

> The main reason it's not done so is because pg_dump is working across
> server versions. You will want to do the dump using the newer pg_dump,
> because it can create SQL which compensates for postgres internal
> changes like user/group/role creation syntax and the like.
>
> If you tie the dump software to the server, how will you do a dump of an
> old server using the new dump client ?

I'm not sure I understand...why would you want to do that? Why not
dump the old database with the old dump logic?

t.n.a.

Csaba Nagy <nagy@ecircle-ag.com> writes:

> If you tie the dump software to the server, how will you do a dump of an
> old server using the new dump client ?

Simply by leaving a deprecated, backward-compatibility mode in
the pg_dump client?

Or, as said above, even more simply by using an old client.

I think everyone agrees that a dump involves a good knowledge of
internal server data. So I fully agree with Tomi: it seems a bit
awkward to implement this externally.

Csaba Nagy <nagy@ecircle-ag.com> writes:

> The main reason it's not done so is because pg_dump is working across
> server versions. You will want to do the dump using the newer pg_dump,
> because it can create SQL which compensates for postgres internal
> changes like user/group/role creation syntax and the like.
>
> If you tie the dump software to the server, how will you do a dump of an
> old server using the new dump client ?

Sorry I think I got it finally: what you are saying is that pg_dump is
not only a backup tool, but some kind of database format conversion
tool. Now I think I understand why implementing externally makes sense.

However, it would be nice to also have a simple, non-converting
pg_dumb_dump utility available, easy to re-implement and maintain in
other languages than C with minimal duplication. Something based on a
two commands only protocol: "SAVE DATABASE" and "RESTORE DATABASE".

Most backups operations do not care about cross-compatibility. Backups
happen much more often than upgrades!

On 30-Nov-06, at 12:54 PM, Marc Herbert wrote:

> Csaba Nagy <nagy@ecircle-ag.com> writes:
>
>> If you tie the dump software to the server, how will you do a dump
>> of an
>> old server using the new dump client ?
>
> Simply by leaving a deprecated, backward-compatibility mode in
> the pg_dump client?
>
> Or, as said above, even more simply by using an old client.
>
> I think everyone agrees that a dump involves a good knowledge of
> internal server data. So I fully agree with Tomi: it seems a bit
> awkward to implement this externally.
>
What Csaba is saying is that newer versions of pg_dump can work with
older server versions.

regarding port 5432, pg_dump can use any port, server, user, you
want. It is quite versatile.

Dave
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>

Seems like a hybrid approach may be the most practical.  If you dump
the DDL rarely (or whenever it changes) with pg_dump or a similar
DB-specific tool, then you could probably generate a frequent data
backup fairly easily with pure JDBC code.  The only trick on the
restore would be to do it in an order consistent with the keys, and
again, you ought to be able to determine that via JDBC (or perhaps
determine that it's not possible, in which case maybe you manually
insert some statements to suspend RI during the import or whatever).

Thanks,
      Aaron

On 11/27/06, tommaso.taruffi@ricesoft.it <tommaso.taruffi@ricesoft.it> wrote:
> Hello,
> I have a question about how I can make a database backup
> with Java. I have made a class Connection.java that connect
> my application with Postgresql Database. I would make a
> function in this class for backup the state of the database.
> Anyone can help me?
>
> Thanks!
> Dr. Tommaso Taruffi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: You can help support the PostgreSQL project by donating at
>
>                 http://www.postgresql.org/about/donate
>

> Sorry I think I got it finally: what you are saying is that pg_dump is
> not only a backup tool, but some kind of database format conversion
> tool. Now I think I understand why implementing externally makes sense.

This is what I meant.

> However, it would be nice to also have a simple, non-converting
> pg_dumb_dump utility available, easy to re-implement and maintain in
> other languages than C with minimal duplication. Something based on a
> two commands only protocol: "SAVE DATABASE" and "RESTORE DATABASE".
>
> Most backups operations do not care about cross-compatibility. Backups
> happen much more often than upgrades!

Actually, if there were a working and maintained pg_upgrade, I'm pretty
sure nobody would use pg_dump as an upgrade facility anymore. Then it
could be integrated to the server...

Or alternatively, given that when you upgrade you usually dump/reload
anyway, the new server itself could connect to the old one and pull the
dump/reload in one go.

Cheers,
Csaba.

Csaba Nagy <nagy@ecircle-ag.com> writes:
> Actually, if there were a working and maintained pg_upgrade, I'm pretty
> sure nobody would use pg_dump as an upgrade facility anymore.

You think so eh?  Hint: the only workable design I've seen for pg_upgrade
uses pg_dump as a component.  It's much easier to handle
version-to-version changes in pg_dump than it would be inside the server.
Example: there is no way that a pre-8.1 server could be expected to know
that it had better set standard_conforming_strings = off to ensure that
the SQL it's emitting will be understood properly by a post-8.3 server.

            regards, tom lane

So back to Marc's request, it would seem that making pg_dump do
something predictable if specifically requested to do so would
alleviate Marc's problem ?

Dave
On 1-Dec-06, at 10:27 AM, Tom Lane wrote:

> Csaba Nagy <nagy@ecircle-ag.com> writes:
>> Actually, if there were a working and maintained pg_upgrade, I'm
>> pretty
>> sure nobody would use pg_dump as an upgrade facility anymore.
>
> You think so eh?  Hint: the only workable design I've seen for
> pg_upgrade
> uses pg_dump as a component.  It's much easier to handle
> version-to-version changes in pg_dump than it would be inside the
> server.
> Example: there is no way that a pre-8.1 server could be expected to
> know
> that it had better set standard_conforming_strings = off to ensure
> that
> the SQL it's emitting will be understood properly by a post-8.3
> server.
>
>             regards, tom lane
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>        subscribe-nomail command to majordomo@postgresql.org so that
> your
>        message can get through to the mailing list cleanly
>

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Csaba Nagy <nagy@ecircle-ag.com> writes:
>> Actually, if there were a working and maintained pg_upgrade, I'm pretty
>> sure nobody would use pg_dump as an upgrade facility anymore.
>
> You think so eh?  Hint: the only workable design I've seen for pg_upgrade
> uses pg_dump as a component.  It's much easier to handle
> version-to-version changes in pg_dump than it would be inside the server.

This discussion looks a bit weird to me since currently pg_dump IS
pg_upgrade; it's just misnamed.

Quite obviously it also implements pg_dumb_dump as a particular case.

Too bad the "real", simple pg_dumb_dump does not exist, it would have
been nice and useful to re-implement it in java.