From: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Organization: Dynacom Tankers Mgmt
To: pgsql-admin@postgresql.org
Subject: Re: Examining very large dumps
Date: Thu, 17 Apr 2008 17:15:55 +0300
User-Agent: KMail/1.9.7
References: <200804161235.27157.achill@matrix.gatewaynet.com>
	<9879.1208409922@sss.pgh.pa.us>
	<200804170846.24713.achill@matrix.gatewaynet.com>
In-Reply-To: <200804170846.24713.achill@matrix.gatewaynet.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-7"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200804171715.55961.achill@matrix.gatewaynet.com>

=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@matrix.gatewaynet.com> 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 IMPORTANT. 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 importa=
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=
=2E 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


=2D-=20
Achilleas Mantzios