PostgreSQL deleted user postgres on accident

0
votes

Cannot find any tutorials that show what you do when you deleted default postgres user through root.

  1. Created local postgres server through sudo apt-get install postgresql postgresql-contrib. Inside shell, typed psql and was able to initiate db.

  2. Logged into root and deleted postgres account in linux while trying something out. 2a: sudo pkill -KILL -u postgres 2b: sudo userdel postgres

  3. Deleted postgres server. Re-installed. Got user not found when trying to log into postgres user account through shell.

  4. Tried running postgres server through root and got unable to connect to server through shell.

  5. Re-created postgres user account through root: sudo adduser postgres --disabled-password.

  6. Postgres user through root: sudo usermod -a -G sudo postgres

  7. Still getting unable to connect to server: connection refused. Logged into postgres user. No change.

  8. Re-installed postgres. Same error.

Suggestions?

Thanks a bunch.

1
postgresqlubuntu
Am I right to assume that you don't have any valuable data in the database yet? - OscarJ
@OscarJ Yeah, no valuable data. - Johnathan
The configuration file should tell you in which directory postgres keeps its data. When you uninstall it, it probably doesn't remove that data directory. So unistall, remove the data directory manually, then reinstall. That might work. - OscarJ

1 Answers

1
votes

You don't exactly need a user who is named postgres, you need a user who owns the data directory, the binaries and configuration files. You should check which uid and gid are now associated with your data directory, then create a user with that specific uid and gid. But check who those id's belong to first. If no user currently has those uid and gid then create a new user with those values:

useradd -u 123 -g 456 postgres

Alternatively, re-create user postgres and assign data directory and binaries to that new user (assuming group postgres still exists):

useradd -G postgres postgres
chown -r postgres:postgres $PGDATA
chown -r postgres:postgres /wherever/bin/is
chown -r postgres:postgres /wherever/conf/is