How to reset PostgreSQL password

Resetting my PostgreSQL root user password

This process will disrupt access to your system.

During this process, if you received the message No such file or directory, you may need to enter the following command which puts it on the path environment variable. This is necessary once per shell session. source /usr/local/groundwork/scripts/setenv.sh

1. Stop gwservices with the command: /usr/local/groundwork/ctlscript.sh stop gwservices
   Example:

   [root@training67~]# /usr/local/groundwork/ctlscript.sh stop gwservices
   Stopping GroundWork Services:                              [ OK ]

2. Stop httpd with the command: /usr/local/groundwork/ctlscript.sh stop apache
   Example:

   [root@training67~]# /usr/local/groundwork/ctlscript.sh stop apache
   Syntax OK
   /usr/local/groundwork/apache2/scripts/ctl.sh : httpd stopped

3. Update the file /usr/local/groundwork/postgresql/data/pg_hba.conf to trust for local connections:
   Example:

   [root@training67~]# vi /usr/local/groundwork/postgresql/data/pg_hba.conf

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
     host  all             all             127.0.0.1/32            trust

4. Restart postgres with the command: /usr/local/groundwork/ctlscript.sh restart postgresql
   Example:

   [root@training67 /]# /usr/local/groundwork/ctlscript.sh restart postgresql
   waiting for server to shut down.... done
   server stopped
   /usr/local/groundwork/postgresql/scripts/ctl.sh : postgresql stopped
   waiting for server to start.... done
   server started
   /usr/local/groundwork/postgresql/scripts/ctl.sh : postgresql  started at port 5432

5. Open a psql session as the postgres user (you won't be asked for the password under TRUST authentication) to the database:

   [root@training67 /]# psql -U postgres

6. Execute the command:

   postgres=# ALTER USER postgres WITH PASSWORD '<newpassword>';

7. Leave the session:

   postgres=# \q

8. Undo the settings to trust in the file: /usr/local/groundwork/postgresql/data/pg_hba.conf

   # TYPE  DATABASE        USER            ADDRESS                 METHOD
     host  all             all             127.0.0.1/32            md5

9. Restart gwservices with the command: /usr/local/groundwork/ctlscript.sh restart gwservices
10. Restart httpd with the command: /usr/local/groundwork/ctlscript.sh restart apache
11. Restart postgres with the command: /usr/local/groundwork/ctlscript.sh restart postgresql
12. You should now be able to connect using the new password.