How to fix a slightly corrupted table on PostgreSQL 9.0.4
Posted on 2012-08-10
SQL select is returning strange results on a table that I have:
select DISTINCT symbol from historical_prices where symbol='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:
Primary key symbol, date
Rows (estimated) 1096900
Rows (counted) not counted
Inherits tables No
Inherited tables count 0
Has OIDs? No
System table? No
-- Table: historical_prices--
DROP TABLE historical_prices;
CREATE TABLE historical_prices(
symbol text NOT NULL,
date date NOT NULL,
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();