Re: Table has 22 million records, but backup doesn't see them

From: Radcon Entec <radconentec(at)yahoo(dot)com>
To: Steve Crawford <scrawford(at)pinpointresearch(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: Re: Table has 22 million records, but backup doesn't see them
Date: 2009-04-08 16:47:44
Message-ID: 857988.41925.qm@web43406.mail.sp1.yahoo.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

From: Steve Crawford scrawford(at)pinpointresearch(dot)com
 
<snip>And what was the result? Zero-size file? If not, what was in the file?
</snip>

Here is the text that results from dumping my 22-million-row feedback table:

--
-- PostgreSQL database dump
--
-- Started on 2009-04-08 10:10:49 Eastern Daylight Time
SET client_encoding = 'UTF8';
SET check_function_bodies = false;
SET client_min_messages = warning;
SET search_path = public, pg_catalog;
SET default_tablespace = '';
SET default_with_oids = false;
--
-- TOC entry 1417 (class 1259 OID 7667616)
-- Dependencies: 5
-- Name: feedback; Type: TABLE; Schema: public; Owner: caps; Tablespace:
--
CREATE TABLE feedback (
feedback_key bigserial NOT NULL,
charge integer,
elapsed_time smallint,
tag_type character varying(16),
stack smallint,
tag_value real,
heating smallint,
status smallint
);
 
ALTER TABLE public.feedback OWNER TO caps;
--
-- TOC entry 1783 (class 0 OID 0)
-- Dependencies: 1416
-- Name: feedback_feedback_key_seq; Type: SEQUENCE SET; Schema: public; Owner: caps
--
SELECT pg_catalog.setval(pg_catalog.pg_get_serial_sequence('feedback', 'feedback_key'), 22326846, true);
 
--
-- TOC entry 1780 (class 0 OID 7667616)
-- Dependencies: 1417
-- Data for Name: feedback; Type: TABLE DATA; Schema: public; Owner: caps
--
COPY feedback (feedback_key, charge, elapsed_time, tag_type, stack, tag_value, heating, status) FROM stdin;
\.
 
--
-- TOC entry 1779 (class 2606 OID 7667620)
-- Dependencies: 1417 1417
-- Name: feedback_pkey; Type: CONSTRAINT; Schema: public; Owner: caps; Tablespace:
--
ALTER TABLE ONLY feedback
ADD CONSTRAINT feedback_pkey PRIMARY KEY (feedback_key);
 
--
-- TOC entry 1777 (class 1259 OID 7829003)
-- Dependencies: 1417
-- Name: feedback_charge_idx; Type: INDEX; Schema: public; Owner: caps; Tablespace:
--
CREATE INDEX feedback_charge_idx ON feedback USING btree (charge);
 
--
-- TOC entry 1514 (class 2618 OID 7667631)
-- Dependencies: 1417 1417 1418 1417 1417 1417 1417 1417 1417 1417 1417
-- Name: feedback_partition_active; Type: RULE; Schema: public; Owner: caps
--
CREATE RULE feedback_partition_active AS ON INSERT TO feedback WHERE (new.charge > 7000) DO INSTEAD INSERT INTO feedback_active (charge, elapsed_time, tag_type, stack, tag_value, heating, status) VALUES (new.charge, new.elapsed_time, new.tag_type, new.stack, new.tag_value, new.heating, new.status);
 
--
-- TOC entry 1515 (class 2618 OID 7667632)
-- Dependencies: 1417 1417 1419 1417 1417 1417 1417 1417 1417 1417 1417
-- Name: feedback_partition_archived; Type: RULE; Schema: public; Owner: caps
--
CREATE RULE feedback_partition_archived AS ON INSERT TO feedback WHERE (new.charge <= 7000) DO INSTEAD INSERT INTO feedback_archived_7000 (charge, elapsed_time, tag_type, stack, tag_value, heating, status) VALUES (new.charge, new.elapsed_time, new.tag_type, new.stack, new.tag_value, new.heating, new.status);
 
--
-- TOC entry 1782 (class 0 OID 0)
-- Dependencies: 1417
-- Name: feedback; Type: ACL; Schema: public; Owner: caps
--
REVOKE ALL ON TABLE feedback FROM PUBLIC;
REVOKE ALL ON TABLE feedback FROM caps;
GRANT ALL ON TABLE feedback TO caps;
GRANT ALL ON TABLE feedback TO anneal_operator;
GRANT ALL ON TABLE feedback TO anneal_supervisor;
GRANT ALL ON TABLE feedback TO anneal_administrator;
GRANT SELECT ON TABLE feedback TO anneal_metallurgist;
GRANT SELECT ON TABLE feedback TO anneal_guest;
 
--
-- TOC entry 1784 (class 0 OID 0)
-- Dependencies: 1416
-- Name: feedback_feedback_key_seq; Type: ACL; Schema: public; Owner: caps
--
REVOKE ALL ON TABLE feedback_feedback_key_seq FROM PUBLIC;
REVOKE ALL ON TABLE feedback_feedback_key_seq FROM caps;
GRANT ALL ON TABLE feedback_feedback_key_seq TO caps;
GRANT ALL ON TABLE feedback_feedback_key_seq TO PUBLIC;
 
-- Completed on 2009-04-08 10:10:52 Eastern Daylight Time
--
-- PostgreSQL database dump complete
--
 
I ran a DOS batch file.  Here's the first few lines:
 
"\program files\postgresql\8.1\bin\pg_dump" -f schema.sql -v -s -h 159.138.80.150 -U postgres -X disable-triggers Anneal > backup_in_pieces.log
"\program files\postgresql\8.1\bin\pg_dump" -f adhoc_query.sql -v -a -t adhoc_query -h 159.138.80.150 -U postgres -X disable-triggers Anneal
"\program files\postgresql\8.1\bin\pg_dump" -f base_cycle_compatibility.sql -v -a -t base_cycle_compatibility -h 159.138.80.150 -U postgres -X disable-triggers Anneal
"\program files\postgresql\8.1\bin\pg_dump" -f base_type.sql -v -a -t base_type -h 159.138.80.150 -U postgres -X disable-triggers Anneal

Thanks again for your help!
 
RobR
>  When I ran a batch file dumping the schema and about forty tables into separate files, no problems were encountered.  All of the resulting files have reasonable sizes.

What method did you use for that process?

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jennifer Trey 2009-04-08 16:49:46 Re: Now I am back, next thing. Final PGS tuning.
Previous Message David Wilson 2009-04-08 16:44:07 Re: Now I am back, next thing. Final PGS tuning.