Re: Is there a way to backup Postgres via SQL commands?

On 14/06/2010 9:05 PM, Raymond O'Donnell wrote:
> On 14/06/2010 12:28, Frank Church wrote:
>> Are there SQL commands that can do a backup over a client connection,
>> rather than from the command line like pgsql etc?
>
> No, not that I'm aware of.
>
>> By that I mean some kind of SELECT commands that can retrieve the
>> database's content as SQL commands that can be replayed to a server to
>> restore it, rather than something that saves directly to file, or
>> passes it through a pipe?
>
> What exactly are you trying to do? If you're building database backup
> functionality into an application, you're better off just spawning a
> sub-process with pg_dump.

If you really, really need it, you might be able to experiment with a
gruesome hack like:

- Use a plperlu function running in the server to spawn the desired
"pg_dump" command, with stdout connected to a pipe. Make sure this
function is only accessible to the DB superuser.

- Stream the pg_dump output from plperlu to your client. You could read
line-by-line and return SETOF TEXT, or read (say) 1MB chunks and return
SETOF BYTEA, for example. Appropriate client buffering and/or an
explicit cursor may be required.

I haven't tested this, and it might not work due to requirements/issues
I'm unaware of. If it doesn't work, you should be able to at least
invoke pg_dump via a stored function to dump to a server-side file, then
slurp that file via the admin functions.

I agree with others that if at all possible, pg_dump should be spawned
client side. That said, spawning pg_dump client side is a pain if the
app doesn't use libpq (say it's a Java/JDBC app), as you have to
maintain two different configurations for database access. It's worse
yet if the DB requires installation of a trusted SSL root cert, and
totally impractical if the DB requires client certificates.

I've never had any desire to perform client-side backups, so this hasn't
come up as an issue for me. What problem are you trying to solve with
this, and in what situation?

--
Craig Ringer