Re: Schema is Missing

> ----- Original Message -----
> From: "Michael Fuhr" <mike(at)fuhr(dot)org>
> To: "Ron St-Pierre" <ronstp(at)mail(dot)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 the same 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 added data 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 think about 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've noticed (otherwise). I've been moving a lot of data into and out of the database over the last two weeks, probably in excess 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/