Cannot start Postgres- FATAL: invalid cache id: 19

On Wed, Dec 1, 2010 at 9:20 AM, Jolles, Peter M (GE Energy)
<peter.jolles@ge.com> wrote:

2010-11-29 12:39:17 EST HINT:  Stop the postmaster and use a standalone
backend to vacuum that database.

so did you do what the HINT told you to do? does it still not startup?

I had multiple auto-vacuum instances running, but it looks like they
were unable to keep up.

are there any log lines complaining that autovacuum terminated any
jobs? anything from autovacuum?

Vick Khera <vivek@khera.org> writes:

On Wed, Dec 1, 2010 at 9:20 AM, Jolles, Peter M (GE Energy)
<peter.jolles@ge.com> wrote:

2010-11-29 12:39:17 EST HINT: �Stop the postmaster and use a standalone
backend to vacuum that database.

so did you do what the HINT told you to do? does it still not startup?

This is the second report we've seen of relatively new 9.0 installations
shutting down due to wraparound (the other was bug #5718). I find this
disturbing. If we're lucky, the cause is already fixed:
http://git.postgresql.org/gitweb?p=postgresql.git;a=commitdiff;h=b58c25055ef6d7097618c680f6768689a110d529
but if that's the only contributing factor then why weren't we seeing
similar reports about 8.3 and 8.4? I'm worried that there is some
other bug in 9.0 autovacuum. Has anyone stress-tested 9.0 to see if
autovacuum manages to keep the wraparound threshold moving forward?

regards, tom lane

On Wednesday, December 01, 2010 9:58 AM Vick Khera wrote:

On Wed, Dec 1, 2010 at 9:20 AM, Jolles, Peter M (GE Energy) wrote:

2010-11-29 12:39:17 EST HINT:  Stop the postmaster and use a
standalone backend to vacuum that database.

so did you do what the HINT told you to do? does it still not startup?

When I realized what was happening I stopped the server and attempted to restart it after making a backup. I was under the impression the database had to be able to start up in order to vacuum it. If there is a way to do it with the database not running, I don't know how to do that.

I had multiple auto-vacuum instances running, but it looks like they
were unable to keep up.

are there any log lines complaining that autovacuum terminated any jobs?
anything from autovacuum?

I don't see anything in the log that the autovacuum specifically terminated any jobs or gave any error. Approximately 4 hours before the database started refusing transactions the following was present, but I wasn't able to shut everything down in time:

EST WARNING: database "fps_data" must be vacuumed within 11000000 transactions

I know now that I'm going to have to slow down the transaction rate in order to let the autovacuum keep up, but at this point I am just trying to salvage the data present in the database.

Thanks,
Peter Jolles

On Wed, Dec 1, 2010 at 11:23 AM, Jolles, Peter M (GE Energy)
<peter.jolles@ge.com> wrote:

EST WARNING:  database "fps_data" must be vacuumed within 11000000 transactions

I know now that I'm going to have to slow down the transaction rate in order to let the autovacuum keep up, but at this point I am just trying to salvage the data present in the database.

Just how big is the table and how many transactions per second do you
push through it? Perhaps your I/O bandwidth is just insufficient for
your load level.

On Wednesday, December 01, 2010 1:27 PM Vick Khera wrote:

Just how big is the table and how many transactions per second do you

push

through it? Perhaps your I/O bandwidth is just insufficient for your

load

level.

The entire database is about 700 GB. In the database I've got
approximately 1200 tables, most in the 300-500 mb range (254 columns,
500k to 1000k rows). When a table is being accessed it has approximately
5-7 transactions/sec. Usually 10-12 tables are being accessed at a time.
Each table is hit 2-3 times a day for about 10 minutes each time.

I know I/O is an issue, but I didn't expect the database to fail, and
then be what I presume to be unrecoverable. Is there a way to recover
data if I can't get Postgres to start up? Is there a way to vacuum the
table if Postgres won't start up in hopes to get it to restart? I had
the database replicated, but the copy won't start up for the same
reason.

the original hint said to "use a standalone backend".

If you go to http://www.postgresql.org/docs/9.0/interactive/routine-vacuuming.html
section 23.1.4 it will describe exactly what just happened to you and
how to recover.

On Wed, Dec 1, 2010 at 1:58 PM, Jolles, Peter M (GE Energy)
<peter.jolles@ge.com> wrote:

On Wednesday, December 01, 2010 1:59 PM Vick Khera wrote:

the original hint said to "use a standalone backend".

If you go to http://www.postgresql.org/docs/9.0/interactive/routine-
vacuuming.html
section 23.1.4 it will describe exactly what just happened to you and

how to

recover.

I apologize for the seeming dumb questions, but I have tried to start
the database using the following command:

postgres --single -D "d:/database" mydatabase

That gets me a new line in my command prompt window and nothing else.
Postgres 8.4 at least throws a warning if I am not logged in as an under
privileged user, but 9.0 gives nothing, no log file entries or other
error message that indicates it is trying and why it is failing. Using
the -r command didn't produce a log file.

Trying to run the vacuumdb.exe command fails because the database isn't
running. Using pg_ctl I can get a response, but it gives the same
startup error as originally mentioned. I must be missing something, but
I don't see what it is.

On Wed, Dec 1, 2010 at 4:15 PM, Jolles, Peter M (GE Energy)
<peter.jolles@ge.com> wrote:

I apologize for the seeming dumb questions, but I have tried to start
the database using the following command:

postgres --single -D "d:/database" mydatabase

That gets me a new line in my command prompt window and nothing else.
Postgres 8.4 at least throws a warning if I am not logged in as an under

Does the user you are logged in under have write permission to the
d:/database directory? What you should get is a postgres command line
prompt.

"Jolles, Peter M (GE Energy)" <peter.jolles@ge.com> writes:

I apologize for the seeming dumb questions, but I have tried to start
the database using the following command:

postgres --single -D "d:/database" mydatabase

That gets me a new line in my command prompt window and nothing else.

Hmm ... try it with "-d 1" added, or maybe higher if that doesn't show
anything.

regards, tom lane

On Wednesday 01 December 2010 1:15:45 pm Jolles, Peter M (GE Energy) wrote:

On Wednesday, December 01, 2010 1:59 PM Vick Khera wrote:

the original hint said to "use a standalone backend".

If you go to http://www.postgresql.org/docs/9.0/interactive/routine-
vacuuming.html
section 23.1.4 it will describe exactly what just happened to you and

how to

recover.

I apologize for the seeming dumb questions, but I have tried to start
the database using the following command:

postgres --single -D "d:/database" mydatabase

This is on Windows right, maybe:

postgres --single -D d:\database mydatabase
or
postgres --single -D "d:\database" mydatabase

That gets me a new line in my command prompt window and nothing else.
Postgres 8.4 at least throws a warning if I am not logged in as an under
privileged user, but 9.0 gives nothing, no log file entries or other
error message that indicates it is trying and why it is failing. Using
the -r command didn't produce a log file.

Trying to run the vacuumdb.exe command fails because the database isn't
running. Using pg_ctl I can get a response, but it gives the same
startup error as originally mentioned. I must be missing something, but
I don't see what it is.

Vacuum is also an SQL command so you can run it from the command prompt once you
get to it:

PostgreSQL stand-alone backend 9.0beta2
backend> vacuum
backend>

--
Adrian Klaver
adrian.klaver@gmail.com