Connection refused on connecting to postgresql:dbserver db to Databricks via JDBC connection

Question

I'm trying to connect to a postgresql database on my local machine from databricks using a JDBC connection. There are several useful posts in stackoverflow. I'm following the procedure mentioned in the documentation in spark.apache.org and databricks website.

also these posts : 1 , 2 & 3

In RStudio I can connect to postgresql database via this script :

# Define data base credential ----
psql <- DBI::dbDriver("PostgreSQL")

con <- dbConnect(
  psql,
  dbname = 'mydbname',
  host = 'hostname',
  port = 5444,
  user = 'username',
  password = 'password')

I am trying to connect postgresql via JDBC to databricks. I used two approaches:

    # method 1
    jdbcHostname = "hostname"
    jdbcDatabase = "mydbname"
    jdbcPort = 5444
    
    
    jdbcUrl = "jdbc:postgresql://{0}:{1}/{2}".format(jdbcHostname, jdbcPort, jdbcDatabase)
    connectionProperties = {
      "user" : "username",
      "password" : "password",
      "driver" : "org.postgresql.Driver"
    }
pushdown_query = "SELECT * FROM mytable"
df = spark.read.jdbc(url=jdbcUrl, table= pushdown_query , properties=connectionProperties)

and this one

#method2
jdbcDF = spark.read \
    .format("jdbc") \
    .option("url", "jdbc:postgresql://hostname:5444/mydbname") \
    .option("dbtable", "SELECT * FROM schema.mytable") \
    .option("user", "username") \
    .option("password", "password")\
    .load()

but I am getting error on both tries like this :

Connection to localhost:5432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections

I couldn't understand why it tries to connect to localhost:5432 when I specified localhost:5444?

it was in Spark Apache spark documentation , without dbserver , the error is the connection attempt failed — DanG
– DanG, Commented Jul 1, 2020 at 8:22
In your RStudio example, your username is username. In the others it is user. — Mike Organek
– Mike Organek, Commented Jul 1, 2020 at 8:29
yeah, but the password and username are something else and identical , i will edit my post to avoid confusion — DanG
– DanG, Commented Jul 1, 2020 at 8:32

ziv Daniel · Accepted Answer · 2022-03-19 19:37:56Z

0

Check the following file in intelij "application.properties" for example:

specifically: Username, Password, Port(this case: 5432) and make sure you actually created the database by its name(this case: 'student') before and its consist with this lines for example:

spring.datasource.url=jdbc:postgresql://localhost:5432/student
spring.datasource.username=
spring.datasource.password=
spring.jpa.hibernate.ddl-auto=create-drop
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.dialect = `org.hibernate.dialect.PostgreSQLDialect`
spring.jpa.properties.hibernate.format_sql = true

server.error.include-message=always

edited Mar 19, 2022 at 19:37

answered Mar 19, 2022 at 19:32

ziv Daniel

11 bronze badge

Sign up to request clarification or add additional context in comments.

2 Comments

Alex Ott Over a year ago

Question is about Spark, not Spring

ziv Daniel Over a year ago

I guess I missed it somehow. Thank you for correcting me.

Collectives™ on Stack Overflow

Connection refused on connecting to postgresql:dbserver db to Databricks via JDBC connection

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related