|
Home > Archive > PostgreSQL Discussion > January 2006 > Missing database entry in pg_database
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Missing database entry in pg_database
|
|
| Robert Korteweg 2006-01-26, 4:56 pm |
| Hi,
I have a problem with a database i'm maintaining. I first noticed the
problem because i could not make a backup of the database i got the
following error:
pg_dump: missing pg_database entry for database "xxx"
I verified this by selecting the pg_database. It was indeed gone.
I did some more diggin and noticed that on doing a describe (\d <table> )
of a table i could not see any or some of the columns in the table, and
a few tables i also could just see the correct layout. It looks random.
The database is a very active database. It is running on Postgresql 7.3.
The database is getting a VACUUM FULL ANALYZE every night. No updates
where made on the machine and the error did not accour after a human
action as far as i can see.
Can someone tell me what happened here?
Can i fix my system table?
Can i still trust the other databases on the system?
--
"You can't reach second base, and keep your foot on first."
Groeten,
Robert
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
| |
| Tom Lane 2006-01-26, 4:56 pm |
| Robert Korteweg <robert@sambalbij.nl> writes:
> I have a problem with a database i'm maintaining. I first noticed the
> problem because i could not make a backup of the database i got the
> following error:
> pg_dump: missing pg_database entry for database "xxx"
> I verified this by selecting the pg_database. It was indeed gone.
> I did some more diggin and noticed that on doing a describe (\d <table> )
> of a table i could not see any or some of the columns in the table, and
> a few tables i also could just see the correct layout. It looks random.
This sounds suspiciously like a transaction ID wraparound problem.
> The database is a very active database. It is running on Postgresql 7.3.
> The database is getting a VACUUM FULL ANALYZE every night.
The *entire* database ... or are you just vacuuming the user tables
and not the system catalogs? Daily vacuuming of the catalogs should
have prevented any such problem (unless you are managing to exceed
1 billion transactions per day...)
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
| |
| Robert Korteweg 2006-01-26, 4:57 pm |
| Robert Korteweg <robert ( at ) sambalbij ( dot ) nl> writes:
[color=darkred]
[color=darkred]
[color=darkred]
[color=darkred]
> This sounds suspiciously like a transaction ID wraparound problem.
Yes i read about that, but i thought this was not my problem because i
vacuumed like i thought i should.
[color=darkred]
> The *entire* database ... or are you just vacuuming the user tables
> and not the system catalogs? Daily vacuuming of the catalogs should
> have prevented any such problem (unless you are managing to exceed 1
> billion transactions per day...)
VACUUM FULL ANALYZE is the exact query i do on the database every night.
I do not know if postgres will vacuum the systemtables as well with this
command. And i do not believe the database will exceed the billion
transactions a day.
I also do not see the template databases, but i do not know if this is
important.
--
"You can't reach second base, and keep your foot on first."
Groeten,
Robert
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
| |
| Robert Korteweg 2006-01-27, 3:23 am |
| Robert Korteweg wrote:
> Robert Korteweg <robert ( at ) sambalbij ( dot ) nl> writes:
>
>
>
>
>
>
>
>
>
>
> Yes i read about that, but i thought this was not my problem because i
> vacuumed like i thought i should.
>
>
>
>
>
> VACUUM FULL ANALYZE is the exact query i do on the database every night.
> I do not know if postgres will vacuum the systemtables as well with this
> command. And i do not believe the database will exceed the billion
> transactions a day.
>
> I also do not see the template databases, but i do not know if this is
> important.
Any thought? i'm about too decide to do an initdb on this database, but
would prever too fix the problem.
--
"You can't reach second base, and keep your foot on first."
Groeten,
Robert
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
|
|
|
|
|