Thread: BUG #15979: PG_VERSION , pg_tblspc got deleted automatically & postgres server creased.

The following bug has been logged on the website:

Bug reference:      15979
Logged by:          mahesh Shetty
Email address:      maheshetty20@gmail.com
PostgreSQL version: 9.4.4
Operating system:   Oracle Linux Server release 6.7
Description:

Investigation through postmaster while starting the database service to get
DB connect: 
 
-bash-4.1$  /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data <
2019-08-20 00:13:29.353 CDT >FATAL:  "/var/lib/pgsql/9.4/data" is not a
valid data directory < 2019-08-20 00:13:29.353 CDT >DETAIL:  File
"/var/lib/pgsql/9.4/data/PG_VERSION" is missing.     
There was missing PG_VERSION  which was created manually  
 
-bash-4.1$ postmaster -D /var/lib/pgsql/9.4/data/ < 2019-08-20 00:27:35.275
CDT >FATAL:  could not open directory "pg_tblspc": No such file or directory
-bash-4.1$   
There was missing pg_tblspc  which was created manually 
 
-bash-4.1$  /usr/pgsql-9.4/bin/pg_ctl  -D /var/lib/pgsql/9.4/data/ start  
server starting  
-bash-4.1$ < 2019-08-20 00:29:31.982 CDT >LOG:  redirecting log output to
logging collector process < 2019-08-20 00:29:31.982 CDT >HINT:  Future log
output will appear in directory "pg_log". 
-bash-4.1$ psql -p 1433  
psql: FATAL:  the database system is starting up  
-bash-4.1$ postmaster -D /var/lib/pgsql/9.4/data/ < 2019-08-20 00:29:47.588
CDT >FATAL:  lock file "postmaster.pid" already exists < 2019-08-20
00:29:47.588 CDT >HINT:  Is another postmaster (PID 30083) running in data
directory "/var/lib/pgsql/9.4/data"? -bash-4.1$ psql -p 1433 psql: FATAL:
the database system is starting up 
  
Deleted manually pid files and started postgres service 
-bash-4.1$ /usr/pgsql-9.4/bin/pg_ctl  -D /var/lib/pgsql/9.4/data/ start
pg_ctl: another server might be running; trying to start server anyway
server starting -bash-4.1$ < 2019-08-20 00:31:04.932 CDT >FATAL:  lock file
"postmaster.pid" already exists < 2019-08-20 00:31:04.932 CDT >HINT:  Is
another postmaster (PID 30083) running in data directory
"/var/lib/pgsql/9.4/data"? 
-bash-4.1$ psql -p 1433 psql: FATAL:  the database system is starting up
-bash-4.1$    
 
 
 
Server went into recovery mode as it was stopped abruptly. 
•    postgres 30083     1  0 00:29 pts/1    00:00:00
/usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data 
postgres 30084 30083  0 00:29 ?        00:00:00 postgres: logger process 
postgres 30085 30083 86 00:29 ?        00:01:48 postgres: startup process
recovering 0000000100000EA9000000A6 
    
•    postgres=# SELECT * FROM pg_stat_activity; 
server closed the connection unexpectedly 
        This probably means the server terminated abnormally 
        before or while processing the request. 
The connection to the server was lost. Attempting reset: Succeeded. 
 
•    Connections got normal after successful recovery: 
214847 | jiradb_7_0_10 | 31453 |    16385 | jirauser |                  |
172.28.50.226 |                 |       57530 | 2019-08-20
00:33:54.444258-05 | 
                 | 2019-08-20 00:36:57.03654-05  | 2019-08-20
00:36:57.276092-05 | f       | idle                |             |
   | SELECT COUNT(I.ID) FROM 
public.jiraissue I WHERE I.PROJECT=$1 
214847 | jiradb_7_0_10 | 31667 |    16385 | jirauser |                  |
172.28.50.226 |                 |       57632 | 2019-08-20
00:35:42.319525-05 | 2019-08-20 00 
:36:58.355277-05 | 2019-08-20 00:36:58.355307-05 | 2019-08-20
00:36:58.355308-05 | f       | active              |             |
1672613460 | INSERT INTO "artstatemap 
" (issueid, stateid, year, month, day) SELECT issueid, stateid, $1, $2, $3
FROM "artstatemap" WHERE year=$4 AND month=$5 AND (day = $6) AND (issueid >=
$7 AND issueid < 
= $8) 
214847 | jiradb_7_0_10 | 31549 |    16385 | jirauser |                  |
172.28.50.226 |                 |       57576 | 2019-08-20
00:34:44.761641-05 | 
                 | 2019-08-20 00:36:57.363577-05 | 2019-08-20
00:36:57.363587-05 | f       | idle                |             |
   | COMMIT 
  13003 | postgres      | 31672 |       10 | postgres | psql             |
            |                 |          -1 | 2019-08-20 00:35:43.673998-05
| 
                 | 2019-08-20 00:35:48.573473-05 | 2019-08-20
00:35:48.720062-05 | f       | idle                |             |
   | SELECT d.datname as "Nam 
e",

On Mon, Aug 26, 2019 at 12:33:38PM +0000, PG Bug reporting form wrote:
> -bash-4.1$  /usr/pgsql-9.4/bin/postgres -D /var/lib/pgsql/9.4/data <
> 2019-08-20 00:13:29.353 CDT >FATAL:  "/var/lib/pgsql/9.4/data" is not a
> valid data directory < 2019-08-20 00:13:29.353 CDT >DETAIL:  File
> "/var/lib/pgsql/9.4/data/PG_VERSION" is missing.
> There was missing PG_VERSION  which was created manually
>
> -bash-4.1$ postmaster -D /var/lib/pgsql/9.4/data/ < 2019-08-20 00:27:35.275
> CDT >FATAL:  could not open directory "pg_tblspc": No such file or directory
> -bash-4.1$
> There was missing pg_tblspc  which was created manually

Those first two problems point out that you may have much more
problems than you think, so you should be careful with the way you do
things if you have a corrupted cluster and not employ shortcuts you
think may work.  Here are some guidelines from the wiki:
https://wiki.postgresql.org/wiki/Corruption

This is hard to qualify as a bug by the way, and 9.4.4 is utterly
outdated.  You should consider upgrading to a newer version as well as
9.4 is going to be EOL'd by community in a couple of months.
--
Michael