Thread: BUG #15213: unable to login on PostgreSQL by postgres user

BUG #15213: unable to login on PostgreSQL by postgres user

From
PG Bug reporting form
Date:
The following bug has been logged on the website:

Bug reference:      15213
Logged by:          dhirendra singh
Email address:      dhirendra04@hotmail.com
PostgreSQL version: 9.3.17
Operating system:   Centos 7
Description:

Hi Team,
I need your assistance on below error.
----------------------------------------------------------------------------------------------
[dhirsing@jira-stg ~]$ sudo -u postgres psql postgres
[sudo] password for dhirsing:
could not change directory to "/home/dhirsing"
psql: FATAL:  database "postgres" does not exist
DETAIL:  The database subdirectory "base/12950" is missing.
-----------------------------------------------------------------------------------------------
I was able to log in to this server before a few days now I am not able to
log in due to above error.
I am sharing error log file for more information, please advise me the
correct process to get support from Postgres support team in case I am not
following the correct process 

Regards,
Dhirendra


Re: BUG #15213: unable to login on PostgreSQL by postgres user

From
Arthur Zakirov
Date:
Hello,

On Tue, May 29, 2018 at 07:32:38AM +0000, PG Bug reporting form wrote:
> [dhirsing@jira-stg ~]$ sudo -u postgres psql postgres
> [sudo] password for dhirsing:
> could not change directory to "/home/dhirsing"
> psql: FATAL:  database "postgres" does not exist
> DETAIL:  The database subdirectory "base/12950" is missing.

I think you missed postgres database directory. Can you check using a
file manager if the directory "base/12950" exists in your PGDATA?

If it is lost the only way is to restore your instance from a backup.

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company


Re: BUG #15213: unable to login on PostgreSQL by postgres user

From
dhirendra Singh
Date:

Thanks for the update, 
you are correct, /12950 is not available in /var/lib/pgsql/9.3/data/base also I do not have the database server or DB backup to restore it.
do I need to create new Postgres DB to resolve it or I need to follow any other step?

FYI, due to a space issue on default (/var/lib/pgsql/9.3) location of DB, I had tried to change data directory location of PostgreSQL and followed below step to change location but to verify data directory location I need to log in by user (postgres) to run (show data_directory) which is not possible due to original error posted in request 😞

1.       Step : stope postgresql service $ sudo systemctl stop postgresql-9.3 and verify by command sudo systemctl status postgresql-9.3

2.       Created new directory/ location for db data ($ sudo mkdir postgresql)

3.       Sudo chown postgres:postgres postgresql

4.       Then copy all data by command (sudo rsync -av /var/lib/pgsql/9.3/data /opt/postgresql/)

5.       Login with (sudo –u postgres –i) on server

  1. Open (vim /opt/postgresql/data/postgresql.conf)
  2. Connented out (#data_directory = 'ConfigDir')
  3. Added new line below above line (data_directory = '/opt/postgresql/data')

6.       Updated (sudo vim /usr/lib/systemd/system/postgresql-9.3.service) by adding (Environment=PGDATA=/opt/postgresql/data) line below line and commented line (#Environment=PGDATA=/var/lib/pgsql/9.3/data/) 7. unable to run show data_directory to verify data directory location due to below error same error 


psql: FATAL:  database "postgres" does not exist
DETAIL:  The database subdirectory "base/12950" is missing. 


Regards,
Dhirendra Singh| 91-9902074990 


From: Arthur Zakirov <a.zakirov@postgrespro.ru>
Sent: Tuesday, May 29, 2018 1:53 PM
To: dhirendra04@hotmail.com; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15213: unable to login on PostgreSQL by postgres user
 
Hello,

On Tue, May 29, 2018 at 07:32:38AM +0000, PG Bug reporting form wrote:
> [dhirsing@jira-stg ~]$ sudo -u postgres psql postgres
> [sudo] password for dhirsing:
> could not change directory to "/home/dhirsing"
> psql: FATAL:  database "postgres" does not exist
> DETAIL:  The database subdirectory "base/12950" is missing.

I think you missed postgres database directory. Can you check using a
file manager if the directory "base/12950" exists in your PGDATA?

If it is lost the only way is to restore your instance from a backup.

--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company

Re: BUG #15213: unable to login on PostgreSQL by postgres user

From
Arthur Zakirov
Date:
On Tue, May 29, 2018 at 10:31:44AM +0000, dhirendra Singh wrote:
> Thanks for the update,
> you are correct, /12950 is not available in /var/lib/pgsql/9.3/data/base also I do not have the database server or DB
backupto restore it.
 
> do I need to create new Postgres DB to resolve it or I need to follow any other step?

Do you have that directory and other data files and directories in
/opt/postgresql/data? Maybe you moved it from /var/lib/pgsql/9.3/data,
not copied.


PS. It was better to post your original message to pgsql-general list to
get wider help.

-- 
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company