Обсуждение: Table has 22 million records, but backup doesn't see them

Greetings!

 

I'm running PostgreSQL 8.1 under Windows XP, looking at a database hosted on a machine running PostgreSQL under Windows Server 2003.

 

The database has a table with three simple columns and 22 million rows.  I am trying to back up that table by itself.  However, pg_dump finishes almost instantly, obviously not backing up any data from the table.  I've tried it from the DOS command line with and without the -a (data only) option, and from inside PGAdmin.  Can anyone suggest what might cause this behavior?

 

Please remember to reply-all so others can help or see the solution as
well (also, the convention on this list is to bottom-post, not top-post).

Radcon Entec wrote:
> Steve,
>
> Here's the exact command and output, taken from the DOS command window:
>
> C:\Documents and Settings\entec>"\program
> files\postgresql\8.1\bin\pg_dump" -f f
> eedback.sql -v -a -t feedback -h 159.138.80.150 -U postgres Anneal
> pg_dump: reading schemas
> pg_dump: reading user-defined functions
> pg_dump: reading user-defined types
> pg_dump: reading procedural languages
> pg_dump: reading user-defined aggregate functions
> pg_dump: reading user-defined operators
> pg_dump: reading user-defined operator classes
> pg_dump: reading user-defined conversions
> pg_dump: reading user-defined tables
> pg_dump: reading table inheritance information
> pg_dump: reading rewrite rules
> pg_dump: reading type casts
> pg_dump: finding inheritance relationships
> pg_dump: reading column info for interesting tables
> pg_dump: finding the columns and types of table "feedback"
> pg_dump: finding default expressions of table "feedback"
> pg_dump: flagging inherited columns in subtables
> pg_dump: reading indexes
> pg_dump: reading indexes for table "feedback"
> pg_dump: reading constraints
> pg_dump: reading triggers
> pg_dump: reading dependency data
> pg_dump: saving encoding
> pg_dump: executing SEQUENCE SET feedback_feedback_key_seq
> pg_dump: restoring data for table "feedback"
> pg_dump: dumping contents of table feedback
And what was the result? Zero-size file? If not, what was in the file?
>
> 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?
> I haven't tried psql yet, nor pg_dumpall.  I've got a full backup
> running now.
Via what utility if you aren't using pg_dumpall? Also, running a full
backup won't impede testing your connection with psql.
>
> I am sure that I'm looking at a table.
>
> I'm not sure where to find the server logs (which gives you some idea
> of my knowledge of PostgreSQL administration).  I'll go check them now.
>
> RobR
>
> ------------------------------------------------------------------------
> *From:* Steve Crawford <scrawford@pinpointresearch.com>
> *To:* Radcon Entec <radconentec@yahoo.com>
> *Cc:* pgsql-general@postgresql.org
> *Sent:* Wednesday, April 8, 2009 11:25:20 AM
> *Subject:* Re: [GENERAL] Table has 22 million records, but backup
> doesn't see them
>
> Radcon Entec wrote:
>> Greetings!
>>
>> I'm running PostgreSQL 8.1 under Windows XP, looking at a database
>> hosted on a machine running PostgreSQL under Windows Server 2003.
>>
>> The database has a table with three simple columns and 22 million
>> rows.  I am trying to back up that table by itself.  However, pg_dump
>> finishes almost instantly, obviously not backing up any data from the
>> table.  I've tried it from the DOS command line with and without the
>> -a (data only) option, and from inside PGAdmin.  Can anyone suggest
>> what might cause this behavior?
>>
> What is the exact command and what is the output (I'll be surprised if
> there is no output at all to either stdout or stderr)? Does pg_dumpall
> run fine from the same machine? How about psql? Are you sure you are
> hitting a base-table and not a view? Do the server logs show anything
> interesting?
>
> Cheers,
> Steve
>
>

Radcon Entec wrote:
>
>
> Here is the text that results from dumping my 22-million-row feedback
> table:
>
> ...
>
> 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);
>
> ...
>
> 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);
>

Are you certain that feedback actually contains any data or is it just
the parent table and the real data is in the child tables? What is the
output of "select count(*) from only feedback;" ?

Cheers,
Steve

Radcon Entec wrote:
> ------------------------------------------------------------------------
> *
> *
> You are, of course, correct.  "select count(*) from only feedback"
> returns 0.  I have never used (or even seen) PostgreSQL rules before.
>
> When I run the query "select * from feedback where charge = 23017", I
> get 538 records.  Adding the word "only" gives me zero records, as
> expected, and querying the feedback_active table gets me my 538
> records.  But the feedback table only has the INSERT rules you quoted
> above.  I clicked on the feedback table's Rules leaf and selected "New
> Rule", and saw that I can create SELECT, INSERT, UPDATE and DELETE
> rules.  But even though I don't have a SELECT rule explicitly defined,
> PostgreSQL appears to be smart enough to retrieve data from the
> correct actual table when I think I'm selecting from the feedback
> table.  Is that standard behavior?
>
> Of course, my next step will be to read the documentation.
>
You won't find the cause of your surprise reading up on rules. Read up
on inheritance, instead. It can do a lot, but a typical simple use is
table-partitioning as appears to be the case in your situation.

You start with the main (parent) table, say "events" then create a bunch
of child tables that inherit events, say events_jan, events_feb,
events_mar...

You never put actual data in "events" but create a rule or trigger that
looks at the month information and puts January events in events_jan,
February in events_feb and so on.

Select * from events is more-or-less equivalent to:
select * from events_jan union
select * from events_feb union...

Inheritance goes far beyond the simple case shown above. There's a lot
of nifty stuff you can do and a number of things that can bite you. But,
as you say, that's where the documentation comes in.

Cheers,
Steve

> I still would like to understand why the feedback table cannot be backed
> up by itself.

Because there is no actual data in the feedback table.

It's being stored in the "feedback_active", "feedback_archived_7000" and
your other tables instead.

--
Postgresql & php tutorials
http://www.designmagick.com/