Обсуждение: Slow or Cannot Connect to PostgreSQL Instance Service on Windows 10

Hi,

I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit).
In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).
By two or three times I mean, if this slow connection problem happen, than it wil occur in a view days in a row. Then it will return to normal with fast login/connection process.

I've try to restart the laptop, restart the service but nothing give the sign to improve the slow login/connection process.

I've also try to turn off the log_hostname config in postgresql.conf and try to turn off the Windows Firewall, Windows Defender Real-Time Scanner but nothing works to improve the slow login connection.

I've also try to check the Postgres Log file but not much error shows in the log except for this error that appears a few time:
2020-07-22 14:43:22.312 +00 [13740] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

This problem happens with all of the application such as psql, pgAdmin, Navicat, all with the same symptoms of slow login/connection to my PostgreSQL v12.3 (x64) service and if connected, then it will be easy to disconnected.

If needed my laptop specs are:

• Intel Core i7-9750H
• 16GB of RAM
• Samsung SSD 970 EVO Plus 2TB
  

Is database size can affect this connection time required to start a database session?
FYI one of my database has size of 209GB, but currently I'm not using or connect to that database.

Thanks in advance.

Regards,
Surya

On 7/22/20 10:24 AM, Surya Widyanto wrote:
> Hi,
> 
> I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 
> Home Single Language (64-bit).
> In two or three times since I fresh install the PostgreSQL and windows 
> on 17-05-2020, 06:04:56 (GMT).
> By two or three times I mean, if this slow connection problem happen, 
> than it wil occur in a view days in a row. Then it will return to normal 
> with fast login/connection process.
> 
> I've try to restart the laptop, restart the service but nothing give the 
> sign to improve the slow login/connection process.
> 
> I've also try to turn off the *log_hostname* config in *postgresql.conf* 
> and try to turn off the *Windows Firewall, Windows Defender Real-Time 
> Scanner* but nothing works to improve the slow login connection.
> 
> I've also try to check the Postgres Log file but not much error shows in 
> the log except for this error that appears a few time:
> 2020-07-22 14:43:22.312 +00 [13740] LOG:  could not receive data from 
> client: An existing connection was forcibly closed by the remote host.

Is the database server you are connecting to on the laptop or some other 
machine?

Where is this Postgres log?

> 
> This problem happens with all of the application such as *psql*, 
> *pgAdmin*, *Navicat*, all with the same symptoms of slow 
> login/connection to my PostgreSQL v12.3 (x64) service and if connected, 
> then it will be easy to disconnected.
> 
> If needed my laptop specs are:
> 
>   * Intel Core i7-9750H
>   * 16GB of RAM
>   * Samsung SSD 970 EVO Plus 2TB
> 
> Is database size can affect this connection time required to start a 
> database session?

No, a connection does not read the entire database.

> FYI one of my database has size of 209GB, but currently I'm not using or 
> connect to that database.
> 
> Thanks in advance.
> 
> Regards,
> Surya
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/20 10:24 AM, Surya Widyanto wrote:

Hi,

I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit).
In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).
By two or three times I mean, if this slow connection problem happen, than it wil occur in a view days in a row. Then it will return to normal with fast login/connection process.

I've try to restart the laptop, restart the service but nothing give the sign to improve the slow login/connection process.

I've also try to turn off the *log_hostname* config in *postgresql.conf* and try to turn off the *Windows Firewall, Windows Defender Real-Time Scanner* but nothing works to improve the slow login connection.

I've also try to check the Postgres Log file but not much error shows in the log except for this error that appears a few time:
2020-07-22 14:43:22.312 +00 [13740] LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.

Is the database server you are connecting to on the laptop or some other machine?

The Database is on the same machine/laptop, I try to connect using localhost and 127.0.0.1, both have the same slow connection problem.

Where is this Postgres log?

The path of the log is on <data_directory>\log\postgresql-%Y-%m-%d_%H%M%S.log
Is there any config that I can change to get more verbose log information to trace and debug the connection problem?

