Createdb: database creation failed: ERROR: role "<username>" does not exist

Products

• CA Application Performance Management Agent (APM / Wily / Introscope)
• INTROSCOPE

Issue/Introduction

During an upgrade from 10.5.2.15 to 10.7.0.45, I've manually installed the PostgreSQL 9.6.2 using the install-postgres.sh script in
 /<em_home>/install/database-install/linux. When I tried to restore the <database_name> backup copy, it failed with the following error:

[apmadmin unix]$ ./dbrestore-postgres.sh localhost /apmdb/PostgreSQL-9.6.2/ postgres  <db name> <username> <password> 5432 /apmdb/dbbackup/cemdb.backup

createdb: database creation failed: ERROR: role "<role_name>" does not exist
Restoring the database data. The restore may take a long time depending on the size of the backup file.
pg_restore: invalid option -- 'i'
Try "pg_restore --help" for more information.
Upgrading the database

[INFO] [main] [root] [UpgradeSchema] - DBDetails: hostName: <host> port: 5432 databaseName:<database_name> user: <user>

[INFO] [main] [root] [UpgradeSchema] - DBType: postgres
[INFO] [main] [root] [UpgradeSchema] - Desired Version: 10.5.2.0
[INFO] [main] [root] [UpgradeSchema] - Scripts location: ../
[INFO] [main] [root] [UpgradeSchema] - Postgres installation directory: /apmdb/PostgreSQL-9.6.2/
[ERROR] [main] [root] [UpgradeSchema] - FATAL: password authentication failed for user "<user>"
org.postgresql.util.PSQLException: FATAL: password authentication failed for user "<user>"
at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:398)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:173)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
at org.postgresql.Driver.makeConnection(Driver.java:393)
at org.postgresql.Driver.connect(Driver.java:267)
at java.sql.DriverManager.getConnection(DriverManager.java:664)
at java.sql.DriverManager.getConnection(DriverManager.java:247)
at com.wily.apm.dbutil.SqlExecutor.getConnection(SqlExecutor.java:87)
at com.wily.apm.dbtools.upgradeschema.UpgradeSchema.upgradeSchema(UpgradeSchema.java:204)
at com.wily.apm.dbtools.upgradeschema.UpgradeSchema.main(UpgradeSchema.java:158)

[ERROR] [main] [root] [UpgradeSchema] - Error creating schema
Doing VACUUM ANALYZE
psql.bin: FATAL: database "<database_name>" does not exist
Database restoration is done

 

Environment

CA Application Performance Management 10.7.0.45

Cause

User "<username>" required to run the restore script, was not created by the install-postgres.sh script.

Resolution

The normal and recommended practice is to use the EM installer to install the PostgreSQL, which it will create the user "admin" along with the cemdb instance.

To work around the issue in this case, you can manually create the user "admin" using PgAdmin > PostgreSQL-9.6.2 > Login Roles.
The user admin does not require SuperUser privilege, the default privilege is sufficient.

Once the user is manually created, re-run the restore command again.

Additional Information

Login/Group Role Dialog
Install and Configure the APM Database