Thread: 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 solutionsfor 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