This problem happens with all of the application such as *psql*, *pgAdmin*, *Navicat*, all with the same symptoms of slow login/connection to my PostgreSQL v12.3 (x64) service and if connected, then it will be easy to disconnected.

If needed my laptop specs are:

  * Intel Core i7-9750H
  * 16GB of RAM
  * Samsung SSD 970 EVO Plus 2TB

Is database size can affect this connection time required to start a database session?

No, a connection does not read the entire database.

FYI one of my database has size of 209GB, but currently I'm not using or connect to that database.

Thanks in advance.

Regards,
Surya

On 7/22/20 2:43 PM, Surya Widyanto wrote:
> On 23-07-2020 02:07, Adrian Klaver wrote:
>> On 7/22/20 10:24 AM, Surya Widyanto wrote:
>>> Hi,
>>>

>> Is the database server you are connecting to on the laptop or some 
>> other machine?
>>
> The Database is on the same machine/laptop, I try to connect using 
> *localhost* and *127.0.0.1*, both have the same slow connection problem.
>> Where is this Postgres log?
>>
> The path of the log is on 
> *<data_directory>\log\postgresql-%Y-%m-%d_%H%M%S.log*
> Is there any config that I can change to get more verbose log 
> information to trace and debug the connection problem?

Take a look here:

https://www.postgresql.org/docs/12/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHEN


>>> Thanks in advance.
>>>
>>> Regards,
>>> Surya
>>>
>>>
>>
>>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/20 10:24 AM, Surya Widyanto wrote:
> Hi,
> 
> I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 
> Home Single Language (64-bit).
> In two or three times since I fresh install the PostgreSQL and windows 
> on 17-05-2020, 06:04:56 (GMT).

Meant to ask earlier, where did you install Postgres from?

> By two or three times I mean, if this slow connection problem happen, 
> than it wil occur in a view days in a row. Then it will return to normal 
> with fast login/connection process.
> 

> Regards,
> Surya
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/20 10:24 AM, Surya Widyanto wrote:

Hi,

I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit).
In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).

Meant to ask earlier, where did you install Postgres from?

I've download the installer executable from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, the Windows x86-64 version.

Then I've install it on my Windows with binaries path to "C:\Program Files\PostgreSQL\12\" and for data path to "H:\PostgreSQL\Data\12.x\".
Both C: and H: drive are partition on my NVMe PCIe Gen. 3 x 4 SSD Drive mentioned earlier.

For additional information, I've also install the PostGIS v3.0 r15475 (x64) for PostgreSQL v12.

By two or three times I mean, if this slow connection problem happen, than it wil occur in a view days in a row. Then it will return to normal with fast login/connection process.

Regards,
Surya

On Jul 22, 2020, at 4:58 PM, Surya Widyanto <ayrus02@gmail.com> wrote:

On 23-07-2020 05:26, Adrian Klaver wrote:

On 7/22/20 10:24 AM, Surya Widyanto wrote:

Hi,

I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit).
In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).

Meant to ask earlier, where did you install Postgres from?

I've download the installer executable from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, the Windows x86-64 version.

Then I've install it on my Windows with binaries path to "C:\Program Files\PostgreSQL\12\" and for data path to "H:\PostgreSQL\Data\12.x\".
Both C: and H: drive are partition on my NVMe PCIe Gen. 3 x 4 SSD Drive mentioned earlier.

For additional information, I've also install the PostGIS v3.0 r15475 (x64) for PostgreSQL v12.

By two or three times I mean, if this slow connection problem happen, than it wil occur in a view days in a row. Then it will return to normal with fast login/connection process.

Regards,
Surya

Is there by any chance a correlation between your physical location and the slowness episodes?

Any AV software in play?

