Обсуждение: Postgres db corrupted ?
What's up here ? Google is deathly silent. bash-2.05a$ pg_dumpall > db.out connected to template1... dumping database "MyPgSQLTest"... dumping database "bb1"... dumping database "bbadmin"... pg_dump: [archiver (db)] connection to database "bbadmin" failed: FATAL 1: SetDatabaseEncoding(): invalid database encoding pg_dump failed on bbadmin, exiting Hmm. Curiously, the database I'm playing with; bbadmin | | SQL_ASCII Says it is SQL_ASCII...but it doesn't have an owner. Would this mean the database is corrupt ? This is on 7.2.3 - I had been running 7.1.3 and upgraded with the assistance pg_dumpall. Is this an artifact of the upgrade ? John
Вложения
"John P. Looney" <valen@tuatha.org> writes:
> What's up here ? Google is deathly silent.=20
> bash-2.05a$ pg_dumpall > db.out
> connected to template1...
> dumping database "MyPgSQLTest"...
> dumping database "bb1"...
> dumping database "bbadmin"...
> pg_dump: [archiver (db)] connection to database "bbadmin" failed: FATAL
> 1: SetDatabaseEncoding(): invalid database encoding
> pg_dump failed on bbadmin, exiting
A quick-and-dirty nostrum that sometimes helps for this sort of thing
is "vacuum full pg_database". (The idea is to get rid of dead tuples
that a new backend might be accidentally picking instead of the live
ones --- it has to read pg_database before it's joined the PROC array,
which among other things means it can't tell 100% accurately which rows
are really committed.)
If no go, could we see the output of "select * from pg_database"?
regards, tom lane
On Tue, Jul 29, 2003 at 10:38:50AM -0400, Tom Lane mentioned:
> A quick-and-dirty nostrum that sometimes helps for this sort of thing
> is "vacuum full pg_database". (The idea is to get rid of dead tuples
> that a new backend might be accidentally picking instead of the live
> ones --- it has to read pg_database before it's joined the PROC array,
> which among other things means it can't tell 100% accurately which rows
> are really committed.)
Ah, OK. That's the sort of help I was hoping to get. Alas, for very
different reasons (I'd run out of ideas), I'd run that myself, with no
gain.
> If no go, could we see the output of "select * from pg_database"?
Yep, no worries...
postgres=# select * from pg_database;
datname | datdba | encoding | datistemplate | datallowconn | datlastsysoid | datvacuumxid | datfrozenxid |
datpath
-----------------+--------+----------+---------------+--------------+---------------+--------------+--------------+---------
pguser | | 0 | f | t | 16554 | 611746 | 3221837219 |
phpuser-654937 | | 0 | f | t | 16554 | 611746 | 3221837219 |
gestr-654937 | | 0 | f | t | 16554 | 611746 | 3221837219 |
template0 | 1 | 0 | t | f | 16554 | 49 | 49 |
postgres | 1 | 0 | f | t | 16554 | 658793 | 658793 |
rob-654937 | | 0 | f | t | 16554 | 611746 | 3221837219 |
template1 | 1 | 0 | t | t | 16554 | 611746 | 3221837219 |
testtest | 28 | 0 | f | t | 16554 | 49 | 49 |
dsitestogo | 33 | 0 | f | t | 16554 | 49 | 49 |
bb1 | 36 | 0 | f | t | 16554 | 49 | 49 |
carlstest | 37 | 0 | f | t | 16554 | 49 | 49 |
pgdbase | 38 | 0 | f | t | 16554 | 49 | 49 |
novadance | 41 | 0 | f | t | 16554 | 49 | 49 |
MyPgSQLTest | 43 | 0 | f | t | 16554 | 49 | 49 |
forum | 45 | 0 | f | t | 16554 | 49 | 49 |
ct2 | 48 | 0 | f | t | 16554 | 49 | 49 |
directory | 49 | 0 | f | t | 16554 | 49 | 49 |
faqsection | 50 | 0 | f | t | 16554 | 49 | 49 |
demo | 51 | 0 | f | t | 16554 | 49 | 49 |
planetepochdb | 53 | 0 | f | t | 16554 | 49 | 49 |
bbadmin | | 0 | f | t | 16554 | 49 | 49 |
shanecody | | 0 | f | t | 16554 | 49 | 49 |
dmduffy-654937 | | 0 | f | t | 16554 | 49 | 49 |
zozallen-665478 | | 0 | f | t | 16554 | 49 | 49 |
zozhugh-665478 | | 0 | f | t | 16554 | 49 | 49 |
zozea | 41 | 0 | f | t | 16554 | 49 | 49 |
(26 rows)
John
"John P. Looney" <valen@tuatha.org> writes:
>> If no go, could we see the output of "select * from pg_database"?
> Yep, no worries...
> postgres=# select * from pg_database;
> datname | datdba | encoding | datistemplate | datallowconn | datlastsysoid | datvacuumxid | datfrozenxid |
datpath
>
-----------------+--------+----------+---------------+--------------+---------------+--------------+--------------+---------
> bbadmin | | 0 | f | t | 16554 | 49 | 49 |
Hm, that datdba field actually is NULL, isn't it? That's what you need
to fix. The low-level code isn't expecting it to be NULL, and falls
over in surprising ways. (Recent releases try to enforce NOT NULL on
system catalog columns that mustn't be NULL, but I'm not sure how
bulletproof that really is.)
I'd try
UPDATE pg_database SET datdba = <something reasonable> WHERE datdba IS NULL;
You could just set them all to 1 (the postgres userid); if you want
these databases owned by specific users then see pg_shadow.usesysid for
the numbers to insert.
Once you have all the entries fixed, do the "VACUUM FULL pg_database"
fandango for good luck, and you should be back in business.
regards, tom lane
On Tue, 2003-07-29 at 17:55, Tom Lane wrote: > Hm, that datdba field actually is NULL, isn't it? That's what you need > to fix. The low-level code isn't expecting it to be NULL, and falls > over in surprising ways. (Recent releases try to enforce NOT NULL on > system catalog columns that mustn't be NULL, but I'm not sure how > bulletproof that really is.) Ah, OK. That makes sense. > I'd try > > UPDATE pg_database SET datdba = <something reasonable> WHERE datdba IS NULL; > > You could just set them all to 1 (the postgres userid); if you want > these databases owned by specific users then see pg_shadow.usesysid for > the numbers to insert. OK; is there an already existing table that should have those entries in it ? As a postgres novice, does that mean that these broken tables aren't accessible ? John
Вложения
"John P. Looney" <valen@tuatha.org> writes:
> OK; is there an already existing table that should have those entries
> in it ? As a postgres novice, does that mean that these broken tables
> aren't accessible ?
pg_database *is* the table that should have those entries in it. I'm
not real sure how you got into this state, but given that you are in it,
the "SetDatabaseEncoding" failure will probably occur when you try to
connect to any of the databases with the bad pg_database rows.
Fortunately, since that table is shared across databases, you can fix it
from a connection to any of the non-broken databases (like template1).
regards, tom lane
I am compiling 7.3.4 with openssl turned on. I ran into the same
crypt.h errors. I commented out the crypt.h reference as Tom Lane
suggested and then ran in to another error below.
I am using cc: WorkShop Compilers 5.0 98/12/15 C 5.0
on SunOS 5.7 Generic_106541-25 sun4u sparc SUNW,Ultra-1
I am using OpenSSL 0.9.7b 10 Apr 2003
cc -Xa -mt -xO5 -I../../../src/interfaces/libpq -I../../../src/include
-I/usr/local/include -I/usr/local/s
sl/include -c -o tab-complete.o tab-complete.c
"tab-complete.c", line 108: warning: assignment type mismatch:
pointer to function() returning pointer to pointer to char "="
pointer to void
"tab-complete.c", line 313: undefined symbol: rl_compentry_func_t
"tab-complete.c", line 313: syntax error before or at: )
"tab-complete.c", line 317: undefined symbol: rl_compentry_func_t
"tab-complete.c", line 317: syntax error before or at: )
"tab-complete.c", line 322: undefined symbol: rl_compentry_func_t
"tab-complete.c", line 322: syntax error before or at: )
"tab-complete.c", line 330: syntax error before or at: )
"tab-complete.c", line 341: syntax error before or at: )
"tab-complete.c", line 346: syntax error before or at: )
"tab-complete.c", line 353: syntax error before or at: )
"tab-complete.c", line 358: syntax error before or at: )
"tab-complete.c", line 363: syntax error before or at: )
"tab-complete.c", line 368: syntax error before or at: )
"tab-complete.c", line 371: syntax error before or at: )
"tab-complete.c", line 376: syntax error before or at: )
"tab-complete.c", line 379: syntax error before or at: )
"tab-complete.c", line 395: syntax error before or at: )
"tab-complete.c", line 400: syntax error before or at: )
"tab-complete.c", line 408: syntax error before or at: )
"tab-complete.c", line 411: syntax error before or at: )
"tab-complete.c", line 422: syntax error before or at: )
"tab-complete.c", line 430: syntax error before or at: )
"tab-complete.c", line 436: syntax error before or at: )
"tab-complete.c", line 440: syntax error before or at: )
"tab-complete.c", line 443: syntax error before or at: )
"tab-complete.c", line 450: syntax error before or at: )
"tab-complete.c", line 453: syntax error before or at: )
"tab-complete.c", line 459: syntax error before or at: )
"tab-complete.c", line 465: syntax error before or at: )
"tab-complete.c", line 470: syntax error before or at: )
"tab-complete.c", line 478: syntax error before or at: )
"tab-complete.c", line 485: syntax error before or at: )
"tab-complete.c", line 490: syntax error before or at: )
"tab-complete.c", line 495: syntax error before or at: )
"tab-complete.c", line 503: syntax error before or at: )
"tab-complete.c", line 508: syntax error before or at: )
"tab-complete.c", line 521: syntax error before or at: )
"tab-complete.c", line 524: syntax error before or at: )
"tab-complete.c", line 527: syntax error before or at: )
"tab-complete.c", line 537: syntax error before or at: )
"tab-complete.c", line 545: syntax error before or at: )
"tab-complete.c", line 552: syntax error before or at: )
"tab-complete.c", line 563: syntax error before or at: )
"tab-complete.c", line 572: syntax error before or at: )
"tab-complete.c", line 576: syntax error before or at: )
"tab-complete.c", line 588: syntax error before or at: )
"tab-complete.c", line 591: syntax error before or at: )
"tab-complete.c", line 594: syntax error before or at: )
"tab-complete.c", line 604: syntax error before or at: )
"tab-complete.c", line 607: syntax error before or at: )
"tab-complete.c", line 610: syntax error before or at: )
"tab-complete.c", line 620: syntax error before or at: )
"tab-complete.c", line 628: syntax error before or at: )
"tab-complete.c", line 633: syntax error before or at: )
"tab-complete.c", line 639: syntax error before or at: )
"tab-complete.c", line 646: syntax error before or at: )
"tab-complete.c", line 657: syntax error before or at: )
"tab-complete.c", line 662: syntax error before or at: )
"tab-complete.c", line 669: syntax error before or at: )
"tab-complete.c", line 674: syntax error before or at: )
"tab-complete.c", line 676: syntax error before or at: )
"tab-complete.c", line 678: syntax error before or at: )
"tab-complete.c", line 689: syntax error before or at: )
"tab-complete.c", line 692: syntax error before or at: )
"tab-complete.c", line 696: syntax error before or at: )
"tab-complete.c", line 704: syntax error before or at: )
"tab-complete.c", line 710: syntax error before or at: )
"tab-complete.c", line 716: syntax error before or at: )
"tab-complete.c", line 725: syntax error before or at: )
"tab-complete.c", line 731: syntax error before or at: )
"tab-complete.c", line 735: syntax error before or at: )
"tab-complete.c", line 744: syntax error before or at: )
"tab-complete.c", line 750: syntax error before or at: )
"tab-complete.c", line 756: syntax error before or at: )
"tab-complete.c", line 762: syntax error before or at: )
"tab-complete.c", line 766: syntax error before or at: )
"tab-complete.c", line 771: syntax error before or at: )
"tab-complete.c", line 774: syntax error before or at: )
"tab-complete.c", line 782: syntax error before or at: )
"tab-complete.c", line 786: syntax error before or at: )
"tab-complete.c", line 788: syntax error before or at: )
"tab-complete.c", line 793: syntax error before or at: )
"tab-complete.c", line 797: syntax error before or at: )
"tab-complete.c", line 802: syntax error before or at: )
"tab-complete.c", line 804: syntax error before or at: )
"tab-complete.c", line 806: syntax error before or at: )
"tab-complete.c", line 813: syntax error before or at: )
"tab-complete.c", line 823: syntax error before or at: )
"tab-complete.c", line 838: syntax error before or at: )
"tab-complete.c", line 852: syntax error before or at: )
"tab-complete.c", line 867: cannot recover from previous errors
cc: acomp failed for tab-complete.c
gmake[3]: *** [tab-complete.o] Error 2
gmake[3]: Leaving directory `/opt/sft/postgresql-7.3.4/src/bin/psql'
gmake[2]: *** [all] Error 2
gmake[2]: Leaving directory `/opt/sft/postgresql-7.3.4/src/bin'
gmake[1]: *** [all] Error 2
gmake[1]: Leaving directory `/opt/sft/postgresql-7.3.4/src'
gmake: *** [all] Error 2
Renney Thomas <renneyt@yahoo.com> writes:
> "tab-complete.c", line 108: warning: assignment type mismatch:
> pointer to function() returning pointer to pointer to char "="
> pointer to void
> "tab-complete.c", line 313: undefined symbol: rl_compentry_func_t
> "tab-complete.c", line 313: syntax error before or at: )
> "tab-complete.c", line 317: undefined symbol: rl_compentry_func_t
> "tab-complete.c", line 317: syntax error before or at: )
> "tab-complete.c", line 322: undefined symbol: rl_compentry_func_t
These aren't ssl problems, they're readline problems. You may have
a version of libreadline we've never heard of, but a more likely bet
is that you have a libreadline library file that's out of sync with
the readline header files you have. The above looks like configure
made the wrong guesses about how to set up our readline support, and
that's usually a versioning problem ...
regards, tom lane
""John P. Looney"" <valen@tuatha.org> writes: > bbadmin | | SQL_ASCII For sure a way to obtain this inconsistence is delete the owner of a db and voila' a DB result to have no owner. May be is a good idea that the user postgres adopt these orfan DB. Regards Gaetano Mendola
I reinstalled readline 4.3 and everything compiled well. I have a new
problem.
I can get psql to connect with ssl on.
--
share/pg_hba.conf
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all
all
trust
hostssl all all 127.0.0.1
255.255.255.255 trust
--
share/postgresql.conf
#
# Connection Parameters
#
tcpip_socket = true
ssl = true
--
> psql -h localhost template1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=#
Tom Lane wrote:
>Renney Thomas <renneyt@yahoo.com> writes:
>
>
>>"tab-complete.c", line 108: warning: assignment type mismatch:
>> pointer to function() returning pointer to pointer to char "="
>>pointer to void
>>"tab-complete.c", line 313: undefined symbol: rl_compentry_func_t
>>"tab-complete.c", line 313: syntax error before or at: )
>>"tab-complete.c", line 317: undefined symbol: rl_compentry_func_t
>>"tab-complete.c", line 317: syntax error before or at: )
>>"tab-complete.c", line 322: undefined symbol: rl_compentry_func_t
>>
>>
>
>These aren't ssl problems, they're readline problems. You may have
>a version of libreadline we've never heard of, but a more likely bet
>is that you have a libreadline library file that's out of sync with
>the readline header files you have. The above looks like configure
>made the wrong guesses about how to set up our readline support, and
>that's usually a versioning problem ...
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
Correction:
I reinstalled readline 4.3 and everything compiled well. I have a new
problem.
I _can't_ get psql to connect with ssl on.
confiugre : configure --with-CXX -with-odbcinst=/usr/local/etc
--with-includes=/usr/local/include --
with-unixodbc --with-perl --with-openssl
--
share/pg_hba.conf
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
local all
all trust
hostssl all all 127.0.0.1 255.255.255.255 trust
--
share/postgresql.conf
#
# Connection Parameters
#
tcpip_socket = true
ssl = true
--
> psql -h localhost template1
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=#
>
>
> Tom Lane wrote:
>
>> Renney Thomas <renneyt@yahoo.com> writes:
>>
>>
>>> "tab-complete.c", line 108: warning: assignment type mismatch:
>>> pointer to function() returning pointer to pointer to char
>>> "=" pointer to void
>>> "tab-complete.c", line 313: undefined symbol: rl_compentry_func_t
>>> "tab-complete.c", line 313: syntax error before or at: )
>>> "tab-complete.c", line 317: undefined symbol: rl_compentry_func_t
>>> "tab-complete.c", line 317: syntax error before or at: )
>>> "tab-complete.c", line 322: undefined symbol: rl_compentry_func_t
>>>
>>
>>
>> These aren't ssl problems, they're readline problems. You may have
>> a version of libreadline we've never heard of, but a more likely bet
>> is that you have a libreadline library file that's out of sync with
>> the readline header files you have. The above looks like configure
>> made the wrong guesses about how to set up our readline support, and
>> that's usually a versioning problem ...
>>
>> regards, tom lane
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faqs/FAQ.html
>>
>>
>>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
"Mendola Gaetano" <mendola@bigfoot.com> writes:
> ""John P. Looney"" <valen@tuatha.org> writes:
>> bbadmin | | SQL_ASCII
> For sure a way to obtain this inconsistence is delete the owner of
> a db and voila' a DB result to have no owner.
Nope. I don't know how John got into this state, but it most definitely
was not that way. Try it.
regards, tom lane
Renney Thomas <renneyt@yahoo.com> writes:
> I _can't_ get psql to connect with ssl on.
You're not showing us any evidence to support that statement ...
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us>
> "Mendola Gaetano" <mendola@bigfoot.com> writes:
> > ""John P. Looney"" <valen@tuatha.org> writes:
> >> bbadmin | | SQL_ASCII
>
> > For sure a way to obtain this inconsistence is delete the owner of
> > a db and voila' a DB result to have no owner.
>
> Nope. I don't know how John got into this state, but it most definitely
> was not that way. Try it.
Here it his:
kalman@XXX [] > createuser jfk
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) y
CREATE USER
kalman@XXX [] > psql -U jfk template1
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# create database db_jfk;
CREATE DATABASE
template1=# \q
kalman@XXX [] > psql -l | grep jfk
db_jfk | jfk | SQL_ASCII
kalman@XXX [] > psql template1
Welcome to psql 7.3.3, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
template1=# delete from pg_shadow where usename = 'jfk';
DELETE 1
template1=# \q
kalman@XXX [] > psql -l | grep jfk
db_jfk | | SQL_ASCII
Regards
Gaetano
"Mendola Gaetano" <mendola@bigfoot.com> writes:
> "Tom Lane" <tgl@sss.pgh.pa.us>
>> Nope. I don't know how John got into this state, but it most definitely
>> was not that way. Try it.
> Here it his:
No, you're looking at the result of psql -l. What John was showing us
was the actual contents of pg_database, which would still contain a
numeric datdba value after your example.
regards, tom lane
"Tom Lane" <tgl@sss.pgh.pa.us> wrote: > "Mendola Gaetano" <mendola@bigfoot.com> writes: > > "Tom Lane" <tgl@sss.pgh.pa.us> > >> Nope. I don't know how John got into this state, but it most definitely > >> was not that way. Try it. > > > Here it his: > > No, you're looking at the result of psql -l. What John was showing us > was the actual contents of pg_database, which would still contain a > numeric datdba value after your example. > > regards, tom lane You're right ( like usual :-) ) but you agree anyway that should not possible delete a user that own DB object or at least change the owner to postgres? Regards Gaetano Mendola
"Mendola Gaetano" <mendola@bigfoot.com> writes:
> but you agree anyway that should not possible delete
> a user that own DB object or at least change the owner
> to postgres?
If you do it the approved way, with DROP USER, you'll find there's
already an interlock:
regression=# create user foo;
CREATE USER
regression=# create database foo owner foo;
CREATE DATABASE
regression=# drop user foo;
ERROR: user "foo" cannot be dropped
DETAIL: The user owns database "foo".
regression=#
When you issue direct UPDATE/DELETEs on the system catalogs, consistency
is your responsibility.
regards, tom lane
Did I forget a step in setting up SSL? Is their a way to find out if SSL is in use on a session or that it was compiled properly into the back-end and client? Tom Lane wrote: >Renney Thomas <renneyt@yahoo.com> writes: > > >>I _can't_ get psql to connect with ssl on. >> >> > >You're not showing us any evidence to support that statement ... > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > >
On Wed, 2003-07-30 at 15:26, Tom Lane wrote: > "Mendola Gaetano" <mendola@bigfoot.com> writes: > > ""John P. Looney"" <valen@tuatha.org> writes: > >> bbadmin | | SQL_ASCII > > > For sure a way to obtain this inconsistence is delete the owner of > > a db and voila' a DB result to have no owner. > > Nope. I don't know how John got into this state, but it most definitely > was not that way. Try it. OK, it turns out that the cause was the shared hosting admin software that was trying to add users and databases had some wierd problems (hidden obscure shell scripts crashing out when it couldn't find an up to date text dump of the pg_shadow file), which resulted in tables being created, given specific sysids for users that weren't created. Anyway, it looks like it wasn't a postgres bug that caused the problems. If any field should be considered "not null" though, it's that owner field! John