[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 892
  • Last Modified:

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();
0
sevzas
Asked:
sevzas
1 Solution
 
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

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Tackle projects and never again get stuck behind a technical roadblock.
Join Now