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

Frank Church <voipfc(at)googlemail(dot)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