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

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

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

Dennis Gearon

Never, ever approach a computer saying or even thinking "I will just do this
quickly."

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

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

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.

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.

Hope that answers your questions.

Dennis Gearon

Never, ever approach a computer saying or even thinking "I will just do this
quickly."

________________________________
From: Adrian Klaver <adrian.klaver@gmail.com>
To: Dennis Gearon <gearond@sbcglobal.net>
Cc: pgsql-general@postgresql.org
Sent: Thu, September 27, 2012 6:34:31 AM
Subject: Re: [GENERAL] problem with recreating database with export

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.

Not needed if you are logged in as user postgres in your OS when you enter psql.
At least, with the hba_conf file the way it is stock.

But a good point, though. I wonder if I would then have to know the DATABASE
password for postgres if I wasn't user postgres in the OS?

Dennis Gearon

Never, ever approach a computer saying or even thinking "I will just do this
quickly."

________________________________
From: Alban Hertroys <haramrae@gmail.com>
To: Dennis Gearon <gearond@sbcglobal.net>
Cc: Adrian Klaver <adrian.klaver@gmail.com>; pgsql-general@postgresql.org
Sent: Thu, September 27, 2012 8:13:17 AM
Subject: Re: [GENERAL] problem with recreating database with export

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

The only thing that I can offer is that it works from inside psql as user postgre but NOT from invoking postgres using -f filename from the commas line as user postgre. I have to admit that I did not use -U as OS user postgres, but that shouldn't be necessary with a stock gnarled_conf file.

The errors are 99% 'illegal command' associated with '\B'day or '\.' ending a COPY command.

Sent from Yahoo! Mail on Android

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