Обсуждение: 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
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/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
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
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
Maybe with a stored procedure that reads metadata plus data and spits everything out in sql-form to stdout somehow. plperlmight 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
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: > 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 >