Thread: pg_restore / psql -f massive problems with copy from ...

I skimmed it in the mailing list, there were some references that
pg_restore is not working within win32 yet.

Question: is this a known - and - workedon - issue?

Concrete problems:

a) pg_restore -f <filename> ends with "Archiver could not open.." abort
b) when pg_dumping to SQL, psql -f <filename> is able to read the dump

*untill*  it get's to one copy -statement to read in data. After that,
the rest of the file is sucked in.... it does not end at the
stop-Characters.

Skimming the fixes from beta-2 to beta-3 I could not detect any
development besides some translations and some rights-fixes in the main
trunc.

So: are this bugs known? Is it a "singular happening" Can I provide more
details? I suggest it is of mission critical importance to have the
ability to DUMP and RESTORE a database, isn't it?

Harald

Harald Armin Massa wrote:

> I skimmed it in the mailing list, there were some references that
> pg_restore is not working within win32 yet.
>
> Question: is this a known - and - workedon - issue?
>
> Concrete problems:
>
> a) pg_restore -f <filename> ends with "Archiver could not open.." abort


This looks like you tried to restore a text dump. That is documented not
to work. You can only use pg_restore with dumps created with pg_dump -F
c or pg_dump -F t

> b) when pg_dumping to SQL, psql -f <filename> is able to read the dump
>
> *untill*  it get's to one copy -statement to read in data. After that,
> the rest of the file is sucked in.... it does not end at the
> stop-Characters.


This looks like the line-end bug that we fixed (or thought we fixed) in
psql some time ago, specifically here:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/bin/psql/copy.c.diff?r1=1.51;r2=1.52;f=h

 If it is not working please provide a concrete small example dump file.


cheers

andrew

Andrey,

thank you very much! Both informations are correct.

It is indeed possible to restore with using the -F c option on pg_dump,
and I replaces psql.exe with the one from beta3, and everything worked
correct.

So it is positive that this information is now on the newer dates in the
psql-mailinglists to google it up - I threw my hopes away after reading
"pg_restore is known not to work" within win32-hackers .)))

###########################################################

Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        output database name
  -f, --file=FILENAME      output file name

I struggled some syntax-times with "output file name". What is a "output
file" supposed to be in connection with "pg_Restore"?

Harald

Unless you restore to a database using -d, pg_restore gives you back the
SQL  (or a list if you use -l). -f specifies where this should go
instead of to stdout.

Or, as the man page clearly says:

       pg_restore  can  operate in two modes: If a database name is
specified,
       the archive is restored directly into the database. Otherwise, a
script
       containing  the  SQL commands necessary to rebuild the database
is cre-
       ated (and written to a file or standard output), similar  to
the  ones
       created  by the pg_dump plain text format.

does that make it clearer? (Reading the man pages is a Good Thing (tm). )

cheers

andrew

Harald Armin Massa wrote:

> Andrey,
>
> thank you very much! Both informations are correct.
>
> It is indeed possible to restore with using the -F c option on
> pg_dump, and I replaces psql.exe with the one from beta3, and
> everything worked correct.
>
> So it is positive that this information is now on the newer dates in
> the psql-mailinglists to google it up - I threw my hopes away after
> reading "pg_restore is known not to work" within win32-hackers .)))
>
> ###########################################################
>
> Usage:
>  pg_restore [OPTION]... [FILE]
>
> General options:
>  -d, --dbname=NAME        output database name
>  -f, --file=FILENAME      output file name
>
> I struggled some syntax-times with "output file name". What is a
> "output file" supposed to be in connection with "pg_Restore"?
>
> Harald
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>

Andrew,

thank you very much! yes, that makes it clearer....

and:

> (Reading the man pages is a Good Thing (tm). )

But sth. especially hard to do when working with beta3 on win32 ... "man
pg_restore" only gives ... "command not found" :)))

May I suggest to alter the
pg_restore --help output from
-----------------------------------------
Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        output database name
  -f, --file=FILENAME      output file name
-----------------------------------------------
to:
----------------------------------------------
Usage:
  pg_restore [OPTION]... [FILE]

General options:
  -d, --dbname=NAME        output database name (restore into a psql
database)
  -f, --file=FILENAME      output file name (create a plain text sql file)
----------------------------------------------

These to comments in brackets of course take some bytes, but it's easier
than porting man to win32, and will save a lot of bytes within the
mailinglists when PostgreSQL spreads within the win32 world

Harald

uuupsss.. and while at comments:

pg_restore restores a PostgreSQL database from an archive cr

Usage:
  pg_restore [OPTION]... [FILE]

General options:
 -d, --dbname=NAME        output database name (restore into a psql
database)
 -f, --file=FILENAME      output file name (create a plain text sql file)
 -F, --format=c|t         specify backup file format (custom or tar,
plain text is not possible)
---------

