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

From: Steve Crawford <scrawford(at)pinpointresearch(dot)com>
To: Radcon Entec <radconentec(at)yahoo(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:15:26
Message-ID: 49DCCD9E.2010106@pinpointresearch.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)pinpointresearch(dot)com>
> *To:* Radcon Entec <radconentec(at)yahoo(dot)com>
> *Cc:* pgsql-general(at)postgresql(dot)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
>
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Mead 2009-04-08 16:15:56 Re: Now I am back, next thing. Final PGS tuning.
Previous Message Jennifer Trey 2009-04-08 16:05:26 Re: Now I am back, next thing. Final PGS tuning.