Troubleshooting Postgres Connection Issues

Hey everybody, my name is Cecil and I'm here with my colleague Jay. We're part of the Posit support team and today we're going to walk through testing Postgres database connections from the command line.

So both Posit Workbench, Connect and also Package Manager use an SQLite3 database by default which is just a file, a text file based database. And at some point you may want to move to Postgres and before you do that a good idea is to check that your Linux terminal can connect to your Postgres host before you try connecting the product.

Using pg_isready to test connectivity

So a good utility that you can use is the pg underscore is ready command. So I have a pre-made example here but effectively you put in your Postgres database details. So using pg underscore is ready the database in this case is Posit. The host name is called Postgres host. The port is 5432 and the user is Postgres. If we hit enter there we'll see that our database is accepting connections.

And in most cases that should be enough to then go into your product configuration file and start migrating your data from SQLite3 to Postgres.

Validating permissions with psql

But something else you can do is to validate that you have permissions into the specific database itself is you can run a psql command. So this is a Postgres command which specifies largely the same details. So you specify your user here which is Postgres port 5432. The host is still Postgres host and the database is Posit. And you'll see that we're connected to Postgres and we can view the schema that way.