1

I began following https://www.postgresql.org/docs/9.6/runtime.html for setting up a local postgresql server for testing and development purposes. I discovered that I already have the postgresql package installed, a postgres user already exists, and there appears to already be a postgresql server listening on 127.0.0.1:5432.

This is strange, because while I've previously used the psql command to connect to remote postgresql databases, I don't remember ever setting up a local postgresql database.

I tried connecting to the database with psql -h 127.0.0.1 -p 5432 but I was prompted for a password. Entering a blank password did not work. I also tried becoming the postgres user with su postgres, but was again prompted for a password.

I do not know the location of the server's data directory. Creating a Database Cluster recommends using /usr/local/pgsql/data, but /usr/local/pgsql did not exist before I created it while attempting to set up a local server.

My ultimate question is:

  • Why is there a postgresql server running on my local machine?

Some related questions are:

  • How do I locate the server's data directory? (Hopefully it contains logs.)
  • How do I determine when the postgres user was created?
  • How do I recover the passwords for the database and the postgres user?
  • Alternatively, can I view the database's contents without recovering the database password?
  • sudo su postgres – john doe Sep 27 at 17:13
2

It sounds like you ran apt-get install postgresql at some point. In which case, you likely have Debian's default PostgreSQL setup. Some information about this is available on the Debian wiki and some is in /usr/share/doc/postgresql-common/README.Debian.gz.

The Debian setup supports multiple "clusters" (running instances of PostgreSQL, and multiple different versions of PostgreSQL too). To find out which ones are on your machine, run pg_lsclusters:

$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.6 main    5432 online postgres /var/lib/postgresql/9.6/main /var/log/postgresql/postgresql-9.6-main.log

The default setup for authentication (check /etc/postgresql/«version»/«cluster-name»/pg_hba.conf — likely /etc/postgresql/9.6/main/pg_hba.conf) is for local users to use "ident" authentication, i.e., authenticate as their Unix username, without a password prompt, by checking which user is running psql. So to get into the database, this should work:

$ sudo -u postgres psql template1

Your command didn't work because you forced it to use a TCP connection instead of the local Unix socket (you can still use the -p option, but -p 5432 is default, so I omitted it). And the default authentication only works over the Unix socket.

PS: The Debian PostgreSQL setup is pretty nice. I personally use it, and would recommend sticking to it instead of trying to remove it and install upstream packages, unless you really have to (to get a specific version of PostgreSQL, for example).

  • Yep, that's what happened. Thanks for the detailed answer. I don't want a dedicated postgresql server running on my local machine, so I'll probably opt for running one in a Docker container that I can spin up/down as needed. (I'm just working on a web app that uses a postgresql database.) – jth Sep 28 at 0:36
  • @jth you can just stop the server when you don't need it. Of course, there are other advantages to one in a Docker container for dev work. – derobert Sep 28 at 2:39
  • There are also advantages to a non-containerised postgresql running on a physical host or on a VM, if there are (or may be) multiple docker containers needing access to a pg database. BTW, running postgres in a container requires you to set up persistent storage (e.g. with docker run ... --volume= ...) if you want the database(s) to survive when the container shuts down. – cas Sep 28 at 4:02
1

If something is running on port 5432 that doesn't mean it's postgres. It could be anything. If and why postgresql is running? God knows. It could be needed for some application you installed earlier.

Run (as root or sudo):netstat -tlnp and look for port 5432. If you see on PID/Program name tab something like this 1361/postgres it's most likely postgresql.

When was user postgres created? When postgres was installed. Look trough APT history files and maybe you are able to find exact file to investigate further and get the date-time.

for i in $(ls /var/log/apt/history.log*); do echo $i && zcat $i | grep --color postgresql; done

How to reset postgres password: LINK

EDITED: Most likely you need: sudo -u postgres psql.

  • Depends, sudo su - postgres works fine if regular user has sudo rights, su - postgres needs root password. – Jaur Sep 27 at 17:56
  • Have you tried it at least? – Jaur Sep 27 at 17:57
  • I know the differents, it's old habbit to do so (it works btw). Correct way should be sudo -u postgres psql. But, have you tried at least sudo su - postgres? – Jaur Sep 27 at 18:02
  • I should have clarified that I already ran netstat and verified it was postgres. – jth Sep 28 at 0:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.