Thread: 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