Check for reachability of a PostgreSQL DB

[nadja]

We've got a PostgreSQL database online which is available via a url and login. Before trying to access the database using an sde-file, I'd like to verify that the database is actually reachable from my current ArcGIS Pro project. What's the fastest way to do that?

I tested using the actual credentials, but that takes ages compared to checking with a bad login. But the bad login method sometimes opens the default database connection dialog which is not good practice. 

I could verify externally (cmd, python, etc.) but I'd much rather perform the check from within the AddIn. Does anyone know a fast way to check if a given database is reachable?

thanks

[JeffSilberberg]

If I understand your queston - " Before trying to access the database using an sde-file," 

I would look at this documentation -  
Manage connections to a geodatabase in PostgreSQL

[SumitMishra_016]

using Npgsql;
using System;
using System.Threading;
using System.Threading.Tasks;

public static async Task<bool> CanConnectWithCredentialsAsync(string host, int port, string database, string user, string password, int timeoutSeconds = 3)
{
    // Build a connection string tuned for fast failure (timeoutSeconds)
    var csb = new NpgsqlConnectionStringBuilder
    {
        Host = host,
        Port = port,
        Database = database,
        Username = user,
        Password = password,
        Timeout = timeoutSeconds,        // connection timeout (seconds)
        CommandTimeout = timeoutSeconds, // command timeout (seconds)
        Pooling = false                  // avoid pooled connections staying open
    };

    using var cts = new CancellationTokenSource(TimeSpan.FromSeconds(timeoutSeconds + 1));
    try
    {
        await using var conn = new NpgsqlConnection(csb.ConnectionString);
        await conn.OpenAsync(cts.Token).ConfigureAwait(false);

        // optional: run a tiny, fast query to ensure DB responds
        await using var cmd = new NpgsqlCommand("SELECT 1", conn);
        cmd.CommandTimeout = timeoutSeconds;
        var result = await cmd.ExecuteScalarAsync(cts.Token).ConfigureAwait(false);
        return result != null;
    }
    catch (Exception)
    {
        // log exception if you want; return false on any failure
        return false;
    }
}

[SelimDissem]

While the above code checks if the connection to Postgres is working you could precede it by checking if the URL is accessible altogether using .NET only, like:

using var client = new WebClient();
string html = client.DownloadString("<Postgres URL>");
Console.WriteLine(html);

.NET has other options that you can explore to connect to Web APIs that might be more up to date than the above snippet.

Something even more basic would be to do a ping programmatically: Ping Class (System.Net.NetworkInformation) | Microsoft Learn

This could allow you to "fail faster" if the server is down or inaccessible.