Обсуждение: Examining very large dumps

Hi, i just started the process of migrating from 7.4 to 8.3.1.
On restoring, apart from the easily explainable ERRORs (both in DDL, COPY), i got some ugly ERRORs
denoting foreign key constraint violations.
Since the dump is 35 Gbytes, i'd like your advice for examining, editing, extracting portions of large dumps,
in order to find the cause of the problem. vim (linux), vi (FreeBSD) or any other unix editor i tried, could not handle
the
volume.

Thank you very much for any help.
It's been the first day of the upgrade effort, and if i judge from the begining, the rest of the process will not be
anyeasier. 
--
Achilleas Mantzios

On Wed, Apr 16, 2008 at 12:35:26PM +0300, Achilleas Mantzios wrote:
> Hi, i just started the process of migrating from 7.4 to 8.3.1.
> On restoring, apart from the easily explainable ERRORs (both in DDL, COPY), i got some ugly ERRORs
> denoting foreign key constraint violations.
> Since the dump is 35 Gbytes, i'd like your advice for examining, editing, extracting portions of large dumps,
> in order to find the cause of the problem. vim (linux), vi (FreeBSD) or any other unix editor i tried, could not
handlethe 
> volume.

Do you have a plain text dump or a compressed archive? Try using less
for a first view, then extract things with grep or alter with sed.

HTH,

Tino.

--
„What we resist, persists.” (Zen saying)

www.craniosacralzentrum.de
www.forteego.de

Στις Wednesday 16 April 2008 12:52:45 ο/η Tino Schwarze έγραψε:
> On Wed, Apr 16, 2008 at 12:35:26PM +0300, Achilleas Mantzios wrote:
> > Hi, i just started the process of migrating from 7.4 to 8.3.1.
> > On restoring, apart from the easily explainable ERRORs (both in DDL, COPY), i got some ugly ERRORs
> > denoting foreign key constraint violations.
> > Since the dump is 35 Gbytes, i'd like your advice for examining, editing, extracting portions of large dumps,
> > in order to find the cause of the problem. vim (linux), vi (FreeBSD) or any other unix editor i tried, could not
handlethe 
> > volume.
>
> Do you have a plain text dump or a compressed archive? Try using less
> for a first view, then extract things with grep or alter with sed.
Its a text dump. however less, tail, head seem dead slow, i'll try with some form
of grep.
I'd like to know if there is some kind of more efficient editor for viewing, searching
very large text files, searching by lineno or content.
>
> HTH,
>
> Tino.
>
> --
> β€žWhat we resist, persists.” (Zen saying)
>
> www.craniosacralzentrum.de
> www.forteego.de
>



--
Achilleas Mantzios

On Wed, Apr 16, 2008 at 12:58:29PM +0300, Achilleas Mantzios wrote:

> > > Hi, i just started the process of migrating from 7.4 to 8.3.1.
> > > On restoring, apart from the easily explainable ERRORs (both in
> > > DDL, COPY), i got some ugly ERRORs denoting foreign key constraint
> > > violations.  Since the dump is 35 Gbytes, i'd like your advice for
> > > examining, editing, extracting portions of large dumps, in order
> > > to find the cause of the problem. vim (linux), vi (FreeBSD) or any
> > > other unix editor i tried, could not handle the volume.
> >
> > Do you have a plain text dump or a compressed archive? Try using less
> > for a first view, then extract things with grep or alter with sed.

> Its a text dump. however less, tail, head seem dead slow, i'll try with some form
> of grep.

less will try to count the number of lines. You may savely interrupt
this by CTRL-C.

> I'd like to know if there is some kind of more efficient editor for viewing, searching
> very large text files, searching by lineno or content.

I'm afraid that would be sed in this case. At least I don't know of any
special large text file editor. After all, if you need to search,
there's almost no way around searching through the whole 35 GB.

Tino.

--
„What we resist, persists.” (Zen saying)

www.craniosacralzentrum.de
www.forteego.de

Achilleas Mantzios <achill@matrix.gatewaynet.com> writes:
> Hi, i just started the process of migrating from 7.4 to 8.3.1.
> On restoring, apart from the easily explainable ERRORs (both in DDL, COPY), i got some ugly ERRORs
> denoting foreign key constraint violations.
> Since the dump is 35 Gbytes, i'd like your advice for examining, editing, extracting portions of large dumps,

Did you make the dump using 8.3's pg_dump?

IIRC 7.4 still didn't know about ordering the elements of a dump to
avoid forward-reference problems.

            regards, tom lane

> vim (linux), vi (FreeBSD) or any other unix editor i tried, could not
handle the volume.

Vi(m) should be able to handle that given enough hardware (CPU and RAM)...
And if hardware is your limitation then no editors like those are going to
be able to handle it.

sed and sed-like tools are going to be the only options.


THINK BEFORE YOU PRINT - Save paper if you don't really need to print this

*******************Confidentiality and Privilege Notice*******************

The material contained in this message is privileged and confidential to
the addressee.  If you are not the addressee indicated in this message or
responsible for delivery of the message to such person, you may not copy
or deliver this message to anyone, and you should destroy it and kindly
notify the sender by reply email.

Information in this message that does not relate to the official business
of Weatherbeeta must be treated as neither given nor endorsed by Weatherbeeta.
Weatherbeeta, its employees, contractors or associates shall not be liable
for direct, indirect or consequential loss arising from transmission of this
message or any attachments
e-mail.

Achilleas Mantzios <achill@matrix.gatewaynet.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

http://www.postgresql.org/docs/8.3/static/populate.html

            regards, tom lane

Στις Thursday 17 April 2008 08:25:22 ο/η Tom Lane έγραψε:
> Achilleas Mantzios <achill@matrix.gatewaynet.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
>

Ooops, now it seems i have an issue.
The whole point i went this way, was because i wanted to have a schema-only dump first,
in order to clean it from everything it had to do with contrib/tsearch2, contrib/intarray, dbsize
as well as to edit the triggers (substitute tsearch2 with tsvector_update_trigger), update the tsearch2 indexes
to use GIN.

So the plan was:
1) i take the schema-only dump
2) i edit the schema dump
3) i create the db
4) import _int.sql
5) import the schema
6) restore data
This procedure is kind of the official upgrade noted on http://www.sai.msu.su/~megera/wiki/Tsearch2_83_changes
and described on http://sql-info.de/postgresql/notes/converting-tsearch2-to-8.3.html

> http://www.postgresql.org/docs/8.3/static/populate.html
I am reading this link right away.

Any thoughts very welcome.
>
>             regards, tom lane
>



--
Achilleas Mantzios

Στις Thursday 17 April 2008 08:46:24 ο/η Achilleas Mantzios έγραψε:
> Στις Thursday 17 April 2008 08:25:22 ο/η Tom Lane έγραψε:
> > Achilleas Mantzios <achill@matrix.gatewaynet.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 http://www.sai.msu.su/~megera/wiki/Tsearch2_83_changes
> and described on http://sql-info.de/postgresql/notes/converting-tsearch2-to-8.3.html
>
> > 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
disabletriggers,  
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
officialtsearch2 
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=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 
tsearch2.so, _int.so related stmts to break.
4)
    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
7)
    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.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