sql dump

2010/7/26 Scott Frankel <frankel@circlesfx.com>:

Hi all,

Is it possible to perform an SQL Dump without using pg_dump?

I have a special case situation wherein my application has access to a
remotely-hosted PG (8.3) database, but does not have access to its admin
tools.  (There's a longer backstory here that I'm happy to explain if
necessary.)  I'm looking for an efficient way to dump all the data in the DB
without having to SELECT * on each table.

Thanks in advance!
Scott

You can use COPY but you won't easily have the DDL, just the data.

--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS

On 26/07/2010 16:58, Scott Frankel wrote:

Hi all,

Is it possible to perform an SQL Dump without using pg_dump?

I have a special case situation wherein my application has access to a
remotely-hosted PG (8.3) database, but does not have access to its admin
tools. (There's a longer backstory here that I'm happy to explain if
necessary.) I'm looking for an efficient way to dump all the data in the
DB without having to SELECT * on each table.

I don't think so. Are you in a position to run pg_dump locally to the
application, using the -h parameter to connect to the remote DB? You
really are better off to use pg_dump if you can at all, since it looks
after FK dependencies between tables, permissions, etc.

Ray.

--
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie

2010/7/27 Scott Frankel <frankel@circlesfx.com>:

Hi all,

Is it possible to perform an SQL Dump without using pg_dump?

I have a special case situation wherein my application has access to a
remotely-hosted PG (8.3) database, but does not have access to its admin
tools.  (There's a longer backstory here that I'm happy to explain if
necessary.)  I'm looking for an efficient way to dump all the data in the DB
without having to SELECT * on each table.

COPY might be of use:
http://www.postgresql.org/docs/8.3/interactive/sql-copy.html

though you will have to specify each table of course.

Ian Barwick

Maybe with a stored procedure that reads metadata plus data and spits everything out in sql-form to stdout somehow. plperl might be a good language choice.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Scott Frankel
Sent: Monday, July 26, 2010 11:59 AM
To: PostgreSQL List
Subject: [GENERAL] sql dump

Hi all,

Is it possible to perform an SQL Dump without using pg_dump?

I have a special case situation wherein my application has access to a
remotely-hosted PG (8.3) database, but does not have access to its
admin tools. (There's a longer backstory here that I'm happy to
explain if necessary.) I'm looking for an efficient way to dump all
the data in the DB without having to SELECT * on each table.

Thanks in advance!
Scott

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Jul 26, 2010 at 08:58:59AM -0700, Scott Frankel wrote:

Hi all,

Is it possible to perform an SQL Dump without using pg_dump?

No, but there may be more options for using pg_dump than you have
looked at. One example would be to use pg_dump on one with an SSH
tunnel to the other one's local PostgreSQL port (5432 by default, but
check which yours is). For example:

ssh -fNR 5432:localhost:5555 postgres@your.host.dom

would let you connect to localhost:5555 with pg_dump and any other
PostgreSQL tools.

Cheers,
David.

I have a special case situation wherein my application has access to
a remotely-hosted PG (8.3) database, but does not have access to its
admin tools. (There's a longer backstory here that I'm happy to
explain if necessary.) I'm looking for an efficient way to dump all
the data in the DB without having to SELECT * on each table.

Thanks in advance!
Scott

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Thanks for all the suggestions. COPY may work for my purposes. The
SSH tunnel option for using pg_dump is very interesting.

Thanks!
Scott

On Jul 26, 2010, at 9:18 AM, David Fetter wrote: