0

I am unable to start the Postgres server and whenever I use pg_ctl I am getting the following error - can some one help me to fix this. I changed the folder permissions using CHmod and tried running with Sudo -s also but still the problem exists.

one error I did was, I deleted the Postmaster.pid when the server was running- post this I am getting this issue when ever I try to start the server through pg_ctl and another error when I use the pgadmin.

Any suggestions here will be really helpful- thanks.

Using Macos Shell command :

'pg_ctl start -D /Library/PostgreSQL/12/data waiting for server to start....2020-05-05 11:40:04.838 IST [1216] FATAL: data directory "/Library/PostgreSQL/12/data" has wrong ownership 2020-05-05 11:40:04.838 IST [1216] HINT: The server must be started by the user that owns the data directory. stopped waiting pg_ctl: could not start server Examine the log output.'

Using pgadmin the error is as follows :

'could not connect to server: Connection refused Is the server running on host "localhost" (::1) and accepting TCP/IP connections on port 5434? could not connect to server: Connection refused Is the server running on host "localhost" (127.0.0.1) and accepting TCP/IP connections on port 5434?'

p.s. : I modified the hba.conf and also the postgres.conf files to allow connection from the local ip

Error received on 5May

waiting for server to start....2020-05-05 19:54:13.029 IST [7274] LOG: starting PostgreSQL 12.2 on x86_64-apple-darwin, compiled by Apple LLVM version 6.0 (clang-600.0.54) (based on LLVM 3.5svn), 64-bit 2020-05-05 19:54:13.030 IST [7274] LOG: listening on IPv6 address "::", port 5433 2020-05-05 19:54:13.030 IST [7274] LOG: listening on IPv4 address "0.0.0.0", port 5433 2020-05-05 19:54:13.030 IST [7274] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-05-05 19:54:13.039 IST [7274] LOG: redirecting log output to logging collector process... 2020-05-05 19:54:13.039 IST [7274] HINT: Future log output will appear in directory "log" stopped waiting .. pg_ctl: could not start server

Examine the log output.

Log file details

2020-05-05 21:29:30.748 IST [8853] LOG:  invalid authentication method "0.0.0.0/0"
2020-05-05 21:29:30.748 IST [8853] CONTEXT:  line 80 of configuration file "/Library/PostgreSQL/12/data/pg_hba.conf"
2020-05-05 21:29:30.748 IST [8853] FATAL:  could not load pg_hba.conf
2020-05-05 21:29:30.749 IST [8853] LOG:  database system is shut down

Details of my pg_HBA conf 

# "local" is for Unix domain socket connections only
local   all             all            0.0.0.0/0                md5
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.

local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

host all all ::/0 md5

latest log file

bash-3.2$ cat postgresql-2020-05-05_221328.log 2020-05-05 22:13:28.794 IST [9834] LOG: database system was interrupted; last known up at 2020-05-05 22:13:09 IST 2020-05-05 22:13:28.872 IST [9834] LOG: database system was not properly shut down; automatic recovery in progress 2020-05-05 22:13:28.874 IST [9834] LOG: redo starts at 0/17742C8 2020-05-05 22:13:28.874 IST [9834] LOG: invalid record length at 0/1774300: wanted 24, got 0 2020-05-05 22:13:28.874 IST [9834] LOG: redo done at 0/17742C8 2020-05-05 22:13:28.881 IST [9832] LOG: database system is ready to accept connections ...... also I found this error while staring the server and the PID is chaning everytime..

pg_ctl: another server might be running; trying to start server anyway waiting for server to start....2020-05-05 22:09:21.941 IST [9746] FATAL: lock file "postmaster.pid" already exists 2020-05-05 22:09:21.941 IST [9746] HINT: Is another postmaster (PID 9735) running in data directory "/Library/PostgreSQL/12/data"? stopped waiting pg_ctl: could not start server Examine the log output. bash-3.2$ kill -9 9735 bash-3.2$ pg_ctl start -D /Library/PostgreSQL/12/data pg_ctl: another server might be running; trying to start server anyway waiting for server to start....2020-05-05 22:09:35.829 IST [9758] FATAL: lock file "postmaster.pid" already exists 2020-05-05 22:09:35.829 IST [9758] HINT: Is another postmaster (PID 9747) running in data directory "/Library/PostgreSQL/12/data"? stopped waiting pg_ctl: could not start server

Examine the log output.

