Обсуждение: Importing the dump file in postgresql-7.4.23
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.
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
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?
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.
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
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.
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.