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:
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:
pg_isready -d <db_name> -h <host_name> -p <port_number> -U <db_user>
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).
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.
Once you've gotten confirmation from pg_isready, you can begin connecting your RStudio server to the database. Our documentation can be found here:
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: