Обсуждение: Pb migrating database from Postgres 8.1 to 8.4
Hi all, I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4. The way I proceed is as 'root' : su - postgres createuser -A -R -d -E my_user createdb --locale C --template template0 --encoding SQL_ASCII my_db tsearch2_sql='/usr/share/pgsql/contrib/tsearch2.sql' psql my_db < $tsearch2_sql pg_restore -d my_db my_db_tar ####################################### Msgs from during 'restore' : pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 79; 1255 16554 FUNCTION gtsvector_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file "/usr/lib64/pgsql/tsearch2.so" Command was: CREATE FUNCTION gtsvector_in(cstring) RETURNS gtsvector AS '$libdir/tsearch2', 'gtsvector_in' LANGUAGE c STRICT; pg_restore: [archiver (db)] could not execute query: ERROR: function public.gtsvector_in(cstring) does not exist Command was: ALTER FUNCTION public.gtsvector_in(cstring) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 80; 1255 16555 FUNCTION gtsvector_out(gtsvector) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_out" in file "/usr/lib64/pgsql/tsearch2.so" Command was: CREATE FUNCTION gtsvector_out(gtsvector) RETURNS cstring AS '$libdir/tsearch2', 'gtsvector_out' LANGUAGE c STRICT; pg_restore: [archiver (db)] could not execute query: ERROR: function public.gtsvector_out(gtsvector) does not exist Command was: ALTER FUNCTION public.gtsvector_out(gtsvector) OWNER TO postgres; pg_restore: [archiver (db)] Error from TOC entry 350; 1247 16553 TYPE gtsvector postgres pg_restore: [archiver (db)] could not execute query: ERROR: function gtsvector_in(cstring) does not exist Command was: CREATE TYPE gtsvector ( INTERNALLENGTH = variable, INPUT = gtsvector_in, OUTPUT = gtsvector_out, ALIGNMENT =... pg_restore: [archiver (db)] Error from TOC entry 56; 1255 16527 FUNCTION tsquery_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "tsquery_in" in file "/usr/lib64/pgsql/tsearch2.so" Command was: CREATE FUNCTION tsquery_in(cstring) RETURNS tsquery AS '$libdir/tsearch2', 'tsquery_in' LANGUAGE c STRICT; pg_restore: [archiver (db)] Error while PROCESSING TOC: pg_restore: [archiver (db)] Error from TOC entry 79; 1255 16554 FUNCTION gtsvector_in(cstring) postgres pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file "/usr/lib64/pgsql/tsearch2.so" Command was: CREATE FUNCTION gtsvector_in(cstring) RETURNS gtsvector AS '$libdir/tsearch2', 'gtsvector_in' LANGUAGE c STRICT; ............ ######################################## Thx in advance for any help Regards Chanh
chanh.tran@free.fr writes:
> I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4.
> pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file
"/usr/lib64/pgsql/tsearch2.so"
Looks like you're missing the shared library that underlies tsearch2.
The other errors seem to be consequences of this one.
regards, tom lane
On Fri, Apr 19, 2013 at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > chanh.tran@free.fr writes: >> I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4. > >> pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file "/usr/lib64/pgsql/tsearch2.so" > > Looks like you're missing the shared library that underlies tsearch2. > The other errors seem to be consequences of this one. > It might be cleaner to try and remove the tsearch bits from your schema dump and get a fresh install of tsearch by itself before loading in your own schema. Also, given you're making this leap, I'd strongly encourage you to try and migrate to Postgres 9.2 instead of 8.4, which is the next version to be EOL'd. It's unlikely the pain will be significantly worse than what you are going through now. Robert Treat conjecture: xzilla.net consulting: omniti.com
Robert Treat <rob@xzilla.net> writes:
> On Fri, Apr 19, 2013 at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> chanh.tran@free.fr writes:
>>> I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4.
>>> pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file
"/usr/lib64/pgsql/tsearch2.so"
>> Looks like you're missing the shared library that underlies tsearch2.
>> The other errors seem to be consequences of this one.
> It might be cleaner to try and remove the tsearch bits from your
> schema dump and get a fresh install of tsearch by itself before
> loading in your own schema.
Actually, on second look, I think this is expected behavior when trying to
update from pre-8.3 text search --- that whole conversion was none too
clean. Note the warning in the tsearch2 module documentation about
3. Load the dump data. There will be quite a few errors reported
due to failure to recreate the original tsearch2 objects. These
errors can be ignored, but this means you cannot restore the
dump in a single transaction (eg, you cannot use pg_restore's -1
switch).
If you weren't actually using the tsearch2 features before, you might be
best advised to uninstall tsearch2 from the old database before you
convert. If you were, please read the documentation about text search
conversion carefully --- both
http://www.postgresql.org/docs/8.4/static/textsearch-migration.html
and the contrib/tsearch2 page.
regards, tom lane
Hi Tom, First of all, Big Thx for answering. Otherwise, thx also for all this hints. What about my case seems to be more related to the fact 'tsearch2' is already 'integrated' in 8.4 Whereas in 8.1, one has to have it 'imported' on purpose. All this leads to conflicts during 'restore' of my DB cf. http://rockfloat.com/blog/?id=42 I'll keep U posted on know how things work out via help from above link Regards, Chanh ----- Mail original ----- De: "Tom Lane" <tgl@sss.pgh.pa.us> À: "Robert Treat" <rob@xzilla.net> Cc: "chanh tran" <chanh.tran@free.fr>, pgsql-admin@postgresql.org Envoyé: Vendredi 19 Avril 2013 18:09:40 Objet: Re: [ADMIN] Pb migrating database from Postgres 8.1 to 8.4 Robert Treat <rob@xzilla.net> writes: > On Fri, Apr 19, 2013 at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> chanh.tran@free.fr writes: >>> I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4. >>> pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file "/usr/lib64/pgsql/tsearch2.so" >> Looks like you're missing the shared library that underlies tsearch2. >> The other errors seem to be consequences of this one. > It might be cleaner to try and remove the tsearch bits from your > schema dump and get a fresh install of tsearch by itself before > loading in your own schema. Actually, on second look, I think this is expected behavior when trying to update from pre-8.3 text search --- that whole conversion was none too clean. Note the warning in the tsearch2 module documentation about 3. Load the dump data. There will be quite a few errors reported due to failure to recreate the original tsearch2 objects. These errors can be ignored, but this means you cannot restore the dump in a single transaction (eg, you cannot use pg_restore's -1 switch). If you weren't actually using the tsearch2 features before, you might be best advised to uninstall tsearch2 from the old database before you convert. If you were, please read the documentation about text search conversion carefully --- both http://www.postgresql.org/docs/8.4/static/textsearch-migration.html and the contrib/tsearch2 page. regards, tom lane
Also Thx to Robert :) ... ----- Mail original ----- De: "chanh tran" <chanh.tran@free.fr> À: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: pgsql-admin@postgresql.org, "Robert Treat" <rob@xzilla.net> Envoyé: Lundi 22 Avril 2013 13:27:29 Objet: Re: [ADMIN] Pb migrating database from Postgres 8.1 to 8.4 Hi Tom, First of all, Big Thx for answering. Otherwise, thx also for all this hints. What about my case seems to be more related to the fact 'tsearch2' is already 'integrated' in 8.4 Whereas in 8.1, one has to have it 'imported' on purpose. All this leads to conflicts during 'restore' of my DB cf. http://rockfloat.com/blog/?id=42 I'll keep U posted on know how things work out via help from above link Regards, Chanh ----- Mail original ----- De: "Tom Lane" <tgl@sss.pgh.pa.us> À: "Robert Treat" <rob@xzilla.net> Cc: "chanh tran" <chanh.tran@free.fr>, pgsql-admin@postgresql.org Envoyé: Vendredi 19 Avril 2013 18:09:40 Objet: Re: [ADMIN] Pb migrating database from Postgres 8.1 to 8.4 Robert Treat <rob@xzilla.net> writes: > On Fri, Apr 19, 2013 at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> chanh.tran@free.fr writes: >>> I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4. >>> pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file "/usr/lib64/pgsql/tsearch2.so" >> Looks like you're missing the shared library that underlies tsearch2. >> The other errors seem to be consequences of this one. > It might be cleaner to try and remove the tsearch bits from your > schema dump and get a fresh install of tsearch by itself before > loading in your own schema. Actually, on second look, I think this is expected behavior when trying to update from pre-8.3 text search --- that whole conversion was none too clean. Note the warning in the tsearch2 module documentation about 3. Load the dump data. There will be quite a few errors reported due to failure to recreate the original tsearch2 objects. These errors can be ignored, but this means you cannot restore the dump in a single transaction (eg, you cannot use pg_restore's -1 switch). If you weren't actually using the tsearch2 features before, you might be best advised to uninstall tsearch2 from the old database before you convert. If you were, please read the documentation about text search conversion carefully --- both http://www.postgresql.org/docs/8.4/static/textsearch-migration.html and the contrib/tsearch2 page. regards, tom lane
Hi all, As promised, this is a feedback on how things finally worked out OK for me. Method applied : 'pg_restore -l/-L' and based on 'filters' from 'http://rockfloat.com/blog/?id=42' meaning : Step 1. pg_restore -l my_db.tar | egrep -v '\|\||\@\@|stat\(|syn_|token_|tsvector|tsquery|tsdebug|ts_debug|tsearch|pg_ts_|dex_|lexize| parse|prsd_|_cur|snb_|spell_'>| /tmp/pg_restore.list Step 2. pg_restore -L /tmp/pg_restore.list -d my_db_name my_db.tar Regards, Chanh ----- Mail original ----- De: "chanh tran" <chanh.tran@free.fr> À: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: pgsql-admin@postgresql.org, "Robert Treat" <rob@xzilla.net> Envoyé: Lundi 22 Avril 2013 13:28:22 Objet: Re: [ADMIN] Pb migrating database from Postgres 8.1 to 8.4 Also Thx to Robert :) ... ----- Mail original ----- De: "chanh tran" <chanh.tran@free.fr> À: "Tom Lane" <tgl@sss.pgh.pa.us> Cc: pgsql-admin@postgresql.org, "Robert Treat" <rob@xzilla.net> Envoyé: Lundi 22 Avril 2013 13:27:29 Objet: Re: [ADMIN] Pb migrating database from Postgres 8.1 to 8.4 Hi Tom, First of all, Big Thx for answering. Otherwise, thx also for all this hints. What about my case seems to be more related to the fact 'tsearch2' is already 'integrated' in 8.4 Whereas in 8.1, one has to have it 'imported' on purpose. All this leads to conflicts during 'restore' of my DB cf. http://rockfloat.com/blog/?id=42 I'll keep U posted on know how things work out via help from above link Regards, Chanh ----- Mail original ----- De: "Tom Lane" <tgl@sss.pgh.pa.us> À: "Robert Treat" <rob@xzilla.net> Cc: "chanh tran" <chanh.tran@free.fr>, pgsql-admin@postgresql.org Envoyé: Vendredi 19 Avril 2013 18:09:40 Objet: Re: [ADMIN] Pb migrating database from Postgres 8.1 to 8.4 Robert Treat <rob@xzilla.net> writes: > On Fri, Apr 19, 2013 at 10:19 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote: >> chanh.tran@free.fr writes: >>> I'm facing pbs trying to migrate my current database from Postgres 8.1 to 8.4. >>> pg_restore: [archiver (db)] could not execute query: ERROR: could not find function "gtsvector_in" in file "/usr/lib64/pgsql/tsearch2.so" >> Looks like you're missing the shared library that underlies tsearch2. >> The other errors seem to be consequences of this one. > It might be cleaner to try and remove the tsearch bits from your > schema dump and get a fresh install of tsearch by itself before > loading in your own schema. Actually, on second look, I think this is expected behavior when trying to update from pre-8.3 text search --- that whole conversion was none too clean. Note the warning in the tsearch2 module documentation about 3. Load the dump data. There will be quite a few errors reported due to failure to recreate the original tsearch2 objects. These errors can be ignored, but this means you cannot restore the dump in a single transaction (eg, you cannot use pg_restore's -1 switch). If you weren't actually using the tsearch2 features before, you might be best advised to uninstall tsearch2 from the old database before you convert. If you were, please read the documentation about text search conversion carefully --- both http://www.postgresql.org/docs/8.4/static/textsearch-migration.html and the contrib/tsearch2 page. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin