Why don't dumped files parse in pgAdmin3 query editor?

Lists: pgsql-general
From: dlivesay(at)covad(dot)net
To: pgsql-general(at)postgresql(dot)org
Subject: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 15:28:04
Message-ID: 20070305102804.1u0wmc3q9b6048kc@webmail1.covad.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Here's something I've always wondered. When you dump a database, the dumped file
looks like ordinary SQL, but if I load it into a query editor window and try to
execute it, I always get syntax errors. The specific errors vary, but it always
makes it impossible to reload the data that way.

In the past I've just used psql -f [file] [schema] to reload them, but I'm
trying to do this on Windows, and I can't seem to get anything to work from the
command shell, so I'm really stuck.


From: Raymond O'Donnell <rod(at)iol(dot)ie>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 15:35:48
Message-ID: 45EC38D4.2040204@iol.ie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On 05/03/2007 15:28, dlivesay(at)covad(dot)net wrote:

> Here's something I've always wondered. When you dump a database, the dumped file
> looks like ordinary SQL, but if I load it into a query editor window and try to
> execute it, I always get syntax errors. The specific errors vary, but it always
> makes it impossible to reload the data that way.

That's because theses files contain psql-specific "backslash" commands
as well as ordinary SQL - if you look at what the errors tell you,
you'll probably see that they come from lines containing such commands.
As I understand it, the plain-text output of pg_dump is intended to be
restored via psql.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
---------------------------------------------------------------


From: Raymond O'Donnell <rod(at)iol(dot)ie>
To:
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 15:36:47
Message-ID: 45EC390F.4010307@iol.ie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On 05/03/2007 15:28, dlivesay(at)covad(dot)net wrote:

> In the past I've just used psql -f [file] [schema] to reload them, but I'm
> trying to do this on Windows, and I can't seem to get anything to work from the
> command shell, so I'm really stuck.

What happens when you try to do it from the command shell? Show us your
commands and the output.

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
---------------------------------------------------------------


From: Magnus Hagander <magnus(at)hagander(dot)net>
To: dlivesay(at)covad(dot)net
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 15:39:25
Message-ID: 20070305153925.GA16062@svr2.hagander.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Mon, Mar 05, 2007 at 10:28:04AM -0500, dlivesay(at)covad(dot)net wrote:
> Here's something I've always wondered. When you dump a database, the dumped file
> looks like ordinary SQL, but if I load it into a query editor window and try to
> execute it, I always get syntax errors. The specific errors vary, but it always
> makes it impossible to reload the data that way.
>
> In the past I've just used psql -f [file] [schema] to reload them, but I'm
> trying to do this on Windows, and I can't seem to get anything to work from the
> command shell, so I'm really stuck.

It should work perfectly fine to restore it using psql -f on Windows as
well, I'd recommend that you look into why that's not working and try to
fix that instead.

//Magnus


From: "Joris Dobbelsteen" <Joris(at)familiedobbelsteen(dot)nl>
To: <dlivesay(at)covad(dot)net>, <pgsql-general(at)postgresql(dot)org>
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 15:43:45
Message-ID: 73427AD314CC364C8DF0FFF9C4D693FF0379B8@nehemiah.joris2k.local
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

>-----Original Message-----
>From: pgsql-general-owner(at)postgresql(dot)org
>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of
>dlivesay(at)covad(dot)net
>Sent: maandag 5 maart 2007 16:28
>To: pgsql-general(at)postgresql(dot)org
>Subject: [GENERAL] Why don't dumped files parse in pgAdmin3
>query editor?
>
>Here's something I've always wondered. When you dump a
>database, the dumped file looks like ordinary SQL, but if I
>load it into a query editor window and try to execute it, I
>always get syntax errors. The specific errors vary, but it
>always makes it impossible to reload the data that way.
>
>In the past I've just used psql -f [file] [schema] to reload
>them, but I'm trying to do this on Windows, and I can't seem
>to get anything to work from the command shell, so I'm really stuck.

It seems it has some strange syntax that might be psql-specific, perhaps
its even for linux only.

However, it might make some difference if you dump with "Use insert
statements" instead of the normal copy from stdin. This seemed to work
for me last time. Your mileage may vary.

- Joris


