Re: PostgreSQL 9.1 "database system was interrupted; last known up at"

Mostly following the instructions at
https://wiki.postgresql.org/wiki/May_2015_Fsync_Permissions_Bug#I.27ve_hit_this_bug_and_I_can.27t_restart_Postgres._What_do_I_do.3F
I was able to restart the postgres.
<log>
2015-05-30 13:45:38 PDT LOG:  database system was interrupted; last known up at 2015-05-22 09:22:25 PDT
2015-05-30 13:45:38 PDT LOG:  incomplete startup packet
2015-05-30 13:45:39 PDT LOG:  database system was not properly shut down; automatic recovery in progress
2015-05-30 13:45:39 PDT LOG:  record with zero length at 0/41856D18
2015-05-30 13:45:39 PDT LOG:  redo is not required
2015-05-30 13:45:39 PDT LOG:  database system is ready to accept connections
2015-05-30 13:45:39 PDT LOG:  autovacuum launcher started
</log>

I actually tried first renaming server.{crt,key} and copying files as suggested on the wiki.  I did a chown just on the
newserver.{crt,key}.  Postgres gave the same permission error on my renamed links, so I deleted them.  Then
/etc/init.d/postgresqlstart 
succeeded.

Thanks to Albe, Tom, and those contributing to the wiki for your help.

Ross Boylan
________________________________________
From: Albe Laurenz [laurenz.albe@wien.gv.at]
Sent: Tuesday, May 26, 2015 2:12 AM
To: Boylan, Ross; pgsql-admin@postgresql.org
Subject: RE: PostgreSQL 9.1 "database system was interrupted; last known up at"

Ross Boylan wrote:
> I had to power cycle my system because it became unresponsive.  Now PosgtreSQL will not start.  I
> would like advice about how to proceed; I think pg_resetxlog is my next step.  I have made a copy of
> the current database files.
>
> <log file="postgresql-9.1-main.log">
> 2015-05-25 10:44:21 PDT LOG:  database system was interrupted; last known up at 2015-05-22 09:22:25 PDT
> 2015-05-25 10:44:21 PDT LOG:  incomplete startup packet
> 2015-05-25 10:44:21 PDT FATAL:  could not open file "/etc/ssl/certs/ssl-cert-snakeoil.pem": Permission denied
> 2015-05-25 10:44:21 PDT LOG:  startup process (PID 5180) exited with exit code 1
> 2015-05-25 10:44:21 PDT LOG:  aborting startup due to startup process failure
> </log>
>
> I am running PostgreSQL 9.1 on Debian wheezy aka 7 aka oldstable.
> Installed via the Debian package.  I think I accepted the defaults, and have not changed the
> configuration since.
> Linux  3.2.0, stock Debian kernel, amd64.
> Connect via emacs sql-postgresql or psql.  An init script controls startup.
>
> When the system became unresponsive I was able to ssh in; the X process had gone crazy and could not
> be killed.  Most key file systems had been remounted read-only, and many commands (includiing shutdown
> and telinit) produced errors, often I/O errors, when run.  The last kern.log entries showed a process
> being killed.  There was quite a lot of inode deletion and log replaying on restart.  The log message
> above was from just after the restart.
>
> I have no backups*, but could recreate the database in the worst case.  I haven't done anything with
> the database in at least a week, I think, and so if I could get back the state as of 5/22 that would
> be fine.
>
> Filesystem is ext3 on dm-crypt on LVM.
>
> The permission error on the snakeoil cert is weird, since it is readable by all.  I'm guessing it's a
> side effect of the earlier problems.

You didn't say which exact version of PostgreSQL you are running, but I bet you it is 9.1.16
and you are hitting the "Fsync Permissions Bug" introduced with that release:
https://wiki.postgresql.org/wiki/May_2015_Fsync_Permissions_Bug

There are plans to release a fix for that shortly.
A better workaround than the one specified in the current version of the Wiki page
might be to replace the symbolic link with a copy and change ownership of the files to "postgres".

A restore wouldn't help you in this case, but since you probably felt the pain,
please implement a backup strategy.  It may come handy some day if recreating the database
is not a nice option.

Yours,
Laurenz Albe