PostgreSQL Discussion - programatic database dump

Author

programatic database dump

Tomi NA

2006-03-10, 11:24 am

I'd like to dump a postgresql database from my (java) app and copy the dump
file to the client machine.
This backup strategy prooved invalueable in the past (given enough room on
the harddrives, which I have) and I'd like to implement it now with
postgresql.
Is there something like a system stored procedure that does something like
that I can use? Calling pg_dump seems like a bad hack: I'd like to keep
communication at the java<->sql level if possible. I'll probably bare it,
but I'd like to check if I've missed something, first.

TIA,
Tomislav

Reid Thompson

2006-03-10, 11:24 am

Tomi NA wrote:
> I'd like to dump a postgresql database from my (java) app and copy the
> dump file to the client machine.
> This backup strategy prooved invalueable in the past (given enough
> room on the harddrives, which I have) and I'd like to implement it now
> with postgresql.
> Is there something like a system stored procedure that does something
> like that I can use? Calling pg_dump seems like a bad hack: I'd like
> to keep communication at the java<->sql level if possible. I'll
> probably bare it, but I'd like to check if I've missed something, first.
>
> TIA,
> Tomislav
might help...
test=# \h copy
Command: COPY
Description: copy data between a file and a table
Syntax:
COPY tablename [ ( column [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE NOT NULL column [, ...] ]

COPY tablename [ ( column [, ...] ) ]
TO { 'filename' | STDOUT }
[ [ WITH ]
[ BINARY ]
[ OIDS ]
[ DELIMITER [ AS ] 'delimiter' ]
[ NULL [ AS ] 'null string' ]
[ CSV [ QUOTE [ AS ] 'quote' ]
[ ESCAPE [ AS ] 'escape' ]
[ FORCE QUOTE column [, ...] ]

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Michael Schmidt

2006-04-06, 8:25 pm

I've been working on this issue lately. There is quite a bit more to a database than the tables (e.g., functions), and I'm not sure how you would go about getting a backup of the entire database through SQL. I've been working on a Java class to do the dump and restore. It doesn't look terribly hard.

However, having said that, I wonder if someone could answer something. How do pg_dump and pg_restore use stdout and stderr? I saw a very old post that indicated -v caused all output to go to stderr. I couldn't find anything recent and nothing in the manual.

Thanks!
Michael Schmidt

Sponsored Links