Is there a way to backup Postgres via SQL commands?
Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?
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?
/voipfc
In response to Frank Church :
Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?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's wrong with pg_dump?
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431 2EB0 389D 1DC2 3172 0C99
On 14 Jun 2010, at 13: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.
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?
That sounds quite a bit like replicating the DB to a warm standby, is that what you're after? There are several solutions for that.
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4c16281d286215086519202!
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.
Ray.
--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie
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
On Mon, Jun 14, 2010 at 7:28 AM, Frank Church <voipfc@googlemail.com> wrote:
Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?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?
no -- it's actually possible for some trivial things (just grabbing a
few tables) -- but anything more complicated will quickly become
unreasonably difficult.
TBH, I think backups through SQL are quite nice. Judging by the
standard of other databases, I generally always prefer the sql version
to the command line variant with both tools are available.
It would also be nice to be able to generate a restorable script from
any userland object. pg_dump/pg_restore gives you the ability to
specifically grab indexes, functions, table, and triggers, but that's
it.
merlin
Frank Church <voipfc@googlemail.com> writes:
Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?
That's pg_dump ?
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?
If you want to retrieve the SQL commands that allows you to recreate a
live database, use pg_dump.
If you want to build a file-by-file replica of the live system (base
backup) through a usual PostgreSQL connection, you can use pg_basebackup
which is available on github:
http://github.com/dimitri/pg_basebackup
If you want to run pg_dump via an SQL query, I say I don't see any
interest in doing so. Plain client-side pg_dump will get the data it
needs (including necessary DDLs) through a normal PostgreSQL connection
already. Arrange yourself so that you can run pg_dump!
As other said, though, it can certainly be made, but not with some
caveats. Do you want only the schema or the schema and the data? The
first limitation I can think of is the 1GB - 4 bytes bytea datatype
capacity in memory.
Regards,
--
dim
Dimitri Fontaine wrote:
Frank Church <voipfc@googlemail.com> writes:
Are there SQL commands that can do a backup over a client connection,
rather than from the command line like pgsql etc?That's pg_dump ?
Dimitri has correctly pointed out the flaw behind the basic assumption
being made by asking this question. You do not have to use pg_dump from
the server's command line in order to dump a database. You can install
the same PostgreSQL version on whatever system you want to do the dump
from instead, have it connect to the server as a client, and then dump
your database on that remote system. pg_dump is already a client
application that only requires a network connection to the server and
sends it a series of SQL commands.
--
Greg Smith 2ndQuadrant US Baltimore, MD
PostgreSQL Training, Services and Support
greg@2ndQuadrant.com www.2ndQuadrant.us