Обсуждение: Postgresql not accessible, recovering
Hi folks. My Bacula system has stopped working because it cannot access the Postgresql server. I have tried restarting the server using systemctl restart postgresql and pg_ctl start (from postgres user) I get nothing appearing on-screen using either method but the server is still unaccessible. Doing a 'ps ax' found the following output. Can anyone help on what I need to do next as Google only returns posts relating to replication which I don't use. -bash-4.2$ ps ax|grep post 29370 pts/0 S 0:00 su - postgres 29446 pts/0 S 0:00 /usr/bin/postgres 29447 ? Ss 0:00 postgres: logger process 29448 ? Ds 0:07 postgres: startup process recovering 000000010000000C000000FD 29653 ? Ss 0:00 gvim /etc/systemd/system/multi-user.target.wants/postgresql.service 29746 pts/0 S+ 0:00 grep --color=auto post -bash-4.2$ I'm using: [root@lou clients]# uname -a Linux lou.ringways.co.uk 3.6.10-2.fc17.x86_64 #1 SMP Tue Dec 11 18:07:34 UTC 2012 x86_64 x86_64 x86_64 GNU/Linux [root@lou clients]# rpm -qa|grep postgr postgresql-9.1.7-1.fc17.x86_64 postgresql-server-9.1.7-1.fc17.x86_64 postgresql-libs-9.1.7-1.fc17.x86_64 Gary -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
On Tue, Nov 19, 2013 at 3:08 AM, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote:
Doing a 'ps ax' found the following output. Can anyone help on what I need to
do next as Google only returns posts relating to replication which I don't
use.
-bash-4.2$ ps ax|grep post
29448 ? Ds 0:07 postgres: startup process recovering
000000010000000C000000FD
What do the contents of your Postgresql logs look like? Anything to indicate why it is recovering? Are there any errors in the logs?
On Tue, Nov 19, 2013 at 06:25:44AM -0800, bricklen wrote: > On Tue, Nov 19, 2013 at 3:08 AM, Gary Stainburn < > gary.stainburn@ringways.co.uk> wrote: > > > > > Doing a 'ps ax' found the following output. Can anyone help on what I > > need to > > do next as Google only returns posts relating to replication which I don't > > use. > > > > -bash-4.2$ ps ax|grep post > > > > 29448 ? Ds 0:07 postgres: startup process recovering > > 000000010000000C000000FD > > > > What do the contents of your Postgresql logs look like? Anything to > indicate why it is recovering? Are there any errors in the logs? Hi Gary, I had something similar happen when a recovery.conf file was mistakenly set up on the master. It hit after a "simple" restart of the DB. It was a silly problem, but I had not seen it before and it was not on my problem check list -- something to check. Regards, Ken
On Tuesday 19 November 2013 14:25:44 bricklen wrote: > On Tue, Nov 19, 2013 at 3:08 AM, Gary Stainburn < > > 29448 ? Ds 0:07 postgres: startup process recovering > > 000000010000000C000000FD > > What do the contents of your Postgresql logs look like? Anything to > indicate why it is recovering? Are there any errors in the logs? It now says 29448 ? Ds 1:16 postgres: startup process recovering 000000010000000C000000FE so it is doing something. Today's log file (/var/lib/pgsql/data/pg_log/postgresql-Tue.log) contains ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates LOG: unexpected EOF on client connection ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates LOG: could not receive data from client: Connection reset by peer LOG: unexpected EOF on client connection ERROR: table "delcandidates" does not exist STATEMENT: DROP TABLE DelCandidates LOG: database system was shut down in recovery at 2013-11-18 13:32:50 GMT LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at C/FD360978 FATAL: the database system is starting up FATAL: the database system is starting up [snip] FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: received smart shutdown request LOG: shutting down LOG: database system is shut down LOG: database system was shut down in recovery at 2013-11-19 10:22:59 GMT LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at C/FD360978 -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
Gary Stainburn wrote: > My Bacula system has stopped working because it cannot access the Postgresql > server. > > I have tried restarting the server using > > systemctl restart postgresql > > and > > pg_ctl start (from postgres user) > > I get nothing appearing on-screen using either method but the server is still > unaccessible. > > Doing a 'ps ax' found the following output. Can anyone help on what I need to > do next as Google only returns posts relating to replication which I don't > use. > > -bash-4.2$ ps ax|grep post > 29370 pts/0 S 0:00 su - postgres > 29446 pts/0 S 0:00 /usr/bin/postgres > 29447 ? Ss 0:00 postgres: logger process > 29448 ? Ds 0:07 postgres: startup process recovering > 000000010000000C000000FD > 29653 ? Ss 0:00 > gvim /etc/systemd/system/multi-user.target.wants/postgresql.service > 29746 pts/0 S+ 0:00 grep --color=auto post > -bash-4.2$ That means that PostgreSQL is recovering from a crash or immediate shutdown. You won't be able to access the server until recovery has finished. You should look into your PostgreSQL server log file to determine what is going on. Yours, Laurenz Albe
Yesterday's log file contains: FATAL: the database system is starting up LOG: database system shutdown was interrupted; last known up at 2013-11-11 05:48:07 GMT LOG: database system was not properly shut down; automatic recovery in progress LOG: redo starts at C/FD360978 FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up [snip] FATAL: the database system is starting up FATAL: the database system is starting up FATAL: the database system is starting up LOG: received smart shutdown request LOG: shutting down LOG: database system is shut down So it looks like it's been going for some time. Sunday's log file is full of LOG: could not write temporary statistics file "pg_stat_tmp/pgstat.tmp": No space left on device So I'm guessing that was the original cause. Presumably, it'll just take as long as it takes :-( -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk
On Tue, Nov 19, 2013 at 3:39 PM, Gary Stainburn <gary.stainburn@ringways.co.uk> wrote: > On Tuesday 19 November 2013 14:25:44 bricklen wrote: > LOG: received smart shutdown request > LOG: shutting down > LOG: database system is shut down > LOG: database system was shut down in recovery at 2013-11-19 10:22:59 GMT > LOG: database system was not properly shut down; automatic recovery in > progress Are you shutting down the database while recovery is in progress or is there any tool that is doing that for you? I mean, the recovery could be taking so long because something is kicking it away before it is finished? For what I know recovery should be kind of proprotional to the checkpoint configurations, have you changed such settings to get very high windows over checkpoints? Luca
On Wednesday 20 November 2013 10:57:13 Luca Ferrari wrote: > On Tue, Nov 19, 2013 at 3:39 PM, Gary Stainburn > > <gary.stainburn@ringways.co.uk> wrote: > > On Tuesday 19 November 2013 14:25:44 bricklen wrote: > > > > LOG: received smart shutdown request > > LOG: shutting down > > LOG: database system is shut down > > LOG: database system was shut down in recovery at 2013-11-19 10:22:59 > > GMT LOG: database system was not properly shut down; automatic recovery > > in progress > > Are you shutting down the database while recovery is in progress or is > there any tool that is doing that for you? I mean, the recovery could > be taking so long because something is kicking it away before it is > finished? > For what I know recovery should be kind of proprotional to the > checkpoint configurations, have you changed such settings to get very > high windows over checkpoints? > > Luca That may well have been from when I tried restarting the Postgresql service. Thankfully the recovery finished at 10:40 yesterday and normal service has resumed. Thanks to all who responded. Gary -- Gary Stainburn Group I.T. Manager Ringways Garages http://www.ringways.co.uk