pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedgetting a error message when creating temporary table
4+ messages / 2 participants
[nested] [flat]
* getting a error message when creating temporary table
@ 2014-04-28 20:39 Evan Walter <ewalter@decisionanalyst.com>
0 siblings, 1 reply; 4+ messages in thread
From: Evan Walter @ 2014-04-28 20:39 UTC (permalink / raw)
To: pgsql-admin
Not sure what is causing this. When we execute:
CREATE TEMPORARY TABLE test(test_code varchar (255))
We get
ERROR: could not read block 6 in file "global/... " read only 0 of 8192
bytes
SQL state XX001
This just started today.
Thank you
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting a error message when creating temporary table
@ 2014-04-28 22:48 Tom Lane <tgl@sss.pgh.pa.us>
parent: Evan Walter <ewalter@decisionanalyst.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2014-04-28 22:48 UTC (permalink / raw)
To: Evan Walter <ewalter@decisionanalyst.com>; +Cc: pgsql-admin
Evan Walter <ewalter@decisionanalyst.com> writes:
> Not sure what is causing this. When we execute:
> CREATE TEMPORARY TABLE test(test_code varchar (255))
> We get
> ERROR: could not read block 6 in file "global/... " read only 0 of 8192
> bytes
What's the exact filename you omitted there, and which PG version is this?
I'm guessing this indicates corruption of pg_shdepend or one of its
indexes, as that seems like the only shared catalog that's really likely
to get touched during a CREATE TEMP TABLE. If you're lucky, it's one of
the indexes, and a REINDEX will fix it.
Have you had any OS-level crashes lately, power failures, etc? Whatever
is corrupted exactly, this shouldn't have happened if filesystem integrity
was being maintained properly.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting a error message when creating temporary table
@ 2014-04-28 23:38 Evan Walter <ewalter@decisionanalyst.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Evan Walter @ 2014-04-28 23:38 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
Thanks for the replies, sorry for not giving more details/background.
It is version 9.1.9
os is ubuntu on an Amazon ec2 server
The directory is global/26745
The server is a backend for python django applications. We started having
problems last Thursday. Queries were running a very long time. And there
were some application issues that were affecting the database. It is my
understanding that the application issues have been fixed. We were also on
version 9.1.1.
Anyway, on Friday we called an oustide specialist who broke replication.
rsync-ed the files to a new volume drive, upgraded the version of
Postgresql, and rebuilt it with the new version. Performance-wise queries
are running well now, but we are getting that error.
We have rebuilt some of the indexes. Some of the tables are quite large,
so we re-create the indexes concurrently and drop the original.
We will reindex some of the catalog indexes tonight and see if that helps.
Thank you
Evan
On Mon, Apr 28, 2014 at 5:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Evan Walter <ewalter@decisionanalyst.com> writes:
> > Not sure what is causing this. When we execute:
> > CREATE TEMPORARY TABLE test(test_code varchar (255))
>
> > We get
>
> > ERROR: could not read block 6 in file "global/... " read only 0 of 8192
> > bytes
>
> What's the exact filename you omitted there, and which PG version is this?
>
> I'm guessing this indicates corruption of pg_shdepend or one of its
> indexes, as that seems like the only shared catalog that's really likely
> to get touched during a CREATE TEMP TABLE. If you're lucky, it's one of
> the indexes, and a REINDEX will fix it.
>
> Have you had any OS-level crashes lately, power failures, etc? Whatever
> is corrupted exactly, this shouldn't have happened if filesystem integrity
> was being maintained properly.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: getting a error message when creating temporary table
@ 2014-04-29 02:45 Tom Lane <tgl@sss.pgh.pa.us>
parent: Evan Walter <ewalter@decisionanalyst.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2014-04-29 02:45 UTC (permalink / raw)
To: Evan Walter <ewalter@decisionanalyst.com>; +Cc: pgsql-admin
Evan Walter <ewalter@decisionanalyst.com> writes:
> Thanks for the replies, sorry for not giving more details/background.
> The directory is global/26745
OK, so try
select relname from pg_class where relisshared and pg_relation_filenode(oid) = 26745;
That should tell you which table or index is busted.
> It is version 9.1.9
> os is ubuntu on an Amazon ec2 server
Um. AWS is, AFAIK, pretty much guaranteed to lose your data eventually.
It's a great compute service if you can afford to have nodes fail now
and then ... but probably not the best substrate for a database. If
you have to use it for that, you *must* have good backup and replication
strategies in place.
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2014-04-29 02:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-04-28 20:39 getting a error message when creating temporary table Evan Walter <ewalter@decisionanalyst.com>
2014-04-28 22:48 ` Tom Lane <tgl@sss.pgh.pa.us>
2014-04-28 23:38 ` Evan Walter <ewalter@decisionanalyst.com>
2014-04-29 02:45 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox