Can't connect to PostgreSQL on SAP CP Trial, Hyperscaler Option

[gregorw]

Hello SAP CP PostgreSQL Experts, Hi manjunath.baburao,

I've provided the new PostgreSQL Hyperscaler Option to my on SAP Cloud Platform Trial account following the Provision PostgreSQL Using Cockpit documentation. I try to access this database following the guide Export Data from PostgreSQL Service Instance. But I only get this error message:

connect to postgres-a213f9cd-6fef-4687-89d2-xxx.xxx.eu-central-1.rds.amazonaws.com:1267 failed: ssh: rejected: connect failed (dial tcp 10.16.47.xxx:1267: connect: connection timed out)

As an alternative I've also deployed an instance of an adminer docker container and tried to connect. But the error message is quite the same:

Unable to connect to PostgreSQL server: could not connect to server: Operation timed out Is the server running on host "postgres-a213f9cd-6fef-4687-89d2-xxx.xxx.eu-central-1.rds.amazonaws.com" (10.16.47.xxx) and accepting TCP/IP connections on port 1267?

The status of the service in the cockpit looks completely fine:

Looking forward to your suggestions.

Best regards
Gregor

[manjunath_b]

Hi Gregor,

Thanks for reaching out. Let me look into the issue with the team. Will revert as soon as I have an update.

Best regards,

Manju

[gregorw]

Any update manjunath.baburao?

[manjunath_b]

Hi Gregor,

The team has identified the issue and is currently in the process of fixing it. I will keep you posted. Sorry for the inconvenience.

The problem seems to be only with trial accounts and productive accounts are not affected.

Thanks and regards,

Manju

[manjunath_b · Accepted solution]

Hello Gregor,

The fix for accessing PostgreSQL,hyperscaler option instances on trial should be available now. Can you please check and confirm if this is working?

I would want to check with you before confirming in other threads.

Thanks and regards,

Manju

[gregorw · Accepted solution]

Hi Manju,

we're making progress. I can connect to the database using adminer. Unfortunately the deploy using Liquibase fails with the error I've documented in db-deployer-java. Maybe you can try the pg-beershop project yourself. Currently you have to enable ssh to the deployer module and start it manually. That's better to debug stuff. Perhaps you have a PostgreSQL expert that can help us there.

Best regards
Gregor

[manjunath_b · Accepted solution]

Hi Gregor,

Thanks for the confirmation. I will check if someone from the team can take a look at the error.

Regards,

Manju

[manjunath_b · Accepted solution]

Hi Gregor,

Looking at the error, can you please confirm if the pg_stat_statements extensions is enabled on your trial instance?

You can use the procedure to retrieve instance configurations: https://help.sap.com/viewer/b3fe3621fa4a4ed28d7bbe3d6d88f036/Cloud/en-US/2df35cb1c431436cb54ae246757...

Regards,

Manju

[gregorw · Accepted solution]

Hi Manju,

yes, I can confirm that the extension is active:

Checking the instance configuration with the procedure Retrieve Configurations of Instance If Required Once the Service Instance is Created doesn't work for me as I don't se a field named "service instance id":

➜  pg-beershop git:(master) cf service pg-beershop-database
Showing info of service pg-beershop-database in org trial / space dev as ...


name:             pg-beershop-database
service:          postgresql-db
tags:             
plan:             trial
description:      PostgreSQL service on SAP Cloud Platform
documentation:    
dashboard:        
service broker:   sm-backing-services-broker-postgresql-db-ccfd42b3-e481-4241-9173-...

Showing status of last operation from service pg-beershop-database...

status:    update succeeded
message:   
started:   2020-11-17T20:54:36Z
updated:   2020-11-17T20:54:36Z

bound apps:
name                      binding name   status             message
pg-beershop-adminer                      create succeeded   
pg-beershop-db-deployer                  create succeeded   
pg-beershop-srv                          create succeeded   

Upgrades are not supported by this broker.

Is it the GUID in the service broker? If yes, then the documentation needs to be updated.

Best regards
Gregor

[manjunath_b · Accepted solution]

Hi Gregor,

Thanks for the confirmation. Will be helpful for further checks.

The service instance id should be listed as part of the 'message' attribute. It does show up for me for my trial instance but somehow seems to be empty in your case.

Regards,

Manju

[gregorw · Accepted solution]

I did deploy my instance using the MTAR that I've created with mbt build.

[gregorw · Accepted solution]

Hi Manju,

I've added the posibility to run pg-beershop locally with PostgreSQL 11. And there the only extension I see active is plpgsql:

So can it be an authorization issue? I face a simmilar problem with PostgreSQL on Azure. But with my own AWS PostgreSQL instance that I've connected via a User Provided Service the deployment using cds-dbm worked just fine.

Best regards
Gregor

[sandeepmalhotra · Accepted solution]

Hi Manju,

I too facing the same issue I followed the steps mentioned by you but unable to see service instance id in message

[manjunath_b · Accepted solution]

Hi Sandeep,

It might be a CF CLI issue. Let me check that.

But meanwhile, you can also get the service instance id using the command:

cf service <service instance name> --guid

Thanks and regards,

Manju

[manjunath_b · Accepted solution]

Hi Gregor, Sandeep,

I realized that when the service instance is in status 'update succeded', like the ones in your case, there is a chance that the 'message' field is shown as empty. So the fool-proof way to retrieve service instance id would be to use the command:

cf service <service instance name> --guid

The documentation will be adapted accordingly.

Thanks and regards,

Manju

[former_member713852 · Accepted solution]

Hello Manju,

I can see the service instance id but can't retrieve the parameters

[gregorw · Accepted solution]

Hi sandeepmalhotra, Hi manjunath.baburao,

with the command

cf service pg-beershop-database --guid

was able to retrieve the GUID and then use the CURL command:

cf curl /v2/service_instances/71f2eba2-f2ee-4e7b-9794-252b4834067a/parameters
{
   "engine_version": "11",
   "locale": "en_US",
   "postgresql_extensions": [
      "ltree",
      "citext",
      "pg_stat_statements",
      "pgcrypto",
      "fuzzystrmatch",
      "hstore",
      "btree_gist",
      "btree_gin",
      "pg_trgm",
      "uuid-ossp"
   ]
}

sandeepmalhotra : I would suggest that you copy & paste the text instead of posting pictures. Text can be searched and that way found easier by others facing the same issue.

Best regards
Gregor

[gregorw · Accepted solution]

Seems we found the reason for the issue ERROR: function pg_stat_statements(boolean) does not exist. Check out:

pg_stat_statements enabled, but the table does not exist

[former_member713852 · Accepted solution]

Hi Gregor / Manju,

I run the below commands and got error

$ cf service sunrice-grower-db --guid
0d087c2b-c911-4e93-9ca5-4cd918ddf639

sande@LAPTOP-K6KAPSHJ MINGW64 /c/Sandeep/CAP/sunrice-grower (master)
$ cf curl /v2/service_instances/0d087c2b-c911-4e93-9ca5-4cd918ddf639/parameters
{
   "description": "Unknown request",
   "error_code": "CF-NotFound",
   "code": 10000
}

Do I miss any steps. Kindly advise.

Regards,

Sandeep

[gregorw · Accepted solution]

mike.zaschka.teamfact has updated cds-dbm and now the deployment works. Check out the instructions at Run on SAP Cloud Platform - Cloud Foundry Environment with Hyperscaler Option for more details.

[alex_yin1 · Accepted solution]

Hi ,Mr Manjunath Baburao

I am also facing a problem about postgreSql connected to Clould Integration(CPI) and I get an error like this:com.sap.it.rt.adapter.http.api.exception.HttpResponseException: An internal server error occured: Connection to postgres-68a0dcb6-e499-42b3-a84e-17db4edc3239.cqryblsdrbcs.us-east-1.rds.amazonaws.com:4046 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections..

The MPL ID for the failed message is : AGLgnrV7_OvkG9Wbo60_j-GKIs8r

The configration of jdbc data source comes from [PostgreSQL on SAP BTP, hyperscaler option] service key.and the same configration of jdbc data source can be running all right on the javaApi.

I do not know where is proble going on and need a help.

Thanks

[former_member713852]

Hello

I deployed using nodejs data-deployer because java version is not working for me.

However service is unable to connect to PostgreSQL service -instance. Instead it is expectiong localhost with 5432 as port no

VCAP details

[manjunath_b]

Hi Sandeep,

I assume this address is configurable in your code. You should configure the code to read from VCAP parameters.

Regards,

Manju

[gregorw]

Hi Manju,

yes, this is an issue which I've just fixed in the pg-beershop Node.JS deployer. But the issue I've posted yesterday remains.

Best regards
Gregor

[david_sooter2]

Hi Gregor,

i was able to connect to an existing PGDB running on AWS.

What i did. was i created a user-provided-service and called it postgres and placed my credentials there.

Then all you have to do is set the name in your package.json file.

as described here: https://cap.cloud.sap/docs/advanced/config

"vcap": { "name": "postgres"}

And thats it. The cds-pg project should find your db config

cheers

[gregorw]

Instead of my own instance in AWS I want to use the SAP provided PostgreSQL instance. By binding the Service to my App I see all the connection information. But unfortunately I can't connect.