Re: problem with recreating database with export

On 09/27/2012 07:55 AM, Dennis Gearon wrote:
> <note to future>
> To anyone reading this in the future, if you have problems importing a
> plain text database export, it is usually impossible to do:
> psql -d some_dbase -f the_backup.sql. I don't know why. What works is
> doing 'cd ./the_files_directory', going INTO psql command line, then
> issuing '\i the_backup.sql', and it's really fast. 8 seconds for 128
> mbyte file.

As others have said:

http://www.postgresql.org/docs/8.4/interactive/app-psql.html
-f filename
--file filename
Use the file filename as the source of commands instead of reading
commands interactively. After the file is processed, psql terminates.
This is in many ways equivalent to the internal command \i.

Are you sure you are logging in to the database as the postgres user
when doing?:

psql -d some_dbase -f the_backup.sql

Are you also sure that when you are using -f vs \i you are pointing at
the same file?

Another potential issue is encoding. At the top of the dump file should
be something like:

SET client_encoding = 'UTF8';

Does that exist?

>
> PS,do this as user 'postgres' on the system.
> </note to future>
>
> Probably, you were right, it was pg_dump. It says 'database dump' in the
> file at the top.
>
> As far as which version it came from, that was 2 years ago, I couldn't
> tell you. And it doesn't say in the file.
>
> I successfully imported it into 8.4.11.

FYI restoring dump files of unknown provenance into a database is
playing with fire. Major releases of Postgres make no claim to maintain
backward compatibility.

>
> The errors were always 'illegal command', 10s of thousands of them. As
> far as what I did 2 years ago, I can't remember 2 days ago, sorry about
> that ;-) Yes it is a plan text dump. I don't have huge databases yet, so
> to make it easier to go between versions, I use a text backup.

Can you cut and paste one example of the error message?

Going between versions is no easier or harder with the custom format vs
the text format. Just so you know you can always reconstitute a
text/plain dump file from the custom format. pg_dump itself knows how
too deal with Postgres versions back to 7.0, so it is a good idea to use
the version of pg_dump from the Postgres version you want to restore to
to dump the database in question.

As mentioned above major releases can and do introduce incompatibilities
that the dump format may very well have no bearing on. Incompatibilities
on occasion also occur in minor releases. So as a matter of course you
should read the release notes before restoring.

>
> Hope that answers your questions.
> Dennis Gearon

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com