Since this COVID-19 outbreak, I'm working from home. I've try to use different internet connection to test, one with wired connection internet provider on my house, and the other are 4G cellular connection internet from my smartphone and both internet connection I use give the same effect of slow and easy to drop/disconnect connection to PostgreSQL.
For additional information, I've also had this problem when connectiong from PHP Application, so that total 4 apps I try to connect with PostgreSQL (psql, pgAdmin, Navicat, PHP Application Connection)
I'm not installing any AntiVirus software, the only installed AntiVirus software are WIndows Defender that came with Microsoft Windows. (Currently I'm Disabling the real time scan on windows defender)

After I activate the DEBUG5 for log_min_messages and log_min_error_statement, another line of log came up with the other log line I mentioned earlier:
2020-07-22 23:31:29.065 +00 [postgres@postgres] - 127.0.0.1(10191) - [1724] (psql) LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
2020-07-22 23:31:29.065 +00 [postgres@postgres] - 127.0.0.1(10191) - [1724] (psql) DEBUG:  unexpected EOF on client connection

On 7/22/20 4:49 PM, Surya Widyanto wrote:
> On 23-07-2020 06:12, Rob Sargent wrote:
>>
>>
>>> On Jul 22, 2020, at 4:58 PM, Surya Widyanto <ayrus02@gmail.com 
>>> <mailto:ayrus02@gmail.com>> wrote:
>>>
>>> On 23-07-2020 05:26, Adrian Klaver wrote:
>>>> On 7/22/20 10:24 AM, Surya Widyanto wrote:
>>>>> Hi,
>>>>>
>>>>> I have PostgreSQL v12.3 (x64) installed on my Laptop running 
>>>>> Windows 10 Home Single Language (64-bit).
>>>>> In two or three times since I fresh install the PostgreSQL and 
>>>>> windows on 17-05-2020, 06:04:56 (GMT).
>>>>
>>>> Meant to ask earlier, where did you install Postgres from?
>>>>
>>> I've download the installer executable from 
>>> https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, 
>>> the Windows x86-64 version.
>>>
>>> Then I've install it on my Windows with binaries path to *"C:\Program 
>>> Files\PostgreSQL\12\" *and for data path to *"H:\PostgreSQL\Data\12.x\"*.
>>> Both *C:* and *H:* drive are partition on my NVMe PCIe Gen. 3 x 4 SSD 
>>> Drive mentioned earlier.
>>>
>>> For additional information, I've also install the *PostGIS v3.0 
>>> r15475 (x64)* for *PostgreSQL v12.*
>>>
>>>>> By two or three times I mean, if this slow connection problem 
>>>>> happen, than it wil occur in a view days in a row. Then it will 
>>>>> return to normal with fast login/connection process.
>>>>>
>>>>
>>>>> Regards,
>>>>> Surya
>>>>>
>>>>>
>> Is there by any chance a correlation between your physical location 
>> and the slowness episodes?
>> Any AV software in play?
>>
> 
> Since this COVID-19 outbreak, I'm working from home. I've try to use 
> different internet connection to test, one with wired connection 
> internet provider on my house, and the other are 4G cellular connection 
> internet from my smartphone and both internet connection I use give the 
> same effect of slow and easy to drop/disconnect connection to PostgreSQL.

I thought you said earlier you where only connecting to the database on 
your laptop at localhost?:

"The Database is on the same machine/laptop, I try to connect using 
localhost and 127.0.0.1, both have the same slow connection problem."

So Internet connection should not be of concern here or is there more to 
this story?



