0

My problem was originally that I am unable to use PostgreSQL because I do not know the password - nor have I ever made one. I was trying to reset or recover the password and followed various advice in trying to do this.

At first I tried to edit the pg_hba.conf file, which I located by using the following command:

sudo vim /etc/postgresql/9.3/main/pg_hba.conf

But this just took me to a blank screen that I could do nothing with except close the window.

I was told to try:

sudo nano /etc/postgresql/9.3/main/pg_hba.conf

...which was better because this included key commands at the bottom of the page, but the file was blank, and so couldn't be edited.

After going back into this, and I suppose causing some error, if I go back into it now, I get this:

E325: ATTENTION
Found a swap file by the name "/var/tmp/pg_hba.conf.swp"
      owned by: root   dated: Tue Oct 17 15:57:30 2017
     file name: /etc/postgresql/9.3/main/pg_hba.conf
      modified: YES
     user name: root   host name: Roberts-MacBook-Pro.local
    process ID: 2668
While opening file "/etc/postgresql/9.3/main/pg_hba.conf"

(1) Another program may be editing the same file.  If this is the case,
be careful not to end up with two different instances of the same
file when making changes.  Quit, or continue with caution.
(2) An edit session for this file crashed.
If this is the case, use ":recover" or "vim -r 
/etc/postgresql/9.3/main/pg_hba.conf"
to recover the changes (see ":help recovery").
If you did this already, delete the swap file 
"/var/tmp/pg_hba.conf.swp"
to avoid this message.

Swap file "/var/tmp/pg_hba.conf.swp" already exists!
[O]pen Read-Only, (E)dit anyway, (R)ecover, (D)elete it, (Q)uit,
(A)bort:

I tried deleting the .swp file by typing D, but this didn't seem to do anything.

I'm really confused about all of this and I don't really know how I can learn more to understand what I'm doing. When I go to the PostgreSQL website I read what the pp_hba.conf file should look like, but the only way I can access this file, it has been completely empty.

I don't know where to go from here so I would really appreciate advice from anyone who can point me in the right direction, thanks.

Dave Gray
  • 715
  • 5
  • 11
Robert
  • 109
  • 3
  • 13
  • this may be worth a read https://stackoverflow.com/questions/7975414/how-to-check-status-of-postgresql-server-mac-os-x – Ian Kenney Oct 18 '17 at 02:40
  • Thanks, but I've already been through this stage. I should have mentioned this. – Robert Oct 18 '17 at 02:42
  • Are you even using PostgreSQL 9.3? And how did you install it? I would be surprised if the configuration was in `/etc` on Mac OS. – Craig Ringer Oct 18 '17 at 03:06
  • Oh silly me - yes it should have been: Library/PostgreSQL/9.6/data/pg_hba.conf – Robert Oct 18 '17 at 03:22
  • But it still takes me to the same error message(above) in vim – Robert Oct 18 '17 at 03:23
  • Or if I choose one of the file options, it just takes me to a empty page - I don't know how to edit the pga.conf file if there is nothing to edit. – Robert Oct 18 '17 at 03:25

1 Answers1

0

As long as PostgreSQL has been started, you can find out which pg_hba.conf you should be editing by running:

ps -ef | grep 'postgres -D'

In my bizarre setup, this returns:

/opt/boxen/homebrew/opt/postgresql/bin/postgres -D /opt/boxen/homebrew/var/postgres

so I know to edit the file:

/opt/boxen/homebrew/var/postgres/pg_hba.conf

to change which connections are allowed to which databases, etc. See also the pg_hba.conf docs for more info.

Dave Gray
  • 715
  • 5
  • 11
  • Thanks, but I've been through these stages and I have the pg_hba.conf file, I'm just having trouble editing it. The docs that you refer to lead me to believe that I would see something on the file, but when I go to it through VIM or Nano, it's completely blank. So I'm trying to work out how to edit this so that I can reset my PSQL password. Any idea? – Robert Oct 18 '17 at 06:24
  • The fact that it's blank means either the file doesn't exist (vim will show `[New File]`), or you don't have permission to edit the file (vim will show `[Permission Denied]`). – Dave Gray Oct 18 '17 at 06:31
  • Actually VIM shows [new directory] at the bottom. What can I do in this case? – Robert Oct 20 '17 at 09:54
  • `[New DIRECTORY]` is what vim will show if one of the directories doesn't exist that's part of the path you're trying to edit.You can confirm this by doing something silly like `vim /foo/bar/baz.txt` and comparing with what you see when you're attempting to edit `pg_hba.conf` - bottom line is that the file you think should exist, doesn't, for whatever reason. – Dave Gray Nov 02 '17 at 04:22