Thread: Problem with starting PostgreSQL server 7.4.19
Hello all,
It was running fine initially and the database was lying idle for a few days. Today I looged into the machine and restarted the server by killing the process by 'kill -9 pid'. And then restarted it by 'postmaster -i -D /opt/pgsql/data/'.
Then it gives the following error on stdout :
LOG: database system was interrupted at 2008-03-06 14:15:17 IST
LOG: record with incorrect prev-link 1/0 at 0/A4EB08
LOG: invalid primary checkpoint record
LOG: record with incorrect prev-link 42FD/0 at 0/A4EAC8
LOG: invalid secondary checkpoint record
PANIC: could not locate a valid checkpoint record
LOG: startup process (PID 3388) was terminated by signal 6
LOG: aborting startup due to startup process failure
What is the problem? It was running fine all this time.
I have to use an older version for it to be used with another software. So changing versions is not feasible.
Thanks & Regards,
Kakoli
________________________________________________________________________
KAKOLI SEN Ph:91-80-25341909/215(Extn. 309)
C-DAC Knowledge Park E-mail:
#1, Old Madras Road kakolis@cdacb.ernet.in
Bangalore - 560 038, INDIA kakoli_basu@yahoo.co.in
________________________________________________________________________
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Kakoli Sen wrote: > Hello all, > It was running fine initially and the database was lying idle for a > few days. Today I looged into the machine and restarted the server by > killing the process by 'kill -9 pid'. And then restarted it by > 'postmaster -i -D /opt/pgsql/data/'. > Why did you use `kill -9' ? Was it not responding to `kill -15' ( ie SIGTERM, kill -TERM ) or shutdown using the init script? SIGKILL, ie signal 9, terminates the process without giving it a chance to clean its state up. It gets no chance to write out buffered data, mark data files as clean, or take any other safe shutdown actions. It's a REALLY REALLY BAD IDEA to do this on a database server, though it should still be able to recover if it's configured to operate with fsync enabled etc. > Then it gives the following error on stdout : > > LOG: database system was interrupted at 2008-03-06 14:15:17 IST > LOG: record with incorrect prev-link 1/0 at 0/A4EB08 > LOG: invalid primary checkpoint record > LOG: record with incorrect prev-link 42FD/0 at 0/A4EAC8 > LOG: invalid secondary checkpoint record > PANIC: could not locate a valid checkpoint record Ouch. It can't handle either of the checkpoints, and so it can't load the database. I don't know what database repair tools exist, but personally at this point I'd be glad my backups are always kept up to date. > What is the problem? It was running fine all this time. > I suspect that killing it without giving it a chance to do any cleanup operations might not have helped. What's your server configuration? Could you have disabled any safe I/O options to get some more speed out of the database, perhaps? I'm pretty sure 8.x copes with SIGKILL (because of its use of WAL logging, strong fsync requirements, etc) though of course it's still not a good idea. I don't know about 7.x . -- Craig Ringer
Craig Ringer <craig@postnewspapers.com.au> writes:
> Kakoli Sen wrote:
>> It was running fine initially and the database was lying idle for a
>> few days. Today I looged into the machine and restarted the server by
>> killing the process by 'kill -9 pid'. And then restarted it by
>> 'postmaster -i -D /opt/pgsql/data/'.
>>
> Why did you use `kill -9' ?
Certainly not good practice, but theoretically PG should be proof
against even such deliberate abuse as that.
What seemed odd to me was
>> LOG: database system was interrupted at 2008-03-06 14:15:17 IST
>> LOG: record with incorrect prev-link 1/0 at 0/A4EB08
>> LOG: invalid primary checkpoint record
>> LOG: record with incorrect prev-link 42FD/0 at 0/A4EAC8
>> LOG: invalid secondary checkpoint record
Experimentation shows that a freshly initialized 7.4 database has
WAL locations like this:
Latest checkpoint location: 0/9DFCF0
Prior checkpoint location: 0/9D92C0
so either you'd only ever thrown a few kilobytes of stuff into the DB
or there was something seriously wrong with pg_control to begin with.
I'm wondering about mistaken filesystem restores ...
regards, tom lane
Hi,
Actually, I tried stopping server by 'kill `cat /opt/pgsql/data/postmaster.pid`. This did not work. So I used kill -9 on Red Hat 4.
This is a test database where we are in the process of setting up. So it does not have live data. Still I do agree, it was not a good idea.
Now, do I have to re-install PostgreSQL or is there any way out?
Server configuration is default. Only change from default is allowing tcp/ip connections.
Regards,
Kakoli
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Craig Ringer
> Sent: Wednesday, March 12, 2008 11:09 AM
> To: Kakoli Sen
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Problem with starting PostgreSQL server 7.4.19
>
>
> Kakoli Sen wrote:
> > Hello all,
> > It was running fine initially and the database was
> lying idle for a
> > few days. Today I looged into the machine and restarted the server by
> > killing the process by 'kill -9 pid'. And then restarted it by
> > 'postmaster -i -D /opt/pgsql/data/'.
> >
> Why did you use `kill -9' ? Was it not responding to `kill -15' ( ie
> SIGTERM, kill -TERM ) or shutdown using the init script?
>
> SIGKILL, ie signal 9, terminates the process without giving it a chance
> to clean its state up. It gets no chance to write out buffered data,
> mark data files as clean, or take any other safe shutdown actions. It's
> a REALLY REALLY BAD IDEA to do this on a database server, though it
> should still be able to recover if it's configured to operate with fsync
> enabled etc.
> > Then it gives the following error on stdout :
> >
> > LOG: database system was interrupted at 2008-03-06 14:15:17 IST
> > LOG: record with incorrect prev-link 1/0 at 0/A4EB08
> > LOG: invalid primary checkpoint record
> > LOG: record with incorrect prev-link 42FD/0 at 0/A4EAC8
> > LOG: invalid secondary checkpoint record
> > PANIC: could not locate a valid checkpoint record
> Ouch. It can't handle either of the checkpoints, and so it can't load
> the database.
>
> I don't know what database repair tools exist, but personally at this
> point I'd be glad my backups are always kept up to date.
> > What is the problem? It was running fine all this time.
> >
> I suspect that killing it without giving it a chance to do any cleanup
> operations might not have helped.
>
> What's your server configuration? Could you have disabled any safe I/O
> options to get some more speed out of the database, perhaps?
>
> I'm pretty sure 8.x copes with SIGKILL (because of its use of WAL
> logging, strong fsync requirements, etc) though of course it's still not
> a good idea. I don't know about 7.x .
>
> --
> Craig Ringer
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Kakoli Sen wrote: > Hi, > Actually, I tried stopping server by 'kill `cat > /opt/pgsql/data/postmaster.pid`. This did not work. So I used kill -9 on Red > Hat 4. > > This is a test database where we are in the process of setting up. So it > does not have live data. Still I do agree, it was not a good idea. > > Now, do I have to re-install PostgreSQL or is there any way out? Well, if it's a test database you should be able to rename or remove the data directory and then re-run initdb, since you don't care about the data in the database. I'm sure you want to find out why this happened, though, so maybe you should keep the damaged database around for a while and see if anybody here has ideas about what could've happened. -- Craig Ringer
"Kakoli Sen" <kakolis@cdacb.ernet.in> writes:
> Actually, I tried stopping server by 'kill `cat
> /opt/pgsql/data/postmaster.pid`. This did not work. So I used kill -9 on Red
> Hat 4.
Define "did not work" ... what happened exactly?
I do not know of any prepackaged Postgres distribution for Red Hat that
would put the data directory under /opt. Did you build from source?
If you used a prepackaged build then I'm thinking that you did not find
every place that needed to be changed to move the data directory.
I'm also kind of wondering why you are using either PG 7.4 or RH 4
for a new experimental setup. Both of those versions can see their
EOL dates coming round the corner.
regards, tom lane
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
am Thu, dem 13.03.2008, um 15:09:18 +0530 mailte Kakoli Sen folgendes: > Dear all, > The command GRANT ALL PRIVILEGES ON DATABASE casDatabase to tester; > is giving error: > ERROR: database "casdatabase" does not exist.(Note that the db name in error > is all in lower case). > > Also \l shows that casDatabase exists. > Why is 'casDatabase' being changed to 'casdatabase'? Try: GRANT ALL PRIVILEGES ON DATABASE "casDatabase" to tester; Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Kakoli Sen wrote: > The command GRANT ALL PRIVILEGES ON DATABASE casDatabase to tester; > is giving error: > ERROR: database "casdatabase" does not exist.(Note that the db name in error is all in lower case). > > Also \l shows that casDatabase exists. > Why is 'casDatabase' being changed to 'casdatabase'? PostgreSQL will convert all names to lower case unless you protect them with double quotes. Try: GRANT ALL PRIVILEGES ON DATABASE "casDatabase" to tester; Yours, Laurenz Albe
Hi,
This time the command GRANT ALL PRIVILEGES ON DATABASE "casDatabase" to tester; did not give error.
But the permission is still not there. I run the following commands :
psql -d casDatabase -U tester -W. Then \z command shows empty Access Privileges on all tables.
Also select * from table_name; gives error :
ERROR: permission denied for relation table_name
Regards,
Kakoli
> -----Original Message-----
> From: A. Kretschmer [mailto:andreas.kretschmer@schollglas.com]
> Sent: Thursday, March 13, 2008 3:22 PM
> To: pgsql-general@postgresql.org
> Subject: Re: Problem with GRANT in 7.4.19
>
>
> am Thu, dem 13.03.2008, um 15:09:18 +0530 mailte Kakoli Sen folgendes:
> > Dear all,
> > The command GRANT ALL PRIVILEGES ON DATABASE casDatabase to tester;
> > is giving error:
> > ERROR: database "casdatabase" does not exist.(Note that the db
> name in error
> > is all in lower case).
> >
> > Also \l shows that casDatabase exists.
> > Why is 'casDatabase' being changed to 'casdatabase'?
>
> Try:
> GRANT ALL PRIVILEGES ON DATABASE "casDatabase" to tester;
>
>
> Andreas
> --
> Andreas Kretschmer
> Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
> GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is
> believed to be clean.
--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
am Thu, dem 13.03.2008, um 16:22:28 +0530 mailte Kakoli Sen folgendes: > Hi, > > This time the command GRANT ALL PRIVILEGES ON DATABASE "casDatabase" to > tester; did not give error. Okay. > > But the permission is still not there. I run the following commands : > > psql -d casDatabase -U tester -W. Then \z command shows empty Access Privileges > on all tables. > > Also select * from table_name; gives error : > > ERROR: permission denied for relation table_name Sure. A database != table. You have to grant access to the table. If you have many tables: 12:44 < akretschmer> ??grantall 12:44 < rtfm_please> For information about grantall 12:44 < rtfm_please> see http://pgedit.com/public/sql/acl_admin/index.html 12:44 < rtfm_please> or http://unf.be/postgresql/postgres_grant_all.perl 12:44 < rtfm_please> or http://www.archonet.com/pgdocs/grant-all.html I suggest the last link. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
On Thu, Mar 13, 2008 at 11:52 AM, Kakoli Sen <kakolis@cdacb.ernet.in> wrote:
> This time the command GRANT ALL PRIVILEGES ON DATABASE "casDatabase" to
> tester; did not give error.
>
> But the permission is still not there. I run the following commands :
>
> psql -d casDatabase -U tester -W. Then \z command shows empty Access
> Privileges on all tables.
>
> Also select * from table_name; gives error :
>
> ERROR: permission denied for relation table_name
You have to GRANT permissions to each and every table separately.
This is how most (all big) databases work. :-)
Regards,
Dawid
PS:
SE:LECT 'GRANT ALL ON '||schemaname||'.'||tablename||' TO tester' FROM
pg_tables; -- to get you started. :)