Recover a POSTGRES username and Password

Recover a POSTGRES username and Password

The following will help you recover your username/pw to a Postgres database so you can convert it to MSSQL format.

Current Process:

1. Log into the server, open Services.msc, find Postgres service, and stop it:
 


2. Right-Click the service, select Properties, then copy the full path to the executable:



You will have a value like this copied:
  1. "C:\Program Files\PostgreSQL\14\bin\pg_ctl.exe" runservice -N "postgresql-x64-14" -D "Z:\Program Files\PostgreSQL\14\data" -w

3. In the script above, two things are really important:
            1. The path to pg_ctl.exe
                 This is the {BINPATH}
            2. The path following the -D parameter
                 This is the {DATAPATH}

4. Follow these instructions to launch a System Command Prompt  as the Network Service user.

5. Run a command like:
  1. "{BINPATH}\postgres.exe" --single -D "{DATAPATH}" postgres
but with the values replaced.  It will look like this: 
  1. "C:\Program Files\PostgreSQL\14\bin\postgres.exe" --single -D "Z:\Program Files\PostgreSQL\14\data" postgres

6. You will see a prompt like this:

Run the following commands, one at a time.  When you do, you will not receive any feedback from the command prompt: 
  1. CREATE USER dataexport WITH PASSWORD 'dataexport';
  1. ALTER USER dataexport WITH SUPERUSER;
Press CONTROL+C

7. Open a file like: 
  1. {DATAPATH}\pg_hba.conf
but with the values replaced.  It will look like this: 
  1. Z:\Program Files\PostgreSQL\14\data\pg_hba.conf

8. Add the following to the end of the file and save it: 
  1. host      all     dataexport    127.0.0.1/32     trust
  2. host      all     dataexport    ::1/128          trust
  3. local     all     dataexport                     trust
  4. #The following is an insecure line that can be used in the event that nothing else works
  5. #Remove the # in front of it to activate it.
  6. #host      all     all           all              trust
9. You have now created a user named dataexport with the password dataexport that can access the data.

10. Restart the PostGres Service.  Repeat step 1 except click Start instead of Stop.

Old Process:


1. Log into the server
2. Open up Services and find the postgresql-x64-12 service.
3. Select "Properties" for the service and note a "Path to Executable" similar to the following:
"C:\Program Files\PostgreSQL\12\bin\pg_ctl.exe" runservice -N "postgresql-x64-12" -D "E:\PostgresDB12" -w
The first path is the install path. The second is the data path.
4. Stop the service.
5. Navigate to the data path and make a backup of pg_hba.conf
6. Edit this file and change "md5" to "trust".
7. Restart the Postgres windows service.
8. Open command prompt and navigate to the install path.
9. Run the following commands:
  1. psql --username=postgres --no-password

  3. CREATE USER dataexport WITH PASSWORD 'dataexport';

  5. ALTER USER dataexport WITH SUPERUSER;

  7. GRANT ALL PRIVILEGES ON DATABASE prevail to dataexport;

  9. exit
10. Restore the backup you made of pg_hba.conf
11. Restart the Postgres service

    • Related Articles

    • Create a POSTGRES Backup

      This article assumes that you know your Postgres credentials and PgAdmin is Installed. Connect into PgAdmin. Right-Click on the database and click Backup Click on the folder icon to choose a location for your backup When you reach the location to ...

    • Installing Postgres SQL

      The following instructions describe how to install PostgresSQL. You will need to complete the following two tasks Install PostgresSql Configure PgAdmin 4 Install PostgresSQL Go to this link and download the most recent version at the top of the list: ...

    • Convert a Postgres database to MSSQL

      Full Convert by Spectral Core is a robust tool for converting databases from one format to another. The following instructions will walk you through using Full Convert to convert a Postgres database to MSSQL. Launch Full Convert Click New database ...

    • Restore a POSTGRES Backup

      Once someone sends you a backup created by following these instructions, you can restore it by following the steps below. Navigate to the location your TAR file is saved at. Extract the TAR File A TAR file is similar to a ZIP file. Using 7Zip, open ...

    • Installing Windows

      Installing Windows If you are installing Windows to a laptop, be certain it is plugged in throughout this process. 1. Run the installer using the media of your choice. 2. Select your language, time zone, and keyboard layout, then click Next 3. Click ...