Skip site navigation (1) Skip section navigation (2)

Re: Examining very large dumps

From: Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Examining very large dumps
Date: 2008-04-17 14:15:55
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin
Στις Thursday 17 April 2008 08:46:24 ο/η Achilleas Mantzios έγραψε:
> Στις Thursday 17 April 2008 08:25:22 ο/η Tom Lane έγραψε:
> > Achilleas Mantzios <achill(at)matrix(dot)gatewaynet(dot)com> writes:
> > >> Did you make the dump using 8.3's pg_dump?
> > 
> > > Yes, with 8.3.1's pg_dump (data only dump)
> > 
> > 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
> > 
> This procedure is kind of the official upgrade noted on
> and described on
> >
> 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, 
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 wasnt mentioned (at least from what i've read) 
in the numerous conversations regarding upgrade of tsearch2 to 8.3, plus it is even recommended (indirectly) via the official tsearch2
site ( which points to

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=original_db_encoding (this is essential in order to avoid encoding conversion problems)
3) In the 8.3.1 installation, we make sure tsearch2,intarray are not installed. This step is needed in order to force any, related stmts to break.
	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 CREATE 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 other tables having 0 row count.
I didnt chase this one thru, however i got the idea that it is very important 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, hopefully 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
	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
	    EXECUTE PROCEDURE tsvector_update_trigger('idxfti', 'pg_catalog.english', '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.
> Any thoughts very welcome.
> > 
> > 			regards, tom lane
> > 
> -- 
> Achilleas Mantzios

Achilleas Mantzios

In response to

pgsql-admin by date

Next:From: Tom LaneDate: 2008-04-17 15:10:22
Subject: Re: FATAL: could not open relation xxx: No such file or directory
Previous:From: Mikko PartioDate: 2008-04-17 10:44:22
Subject: Re: FATAL: could not open relation xxx: No such file or directory

Privacy Policy | About PostgreSQL
Copyright © 1996-2018 The PostgreSQL Global Development Group