connect shiny server to a remote postgreSQL database

[SPC]

I would like to create a web tool for analysing data.
My application is on a shiny server and datas are on a remote postgreSQL server.
I first create my app on a local machine. Here is my code :

Server.R

library(shiny)
library(RPostgreSQL)

shinyServer(function(input, output){
  inputcc <- reactive({
    input$cc
  })
 
  dbConn <- function(inputCode,out){
    con <- dbConnect(dbDriver("PostgreSQL"), user="user", password="pwd", dbname="bd", host = "localhost", port = 3535)
    on.exit(dbDisconnect(con), add=TRUE)
    query <- "SELECT kentite, lbcapteur, appareil FROM capteur WHERE kentite ="                   
    stmt <- paste(query,input$cc)
    res <- dbSendQuery(con, statement = stmt)
    out <- fetch(res, n = -1)
    }
 
  output$out1 <- renderTable(
    dbConn(inputCode,out)
    )
  }
)


ui.R

library(shiny)

shinyUI(pageWithSidebar(
  headerPanel("Capteur"),
  sidebarPanel(
    textInput("cc", "Capteur:",value=182)
    ),

  mainPanel(
    tabsetPanel(
      tabPanel("Code capteur", tableOutput("out1"))
      )
    )
  )
)

On the local machine this code works fine if I create a tunnel between the local machine and the remote postgresQL server.
But when I try to deploye this app on the shiny server (or on Rstudio Server) I am not able to create a tunnel so I've got the current message :
"Listening on http://127.0.0.1:3052
Warning: Error in postgresqlNewConnection: RS-DBI driver: (could not connect user@localhost on dbname "bd") "
I wonder if someone could help me
thank you
Antonin

[Stephen]

Hi Antonin,

You are using a local database on your computer while running it from local RStudio. That is what 127.0.0.1 is referring to, your local computer. 

You must refer to your database server as a public IP address (go to myip.com to see your computers CURRENT IP address) and be able to access the database from a remote machine (you must have open firewall ports, allow remote database connections on the Postgres Database, etc)

That is probably why your job fails on the deployed version. 

Best regards,
Stephen McDaniel

Chief Data Scientist

PowerTrip Analytics

Automatic data, analytic & visualization solutions in the cloud.

https://PowerTripAnalytics.com 

Sent from my iPhone, please excuse typos or absurd word "corrections"

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.
To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/shiny-discuss/c54a6f8f-4553-407d-9b52-f1d9ded5304c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Stephen McDaniel]

Apologies, spammers appear to have taken myip.com

Use http://ip4.me/

[SPC]

Thank you for your answer but I think I was not clear enough.
I don't use a local database. I used localhost because I made a ssh tunnel to a specific port on the local machine to connect to the remote postgres server. I don't have database on the local machine.
I think it works fine on local machine because of the ssh tunnel. This is why I would like to make the same on shiny server

[Erik Westlund]

Why not just connect directly to the database? Is the encryption from the SSH tunnel mission critical?

If it works on localhost with your setup, just configure a similar tunnel on your VPS.

Erik

--

You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.

To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discuss+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/shiny-discuss/080e368d-78e9-4b12-93e3-8c92fe335235%40googlegroups.com.

[SPC]

but I don't know how to create a tunnel from my app. When I create it directly from the server, it doesn't work much better. The connection ssh works fine but not the connection to the database. Maybe the app doesn't use the tunnel ?!

Antonin

Le mardi 27 décembre 2016 17:03:40 UTC+1, Erik Westlund a écrit :

Why not just connect directly to the database? Is the encryption from the SSH tunnel mission critical?

If it works on localhost with your setup, just configure a similar tunnel on your VPS.

Erik

On Tue, Dec 27, 2016 at 10:01 AM, SPC <sppr....@gmail.com> wrote:

Thank you for your answer but I think I was not clear enough.
I don't use a local database. I used localhost because I made a ssh tunnel to a specific port on the local machine to connect to the remote postgres server. I don't have database on the local machine.
I think it works fine on local machine because of the ssh tunnel. This is why I would like to make the same on shiny server


Le mardi 27 décembre 2016 16:50:44 UTC+1, Stephen McDaniel a écrit :

Apologies, spammers appear to have taken myip.com

Use http://ip4.me/

--
You received this message because you are subscribed to the Google Groups "Shiny - Web Framework for R" group.

To unsubscribe from this group and stop receiving emails from it, send an email to shiny-discus...@googlegroups.com.