Hi,
I'm having a problem on a standby server (streaming replication) where a table seems to exist but is not queryable. Essentially a select statement (and drop/insert/etc) fails but \d and pg_tables show it exists. The table exists on the master (and is queryable) and replication is still working in that changes to the master table don't cause errors on the standby and changes to other tables are replicated and verified to be on the standby. Queries from the standby pasted below.
I have a couple of questions that arise from this:
1) Any thoughts on what is going on here?
2) If there were corruption or something in the data for that particular table on the standby only, would replication report a failure (i.e., be unable to apply the binary changes) or would the binary changes still just sort of happen overtop the bad data? Because in this case, replication is still running without reporting any errors.
3) We managed to discover this by accident during some other routine work we do from a snapshot we'd taken of the standby drives (6 volume raid0). I had assumed that if replication and the pg_last_xlog_receive_location information was up to date then I was safe but, in this case, replication continued to run but the standby is essentially unusable as a failover option since this table is not available. Is there some other way to be certain that a standby server is "consistent" with master?
Thanks,
Dale
psql session output
----------------------------
live=# set search_path to someschema;
SET
live=# select * from tracked_deductibles;
ERROR: relation "tracked_deductibles" does not exist
LINE 1: select * from tracked_deductibles;
live=# select * from someschema.tracked_deductibles;
ERROR: relation "someschema.tracked_deductibles" does not exist
LINE 1: select * from someschema.tracked_deductibles;
live=# select *,'X'||tablename||'X' from pg_tables where schemaname = 'someschema' and tablename ilike '%tracked%';
schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | ?column?
------------+-------------------------+------------+------------+------------+----------+-------------+---------------------------
someschema | tracked_deductibles | live | | t | f | t | Xtracked_deductiblesX
someschema | tracked_deductibles_log | live | | t | f | f | Xtracked_deductibles_logX
(2 rows)
live=#\d
List of relations
Schema | Name | Type | Owner
------------+-------------------------------------------------------------+----------+-------
<snip>
someschema | tracked_deductibles | table | live
someschema | tracked_deductibles_id_seq | sequence | live
<snip>
