I am not from a PostgreSQL background and wanted to use it for a project I am currently working on as it was the recommended DB for what I am using.
I use PgAdmin 4 to visualise the database, and my literal understanding of "shut down server" on that application was it shut down the database, however it does not. So I wanted to learn how to start/stop/restart the server at the start of the project so I was better equipped to manage it later down the line.
tl;dr: What is the correct way/command to start a DB, on OSX, not using homebrew, and with it's default settings/directory structure?
The beginning of my trial and errors...
After much reading on many websites I found the way to stop the server, a way which worked for me personally – not using Homebrew and on OSX
Stopping DB
$ sudo -u postgres pg_ctl -D /Library/PostgreSQL/12/data/ stop
could not identify current directory: Permission denied
waiting for server to shut down.... done
server stopped
Super!
The DB stopped. The website wasn't working, as was excepted.
Attempting to start...
Now, naturally trying to start it back up again with
$ sudo -u postgres pg_ctl -D /Library/PostgreSQL/12/data/ start
Yeilds...
could not identify current directory: Permission denied
could not identify current directory: Permission denied
could not identify current directory: Permission denied
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "pg_ctl".
Check your installation.
Uh oh...
So pg_ctl is not in the same directory... perhaps I need to specify it's path in the command
$ sudo -u postgres /Library/PostgreSQL/12/bin/pg_ctl -D /Library/PostgreSQL/12/data/ start
could not identify current directory: Permission denied
could not identify current directory: Permission denied
could not identify current directory: Permission denied
The program "postgres" is needed by pg_ctl but was not found in the
same directory as "pg_ctl".
Check your installation.
Attempt #1
Trying something else, omitting the -D as I might be overcomplicating things...
$ sudo -u postgres pg_ctl start
could not identify current directory: Permission denied
pg_ctl: no database directory specified and environment variable PGDATA unset
PGDATA isn't set... I have it set on my user, so I guess it isn't set for the postgres user. Let try that then
$ sudo -u postgres export PGDATA="/Library/PostgreSQL/12/data/"
sudo: export: command not found
Okay so it's not that. WELL WHAT THEN?!
Reading further I see some using initdb (credit to the final update in this Q) rather than start so I thought I'd give that a go. Nothing else seems to be working so lets try it
$ sudo -u postgres /Library/PostgreSQL/12/bin/pg_ctl -D /Library/PostgreSQL/12/data/ initdb
could not identify current directory: Permission denied
could not identify current directory: Permission denied
could not identify current directory: Permission denied
The program "initdb" is needed by pg_ctl but was not found in the
same directory as "pg_ctl".
Check your installation.
Attempt #2
Maybe rearrange the command a little bit...
$ sudo su postgres pg_ctl initdb -D /Library/PostgreSQL/12/data/
shell-init: error retrieving current directory: getcwd: cannot access parent directories: Permission denied
/Library/PostgreSQL/12/bin/pg_ctl: /Library/PostgreSQL/12/bin/pg_ctl: cannot execute binary file
Reoccurring theme, what is all this "cannot access directories"? I am running sudo and the postgres user as is everyone else in everything I've read! Why does it not work in my case?..
Attempt #3
$ ls -la /Library/PostgreSQL/12/data/
ls: : Permission denied
Makes sense... I recall trying to access that /data dir before and it was limited to the postgres user.
So let me try doing what I've experimented with so far and attempt ls as postgres
$ sudo -u postgres ls -la /Library/PostgreSQL/12/data/
total 112
[...]
What is this madness?! It works, I have access but I cannot run any of those commands I need to start my DB! sudo has access to all; postgres has access to that dir...
Attempt #4
Lets check some other things.
sudo -u postgres which pg_ctl
/Library/PostgreSQL/12/bin/pg_ctl
So postgres has pg_ctl, and yet cannot run it.
Maybe lets try as my user account, forgetting the postgres user
$ pg_ctl initdb --pgdata=/Library/PostgreSQL/12/data/
The files belonging to this database system will be owned by user "Freemium".
**This user must also own the server process.**
The database cluster will be initialized with locale "en_GB.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".
Data page checksums are disabled.
initdb: error: could not access directory "/Library/PostgreSQL/12/data": Permission denied
pg_ctl: database system initialization failed
So it cannot be my user account and I don't even want to own that process anyway, it's what the postgres user is for.
Attempt #5
Lets try using root – at this point, why not.
$ sudo pg_ctl initdb --pgdata=/Library/PostgreSQL/12/data/
pg_ctl: cannot be run as root
Please log in (using, e.g., "su") as the (unprivileged) user that will
own the server process.
Nope, even root doesn't want it. In both instances it's hinting that it must be the postgres user. So why tf is it not working???
Attempt #6
At this point I just wondered what would happen if I ran the original stop command
$ sudo -u postgres pg_ctl -D /Library/PostgreSQL/12/data/ stop
could not identify current directory: Permission denied
Hmm... Again with the current directory permissions...
