Support

Troubleshooting Postgres Connection Issues

Follow

Many users of RStudio Workbench & RStudio Connect like to migrate to a PostgreSQL database, as opposed to the default SQLite3 database. Doing so can introduce some new challenges to your infrastructure.

Technically, PostgreSQL is outside of the bounds of RStudio support. However, there are some pointers that we can provide, as this is a very common implementation of the RStudio teams suite.

 

Allow access to the database

By default, a standard PostgreSQL installation will block all inbound connections at the operating system, regardless of your firewall settings. You will need to allow access by modifying your pg_hba.conf configuration file. You will need to add a line that looks something like this:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host  all  all  127.0.0.1/32  trust

This allows all users access to all databases on the localhost. Different organizations will have different security protocols, so it's best to reach out to your organization's security team to ensure that you have implemented the correct settings. More information on the configuration options can be found here:

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

Then, restart PostgreSQL for these changes to take effect.

 

From there, it's important to ensure that you have allowed the necessary inbound firewall rules for your PostgreSQL server. By default, PostgreSQL listens on port 5432. 

 

Verify database access

Now is a good time to verify access to the PostgreSQL database. Prior to making any configuration changes to RStudio products, verify access from your RStudio servers command line, using the pg_isready utility as below: 
 


Example Usage:

pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user>                      

Exit Status

pg_isready returns the following to the shell:

0 - if the server is accepting connections normally, 
1 - if the server is rejecting connections (for example during startup),
2 - if there was no response to the connection attempt, and
3 - if no attempt was made (for example due to invalid parameters).

Notice: man pg_isready states: It is not necessary to supply correct user name, password, or database name values to obtain the server status; however, if incorrect values are provided, the server will log a failed connection attempt.

 

Connect RStudio

Once you've gotten confirmation from pg_isready, you can begin connecting your RStudio server to the database. Our documentation can be found here:

RStudio Workbench

RStudio Connect

 

 

Support Ticket

If you still have issues after completing the above, you can always lodge a support ticket, where our group of friendly, and incredibly knowledgeable staff can assist with any issues that you may be having. You can submit a ticket here:

https://support.rstudio.com/hc/en-us/requests/new

 

Comments