Printer FriendlyEmail Article Link

Spirent TestCenter: How to change "postgres" user password on PostgreSQL if I forgot it?

Answer
To change the PostgreSQL database admin account, which is also called "postgres" account, you have to do the following. 
If this fails, uninstalling postgres and removing the registry entries (HKEY_LOCAL_MACHINE\SOFTWARE\\PostgreSQL Global Development Group\PostgreSQL) should give you a clean start with the default password.  
 
  • Edit file “pg_hba.conf ” located at: C:\Program Files\PostgreSQL\10\data and set the localhost method (Both IPv4 and IPv6 local connections) to trust instead of md5. Do not forget to save.


 
 
 
  • Open up Services manager and restart postgresql service.

 
 
  • Now running psql will not ask any password when run it in the command prompt.
  • Open up a command prompt and go to Postgres Bin directory: "C:\Program Files\PostgreSQL\10\bin"
  • On that location, execute Postgres using the following command: "psql.exe -U postgres -h localhost" and hit enter.
  • Use the following SQL command to set the postgres user password, replacing "new_password" text for the desired password: ALTER USER postgres WITH PASSWORD 'new_password'; and hit enter.
  • NOTE that your new password has to be written between simple quotations as it is showed on image below
  • If the instruction was correctly applied, you will receive the message "ALTER ROLE" which is confirming that the postgres password has been changed
 
  • Revert the pg_hba.conf localhost method (Both IPv4 and IPv6 local connections) back to md5.
  • Restart postgresql service in Services manager.
  • From this point you can confirm if the password was successfully changed executing again psql in the command line, now you will be prompted to type your current postgres password

​​​​​​
 
  • NOTE: If there are more than 1 postgres database version running on the PC, it will use different port numbers.
    • The C:\Program Files\PostgreSQL\xx\data folder will have a file called "postgresql.conf" which can show you what port number its using. (The default seem to be 5432 )
    • Example is if PostgreSQL v10 was installed first it probably be using TCP port 5432
      • In which case if PostgreSQL v14 was installed after, it probably using TCP port 5433.
    • Running the command "psql.exe -U postgres -h localhost" is going to use TCP port 5432, even if running the command from v14 directory.
      • use "psql.exe -U postgres -h localhost -p 5433" to specify the TCP port number (as mentioned, check the "postgresql.conf" file for correct TCP port number)

Product : Windows GUI,Spirent TestCenter