Million dollar Post- PgAgent-Postgresql error- "could not get a connection to the database"

If you’ve ever had to configure a PgAgent job in pgAdmin4 to run a stored procedure or execute a query, let me tell you—it can be a real nightmare, especially if you’re used to how easy it is with SQL Server Agent jobs.

If you don’t know this crucial piece of information, you’re bound to waste hours the first time around.

Here’s the deal: PgAgent doesn’t come installed by default like SQL Server’s agent service, where you just check a box. You’ll need to install PgAgent separately on your PostgreSQL server, either via direct download or through a package.

Typically, we install PgAgent under the postgres superuser or any other superuser. But here’s where it gets tricky—after you’ve done that, if you try to run any query or stored procedure via Pgagent job on a database other than the postgres database, you’ll likely run into the error "could not get a connection to the database."

Trust me, this is where most people start scratching their heads.

Here’s what you need to do to fix this issue step by step:

1. Locate the pgpass file:

• Navigate to the following location on your server:

C:\Users\postgres\AppData\Roaming\postgresql

(Note: The location might vary depending on the user account selected during PgAgent installation.)

2. Edit the pgpass file:

• Open the pgpass file in a text editor.
• You’ll find an entry for the database name, which is typically set to postgres:

Format reference: hostname:port:database_name:username:password

Format example hostname:port:postgres:username:password

3. Modify the file content to

hostname:port:*:username:password

removed the database name and put *

4. Save the changes:

5. Restart PgAgent service

Now you will be able to execute the PgAgent Job against any database

Happy Learning