I have been trying to understand the whole PostgreSQL configuration thing for the past 16 hours. I studied the migration guides, I did a dozen (failed) test migrations on Windows and Linux boxes, switched from MSSQL to PostgreSQL, etc, and I am slowly pulling my hair out. When I google these forums, the only thing that comes up is these forums, where support mentions 'send us the logs' but people never post any answers. Really frustrating, and I think that is what a public forum is trying to solve!

I started out with a single windows SDP installation with a remote database, and I wanted to migrate to the local postgresql instance. But I can't get the local postgresql instance running.

I made a backup of the current MSSQL db (which worked after I gave the sdp db user sa rights) with the backupData.bat script. At least I have some ezip files that are a few hundred megs.

My goal was to create the local psql database, and then restore the backup again.

So I deleted the pgsql\data folder, and I ran the command 'initpgsql.bat postgres' to initialize a database in the pgsql\data folder with the username postgres and the password postgres. That works fine. I can start the db, and then connect to it (on port 5432)

Then I create the database servicedesk, and add a user sdpadmin with password sdp@123, and grant it full access to the database. Works fine. I then shut down the database.

Then I run the changeDBServer.bat script to tell SDP to use this database. I tell it to connect to 127.0.0.1 port 65432, database servicedesk, user sdpadmin, pass sdp@123. I want it to use the normal user, not the superuser, right? When I click save, I see the java code starts the postgresql database on port 65432, 

It spits out some obscure error in the 'setupdb' module, message "Problem while creating version file". In the pg_log I see a critical error on "relation dbcredentialsaudit does not exist at character 15". Oh well, I guess not all the tables have been initialized? Let's run run.bat to have it fix all these tables.

Fatal: password authentication failed for user postgres. 

What? Where did that come from? I never configured the password for the user postgres? I do see a key superuser_password in database_params.conf that came from somewhere.

Hmm.. Perhaps if I configured postgres/postgres like in the config template? Well, no, that spits out the error message "username cannot be postgres. Hint: DB connecting user cannot be a super user. Provide a normal user credential."

Can anyone shed some light on this?