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>
