Thread: Cannot start Postgres- FATAL: invalid cache id: 19
I am having problems starting up Postgres and am getting the following error: 2010-12-01 08:36:42 EST LOG: database system was shut down at 2010-11-29 17:38:17 EST 2010-12-01 08:36:42 EST FATAL: invalid cache id: 19 2010-12-01 08:36:43 EST FATAL: the database system is starting up 2010-12-01 08:36:43 EST LOG: startup process (PID 640) exited with exit code 1 2010-12-01 08:36:43 EST LOG: aborting startup due to startup process failure Prior to shutting down the database, the log file reported the following message: 2010-11-29 12:39:17 EST ERROR: database is not accepting commands to avoid wraparound data loss in database "fps_data" 2010-11-29 12:39:17 EST HINT: Stop the postmaster and use a standalone backend to vacuum that database. I had multiple auto-vacuum instances running, but it looks like they were unable to keep up. I found some reference to bug # 5718 (http://archives.postgresql.org/pgsql-bugs/2010-10/msg00185.php) that lists a patch, but I am unfamiliar with how to apply the referenced patch, or if it will help. I am running Postgres 9.0.0-1 on Windows XP courtesy of Enterprise DB. Hardware is 64bit, but I am running the 32bit version. Is this patch likely to help with startup? How does one apply it via windows, or could I just install Version 9.0.1-1 and get the same result? Thanks, Peter Jolles
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 theimpression the database had to be able to start up in order to vacuum it. If there is a way to do it with the databasenot 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 hoursbefore the database started refusing transactions the following was present, but I wasn't able to shut everything downin 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 pointI 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 thispoint 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: > 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.
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