PostgreSQL deleted user postgres on accident

Question

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

Created local postgres server through sudo apt-get install postgresql postgresql-contrib. Inside shell, typed psql and was able to initiate db.
Logged into root and deleted postgres account in linux while trying something out. 2a: sudo pkill -KILL -u postgres 2b: sudo userdel postgres
Deleted postgres server. Re-installed. Got user not found when trying to log into postgres user account through shell.
Tried running postgres server through root and got unable to connect to server through shell.
Re-created postgres user account through root: sudo adduser postgres --disabled-password.
Postgres user through root: sudo usermod -a -G sudo postgres
Still getting unable to connect to server: connection refused. Logged into postgres user. No change.
Re-installed postgres. Same error.

Suggestions?

Thanks a bunch.

Am I right to assume that you don't have any valuable data in the database yet? — OscarJ
– OscarJ, Commented Mar 2, 2016 at 0:22
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
– OscarJ, Commented Mar 2, 2016 at 1:20

Patrick · Accepted Answer · 2016-03-02 01:00:49Z

1

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

answered Mar 2, 2016 at 1:00

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Johnathan Over a year ago

Tried pg_lsclusters results in @ Missing argument in printf at /usr/bin/pg_lsclusters line 38. 9.1 main 5432 down /var/lib/postgresql/9.1/main /var/log/postgresql/postgresql-9.1-main.log.

Johnathan Over a year ago

In the end, I ran into problems trying to figure out who was assigned the GID 26. After playing around for a bit, I finally did everything that you guys mentioned but a new bug then showed up. I decided it was easier to just reinstall the OS from that point. Thanks for the help.

frankdoescode · Accepted Answer · 2022-03-19 11:23:11Z

Found the answer, Read below.

Been on another rabbit hole hunt. Heard about the data file, how someone deleted it and it has all kinds of info(config stuff) in there. Also heard how some config files get left around even after uninstalling the program. Not just config files but a lot of other files get left around also with other programs that I have installed and uninstalled.

So here I go to start just deleting a bunch of stuff and then I go to this link to postgresql.org where it shows about where the 'data file' is. Or I think maybe it is the 'data file'. This page says the data file is usually in '/var/lib/pgsql/data'. I uninstalled postgresql so I didn't have a pgsql folder but I saw that there was a 'postgresql' folder there. So I deleted it and now all is well. Now I am able to reinstall postgresql with no problems, no cluster problems or nothing. The 'posgresql' folder had 2 hidden files, I've learned from the past to always check if there are hidden files. These hidden files names were 'bash_history' and 'psql_history' & you know they were somehow stopping a fresh complete installation. After deleting them I was able to reinstall postgresql, with a newly created 'postgres' user waiting for me.

Its 3:43am in the morning & another rabbit hole in the books. Happy hunting.

Here is the link that I mentioned: [https://www.postgresql.org/docs/current/storage-file-layout.html][1]

Collectives™ on Stack Overflow

PostgreSQL deleted user postgres on accident

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related