Обсуждение: Schema is Missing
We received the following error on our development server this morning (postgresql 7.4.1, debian woody): org.postgresql.util.PSQLException: ERROR: schema "customer" does not exist When I login to postgres it looks as if the other schemas are okay, but the customer schema is gone. I have a backup frommidnight last night which I can restore, but I want to find out the cause of the problem first. .psql_history doesn't display anything useful, just some queries that I ran today and yesterday. I looked at /var/log/messagesand /var/log/syslog, and there aren't any zipped backups in the directory, which makes me suspicious. Thefiles contents are: root@imperial:/var/log# head messages Feb 16 10:21:43 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 16 10:21:45 ** All firewall rules applied ** Feb 17 10:23:20 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 17 10:23:21 ** All firewall rules applied ** Feb 19 9:59:15 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 19 9:59:17 ** All firewall rules applied ** Feb 22 9:58:10 ** Starting Arno's IPTABLES firewall v1.8.2 ** Feb 22 9:58:13 ** All firewall rules applied ** Mar 10 06:25:52 imperial syslogd 1.4.1#10: restart. Mar 10 06:30:13 imperial postgres[6330]: [9-1] ERROR: schema "customer" does not exist and root@imperial:/var/log# head syslog Mar 10 06:25:52 imperial syslogd 1.4.1#10: restart. Mar 10 06:30:13 imperial postgres[6330]: [9-1] ERROR: schema "customer" does not exist Mar 10 06:36:03 imperial postgres[9058]: [9-1] ERROR: schema "customer" does not exist Where should I look to see if data corruption was the problem? I am holding off restoring the customer schema for now. Thanks Ron St.Pierre -- ___________________________________________________ Play 100s of games for FREE! http://games.mail.com/
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. > 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? SELECT datname, age(datvacuumxid), age(datfrozenxid) FROM pg_database; > .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? Who all has access to the server? Could somebody have dropped the schema without your knowing about it? > The files contents are: [...] > Feb 22 9:58:13 ** All firewall rules applied ** > Mar 10 06:25:52 imperial syslogd 1.4.1#10: restart. > Mar 10 06:30:13 imperial postgres[6330]: [9-1] ERROR: schema "customer" does not exist Is the gap between 22 Feb and 10 Mar expected? 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? -- Michael Fuhr
> ----- 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/