c# - Postgres 连接上的 System.Net.Sockets.SocketException

Question

我有一个连接到 Postgres 数据库的应用程序。我有我的连接功能，但每次我尝试连接到数据库时都会出现异常:

{System.Net.Sockets.SocketException (0x80004005): Connection refused at Npgsql.NpgsqlConnector.Connect (Npgsql.NpgsqlTimeout timeout) [0x001f5] in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:733 at Npgsql.NpgsqlConnector+d__153.MoveNext () [0x005a0] in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:648 --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlConnector+d__149.MoveNext () [0x003e1] in C:\projects\npgsql\src\Npgsql\NpgsqlConnector.cs:455 --- End of stack trace from previous location where exception was thrown --- at Npgsql.ConnectorPool+d__19.MoveNext () [0x001cf] in C:\projects\npgsql\src\Npgsql\ConnectorPool.cs:300 --- End of stack trace from previous location where exception was thrown --- at System.Threading.Tasks.ValueTask1[TResult].get_Result () [0x00022] in <1d288dd8ebaf4c6f890e1e99a5a184f0>:0 at System.Runtime.CompilerServices.ValueTaskAwaiter1[TResult].GetResult () [0x00000] in <1d288dd8ebaf4c6f890e1e99a5a184f0>:0 at Npgsql.NpgsqlConnection+<>c__DisplayClass32_0+<g__OpenLong|0>d.MoveNext () [0x0046d] in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:331 --- End of stack trace from previous location where exception was thrown --- at Npgsql.NpgsqlConnection.Open () [0x00000] in C:\projects\npgsql\src\Npgsql\NpgsqlConnection.cs:153 at App1.MainPage.conexao () [0x00054] in C:\Users\Mario\Documents\projetos\App1\App1\App1\MainPage.xaml.cs:40 }

这是我尝试使用的代码:

   private void conexao()
    {
        NpgsqlConnection pgsqlConnection = null;
        var connString = string.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};",
                                             serverName, port, userName, password, databaseName);
        DataTable dt = new DataTable();

        try
        {
            using (pgsqlConnection = new NpgsqlConnection(connString))
            {
                usuario usuario = new usuario();
                // abre a conexão com o PgSQL e define a instrução SQL
                pgsqlConnection.Open();
                string cmdSeleciona = "Select * from funcionarios order by id_funcionario";
                List<string> lista = new List<string>();
                NpgsqlCommand cmd = new NpgsqlCommand(cmdSeleciona, pgsqlConnection);
                var reader = cmd.ExecuteReader();
                while (reader.Read())
                {
                    usuario.nome_funcionario = reader["nome_funcionario"].ToString();
                    usuario.email = reader["email"].ToString();
                }
                //using (NpgsqlDataAdapter Adpt = new NpgsqlDataAdapter(cmdSeleciona, pgsqlConnection))
                //{
                //}
            }
        }
        catch (NpgsqlException ex)
        {
            throw ex;
        }
        catch (Exception ex)
        {
            throw ex;
        }
        finally
        {
            pgsqlConnection.Close();
        }
    }

异常发生在:pgsqlConnection.Open();

Answer 1

这个问题已经开放一年多了，所以我敢于对您可以检查的事情给出一个不太具体的答案...

检查以下事项:

1. Win+R services.msc 或进入taskmanager查看postgresql服务是否启动
2. 运行在浏览器中打开的 PgAdmin。如果 PgAdmin 可以连接并且您可以浏览您的数据库，那么您可以排除很多问题。
3. 检查您的连接字符串并确保用户拥有所有角色(如果您只是想测试，只需通过 pgadmin 授予他所有权限)

如果您使用的是 dockerized 环境，则需要将容器设置到同一网络中，并使用 host.docker.internal 而不是 localhost 作为主机。

这里是我的 linux docker 容器环境的工作连接字符串示例:

host=host.docker.internal;port=5432;database=mydatabase;username=userxyz;password=password123