Thread: Importing the dump file in postgresql-7.4.23

Importing the dump file in postgresql-7.4.23

From
saravanan
Date:
Hi all,

I'm using PostgreSQL-7.4.23 version. I have one dump file that was taken
using pg_dumpall utility. Now i tried to import the dump file.  But its
throwing me lot of *Invalid Command \N error* , *ERROR:  type tsvector does
not exist* and *\t: extra argument "string" ignored error*

I'm using 7.4.23 psql tool to import
I tried the following ways to import pg_dumpall dump file
psql -f all.dump template1
psql -e template1 < all.dump

Here some of DDL commands were imported correctly. Commands like copy were
not imported properly, its throwing lot of "Invalid Command \N error".


Can anyone help me out of this problem?

Here are the some images while importing dump file
http://postgresql.1045698.n5.nabble.com/file/n4578769/1.jpg





http://postgresql.1045698.n5.nabble.com/file/n4578769/2.jpg



Any help would be appreciated
Advance
Thanks



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Importing-the-dump-file-in-postgresql-7-4-23-tp4578769p4578769.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: Importing the dump file in postgresql-7.4.23

From
Achilleas Mantzios
Date:
is your target DB >=8.x?
Did you use to have tsearch2 installed in 7.4?

Anyway, this is how we used to dump from 7.4 and load into 8.3:

take the dump from 7.4,
install 8.3
initdb, etc...

cd /usr/local/src/postgresql-8.3.3
cd contrib/tsearch2/
make uninstall
cd ../intarray/
make uninstall

create domain public.tsvector as pg_catalog.tsvector;
create domain public.gtsvector as pg_catalog.gtsvector;
create domain public.tsquery as pg_catalog.tsquery;

psql yourdb -f yourdb_DUMP_OUR_DUMP_FROM_7_4.sql >2see 2>&1

DROP TABLE public.pg_ts_cfg;
DROP TABLE public.pg_ts_cfgmap;
DROP TABLE public.pg_ts_dict ;
DROP TABLE public.pg_ts_parser ;
DROP TYPE public.query_int ;
DROP TYPE public.statinfo ;
DROP TYPE public.tokenout ;
DROP TYPE public.tokentype ;
DROP FUNCTION public.ts_debug(text) ;
DROP TYPE public.tsdebug ;

for every table with fields of tsvector do

ALTER TABLE yourtable ALTER idxfti TYPE pg_catalog.tsvector;

DROP DOMAIN public.gtsvector ;
DROP DOMAIN public.tsquery ;
DROP DOMAIN public.tsvector ;

cd /usr/local/src/postgresql-8.3.3/contrib/tsearch2 (compatibility package)
make install

load 'tsearch2';
\i /usr/local/src/postgresql-8.3.3/contrib/tsearch2/tsearch2.sql

setup the new tsearch triggers, and you are ready to go!

Στις Tuesday 12 July 2011 15:54:38 ο/η saravanan έγραψε:
> Hi all,
>
> I'm using PostgreSQL-7.4.23 version. I have one dump file that was taken
> using pg_dumpall utility. Now i tried to import the dump file.  But its
> throwing me lot of *Invalid Command \N error* , *ERROR:  type tsvector does
> not exist* and *\t: extra argument "string" ignored error*
>
> I'm using 7.4.23 psql tool to import
> I tried the following ways to import pg_dumpall dump file
> psql -f all.dump template1
> psql -e template1 < all.dump
>
> Here some of DDL commands were imported correctly. Commands like copy were
> not imported properly, its throwing lot of "Invalid Command \N error".
>
>
> Can anyone help me out of this problem?
>
> Here are the some images while importing dump file
> http://postgresql.1045698.n5.nabble.com/file/n4578769/1.jpg
>
>
>
>
>
> http://postgresql.1045698.n5.nabble.com/file/n4578769/2.jpg
>
>
>
> Any help would be appreciated
> Advance
> Thanks
>
>
>
> --
> View this message in context:
http://postgresql.1045698.n5.nabble.com/Importing-the-dump-file-in-postgresql-7-4-23-tp4578769p4578769.html
> Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
>



--
Achilleas Mantzios

Re: Importing the dump file in postgresql-7.4.23

From
Tom Lane
Date:
saravanan <saravanan.kcse@gmail.com> writes:
> I'm using PostgreSQL-7.4.23 version. I have one dump file that was taken
> using pg_dumpall utility. Now i tried to import the dump file.  But its
> throwing me lot of *Invalid Command \N error* , *ERROR:  type tsvector does
> not exist* and *\t: extra argument "string" ignored error*

