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

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

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'?

Regards,
Kakoli.

--
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. :)