> For additional information, I've also had this problem when connectiong 
> from PHP Application, so that total 4 apps I try to connect with 
> PostgreSQL (psql, pgAdmin, Navicat, PHP Application Connection)
> I'm not installing any AntiVirus software, the only installed AntiVirus 
> software are WIndows Defender that came with Microsoft Windows. 
> (Currently I'm Disabling the real time scan on windows defender)
> 
> After I activate the DEBUG5 for *log_min_messages* and 
> *log_min_error_statement*, another line of log came up with the other 
> log line I mentioned earlier:
> 2020-07-22 23:31:29.065 +00 [postgres@postgres] - 127.0.0.1(10191) - 
> [1724] (psql) LOG:  could not receive data from client: An existing 
> connection was forcibly closed by the remote host.
> 2020-07-22 23:31:29.065 +00 [postgres@postgres] - 127.0.0.1(10191) - 
> [1724] (psql) DEBUG:  unexpected EOF on client connection
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 7/22/20 4:49 PM, Surya Widyanto wrote:

On 23-07-2020 06:12, Rob Sargent wrote:

On Jul 22, 2020, at 4:58 PM, Surya Widyanto <ayrus02@gmail.com <mailto:ayrus02@gmail.com>> wrote:

On 23-07-2020 05:26, Adrian Klaver wrote:

On 7/22/20 10:24 AM, Surya Widyanto wrote:

Hi,

I have PostgreSQL v12.3 (x64) installed on my Laptop running Windows 10 Home Single Language (64-bit).
In two or three times since I fresh install the PostgreSQL and windows on 17-05-2020, 06:04:56 (GMT).

Meant to ask earlier, where did you install Postgres from?

I've download the installer executable from https://www.enterprisedb.com/downloads/postgres-postgresql-downloads, the Windows x86-64 version.

Then I've install it on my Windows with binaries path to *"C:\Program Files\PostgreSQL\12\" *and for data path to *"H:\PostgreSQL\Data\12.x\"*.
Both *C:* and *H:* drive are partition on my NVMe PCIe Gen. 3 x 4 SSD Drive mentioned earlier.

For additional information, I've also install the *PostGIS v3.0 r15475 (x64)* for *PostgreSQL v12.*

By two or three times I mean, if this slow connection problem happen, than it wil occur in a view days in a row. Then it will return to normal with fast login/connection process.

Regards,
Surya

Is there by any chance a correlation between your physical location and the slowness episodes?
Any AV software in play?

Since this COVID-19 outbreak, I'm working from home. I've try to use different internet connection to test, one with wired connection internet provider on my house, and the other are 4G cellular connection internet from my smartphone and both internet connection I use give the same effect of slow and easy to drop/disconnect connection to PostgreSQL.

I thought you said earlier you where only connecting to the database on your laptop at localhost?:

Yes it is a local connection using TCP on port 5432 using local loopback adapter localhost or IP address 127.0.0.1.

"The Database is on the same machine/laptop, I try to connect using localhost and 127.0.0.1, both have the same slow connection problem."

So Internet connection should not be of concern here or is there more to this story?

Yes, I've also try to disconnect from internet connection, and there is no change on the connection behaviour. It is still slow to connect to the database and easily dropped the connection.

For additional information, I've also had this problem when connectiong from PHP Application, so that total 4 apps I try to connect with PostgreSQL (psql, pgAdmin, Navicat, PHP Application Connection)
I'm not installing any AntiVirus software, the only installed AntiVirus software are WIndows Defender that came with Microsoft Windows. (Currently I'm Disabling the real time scan on windows defender)

After I activate the DEBUG5 for *log_min_messages* and *log_min_error_statement*, another line of log came up with the other log line I mentioned earlier:
2020-07-22 23:31:29.065 +00 [postgres@postgres] - 127.0.0.1(10191) - [1724] (psql) LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
2020-07-22 23:31:29.065 +00 [postgres@postgres] - 127.0.0.1(10191) - [1724] (psql) DEBUG:  unexpected EOF on client connection

After so much trial and error I've finally able to solve the problem with my connection to my local PostgreSQL server on my laptop. After I'm managed to fully disable any Windows Defender and Windows Firewall services, I'm now able to connect to my PostgreSQL server quickly and without any dropped connection like before.
The error of DEBUG:  unexpected EOF on client connection based on my research on the internet are caused by suddenly dropped connection between PostgreSQL client apps with the PostgreSQL serve. and most of the case of dropped connection are caused by some kind of firewall in the system.

Thanks to Adrian Klaver and Rob Sargent for giving me insight to solve the problem.

Regards,
Surya