Re: Mysterious table that exists but doesn't exist

Dale Fukami <dale(dot)fukami(at)gmail(dot)com> writes:
> On Tue, Apr 16, 2013 at 3:04 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Either way, if it's working on the master, then you've had a replication
>> failure since the standby's files evidently don't match the master's.
>>
>> What PG version is this (and which versions have been installed since
>> the replication was set up)? Have you had any system-level crashes on
>> the standby?

> My apologies: PostgreSQL 9.0.5 on x86_64-pc-linux-gnu, compiled by GCC
> gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5) 4.4.3, 64-bit
> This is the only version we've had since we've set up streaming replication.

> We believe we've found the moment of corruption. It appears that the db was
> shutdown then restarted and didn't quite catch up to the master and then
> was shut down again. We use this standby to take snapshots on a regular
> basis but it looks like this one didn't quite get into a consistent state
> before the next one started. Logs pasted at the end of this email.

Hm ... there was a fix in 9.0.12 that might be relevant to this:
http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=5840e3181b7e6c784fdb3aff708c4dcc2dfe551d
Whether that explains it or not, 9.0.5 is getting long in the tooth;
you really need to think about an update. Especially in view of
CVE-2013-1899.

> 1) We were quite lucky I think to have discovered this issue. I think the
> only other way we'd have noticed would have been if we'd failed over to it
> and our app stopped working. I'm worried now that we'll end up in a similar
> situation and won't have known it for many weeks. At some point we lose the
> ability to PITR based on how many backups are kept. Is there a way to be
> more confident in our standby machines?

I think you should update your PG version and then resync your standbys
(ie, apply a fresh base backup) at the next convenient maintenance
window.

> Just to clear up the confusion that I had caused John. I had obscured the
> previous schema name by restoring from snapshots and altering the schema
> name itself. So, the queries and output are an exact copy/paste from my
> terminal. I get the same results on the Live standby when using the actual
> Live schema name but, obviously, it shows the Live schema name rather than
> 'someschema'.

Well, in that case there's the question of whether you'd duplicated the
standby's state accurately ...

regards, tom lane