How to fix a slightly corrupted table on PostgreSQL 9.0.4

SQL select is returning strange results on a table that I have:

query:
select DISTINCT symbol from historical_prices where symbol='ACAS'

results:
"ACAS"
"AMAT"
"ACAS"

So the table seems to be slightly corrupt.  One fix that I confirmed that works in a test environment is to export all contents to a flat file, truncate the table and import again.

The complication is that the table has over a million rows and the database is replicated using streaming replication.  I'm afraid that dump/delete/reload of such a large table might kill the replication.

Are there any suggestions?  Perhaps some form of the VACUUM command?

Here are the details on this table as prepared by PGAdminIII:

Property Value
Name historical_prices
OID 24783
Owner produser
Tablespace pg_default
ACL  
Of type  
Primary key symbol, date
Rows (estimated) 1096900
Fill factor  
Rows (counted) not counted
Inherits tables No
Inherited tables count 0
Has OIDs? No
System table? No
Comment  


-- Table: historical_prices--
DROP TABLE historical_prices;
CREATE TABLE historical_prices(
symbol text NOT NULL,  
date date NOT NULL,  
open numeric(6,2),  
high numeric(6,2),  
low numeric(6,2),  
"close" numeric(6,2),  
adjusted_close numeric(6,2),  
datecreated timestamp without time zone DEFAULT now(),  
lastupdated timestamp without time zone DEFAULT now(),  
CONSTRAINT "PrimaryKey" PRIMARY KEY (symbol, date))
WITH (  OIDS=FALSE);

ALTER TABLE historical_prices OWNER TO proddb;
-- Trigger: audit_trig on historical_prices--
DROP TRIGGER audit_trig ON historical_prices;
CREATE TRIGGER audit_trig  BEFORE INSERT OR UPDATE  ON historical_prices  
FOR EACH ROW  EXECUTE PROCEDURE audit_trig_fn();
sevzasAsked:
Who is Participating?
 
sevzasAuthor Commented:
running:
VACUUM FULL VERBOSE historical_prices

did the trick, however "VACUUM VERBOSE historical_prices" (without "FULL") did not work.  The standby of the streaming replication still has the problem, so I'm just going to sync it up manuall.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.