Yes, I know that "--format=c|t contains the same information; but ... it
can take quite some time to recognize. Especially ... pg_restore MAY be
used in a stress situation (database went down, backup has to be
installed...) when maybe the instructions for re-installations did not
got updated during the last 5 system updates....

(I know, in a correct managed world that would not happen.....)

Harald

Harald Armin Massa wrote:

>
>
>> (Reading the man pages is a Good Thing (tm). )
>
>
> But sth. especially hard to do when working with beta3 on win32 ...
> "man pg_restore" only gives ... "command not found" :)))
>
>
IIRC the Windows installer sets up the docs for you. If your are
compiling the beta from source we expect that you know what you're doing.

regarding your other suggestions, feel free to submit a patch to improve
the docs. ;-)

cheers

andrew

Andrew,

I know it is my fault and a "manual not read error" ... but: have you
skimmed lately the PostgreSQL HTML-Docs wich get installed by the
installer beta2-dev3?   ;)))))

Within "Server Administration" there is the topic "Backup and Restore"
- quite where everybody expects it.

22.1. SQL Dump says no word about formats that could get dumped...

and

22.1.1.explaines:

"""
The text files created by pg_dump are intended to be read in by the psql
program. The general command form to restore a dump is

psql dbname < infile

"""

within 22.1.3 there is also described the "official" (hey, it's the
official documentation!) way to compress pg_dumps

pg_dump dbname | gzip > filename.gz

(I took the liberty to update to "bz2" in my installations ...)

###############

Is there a special place to report documentations bugs or fixes to the
documentation? Because I am known for reading a lot of documentation
stuff (still looking for man girls)   .... and if I do not stumble upon
this kind of information while skimming and googling; I really guess
there will be more in the windows world after PostgreSQL spreads there
who really do not find it (and I really wish it will flourish!)

 >regarding your other suggestions, feel free to submit a patch to
improve the docs. ;-)
Could you please point me to a link how to do it best without causing
minimal trouble? The last "documentation patch" I suggested I described
in mail to Bruce Momjan ... around 3 years ago *G*

Thank you very much for all your help and all the effort that you have
put into PostgreSQL! I am a very very happy user of this database; and
really was frightened that there may be some "forgotten" probs with dump
/restore.


Harald

ceterum censeo PSQL needs a bug tracker

You didn't read far enough. Just a little further down, under 22.1.3,
you should have seen this:

    ---------------
If PostgreSQL was built on a system with the zlib compression library
installed, the custom dump format will compress data as it writes it to
the output file. For large databases, this will produce similar dump
sizes to using gzip, but has the added advantage that the tables can be
restored selectively. The following command dumps a database using the
custom dump format:

pg_dump -Fc dbname > filename

See the pg_dump
<http://developer.postgresql.org/docs/postgres/app-pgdump.html> and
pg_restore
<http://developer.postgresql.org/docs/postgres/app-pgrestore.html>
reference pages for details.

     ------

The info i referred you to before was from the pg_restore page referred
to here.

The correct way to submit patches is to send them to the -patches
mailing list. Basically, for docs you would need a patch against the
SGML source.

cheers

andrew

Harald Armin Massa wrote:

> Andrew,
>
> I know it is my fault and a "manual not read error" ... but: have you
> skimmed lately the PostgreSQL HTML-Docs wich get installed by the
> installer beta2-dev3?   ;)))))
>
> Within "Server Administration" there is the topic "Backup and
> Restore"  - quite where everybody expects it.
>
> 22.1. SQL Dump says no word about formats that could get dumped...
>
> and
>
> 22.1.1.explaines:
>
> """
> The text files created by pg_dump are intended to be read in by the
> psql program. The general command form to restore a dump is
>
> psql dbname < infile
>
> """
>
> within 22.1.3 there is also described the "official" (hey, it's the
> official documentation!) way to compress pg_dumps
>
> pg_dump dbname | gzip > filename.gz
>
> (I took the liberty to update to "bz2" in my installations ...)
>
> ###############
>
> Is there a special place to report documentations bugs or fixes to the
> documentation? Because I am known for reading a lot of documentation
> stuff (still looking for man girls)   .... and if I do not stumble
> upon this kind of information while skimming and googling; I really
> guess there will be more in the windows world after PostgreSQL spreads
> there who really do not find it (and I really wish it will flourish!)
>
> >regarding your other suggestions, feel free to submit a patch to
> improve the docs. ;-)
> Could you please point me to a link how to do it best without causing
> minimal trouble? The last "documentation patch" I suggested I
> described in mail to Bruce Momjan ... around 3 years ago *G*
>
> Thank you very much for all your help and all the effort that you have
> put into PostgreSQL! I am a very very happy user of this database; and
> really was frightened that there may be some "forgotten" probs with
> dump /restore.
>
>
> Harald
>
> ceterum censeo PSQL needs a bug tracker