Look at the *first* error (which you did not show us).  I'm betting it's
something along the line of a missing library file, eg contrib/tsearch2
not available.  A missing datatype would cascade to table creation
failures, and then to COPY commands failing.  The stuff you did show us
looks like the detritus from failed COPY commands.

            regards, tom lane

PS: You do know that 7.4.x was obsoleted some time ago, no?

Re: Importing the dump file in postgresql-7.4.23

From
saravanan
Date:
Tom Lane-2,

Thanks for your reply.

This is my first error
Initially i got error *plpgsql.so file missing*, then created symbolic link
for /usr/local/pgsql/lib from /usr/lib/postgresql/lib

First error checks for plpgsql.so files in /usr/lib/postgresql/lib. So i
created symblink.


Second error looks for $libdir/ltree. But there is no such libdir
environment variable in .bash_profile.

Notice: type 'ltree' is not yet defined
*Error : could not access file "$libdir/ltree": no such file or directory*

[postgres@oracle bin]$ pg_config --libdir
/usr/local/pgsql/lib
[postgres@oracle bin]$ pg_config --pkglibdir
/usr/local/pgsql/lib

But i have ltree directory in the following path
I have all the file here like tsearch2 and ltree. How can i map this dir to
libdir?

[postgres@oracle contrib]$ pwd
/usr/local/src/postgresql-7.4.23/contrib


I set environment variables in .bash_profile

LD_LIBRARY_PATH=/usr/local/pgsql/lib
export LD_LIBRARY_PATH
PATH=/usr/local/pgsql/bin:$PATH
export PATH
MANPATH=/usr/local/pgsql/man:$MANPATH
export MANPATH


All my library files are in /usr/local/pgsql/lib
All my tsearch2,ltree files are in /usr/local/src/postgresql-7.4.23/contrib

Any help would be appreciated

Thanks!!!


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Importing-the-dump-file-in-postgresql-7-4-23-tp4578769p4581840.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: Importing the dump file in postgresql-7.4.23

From
Tom Lane
Date:
saravanan <saravanan.kcse@gmail.com> writes:
> [postgres@oracle bin]$ pg_config --libdir
> /usr/local/pgsql/lib
> [postgres@oracle bin]$ pg_config --pkglibdir
> /usr/local/pgsql/lib

> I set environment variables in .bash_profile

> LD_LIBRARY_PATH=/usr/local/pgsql/lib
> export LD_LIBRARY_PATH

That would only help if it were in the environment of the running server
process.  Depending on how you start Postgres, putting it in the
postgres user's .bash_profile and restarting the server might get the
job done ... but it's also possible you need to change your server start
script.

            regards, tom lane

Re: Importing the dump file in postgresql-7.4.23

From
saravanan
Date:
Hi,

I tried to troubleshoot the errors while importing dumps in PostgreSQL -
8.0.0 version. But still i'm getting some permission level errors. Before
importing the dumps, i used to install some functions like
ltree,tsearch2,pgcrypto from directory
/usr/local/src/postgresql-8.0/contrib. So that i can avoid some errors.

Here is the first which i got

*ERROR: Permission denied for schema web* this error occurs while creating
table. Based on this error, i'm getting more errors.
Here is the command i used to import psql -e template1 < sdcvb.dump.

I'm moving dumps from one version to another version. How can i solve this
error?

can anyone help me to crack this error?

Note: i have dump file only. i can't take another dump from db.


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Importing-the-dump-file-in-postgresql-7-4-23-tp4578769p4589674.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.

Re: Importing the dump file in postgresql-7.4.23

From
Scott Marlowe
Date:
On Fri, Jul 15, 2011 at 1:11 AM, saravanan <saravanan.kcse@gmail.com> wrote:
> Hi,
>
> I tried to troubleshoot the errors while importing dumps in PostgreSQL -
> 8.0.0 version. But still i'm getting some permission level errors. Before
> importing the dumps, i used to install some functions like
> ltree,tsearch2,pgcrypto from directory
> /usr/local/src/postgresql-8.0/contrib. So that i can avoid some errors.

Some tips on how to backup / restore across versions.

1: pg assumes you're going from an old to a new version.  i.e. 7.4 to
8.3 etc.  Going the other way is not officially supported and you're
on your own.
2: Run both versions of the db server at once.  Use the pg_dump and
pg_dumpall from the higher version, i.e. the destination db.  So if
you're going from 7.4 to 8.4, use 8.4's pg_dump to dump data.
3: use pg_dumpall --globals to get a list of users to import to the
new database.
4: create tablespaces ahead of time if needed.

so:

pg_dumpall --globals -h pg74host -U postgres | psql -h pg84host -U postgres
createdb -h pg84host dbname
pg_dump -h pg74host dbname | psql -h pg84host dbname

Should get mostly get you there.