502 9833 9832 0 10:13PM ?? 0:00.00 postgres: logger
502 9835 9832 0 10:13PM ?? 0:00.00 postgres: checkpointer
502 9836 9832 0 10:13PM ?? 0:00.04 postgres: background writer
502 9837 9832 0 10:13PM ?? 0:00.01 postgres: walwriter
502 9838 9832 0 10:13PM ?? 0:00.01 postgres: autovacuum launcher
502 9839 9832 0 10:13PM ?? 0:00.01 postgres: stats collector
502 9840 9832 0 10:13PM ?? 0:00.00 postgres: logical replication launcher
0 9641 9504 0 10:03PM ttys000 0:00.02 sudo -u postgres -s /bin/bash 502 9904 9642 0 10:37PM ttys000 0:00.00 grep postgres

Radhakrishnan Rajendran
  • 21
  • 1
  • 6

2 Answers2

1

The data directory should be owned by the postgres user and have user-only access (700 or u+rwx)

Does this match what you have set up?

Thom Brown

Disclosure: I am an EnterpriseDB employee.

Thom Brown
  • 1,869
  • 4
  • 11
  • Thanks Thom.. it was having read write access for all users. I changed it and rerun it .. but still I was unable to start the server.. the error I was getting as below .... 2020-05-05 19:02:40.856 IST [6399] LOG: listening on Unix socket "/tmp/.s.PGSQL.5433" 2020-05-05 19:02:40.864 IST [6399] LOG: redirecting log output to logging collector process2020-05-05 19:02:40.864 IST [6399] HINT: Future log output will appear in directory "log". P.S. -- I inadvertently deleted the Postmaster.pid file while the Postgres server was running.. – Radhakrishnan Rajendran May 05 '20 at 13:51
  • I don't see an error message here. This is what I would expect to see. – Thom Brown May 05 '20 at 13:53
  • Hi Thom - have added the latest error in the post as the data is too much ... – Radhakrishnan Rajendran May 05 '20 at 14:26
  • What error message do you get in your latest log file? (under data/log/) Although I suspect you needed to run chown -R postgres on the data directory to recursively change ownership. Perhaps it can't even write to the log file. In which case, you would need to run pg_ctl with --log to get the error message. – Thom Brown May 05 '20 at 14:36
  • Hi Thom - Thanks for helping me -- added the log file and also hba_conf file details – Radhakrishnan Rajendran May 05 '20 at 16:05
  • Okay, so there's an issue with your pg_hba.conf file. Just remove that 0.0.0.0/0 from your local line as it doesn't use IP addresses. That's for unix domain sockets. – Thom Brown May 05 '20 at 16:11
  • Thanks again- I edited it and started the server - but strange enough it is showing a new error and unable to start the server yet .. I added the latest log file and also the error I received for your review Thom.. – Radhakrishnan Rajendran May 05 '20 at 16:50
  • What do you get with "ps -ef | grep postgres" ? If there are any processes showing, you probably have orphaned processes from when you deleted the pid file. You kill need to kill the main process (but don't use -9). Don't delete the pid file. – Thom Brown May 05 '20 at 17:03
  • Added the details Thom – Radhakrishnan Rajendran May 05 '20 at 17:07
  • Yeah, those look like orphaned processes. You can see that the parent PID of each of those processes is 9832, but the main process itself with that PID isn't listed. Kill off those processes (again, not with -9) and try again. – Thom Brown May 05 '20 at 17:10
  • Can you help me with the exact command - so that I would do it.. sorry to be.a pain..when I user kill 9833 or kill 9835, it is not getting killed - I am able to see it still running .. any help would be highly appreciated – Radhakrishnan Rajendran May 05 '20 at 17:16
  • kill 9832; kill 9833; kill 9835; kill 9836; kill 9837; kill 9838; kill 9839; kill 9840 – Thom Brown May 05 '20 at 17:19
  • Awesome - A big Thank you Thom .. Much appreciate your help .. I am able to start the server successfully now . – Radhakrishnan Rajendran May 05 '20 at 17:23
  • As there appear to have been issues with the shutdown previously, I would strongly recommend you use pg_dump to dump each database to ensure there is no corruption. Also might be worth using pg_catcheck to check for catalog corruption. – Thom Brown May 05 '20 at 17:28
  • hi Thom - need some help again - I have posted one more query - psql - unable to execute the command - permission denied error-- could you help me on this as well – Radhakrishnan Rajendran May 06 '20 at 13:38
  • For this issue, I would suggest posting a new case. – Thom Brown May 06 '20 at 15:01
  • Yes Thom.. I have posted a new case - would you have a look in to that if you got some time please .. https://stackoverflow.com/questions/61634805/psql-unable-to-execute-the-command-permission-denied-error – Radhakrishnan Rajendran May 06 '20 at 15:15
0

Try running this code

pg_ctl -D /usr/local/var/postgres start

Gunjan Dave
  • 161
  • 1
  • 2
  • 10