Thread: Postgresql database does not start and just hangs
We have been using Postgresql for the past 6 months.
Yesterday due to power shutdown the server on which Postgresql database resides was abruptly shutdown.
Now when we try to start it does not start and just hangs. We are running on a Win2k with Postgresql 7.2 with cygwin install, version 2.125.2.10
This is the startup process we run.
On cygwinAdministrator@HPA4W357 ~
$ cd /usr/local/pgsql/bin/Administrator@HPA4W357 /usr/local/pgsql/bin
$ ipc-daemon &
[1] 840Administrator@HPA4W357 /usr/local/pgsql/bin
$ ./postmaster -i -D /usr/local/pgsql/dataNormally when we start in this manner, we get
DEBUG: database system was shut down at 2003-04-16 12:14:24
DEBUG: checkpoint record is at 0/5F8778
DEBUG: redo record is at 0/5F8778; undo record is at 0/0; shutdown TRUE
DEBUG: next transaction id: 9774; next oid: 18504
DEBUG: database system is ready
( we have given this output from a machine where we have another install of Postgresql)But now when we run this , we get
hp@ACACIA ~
$ cd /usr/local/pgsql/bin/hp@ACACIA /usr/local/pgsql/bin
$ ipc-daemon &
[1] 18140hp@ACACIA /usr/local/pgsql/bin
$ ./postmaster -i -D /usr/local/pgsql/dataAnd the process hangs there.
When we check the status of Postgresql using
hp@ACACIA ~
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status
pg_ctl: postmaster is running (pid: 18012)
Command line was:
/usr/local/pgsql/bin/./postmaster '-i' '-D' '/usr/local/pgsql/data'The data directory shows:
hp@ACACIA /usr/local/pgsql/data
$ ls -lrt
total 28
-rw-r--r-- 1 hp None 5 Mar 13 03:09 PG_VERSION
drwxr-xr-x 2 hp None 0 Mar 13 03:09 pg_clog
-rw-r--r-- 1 hp None 4027 Mar 13 03:09 postgresql.conf
-rw-r--r-- 1 hp None 1285 Mar 13 03:09 pg_ident.conf
-rw-r--r-- 1 hp None 10313 Mar 13 03:18 pg_hba.conf
drwxr-xr-x 5 hp None 0 Apr 5 17:00 base
-rw-r--r-- 1 hp None 69 Apr 7 23:17 postmaster.opts
drwxr-xr-x 2 hp None 4096 Apr 9 01:06 pg_xlog
drwxr-xr-x 2 hp None 4096 Apr 15 09:49 global
-rw-r--r-- 1 hp None 30 Apr 17 20:50 postmaster.pidSo the postmaster.pid file is present. When we remove the pid file, even then,
hp@ACACIA ~
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status
pg_ctl: postmaster or postgres is not runninghp@ACACIA ~
$ cd /usr/local/pgsql/bin/hp@ACACIA /usr/local/pgsql/bin
$ ipc-daemon &
[1] 18080hp@ACACIA /usr/local/pgsql/bin
$ ./postmaster -i -D /usr/local/pgsql/dataThe process still hangs.
Also when we check the status using
hp@ACACIA ~
$ /usr/local/pgsql/bin/psql SGDBThis command hangs too.
Viewing the pid file ( newly created ) shows:
18140
/usr/local/pgsql/dataAnd then
hp@ACACIA /usr/local/pgsql/data
$ ps -aef|grep 18140
hp 18140 18204 con 21:19:40 /usr/bin/psThere is a file in /tmp called .s.PGSQL-5432 which we removed and tried the same process. But no use. ( At this stage we were really desperate and trying out things which we picked from the net)
Is there a solution to this problem? We would really appreciate to get the solution.
Please help as this is our production server and we are stuck.
ThanksMandar Salunkhe
HP CDI Team
460 Alexandra Road, PSA Building
Singapore 119963
Phone: 65-63617413 HP Telnet Number: 831-7413
Email: mandar.salunkhe@hp.com
"SALUNKHE,MANDAR (HP-Singapore,ex1)" <mandar.salunkhe@hp.com> writes: > Viewing the pid file ( newly created ) shows: > 18140 > /usr/local/pgsql/data > And then > hp@ACACIA /usr/local/pgsql/data > $ ps -aef|grep 18140 > hp 18140 18204 con 21:19:40 /usr/bin/ps I'm not familiar with how Cygwin assigns process PIDs, but the above sure looks like the ps process has been given the same PID that the postmaster thought it had. Are you copying-and-pasting from more than one test session? If not, there's something odd here. Perhaps it'd be worth your while to reinstall Cygwin --- I wonder if the Cygwin executables could have gotten corrupted in your crash. (While you're at it you might as well reinstall ipc-daemon and Postgres too. Just be sure not to munge your data directory ...) regards, tom lane
Re: Postgresql database does not start and just hangs
Dear Tom,
Now maybe we have some solution here.
Do you mean to say that if we reinstall postgresql but keep the same data directory as in the previous install, the database will start again? If yes, we could try that out.
And I have shown by copy/pasting from two sessions as explained below.
The sessions are as follows:
1st session:
----------------------------------------------------------------
hp@ACACIA ~
$ cd /usr/local/pgsql/bin/
hp@ACACIA /usr/local/pgsql/bin
$ ipc-daemon &
[1] 18140
hp@ACACIA /usr/local/pgsql/bin
$ ./postmaster -i -D /usr/local/pgsql/data
And the process hangs there.
When we check the status of Postgresql using
hp@ACACIA ~
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status
pg_ctl: postmaster is running (pid: 18012)
Command line was:
/usr/local/pgsql/bin/./postmaster '-i' '-D' '/usr/local/pgsql/data'
The data directory shows:
hp@ACACIA /usr/local/pgsql/data
$ ls -lrt
total 28
-rw-r--r-- 1 hp None 5 Mar 13 03:09 PG_VERSION
drwxr-xr-x 2 hp None 0 Mar 13 03:09 pg_clog
-rw-r--r-- 1 hp None 4027 Mar 13 03:09 postgresql.conf
-rw-r--r-- 1 hp None 1285 Mar 13 03:09 pg_ident.conf
-rw-r--r-- 1 hp None 10313 Mar 13 03:18 pg_hba.conf
drwxr-xr-x 5 hp None 0 Apr 5 17:00 base
-rw-r--r-- 1 hp None 69 Apr 7 23:17 postmaster.opts
drwxr-xr-x 2 hp None 4096 Apr 9 01:06 pg_xlog
drwxr-xr-x 2 hp None 4096 Apr 15 09:49 global
-rw-r--r-- 1 hp None 30 Apr 17 20:50 postmaster.pid
So the postmaster.pid file is present. When we remove the pid file, even then,
hp@ACACIA ~
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data status
pg_ctl: postmaster or postgres is not running
2nd session: Did this to check after removing pid file.
----------------------------------------------------------------
hp@ACACIA ~
$ cd /usr/local/pgsql/bin/
hp@ACACIA /usr/local/pgsql/bin
$ ipc-daemon &
[1] 18080
hp@ACACIA /usr/local/pgsql/bin
$ ./postmaster -i -D /usr/local/pgsql/data
The process still hangs.
Also when we check the status using
hp@ACACIA ~
$ /usr/local/pgsql/bin/psql SGDB
This command hangs too.
Viewing the pid file ( newly created ) shows:
18140
/usr/local/pgsql/data
And then
hp@ACACIA /usr/local/pgsql/data
$ ps -aef|grep 18140
hp 18140 18204 con 21:19:40 /usr/bin/ps
Thanks
Mandar Salunkhe
460 Alexandra Road, PSA Building
Singapore 119963
Phone: 65-63617413 HP Telnet Number: 831-7413
Email: mandar.salunkhe@hp.com
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, April 21, 2003 10:00 AM
To: SALUNKHE,MANDAR (HP-Singapore,ex1)
Cc: 'pgsql-novice@postgresql.org'
Subject: Re: [NOVICE] Postgresql database does not start and just hangs
"SALUNKHE,MANDAR (HP-Singapore,ex1)" <mandar.salunkhe@hp.com> writes:
> Viewing the pid file ( newly created ) shows:
> 18140
> /usr/local/pgsql/data
> And then
> hp@ACACIA /usr/local/pgsql/data
> $ ps -aef|grep 18140
> hp 18140 18204 con 21:19:40 /usr/bin/ps
I'm not familiar with how Cygwin assigns process PIDs, but the above sure looks like the ps process has been given the same PID that the postmaster thought it had. Are you copying-and-pasting from more than one test session? If not, there's something odd here.
Perhaps it'd be worth your while to reinstall Cygwin --- I wonder if the Cygwin executables could have gotten corrupted in your crash. (While you're at it you might as well reinstall ipc-daemon and Postgres too. Just be sure not to munge your data directory ...)
regards, tom lane
"SALUNKHE,MANDAR (HP-Singapore,ex1)" <mandar.salunkhe@hp.com> writes: > Do you mean to say that if we reinstall postgresql but keep the same data > directory as in the previous install, the database will start again? I'm not promising that, at all ;-). But it seems worth trying. regards, tom lane
Re: Postgresql database does not start and just hangs
Dear Tom, Well there's no harm in trying this our on our test database. If it works we are through, otherwise back to Square 1. Few pointers needed: Do I just copy and paste the previous /usr/local/pgsql/data directory in the new install of postgresql? Thanks Mandar Salunkhe HP CDI Team 460 Alexandra Road, PSA Building Singapore 119963 Phone: 65-63617413 HP Telnet Number: 831-7413 Email: mandar.salunkhe@hp.com -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 21, 2003 10:38 AM To: SALUNKHE,MANDAR (HP-Singapore,ex1) Cc: 'pgsql-novice@postgresql.org' Subject: Re: [NOVICE] Postgresql database does not start and just hangs "SALUNKHE,MANDAR (HP-Singapore,ex1)" <mandar.salunkhe@hp.com> writes: > Do you mean to say that if we reinstall postgresql but keep the same > data directory as in the previous install, the database will start > again? I'm not promising that, at all ;-). But it seems worth trying. regards, tom lane
"SALUNKHE,MANDAR (HP-Singapore,ex1)" <mandar.salunkhe@hp.com> writes: > Do I just copy and paste the previous /usr/local/pgsql/data directory in the > new install of postgresql? Damifino --- I do not know what the Windows install procedure for Postgres is like. You might be well advised to rename the old data directory out of the way so that the install procedure can't overwrite it. Then rename it back after the install is done. regards, tom lane
Re: Postgresql database does not start and just hangs
Dear Tom, Thanks a lot for your help because the suggestion of yours was a success and we have succeeded in bringing the db back on. We had to install cygwin and postgresql both again. Just reinstall of postgresql does not work. Then finally did a copy and paste the previous /usr/local/pgsql/data directory in the new install of postgresql. And when the db was started with ./postmaster -i -D /usr/local/pgsql/data It worked finally! Thanks for your support. This was a hard and long way to do this thing but finally what mattered is that the work paid off. While working on this was thinking about what exactly made cygwin to go whacko like that? Thanks Mandar Salunkhe 460 Alexandra Road, PSA Building Singapore 119963 Phone: 65-63617413 HP Telnet Number: 831-7413 Email: mandar.salunkhe@hp.com -----Original Message----- From: SALUNKHE,MANDAR (HP-Singapore,ex1) [mailto:mandar.salunkhe@hp.com] Sent: Monday, April 21, 2003 10:43 AM To: 'Tom Lane' Cc: 'pgsql-novice@postgresql.org'; FRANKLIN,JOHNSON (HP-Singapore,ex5) Subject: Re: [NOVICE] Postgresql database does not start and just hangs Dear Tom, Well there's no harm in trying this our on our test database. If it works we are through, otherwise back to Square 1. Few pointers needed: Do I just copy and paste the previous /usr/local/pgsql/data directory in the new install of postgresql? Thanks Mandar Salunkhe HP CDI Team 460 Alexandra Road, PSA Building Singapore 119963 Phone: 65-63617413 HP Telnet Number: 831-7413 Email: mandar.salunkhe@hp.com -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: Monday, April 21, 2003 10:38 AM To: SALUNKHE,MANDAR (HP-Singapore,ex1) Cc: 'pgsql-novice@postgresql.org' Subject: Re: [NOVICE] Postgresql database does not start and just hangs "SALUNKHE,MANDAR (HP-Singapore,ex1)" <mandar.salunkhe@hp.com> writes: > Do you mean to say that if we reinstall postgresql but keep the same > data directory as in the previous install, the database will start > again? I'm not promising that, at all ;-). But it seems worth trying. regards, tom lane ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster