=D3=F4=E9=F2 Thursday 17 April 2008 08:46:24 =EF/=E7 Achilleas Mantzios
=DD=
=E3=F1=E1=F8=E5:
> =D3=F4=E9=F2 Thursday 17 April 2008 08:25:22 =EF/=E7 Tom Lane =DD=E3=F1=
=E1=F8=E5:
> > Achilleas Mantzios <achill@[EMAIL PROTECTED]
> writes:
> > >> Did you make the dump using 8.3's pg_dump?
> >=20
> > > Yes, with 8.3.1's pg_dump (data only dump)
> >=20
> > That would be your problem. *Don't* use a data-only dump, it
> > lobotomizes all intelligence in the system and leaves it up to you
> > to deal with foreign-key ordering issues. There are lots of
> > performance arguments against that as well. See the advice at
> >=20
>=20
> This procedure is kind of the official upgrade noted on
http://www.sai.ms=
u.su/~megera/wiki/Tsearch2_83_changes
> and described on
http://sql-info.de/postgresql/notes/converting-tsearch2-=
to-8.3.html
>=20
> > http://www.postgresql.org/docs/8.3/static/populate.html
> I am reading this link right away.
So it seems that whoever decides to take this path (schema-only dump, then
=
data-only dump), will be initially forced to disable triggers,=20
which means that he/she will have to recrate all FK constraints inorder to
=
enforce/verify the integrity of the DB.
Also all indexes will have to be recreated.
However, one think that puzzles me is why this fact about data-only dumps
w=
asnt mentioned (at least from what i've read)=20
in the numerous conversations regarding upgrade of tsearch2 to 8.3, plus
it=
is even recommended (indirectly) via the official tsearch2
site (http://www.sai.msu.su/~megera/wiki/Tsearch2_83_changes)
which points
=
to http://sql-info.de/postgresql/notes/converting-tsearch2-to-8.3.html
Anyway thats what i did in my case, where i had to cleanup from a previous
=
7.4 installation with tsearch2 and intarray installed.
1) take a full dump using pg_dump of 8.3.1
pg_dump -U postgres -h old7.4.19machine mydbname > DUMPFULL.sql
2) createdb mydbname --encoding=3Doriginal_db_encoding (this is essential
i=
n order to avoid encoding conversion problems)
3) In the 8.3.1 installation, we make sure tsearch2,intarray are not
instal=
led. This step is needed in order to force any=20
tsearch2.so, _int.so related stmts to break.
4)=20
create domain public.tsvector as pg_catalog.tsvector;
create domain public.gtsvector as pg_catalog.gtsvector;
create domain public.tsquery as pg_catalog.tsquery;
(Thanx to Tom). This step is needed in order to force the corresponding
CRE=
ATE TYPE stmts to fail,
in order for the CREATE TABLE stmts with tsvector type columns to succeed!
THIS IS IM****TANT. In my case moving a large DB dump (35 GB) from 7.4.19
to=
8.3.1
for some reason, and while i had this problem of broken CREATE TABLE
stmts,=
i ended up with
incomplete DB. And i mean not only those missing tables, but also several
o=
ther tables having 0 row count.
I didnt chase this one thru, however i got the idea that it is very
im****ta=
nt to have all tables in place.
5) psql dynacom -f DUMPFULL.sql >2see 2>&1
here most things having to do with either intarray or tsearch2 will fail,
h=
opefully tho, we will have all our data in place!
6) We drop whatever tsearch2,intarray related is left from the dump
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 ;
at this point hopefully we are clean of garbage in our new 8.3.1 DB
7)=20
ALTER TABLE vmanews ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE smanews ALTER idxfti TYPE pg_catalog.tsvector;
ALTER TABLE vesselhelp ALTER idxfti TYPE pg_catalog.tsvector;
now we drop the domains used in the intial Tom's trick
DROP DOMAIN public.gtsvector ;
DROP DOMAIN public.tsquery ;
DROP DOMAIN public.tsvector ;
8) Now we create the triggers for the update of tsvector columns:
CREATE TRIGGER mytable_tsvectorupdate
BEFORE INSERT OR UPDATE ON mytable
FOR EACH ROW
EXECUTE PROCEDURE tsvector_update_trigger('idxfti',
'pg_catalog.englis=
h', 'header', 'content');
NOTE here that the previous functionality of having an extra function
(.e.g=
.. dropatsymbol) is removed from tsearch2.
9) installation of intarray and creation of all relevant indexes.
And that was it!
Any comments are welcome.
>=20
> Any thoughts very welcome.
> >=20
> > regards, tom lane
> >=20
>=20
>=20
>=20
> --=20
> Achilleas Mantzios
>=20
--=20
Achilleas Mantzios
--=20
Sent via pgsql-admin mailing list (pgsql-admin@[EMAIL PROTECTED]
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
|
