Обсуждение: Suddenly all tables were gone

Moshe Jacobson <moshe@neadwerx.com> writes:
> Yesterday I found that one of the databases in my database cluster suddenly
> lost all its tables. A \dt in psql showed nothing. I'm not sure how or when
> it happened, but it was either due to an upgrade of postgres from 9.1 to
> 9.3 or else something going wrong with pg_dump.

This sounds like a transaction ID wraparound issue, ie, the pg_class rows
are still there but you can't see them because they're "in the future".
VACUUM FREEZE would probably fix it, but the interesting question is why
didn't the built-in wraparound defenses prevent this from happening.
Could we see the output from pg_controldata?

            regards, tom lane

> Yesterday I found that one of the databases in my database cluster suddenly lost all its tables.
> A \dt in psql showed nothing.

Is there any chance this could be a search_path issue? Do you have a ~/.psqlrc?

Or are you sure you're in the right database? If you are connecting as
the postgres user, are you using \c?

Sorry for the "is it plugged in?" questions, but hopefully it's
something along those lines rather than anything more complicated!

Paul



On Fri, Jan 3, 2014 at 11:20 AM, Moshe Jacobson <moshe@neadwerx.com> wrote:
> Yesterday I found that one of the databases in my database cluster suddenly
> lost all its tables. A \dt in psql showed nothing. I'm not sure how or when
> it happened, but it was either due to an upgrade of postgres from 9.1 to 9.3
> or else something going wrong with pg_dump.
>
> Has anyone had this happen before?
>
> Thanks.
>
> Moshe Jacobson
> Manager of Systems Engineering, Nead Werx Inc.
> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
>
> "Quality is not an act, it is a habit." -- Aristotle



--
_________________________________
Pulchritudo splendor veritatis.

Moshe Jacobson <moshe@neadwerx.com> writes:
> On Fri, Jan 3, 2014 at 2:42 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Could we see the output from pg_controldata?

> Here you go. The problem database has already been dropped and re-created,

Then the evidence is pretty much gone I'm afraid :-(

> Latest checkpoint's NextXID:          0/32280314

Hm, this cluster is nowhere near old enough for wraparound to be an
issue.  No idea now what happened to you.

If it happens again, please rename the broken DB out of the way rather
than summarily dropping it.  We'll not have much chance of identifying
the cause without some forensic work.

            regards, tom lane

On 1/3/2014 12:18 PM, Moshe Jacobson wrote:
> Yeah, I was connecting as postgres, no special search_path, same
> config was working earlier. No harm in asking!

it was working AFTER the 9.1->9.3 upgrade ?




--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

On 01/03/2014 01:18 PM, Moshe Jacobson wrote:
>
> On Fri, Jan 3, 2014 at 3:59 PM, John R Pierce <pierce@hogranch.com
> <mailto:pierce@hogranch.com>> wrote:
>
>     it was working AFTER the 9.1->9.3 upgrade ?
>
>
> I think it was for some amount of time, since I used pg_dump to dump the
> contents, and pg_restore to restore those contents into another database
> in the same cluster.
> I was using the database to test some things, and I seem to recall that
> it was working fine.

I know the database is gone now, still for the record it would be nice
to know what the sequence of events where. You mentioned pg_dump and
upgrade from 9.1 -> 9.3.

So what was done and in what order?

Also what database where you trying to connect to, the one in 9.1 or 9.3?


>
>
> Moshe Jacobson
> Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
>
> "Quality is not an act, it is a habit." -- Aristotle


--
Adrian Klaver
adrian.klaver@gmail.com

On 01/04/2014 10:15 AM, Moshe Jacobson wrote:
> On Fri, Jan 3, 2014 at 8:37 PM, Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>> wrote:
>
>     I know the database is gone now, still for the record it would be
>     nice to know what the sequence of events where. You mentioned
>     pg_dump and upgrade from 9.1 -> 9.3.
>
>
>     So what was done and in what order?
>
>
> First the conversion to 9.3, then I was using pg_dump.

So we are looking at a fresh 9.3 cluster.

So you created an 9.3 instance and then used pg_dump to transfer the
data from the 9.1 instance to the 9.3 instance?

>
>     Also what database where you trying to connect to, the one in 9.1 or
>     9.3?
>
>
> 9.3. The db was fine on 9.1.

So from previous posts, if I follow correctly, the database(with tables)
was in the 9.3 cluster to begin with and then later you only had
database but with no tables in it. Or to put it another way, at some
point you could connect to the 'problem' database but could find no tables.

Is that correct?



>
>
>
>
> Moshe Jacobson
> Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
>
> "Quality is not an act, it is a habit." -- Aristotle


--
Adrian Klaver
adrian.klaver@gmail.com

On 01/04/2014 12:58 PM, Moshe Jacobson wrote:

Ccing the list.
>
> On Sat, Jan 4, 2014 at 2:08 PM, Adrian Klaver <adrian.klaver@gmail.com
> <mailto:adrian.klaver@gmail.com>> wrote:
>
>
>     So we are looking at a fresh 9.3 cluster.
>     So you created an 9.3 instance and then used pg_dump to transfer the
>     data from the 9.1 instance to the 9.3 instance?
>
>
> No. I used pg_upgrade on the 9.1 cluster. The pg_dump was unrelated to
> the upgrade.

So where does the pg_dump fit in?

Or more to the point why do you think it could be a possible cause of
the problem?


>
>     at some point you could connect to the 'problem' database but could
>     find no tables.
>
>
> Correct.


So anything of note happen in that interval?

Do the logs over that time period show anything?

>
>
> Moshe Jacobson
> Manager of Systems Engineering, Nead Werx Inc. <http://www.neadwerx.com>
> 2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339
>
> "Quality is not an act, it is a habit." -- Aristotle


--
Adrian Klaver
adrian.klaver@gmail.com