Re: Schema is Missing

> ----- Original Message -----
> From: "Michael Fuhr" <mike@fuhr.org>
> To: "Ron St-Pierre" <ronstp@mail.com>
> Subject: Re: [GENERAL] Schema is Missing
> Date: Fri, 10 Mar 2006 11:27:54 -0700
>
>
> On Fri, Mar 10, 2006 at 12:57:17PM -0500, Ron St-Pierre wrote:
> > We received the following error on our development server this
> > morning (postgresql 7.4.1, debian woody):
>
> That's pretty old.  If you must run 7.4 then at least consider
> upgrading to the latest minor release, currently 7.4.12.  Lots of
> bugs have been fixed since 7.4.1.
We will be upgrading our servers soon, most likely within a month or two, and are planning on upgrading the database at
thesame time. 

>
> >    org.postgresql.util.PSQLException: ERROR: schema "customer" does not exist
>
> When was the last time you know the schema existed?  Have you been
> doing database-wide vacuums?  What's the output of the following
> command?

Vacuums have not been run on this database for a while now :(  I know that the customer schema was there yesterday, I
addeddata to a few tables, logins to the web pages which it supports worked, etc. It also looks as if the backup from
midnight(last night) is okay. 

>
> SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database;
imperial=# SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database;
     datname      |    age    |    age
------------------+-----------+------------
 impimp           | 298777961 | 1372519784
 imp     TEST     | 332548272 |  332548272
 testdb           | 332548272 |  332548272
 imp              |   2228730 | 1075970551
 fsynchtest       | 332548272 |  332548272
 template1        | 332548272 |  332548272
 template0        | 332548272 |  332548272
 test1            | 332548272 |  332548272
(8 rows)


> > .psql_history doesn't display anything useful, just some queries
> > that I ran today and yesterday. I looked at /var/log/messages and
> > /var/log/syslog, and there aren't any zipped backups in the directory,
> > which makes me suspicious.
>
> When was the last time you saw those zipped files?  Do you know for
> sure that your system does that?
No, not 100% sure. However, the drive was almost full a few weeks ago, and I may have dropped them then, now that I
thinkabout it. 

>
> Who all has access to the server?  Could somebody have dropped the
> schema without your knowing about it?
Possible, but not very likely, I' have to say extremely unlikely.

>
> > The files contents are:
> [...]
> > Feb 22  9:58:13 ** All firewall rules applied **
> > Mar 10 06:25:52 imp syslogd 1.4.1#10: restart.
> > Mar 10 06:30:13 imp postgres[6330]: [9-1] ERROR:  schema
> > "customer" does not exist
>
> Is the gap between 22 Feb and 10 Mar expected?
No, I'm 99% certain it's not.

> What made syslogd
> restart?  Is that an unusual event for that time?  Any hardware
> problems?  Full disk?  Has anything else out of the ordinary happened
> on that system lately?
I don't know why syslogd restarted. The disk has about 13G of free space, and nothing unusual has occured lately that
we'venoticed (otherwise). I've been moving a lot of data into and out of the database over the last two weeks, probably
inexcess of 25G out and 10GB in, but in a different schema. 

Ron

>
> --
> Michael Fuhr
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

>


--
___________________________________________________
Play 100s of games for FREE! http://games.mail.com/