From: dlivesay(at)covad(dot)net
To: Magnus Hagander <magnus(at)hagander(dot)net>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 15:58:32
Message-ID: 20070305105832.nc9tsgaodphss8g8@webmail1.covad.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

Quoting Magnus Hagander <magnus(at)hagander(dot)net>:

> It should work perfectly fine to restore it using psql -f on Windows as
> well, I'd recommend that you look into why that's not working and try to
> fix that instead.

The main problem I'm having is that any command I try to use with psql, even
psql /?, causes it to prompt me for a password, and it tells me my password is
wrong no matter what username/passsword combination I try.

Beyond that, I really don't know what the correct syntax to use in the Windows
command shell is. Would it be "psql -f" or "psql /f"?


From: Raymond O'Donnell <rod(at)iol(dot)ie>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 16:06:23
Message-ID: 45EC3FFF.7070004@iol.ie
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On 05/03/2007 15:58, dlivesay(at)covad(dot)net wrote:

> The main problem I'm having is that any command I try to use with
> psql, even psql /?, causes it to prompt me for a password, and it
> tells me my password is wrong no matter what username/passsword
> combination I try.

Are you using the -U option to connect as a specific user? If you don't,
psql tries to connect as the currently logged-in user.

> Beyond that, I really don't know what the correct syntax to use in
> the Windows command shell is. Would it be "psql -f" or "psql /f"?

Try "psql --help":

C:\Documents and Settings\rod>psql --help
This is psql 8.2.3, the PostgreSQL interactive terminal.

Usage:
psql [OPTIONS]... [DBNAME [USERNAME]]

General options:
-d DBNAME specify database name to connect to (default: "rod")
-c COMMAND run only single command (SQL or internal) and exit
-f FILENAME execute commands from file, then exit
-1 ("one") execute command file as a single transaction
-l list available databases, then exit
-v NAME=VALUE set psql variable NAME to VALUE
-X do not read startup file (~/.psqlrc)
--help show this help, then exit
--version output version information, then exit

Input and output options:
-a echo all input from script
-e echo commands sent to server
-E display queries that internal commands generate
-q run quietly (no messages, only query output)
-o FILENAME send query results to file (or |pipe)
-n disable enhanced command line editing (readline)
-s single-step mode (confirm each query)
-S single-line mode (end of line terminates SQL command)
-L FILENAME send session log to file

Output format options:
-A unaligned table output mode (-P format=unaligned)
-H HTML table output mode (-P format=html)
-t print rows only (-P tuples_only)
-T TEXT set HTML table tag attributes (width, border) (-P
tableattr=)
-x turn on expanded table output (-P expanded)
-P VAR[=ARG] set printing option VAR to ARG (see \pset command)
-F STRING set field separator (default: "|") (-P fieldsep=)
-R STRING set record separator (default: newline) (-P recordsep=)

Connection options:
-h HOSTNAME database server host or socket directory (default:
"local sock
et")
-p PORT database server port (default: "5432")
-U NAME database user name (default: "rod")
-W prompt for password (should happen automatically)

For more information, type "\?" (for internal commands) or "\help"
(for SQL commands) from within psql, or consult the psql section in
the PostgreSQL documentation.

Report bugs to <pgsql-bugs(at)postgresql(dot)org>.

HTH,

Ray.

---------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod(at)iol(dot)ie
---------------------------------------------------------------


From: Magnus Hagander <magnus(at)hagander(dot)net>
To: dlivesay(at)covad(dot)net
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Why don't dumped files parse in pgAdmin3 query editor?
Date: 2007-03-05 16:14:15
Message-ID: 20070305161415.GB16062@svr2.hagander.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-general

On Mon, Mar 05, 2007 at 10:58:32AM -0500, dlivesay(at)covad(dot)net wrote:
> Quoting Magnus Hagander <magnus(at)hagander(dot)net>:
>
> >It should work perfectly fine to restore it using psql -f on Windows as
> >well, I'd recommend that you look into why that's not working and try to
> >fix that instead.
>
> The main problem I'm having is that any command I try to use with psql, even
> psql /?, causes it to prompt me for a password, and it tells me my password
> is
> wrong no matter what username/passsword combination I try.
>
> Beyond that, I really don't know what the correct syntax to use in the
> Windows
> command shell is. Would it be "psql -f" or "psql /f"?

it's psql -f. So you should be using psql -? to get the help.

//Magnus