Re: FATAL: the database system is starting up

On Tue, Nov 1, 2011 at 5:43 AM, patrick keshishian <pkeshish(at)gmail(dot)com> wrote:
> On Wed, Oct 26, 2011 at 5:46 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>> On Wed, Oct 26, 2011 at 6:09 PM, patrick keshishian <pkeshish(at)gmail(dot)com> wrote:
>>> On Wed, Oct 26, 2011 at 4:49 PM, William E. Moreno A.
>>> <wmoreno3(at)yahoo(dot)com> wrote:
>>>> Solution: http://archives.postgresql.org/pgsql-general/2007-12/msg01339.php
>>>> Solution: Message-id: <476D6DE1(dot)4050600(at)latnet(dot)lv>
>>>> <text/plain>
>>>>
>>>> Problem: FATAL:  the database system is starting up
>>>>
>>>> Solved:   change postgresql_flags in /etc/rc.conf to: postgresql_flags="-s -m fast" or postgresql_flags="-s -m smart"
>>>
>>> interesting enough, got bit by this recently. Our set up does not have
>>> a "-w" option. During upgrades we "cycle" our PostgreSQL daemon. We
>>> had to change the "pg_ctl stop" command to "-mimmediate" because
>>> during upgrades we would find that someone had an abandoned psql shell
>>> running for days and that would halt our upgrade script.
>>>
>>> Adding "-m immediate" for shutdown seemed like a logical choice to get
>>> around this sort of a "procedural" issue(s).
>>
>> didn't -m fast work?
>
> I forget now why "-m fast" wasn't used. I tried to dig up anything I
> had in my notes and did a set of new experiments (hence the late
> reply), but didn't come up with much.
>
> About the only difference with '-m fast' and '-m immediate' seems to
> be the following log entry:
>
>  .... database system was
> not properly shut down; automatic recovery in progress
>
> For now, I've changed the script to use '-m fast' and removed the
> sleep. Will find out after some moderate use of it whether it was a
> wise decision or not :-)

(just for the archives ...)

So, even though 'stop -m fast' does in fact work nicer than "-m
immediate", the sleep(3) is required after a "pg_ctl -D $PGDATA start"
command, in our scripts.

Excuse gmail line-wraps:

# /etc/init.d/postgres stop
Stopping PostgreSQL Database (smart)... OK
# date ; /etc/init.d/postgres start ; date ; i=0 ; while : ; do psql
-lU postgres 2>/dev/null && date && break ; i=$((i+1)) ; printf "PG9
connect try #%d\n" $i ; done
Mon Nov 7 20:58:06 PST 2011
Starting PostgreSQL Database... OK
Mon Nov 7 20:58:06 PST 2011
PG9 connect try #1
PG9 connect try #2
PG9 connect try #3
PG9 connect try #4
PG9 connect try #5
PG9 connect try #6
PG9 connect try #7
PG9 connect try #8
PG9 connect try #9
PG9 connect try #10
PG9 connect try #11
PG9 connect try #12
PG9 connect try #13
PG9 connect try #14
PG9 connect try #15
PG9 connect try #16
PG9 connect try #17
PG9 connect try #18
PG9 connect try #19
PG9 connect try #20
PG9 connect try #21
PG9 connect try #22
PG9 connect try #23
PG9 connect try #24
PG9 connect try #25
PG9 connect try #26
PG9 connect try #27
PG9 connect try #28
PG9 connect try #29
PG9 connect try #30
PG9 connect try #31
PG9 connect try #32
PG9 connect try #33
PG9 connect try #34
PG9 connect try #35
PG9 connect try #36
PG9 connect try #37
PG9 connect try #38
PG9 connect try #39
PG9 connect try #40
PG9 connect try #41
PG9 connect try #42
PG9 connect try #43
PG9 connect try #44
PG9 connect try #45
PG9 connect try #46
PG9 connect try #47
PG9 connect try #48
PG9 connect try #49
PG9 connect try #50
PG9 connect try #51
PG9 connect try #52
PG9 connect try #53
PG9 connect try #54
PG9 connect try #55
List of databases
Name | Owner | Encoding | Collation | Ctype |
Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
foo | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
x1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

Mon Nov 7 20:58:07 PST 2011
# /etc/init.d/postgres stop
Stopping PostgreSQL Database (smart)... OK
# date ; /etc/init.d/postgres start ; date ; i=0 ; while : ; do psql
-lU postgres 2>/dev/null && date && break ; i=$((i+1)) ; printf "PG9
connect try #%d\n" $i ; done
Mon Nov 7 20:58:49 PST 2011
Starting PostgreSQL Database... OK
Mon Nov 7 20:58:49 PST 2011
PG9 connect try #1
PG9 connect try #2
PG9 connect try #3
PG9 connect try #4
PG9 connect try #5
PG9 connect try #6
PG9 connect try #7
PG9 connect try #8
PG9 connect try #9
PG9 connect try #10
PG9 connect try #11
PG9 connect try #12
PG9 connect try #13
PG9 connect try #14
PG9 connect try #15
PG9 connect try #16
PG9 connect try #17
PG9 connect try #18
PG9 connect try #19
PG9 connect try #20
PG9 connect try #21
PG9 connect try #22
PG9 connect try #23
PG9 connect try #24
PG9 connect try #25
PG9 connect try #26
PG9 connect try #27
PG9 connect try #28
PG9 connect try #29
PG9 connect try #30
PG9 connect try #31
PG9 connect try #32
PG9 connect try #33
PG9 connect try #34
PG9 connect try #35
PG9 connect try #36
PG9 connect try #37
PG9 connect try #38
PG9 connect try #39
PG9 connect try #40
PG9 connect try #41
PG9 connect try #42
PG9 connect try #43
PG9 connect try #44
List of databases
Name | Owner | Encoding | Collation | Ctype |
Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
foo | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
=c/postgres +
| | | | |
postgres=CTc/postgres
x1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(5 rows)

Mon Nov 7 20:58:50 PST 2011

vs same with PG7:

# date ; /etc/init.d/postgres-db start ; date ; i=0 ; while : ; do
i=$((i+1)) ; psql -lU postgres 2>/dev/null && date && break ; printf
"PG7 connect try #%d\n" $i ; done
Mon Nov 7 21:57:54 PST 2011
Starting PostgreSQL Database... OK
Mon Nov 7 21:57:54 PST 2011
PG7 connect try #1
PG7 connect try #2
List of databases
Name | Owner | Encoding
-----------+----------+-----------
foo | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

Mon Nov 7 21:57:54 PST 2011
# /etc/init.d/postgres-db stop
Stopping PostgreSQL Database (smart)... OK
# date ; /etc/init.d/postgres-db start ; date ; i=0 ; while : ; do
i=$((i+1)) ; psql -lU postgres 2>/dev/null && date && break ; printf
"PG7 connect try #%d\n" $i ; done
Mon Nov 7 21:59:35 PST 2011
Starting PostgreSQL Database... OK
Mon Nov 7 21:59:35 PST 2011
PG7 connect try #1
PG7 connect try #2
List of databases
Name | Owner | Encoding
-----------+----------+-----------
foo | postgres | SQL_ASCII
template0 | postgres | SQL_ASCII
template1 | postgres | SQL_ASCII
(3 rows)

Mon Nov 7 21:59:35 PST 2011

The "cute" thing with PG7 is, we also start the contrib/pg_autovacuum
in "/etc/init.d/postgre start", which would "cover up" for, what seems
to be two connect failures right after start of PG7.

Cheers,
--patrick