Re: [GENERAL] programatic database dump

[Michael Schmidt]

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

[GENERAL] programatic database dump

[Tomi NA]

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

Re: [GENERAL] programatic database dump

[Reid Thompson]

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