[GENERAL] "could not read block 0... " error followed by "database does not exist"
Hi. I am running Postgres 8.2.7 on a Linux system for over a year now with no problems. Today one of the database users reported the following error: psql: FATAL: could not read block 0 of relation 1664/0/1262: read only 0 of 8192 bytes I tried stopping and restarting the Postgres server for the database. >From the logfile: LOG: received smart shutdown request LOG: autovacuum launcher shutting down LOG: shutting down LOG: database system is shut down LOG: database system was shut down at 2010-02-12 17:15:37 PST LOG: autovacuum launcher started LOG: database system is ready to accept connections But when I try to connect to the database, I get the error message: FATAL: database "subptf" does not exist I tried stopping/restarting the database several times. I also killed all user connections to the database. How do I fix this problem? Thanks, Janet -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "could not read block 0... " error followed by "database does not exist"
Hi. What I see when I do ls on the current (corrupt) $PGDATA/global is ... - rw--- 1 jsjacobs deepsky 0 Feb 8 18:51 1262 ... -rw--- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth -rw--- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control -rw--- 1 jsjacobs deepsky 0 Feb 12 17:42 pg_database -rw--- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat I have a pgdump from a month ago. Are you saying to restore that to a different location and then copy over $PGDATA/global/1262? Do I also need to copy over $PGDATA/global/pg_database? Thanks, Janet Tom Lane wrote: > Janet S Jacobsen writes: >> Hi. I am running Postgres 8.2.7 on a Linux system for over >> a year now with no problems. > >> Today one of the database users reported the following error: > >>psql: FATAL: could not read block 0 of relation 1664/0/1262: read >>only 0 of 8192 bytes > > Ugh. 1262 is pg_database --- apparently something has truncated your > pg_database table to zero bytes :-(. Which certainly explains the > "no such database" errors. > > Have you got any chance of pulling that physical file from a backup? > The one bright spot here is that pg_database is pretty static in most > installations, so you could probably use even a not-very-current copy. > The file you want is $PGDATA/global/1262. > > I don't offhand know of any bugs in 8.2.7 that could cause this, > though that is rather an old version ... you might want to think > about an update to 8.2.something-recent. > > regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "could not read block 0... " error followed by "database does not exist"
Thanks, Tom. I will give this a try and let you know what happens. I don't see anything in the logfile prior to the first "could not read block 0..." error. Thanks, Janet Janet S Jacobsen writes: > > Hi. What I see when I do ls on the current (corrupt) > > $PGDATA/global is > > ... > > - rw--- 1 jsjacobs deepsky 0 Feb 8 18:51 1262 > > ... > > -rw--- 1 jsjacobs deepsky 602 Feb 12 17:42 pg_auth > > -rw--- 1 jsjacobs deepsky 8192 Feb 12 17:42 pg_control > > -rw--- 1 jsjacobs deepsky 0 Feb 12 17:42 pg_database > > -rw--- 1 jsjacobs deepsky 10927 Feb 12 21:57 pgstat.stat Looks about as I'd expect from your description. Something clobbered 1262, and then the "flat" file pg_database got updated from that. You might want to look around at what was happening Feb 8 18:51. > > I have a pgdump from a month ago. Are you saying to restore > > that to a different location and then copy over > > $PGDATA/global/1262? Do I also need to copy over > > $PGDATA/global/pg_database? Right on both. Of course, it'd be a good idea to first make a backup of what you have in $PGDATA now (all of it) --- you want to be able to get back to where you are if this makes things worse. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] unable to restore from a pgdump file
Hi. I am trying to restore a database from a pgdump file, something
that I've sucessfully done before.
I created a new database cluster, created the database that I want
to restore using
create database subptf with template = template0;
and then I tried to restore the database using
./psql subptf < subptf.pgdump
The first part of what I get is
ERROR: syntax error at or near "PGDMP"
LINE 1: PGDMP
^
GRANT
GRANT
ERROR: syntax error at or near ""
LINE 1: ptffield integer,
^
ERROR: syntax error at or near ""
LINE 1: f1 integer,
^
ERROR: syntax error at or near ""
LINE 1: AS $$
^
ERROR: syntax error at or near ""
LINE 1: AS $_X$
^
ERROR: syntax error at or near ""
LINE 1: AS $_X$
^
ERROR: syntax error at or near ""
LINE 1: AS $$
...
The last part of what I get is
...
vJ?N'g...@??q7?2g?0k??$??slz^p?>??k??|A?ʞ?A??f-j
\p: extra argument
"zr_?q«+_???c?ܦ?e??...@???ߒqy+?v?*?x?d}x?r?ǯ?67??e???y?/4o?f?u??ڇw?5???.??an...@??_???--kd??i?satb?akƳ?ҕz?k??&?{?zUt_???{
?Tt
???Au.?V9?rpZ?g?l??8???0³???3
ޒ}?-??Vӭf+,?mo?HN??+?
?R??z?:?x[bk˯?R_?8ed?ЀI???&=>%?2H??B"???" ignored
\p: extra argument ":P" ignored
\p: extra argument ";???P?+?1?i?)??a=?" ignored
unterminated quoted string
invalid command \??c?>
??Փ%??U???3+??U?SƳǕ???9?.???e+
invalid command
\W??_tUÁ+MK??z?ܦ?H??`o??-/s??n?s?3?sy?6{7??^grp?...@?l#??g7hw??j?ӣ?t30={??
invalid command \??g.???
invalid command \AJ??E?$?TC?r?f]?B??
invalid command \??Hzp?Y2??8?@
invalid command \Ā?(?%?O
\connect: FATAL: database "???6??$?^" does not exist
I feel like I'm making some beginner's error here, but I haven't
run into this before.
I am running 8.3.7 on a linux system
Thanks,
Janet
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] unable to restore from a pgdump file
Thanks, Steve. You are exactly right. pg_restore was the right command to use! Janet On Feb 13, 2010, at 7:36 PM, Janet S Jacobsen wrote: > > Hi. I am trying to restore a database from a pgdump file, something > > that I've sucessfully done before. > > > > I created a new database cluster, created the database that I want > > to restore using > > > > create database subptf with template = template0; > > > > and then I tried to restore the database using > > > > ./psql subptf < subptf.pgdump > > > > The first part of what I get is > > > > ERROR: syntax error at or near "PGDMP" I'd guess that you created the dump in custom format, rather than sql format. If so, you'd need to use pg_restore to restore it rather than psql. Cheers, Steve -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
