Thread: problem with recreating database with export

On 09/26/2012 07:41 PM, Dennis Gearon wrote:
> I have a database export done via copy. It's all the tables, contraints,
> blah blah.

More information may be in order.

What versions of Postgres are you using?
Are you going from one version to another?
Did you really use COPY for everything(schema included)?
Are you in fact talking about doing a plain text pg_dump?

>
> I know (a couple of years ago) that I just used the defaults. REALLY
> fast btw, I remember.
>
> All the table create commands work fine.
>
> I cannot get the whole file to import using (as postgres) psql -d
> database_name -f the_file.sql
>
> I get errors on \N and various other problems. I've tried dividing the
> file up into:
>
> table creation
> one 'copy' of a table at a time.
>
> One, a 35 mbyte seems to have errors on semi colons.
> Dennis Gearon
>
> Never, ever approach a computer saying or even thinking "I will just do
> this quickly."
>


--
Adrian Klaver
adrian.klaver@gmail.com

On 09/26/2012 08:44 PM, Dennis Gearon wrote:

> It turns out that I had made the export from psql, a text based export.

I just do not see how you can be exporting schema definitions from
within psql. I still say pg_dump is involved somewhere.

>
> So I read that it was actually needing to be imported using '\i
> filename_in_local_directory'

What about the other questions I posed?:

  What versions of Postgres are you using?

  Are you going from one version to another?

  Are you in fact talking about doing a plain text pg_dump?

New questions:

  To be clear what are the actual commands used to create the export?

  Also what are the actual errors you get when trying to import the data?


> Dennis Gearon



--
Adrian Klaver
adrian.klaver@gmail.com

On 27 September 2012 16:55, Dennis Gearon <gearond@sbcglobal.net> 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.

Looks like you forgot -U postgres

--
If you can't see the forest for the trees,
Cut the trees and you'll see there is no forest.

On 27/09/2012 15:55, 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

I think that's a bit of an over-generalisation. :-) What errors do you get?

> 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.

That's equivalent to passing the name of the file to psql via the -f
option... assuming of course that you're in the same directory as the
file; otherwise pass the full relative or absolute path.

Ray.


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

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.klaver@gmail.com

 Torsdag 27. september 2012 16.55.15 skrev Dennis Gearon :
> <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.
>
> PS,do this as user 'postgres' on the system.
> </note to future>

FWIW, here are a few relevant lines from my reload.sh script, which I have
been using since version 7.4, and which is working perfectly:

    dropdb $DB
    createdb --encoding=UNICODE $DB
    psql -U postgres -d $DB -f $INFILE > restore.log 2>&1

Note that I'm running this as a regular postgres user, whose only privilege is
to create new databases.

regards, Leif

On 2012-09-27, Dennis Gearon <gearond@sbcglobal.net> wrote:

> 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.

it's the error before all the the \N errors that has the important
information, the \N errors are the effect not the cause.

--
⚂⚃ 100% natural