部署在Shiny Server上的Shiny应用如何从PostgreSQL加载数据？

Hey there, let's break down how to fix this issue step by step—first we'll tackle the empty logs (since without those, we're flying blind), then we'll address the PostgreSQL connection problems in your app code.

First: Fix the Empty Logs (Critical for Debugging) #

If /var/log/shiny-server is empty, Shiny Server can't write error details, so we need to fix that first:

• Check Shiny Server config: Open /etc/shiny-server/shiny-server.conf and make sure there's a line like log_dir /var/log/shiny-server; (this is usually default, but double-check the path is correct).
• Fix directory permissions: Shiny runs as the shiny system user, so ensure it has write access to the log folder:

  sudo chown shiny:shiny /var/log/shiny-server
  

• Restart Shiny Server:

  sudo systemctl restart shiny-server
  

Now try loading your app again—you should see log files appear with error details that will point us to the root cause.

Second: Fix PostgreSQL Connection Issues in Your App #

Looking at your simplified code, here are the most likely culprits and fixes:

1. Use pool Properly (Don't Directly Use dbConnect) #

You loaded the pool package but didn't use it to create a connection pool—this is a common mistake in Shiny Server environments. Direct dbConnect calls can lead to unclosed connections, connection limits, or unexpected disconnects. Replace your connection code with this:

# Create a connection pool (instead of raw dbConnect)
pool <- pool::dbPool(
  drv = odbc::odbc(),
  Driver = "PostgreSQL",
  Database = "db_name",
  Server = "my_server",
  UID = "user",
  PWD = "password",
  Port = 5432,  # Use numeric instead of quoted string to avoid type issues
  minSize = 1,
  maxSize = 10
)

# Clean up the pool when the app session ends
onStop(function() {
  pool::poolClose(pool)
})

2. Ensure the shiny User Can Access PostgreSQL #

Shiny runs as the shiny system user, so you need to grant this user access to your PostgreSQL database:

• Local database: Edit your pg_hba.conf file (location varies—common paths are /var/lib/pgsql/data/pg_hba.conf or /etc/postgresql/<version>/main/pg_hba.conf). Add this line to allow the shiny user to connect locally:

  local   db_name   user   md5
  

  Then restart PostgreSQL:

  sudo systemctl restart postgresql
  

• Remote database: Make sure your PostgreSQL server's firewall allows incoming traffic on port 5432 from your Shiny Server's IP. Also add this line to pg_hba.conf:

  host    db_name   user   <shiny-server-ip>/32   md5
  

  Restart PostgreSQL after making changes.

3. Verify R Package Installation for the shiny User #

Sometimes packages installed under your personal user account aren't accessible to the shiny user. Install the required packages as the shiny user:

sudo su - shiny
R
install.packages(c("odbc", "RPostgreSQL", "pool", "sf", "leaflet", "DT"))
q()

4. Validate Database Connection Parameters #

• Test connectivity from the command line: Run this on your Shiny Server to confirm you can reach PostgreSQL:

  psql -h my_server -U user -d db_name -p 5432
  

  Enter your password—if this fails, the problem is with your database setup, not Shiny.
• Use localhost for local databases: If PostgreSQL is on the same machine, replace my_server with 127.0.0.1 or localhost to avoid DNS resolution issues.
• Remove quotes from port: Your code uses port = "5432"—change this to Port = 5432 (numeric value) to prevent type mismatches.

5. Optimize Data Loading in Your App #

Avoid running dbGetQuery directly in the shinyServer function—this runs when the app starts, which can block initialization or cause errors if the database is slow to respond. Instead, use a reactive expression:

# Load data reactively (only when needed)
test_data <- reactive({
  dbGetQuery(pool, "SELECT * FROM myschema.mytable")
})

output$table1 <- DT::renderDataTable({
  datatable(test_data(), 
            selection = 'single', 
            rownames = FALSE, 
            editable = TRUE, 
            extensions = c("Scroller"), 
            filter = 'top')
})

output$map <- renderLeaflet({
  leaflet(test_data()) %>% 
    addMarkers(lng = ~as.numeric(longitude), lat = ~as.numeric(latitude)) %>% 
    addTiles(group = "OSM par défaut") %>% 
    addProviderTiles(providers$Esri.WorldImagery, group = "Esri World Imagery")
})

Final Testing Steps #

1. Fix the log directory first and confirm logs are being written.
2. Test PostgreSQL connectivity from the command line to rule out database-side issues.
3. Update your app code with the connection pool and reactive data loading.
4. Restart Shiny Server and reload your app—check the logs if you still have issues, they should now give you specific error messages to work with.

内容的提问来源于stack exchange，提问作者zakros