Thread: Postgresql with nextcloud in Windows Server

On 4/1/19 6:21 PM, 김준형 wrote:
> Hi Community, I have problem so I wanna help from PostgreSQL community.
> My problem is using PostgreSQL with nextcloud(cloud system) and It can 
> make no more connection to server. I checked log files and knew reason. 
> PostgreSQL prevented to disconnecting connection but still tried to 
> connect PostgreSQL server. This problem blocks to connect server and 
> can't stop PostgreSQL service. What I can do is just restart the server 
> and recover PostgreSQL data(I don't know why some data disappear.).
> Can I get some help about this problem?

Not sure I entirely understand the above, so to help can you answer the 
following:

1) The Postgres server is running on nextcloud, correct?

2) The client you are using to connect to the server is on nextcloud 
also or somewhere else?

3) The log files do not show a problem, is this correct?

4) Do you see an error message on the client end when you try to connect?

5) Have you looked at your pg_hba.conf to see if allows connections from 
your client?

6) Not sure what you mean when you say you cannot stop the service, but 
that you can restart it?

7) How do you know that data has disappeared?

> 
> My OS and PostgreSQL version is
> PostgreSQL 10.5
> Windows Server 2012 R2
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/1/19 10:03 PM, 김준형 wrote:
> 
> 보낸사람: *김준형* <wnsuddlsla@gmail.com <mailto:wnsuddlsla@gmail.com>>
> Date: 2019년 4월 2일 (화) 오후 2:02
> Subject: Re: Postgresql with nextcloud in Windows Server
> To: Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>
> 
> 
> Thanks for your reply and I hope this answers can help your questions
> 
> 1) The nextcloud is running on PostgreSQL server. Cloud system needs 
> PostgreSQL server.

Well I got that backwards. Forget that Nextcloud is an ownCloud fork.

> 
> 2) Nextcloud system try to connect PostgreSQL server all time.
> 2019-03-27 20:46:59.396 LOG:  connection received: host=xxx.xxx.xxx.xxx 
> port=xxxx
> 2019-03-27 20:46:59.403 LOG:  connection authorized: user=user_name 
> database=db_name
> 2019-03-27 20:46:59.463 LOG:  disconnection: session time: 0:00:00.067 
> user=user_name database=db_name host=xxx.xxx.xxx.xxx port=xxxx
> this connections repeat almost per 10sec.
> Other clients well... use this PostgreSQL but not so much.(almost 30 
> people use this PostgreSQL include nextcloud system users)

There is nothing unusual about the above, just shows a client 
successfully connecting and then disconnecting. I set up an ownCloud 
server years ago as a test and as I remember it was constantly talking 
to the Postgres server. In postgresql.conf you could set log_statement = 
'all' to see what is actually being done by the client(s). Just be aware 
this will generate a lot of logs so you probably want to do this for 
short period only.

> 
> 
> 3) Yes. log files doesn't shows problems clearly. I just checked log 
> files and saw difference when server couldn't connected.
> 2019-03-27 20:46:59.396 LOG:  connection received: host=xxx.xxx.xxx.xxx 
> port=xxxx
> 2019-03-27 20:46:59.403 LOG:  connection authorized: user=user_name 
> database=db_name
> this log repeated and no disconnection log.

This shows a successful connection. The disconnection maybe much further 
in the future or has not happened at all. To see current connections 
select from pg_stat_activity:

www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

> 
> 4) After problem occur, if try to connect to windows server, windows 
> remote access shows time-out error. PostgreSQL server also too.

What problem occurs?
Where is the Windows server?

> 
> 5) Before the server doesn't work normally, there is no problem to use 
> PostgreSQL (even nextcloud system, too.)
> 
> 6) No, It doesn't work. PostgreSQL service status doesn't changed.

Not sure you know that if you cannot connect to the Windows server?

> 
> 7) When I restart server, I check PostgreSQL data and I see some schema 
> data disappeared(only data). log files says 'db system was not properly 
> shut down' so 'automatic recover in progress' when PostgreSQL server 
> started after Windows Server get restarted.
> I think this 'not properly shut down' causes windows server cold 
> booting.(I need to turn on the server quickly for some reason and my 
> server spends a lot of time to restart.)

To me it looks like the OS is crashing and bringing the Postgres server 
down with it. There is a chance it is the other way around. To figure 
this out I would suggest looking at what is the below just before/at 
time of crash/after restart:

1) The Windows system logs

2) The Postgres log

> 
> 2019년 4월 2일 (화) 오후 1:21, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>님이 작성:
> 
>     On 4/1/19 6:21 PM, 김준형 wrote:
>      > Hi Community, I have problem so I wanna help from PostgreSQL
>     community.
>      > My problem is using PostgreSQL with nextcloud(cloud system) and
>     It can
>      > make no more connection to server. I checked log files and knew
>     reason.
>      > PostgreSQL prevented to disconnecting connection but still tried to
>      > connect PostgreSQL server. This problem blocks to connect server and
>      > can't stop PostgreSQL service. What I can do is just restart the
>     server
>      > and recover PostgreSQL data(I don't know why some data disappear.).
>      > Can I get some help about this problem?
> 
>     Not sure I entirely understand the above, so to help can you answer the
>     following:
> 
>     1) The Postgres server is running on nextcloud, correct?
> 
>     2) The client you are using to connect to the server is on nextcloud
>     also or somewhere else?
> 
>     3) The log files do not show a problem, is this correct?
> 
>     4) Do you see an error message on the client end when you try to
>     connect?
> 
>     5) Have you looked at your pg_hba.conf to see if allows connections
>     from
>     your client?
> 
>     6) Not sure what you mean when you say you cannot stop the service, but
>     that you can restart it?
> 
>     7) How do you know that data has disappeared?
> 
>      >
>      > My OS and PostgreSQL version is
>      > PostgreSQL 10.5
>      > Windows Server 2012 R2
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/7/19 9:53 PM, 김준형 wrote:
> Sorry for late but my server works without problem for a while.
> 
>  > What problem occurs?
>  > Where is the Windows server?
> 
> Problem means Windows server doesn't accept new connection and non-admin 
> connection.
> Only connected admin connection lives.
> Windows server is not so close on my work space. So I use Windows server 
> with remote connection.
> 
>  > Not sure you know that if you cannot connect to the Windows server?
> 
> I know I cannot connect to the Windows server but sometimes I got remote 
> connection of admin when occurred connection problem.
> That time, I had tried to stop the PostgreSQL server but It didn't work.
> 
> 
> At last, I leave logs what I got.
> 
> 1) The Windows system logs
> 
> 1096 Process closing UDP socket with local port number 64347 is running 
> longer than expected. The local port number can not be used until the 
> close operation is completed. This problem is usually caused by 
> malfunctioning network drivers. Make sure you have the latest updates 
> for third-party networking software, including Windows and NIC drivers, 
> firewalls, and other security products.

I rarely use Windows anymore so I am not sure how to interpret the 
above. Some searching found that it is often helpful to look in the 
Event Log over the same time period.  Postgres does use UDP, so it may 
be related I am just not sure how?

More below.

> 
> 2) PostgreSQL logs
> 
> ...
> 2019-04-06 04:34:03.984 KST [129704] LOG:  connection received: 
> host=128.1.99.51 port=40602
> 2019-04-06 04:34:03.990 KST [129704] LOG:  connection authorized: 
> user=oc_abmiadmin database=abminext
> 2019-04-06 04:34:05.117 KST [129720] LOG:  connection received: 
> host=128.1.99.51 port=40604
> 2019-04-06 04:34:05.125 KST [129720] LOG:  connection authorized: 
> user=oc_abmiadmin database=abminext
> 2019-04-06 04:34:05.125 KST [129720] FATAL:  remaining connection slots 
> are reserved for non-replication superuser connections
> 2019-04-06 04:34:05.171 KST [129736] LOG:  connection received: 
> host=128.1.99.51 port=40606
> 2019-04-06 04:34:05.179 KST [129736] LOG:  connection authorized: 
> user=oc_abmiadmin database=abminext
> 2019-04-06 04:34:05.179 KST [129736] FATAL:  remaining connection slots 
> are reserved for non-replication superuser connections
> ...
> 
> p.s) PostgreSQL max_connections =1200. Is there problem in here?

Yeah, it looks like you are maxing out the connections. The overhead of 
maintaining 1200 connections is probably a contributing factor to your 
issues. So:

1) Why is 1200 connections set?

2) Have you looked at connection poolers?

3) When the server is running properly you should monitor pg_stat_activity:

https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW

to see what is connecting to the server and what the connections are doing.

> 
> 
> 
> 2019년 4월 2일 (화) 오후 11:30, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>님이 작성:
> 
>     On 4/1/19 10:03 PM, 김준형 wrote:
>      >
>      > 보낸사람: *김준형* <wnsuddlsla@gmail.com
>     <mailto:wnsuddlsla@gmail.com> <mailto:wnsuddlsla@gmail.com
>     <mailto:wnsuddlsla@gmail.com>>>
>      > Date: 2019년 4월 2일 (화) 오후 2:02
>      > Subject: Re: Postgresql with nextcloud in Windows Server
>      > To: Adrian Klaver <adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>>
>      >
>      >
>      > Thanks for your reply and I hope this answers can help your questions
>      >
>      > 1) The nextcloud is running on PostgreSQL server. Cloud system needs
>      > PostgreSQL server.
> 
>     Well I got that backwards. Forget that Nextcloud is an ownCloud fork.
> 
>      >
>      > 2) Nextcloud system try to connect PostgreSQL server all time.
>      > 2019-03-27 20:46:59.396 LOG:  connection received:
>     host=xxx.xxx.xxx.xxx
>      > port=xxxx
>      > 2019-03-27 20:46:59.403 LOG:  connection authorized: user=user_name
>      > database=db_name
>      > 2019-03-27 20:46:59.463 LOG:  disconnection: session time:
>     0:00:00.067
>      > user=user_name database=db_name host=xxx.xxx.xxx.xxx port=xxxx
>      > this connections repeat almost per 10sec.
>      > Other clients well... use this PostgreSQL but not so much.(almost 30
>      > people use this PostgreSQL include nextcloud system users)
> 
>     There is nothing unusual about the above, just shows a client
>     successfully connecting and then disconnecting. I set up an ownCloud
>     server years ago as a test and as I remember it was constantly talking
>     to the Postgres server. In postgresql.conf you could set
>     log_statement =
>     'all' to see what is actually being done by the client(s). Just be
>     aware
>     this will generate a lot of logs so you probably want to do this for
>     short period only.
> 
>      >
>      >
>      > 3) Yes. log files doesn't shows problems clearly. I just checked log
>      > files and saw difference when server couldn't connected.
>      > 2019-03-27 20:46:59.396 LOG:  connection received:
>     host=xxx.xxx.xxx.xxx
>      > port=xxxx
>      > 2019-03-27 20:46:59.403 LOG:  connection authorized: user=user_name
>      > database=db_name
>      > this log repeated and no disconnection log.
> 
>     This shows a successful connection. The disconnection maybe much
>     further
>     in the future or has not happened at all. To see current connections
>     select from pg_stat_activity:
> 
>     www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
>     <http://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW>
> 
>      >
>      > 4) After problem occur, if try to connect to windows server, windows
>      > remote access shows time-out error. PostgreSQL server also too.
> 
>     What problem occurs?
>     Where is the Windows server?
> 
>      >
>      > 5) Before the server doesn't work normally, there is no problem
>     to use
>      > PostgreSQL (even nextcloud system, too.)
>      >
>      > 6) No, It doesn't work. PostgreSQL service status doesn't changed.
> 
>     Not sure you know that if you cannot connect to the Windows server?
> 
>      >
>      > 7) When I restart server, I check PostgreSQL data and I see some
>     schema
>      > data disappeared(only data). log files says 'db system was not
>     properly
>      > shut down' so 'automatic recover in progress' when PostgreSQL server
>      > started after Windows Server get restarted.
>      > I think this 'not properly shut down' causes windows server cold
>      > booting.(I need to turn on the server quickly for some reason and my
>      > server spends a lot of time to restart.)
> 
>     To me it looks like the OS is crashing and bringing the Postgres server
>     down with it. There is a chance it is the other way around. To figure
>     this out I would suggest looking at what is the below just before/at
>     time of crash/after restart:
> 
>     1) The Windows system logs
> 
>     2) The Postgres log
> 
>      >
>      > 2019년 4월 2일 (화) 오후 1:21, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>>님이 작성:
>      >
>      >     On 4/1/19 6:21 PM, 김준형 wrote:
>      >      > Hi Community, I have problem so I wanna help from PostgreSQL
>      >     community.
>      >      > My problem is using PostgreSQL with nextcloud(cloud
>     system) and
>      >     It can
>      >      > make no more connection to server. I checked log files and
>     knew
>      >     reason.
>      >      > PostgreSQL prevented to disconnecting connection but still
>     tried to
>      >      > connect PostgreSQL server. This problem blocks to connect
>     server and
>      >      > can't stop PostgreSQL service. What I can do is just
>     restart the
>      >     server
>      >      > and recover PostgreSQL data(I don't know why some data
>     disappear.).
>      >      > Can I get some help about this problem?
>      >
>      >     Not sure I entirely understand the above, so to help can you
>     answer the
>      >     following:
>      >
>      >     1) The Postgres server is running on nextcloud, correct?
>      >
>      >     2) The client you are using to connect to the server is on
>     nextcloud
>      >     also or somewhere else?
>      >
>      >     3) The log files do not show a problem, is this correct?
>      >
>      >     4) Do you see an error message on the client end when you try to
>      >     connect?
>      >
>      >     5) Have you looked at your pg_hba.conf to see if allows
>     connections
>      >     from
>      >     your client?
>      >
>      >     6) Not sure what you mean when you say you cannot stop the
>     service, but
>      >     that you can restart it?
>      >
>      >     7) How do you know that data has disappeared?
>      >
>      >      >
>      >      > My OS and PostgreSQL version is
>      >      > PostgreSQL 10.5
>      >      > Windows Server 2012 R2
>      >      >
>      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/8/19 6:51 PM, 김준형 wrote:
> Thanks for your answers.
> 
> 1) First time, I set that value to 200. I think it is connection issue, 
> so increase max_connection = 1200.

Connections consume resources, that can come back to hurt you.

> After I show this email, I search max connection is associated to 
> shared_buffers.
> My configuration set shared_buffers = 192GB because PostgreSQL documents 
> says set shared_buffers to quarter of memory will be good.

With the caveats that this is for a dedicated database server and 
max_wal_size may need to be increased also. Is this machine dedicated to 
Postgres?

> 
> 2) How to see that? I searched postgresql connection pooler but I didn't 
> get good answer.

http://www.craigkerstiens.com/2014/05/22/on-connection-pooling/

http://www.pgpool.net/mediawiki/index.php/Main_Page

http://pgbouncer.github.io/


Before you go any further in any direction I would spend the time 
monitoring your database/OS to see what is actually going on 
hour-to-hour/day-to-day. Flipping settings based on hunches may work by 
chance but most likely will cause more issues. Not sure how Postgres was 
installed on the server and whether you have access to make changes. 
Assuming changes can be made, some suggestions:

1) From the contrib modules:

pg_stat_statements
https://www.postgresql.org/docs/11/pgstatstatements.html

2) Third part extension:

PGAudit
https://www.pgaudit.org/

> 
> 3) I saw pg_stat_activity but nothing specially.
> Sometimes Nextcloud appeared in a short time like this.
> 
>   1795037 | abminext    | 41836 |  1795036 | oc_abmiadmin |              
>                | ip address|                 |       port numer| 
> 2019-04-09 10:35:38.527147+09 |                               | 
> 2019-04-09 10:35:38.594062+09 | 2019-04-09 10:35:38.594259+09 | Client  
>          | ClientRead          | idle   |             |              |  
>                                                                          
>                                                                          
>                                                                          
>                                                                          
>                                                                          
>                                                           +| client backend
>           |             |       |          |              |              
>                |             |                 |             |          
>                       |                               |                  
>               |                               |                 |        
>               |        |             |              |                    
>               SELECT "remote", "share_token", "password", "mountpoint", 
> "owner"                                                                  
>                                                                          
>                                                                          
>                                                                          
>                                         +|
>           |             |       |          |              |              
>                |             |                 |             |          
>                       |                               |                  
>               |                               |                 |        
>               |        |             |              |                    
>               FROM "oc_share_external"                                  
>                                                                          
>                                                                          
>                                                                          
>                                                                          
>                                          +|
>           |             |       |          |              |              
>                |             |                 |             |          
>                       |                               |                  
>               |                               |                 |        
>               |        |             |              |                    
>               WHERE "user" = $1 AND "accepted" = $2                      
>                                                                          
>                                                                          
>                                                                          
>                                                                          
>                                         +|
>           |             |       |          |              |              
>                |             |                 |             |          
>                       |                               |                  
>               |                               |                 |        
>               |        |             |              |
> 
> 2019년 4월 8일 (월) 오후 11:46, Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>>님이 작성:
> 
>     On 4/7/19 9:53 PM, 김준형 wrote:
>      > Sorry for late but my server works without problem for a while.
>      >
>      >  > What problem occurs?
>      >  > Where is the Windows server?
>      >
>      > Problem means Windows server doesn't accept new connection and
>     non-admin
>      > connection.
>      > Only connected admin connection lives.
>      > Windows server is not so close on my work space. So I use Windows
>     server
>      > with remote connection.
>      >
>      >  > Not sure you know that if you cannot connect to the Windows
>     server?
>      >
>      > I know I cannot connect to the Windows server but sometimes I got
>     remote
>      > connection of admin when occurred connection problem.
>      > That time, I had tried to stop the PostgreSQL server but It
>     didn't work.
>      >
>      >
>      > At last, I leave logs what I got.
>      >
>      > 1) The Windows system logs
>      >
>      > 1096 Process closing UDP socket with local port number 64347 is
>     running
>      > longer than expected. The local port number can not be used until
>     the
>      > close operation is completed. This problem is usually caused by
>      > malfunctioning network drivers. Make sure you have the latest
>     updates
>      > for third-party networking software, including Windows and NIC
>     drivers,
>      > firewalls, and other security products.
> 
>     I rarely use Windows anymore so I am not sure how to interpret the
>     above. Some searching found that it is often helpful to look in the
>     Event Log over the same time period.  Postgres does use UDP, so it may
>     be related I am just not sure how?
> 
>     More below.
> 
>      >
>      > 2) PostgreSQL logs
>      >
>      > ...
>      > 2019-04-06 04:34:03.984 KST [129704] LOG:  connection received:
>      > host=128.1.99.51 port=40602
>      > 2019-04-06 04:34:03.990 KST [129704] LOG:  connection authorized:
>      > user=oc_abmiadmin database=abminext
>      > 2019-04-06 04:34:05.117 KST [129720] LOG:  connection received:
>      > host=128.1.99.51 port=40604
>      > 2019-04-06 04:34:05.125 KST [129720] LOG:  connection authorized:
>      > user=oc_abmiadmin database=abminext
>      > 2019-04-06 04:34:05.125 KST [129720] FATAL:  remaining connection
>     slots
>      > are reserved for non-replication superuser connections
>      > 2019-04-06 04:34:05.171 KST [129736] LOG:  connection received:
>      > host=128.1.99.51 port=40606
>      > 2019-04-06 04:34:05.179 KST [129736] LOG:  connection authorized:
>      > user=oc_abmiadmin database=abminext
>      > 2019-04-06 04:34:05.179 KST [129736] FATAL:  remaining connection
>     slots
>      > are reserved for non-replication superuser connections
>      > ...
>      >
>      > p.s) PostgreSQL max_connections =1200. Is there problem in here?
> 
>     Yeah, it looks like you are maxing out the connections. The overhead of
>     maintaining 1200 connections is probably a contributing factor to your
>     issues. So:
> 
>     1) Why is 1200 connections set?
> 
>     2) Have you looked at connection poolers?
> 
>     3) When the server is running properly you should monitor
>     pg_stat_activity:
> 
>     https://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
> 
>     to see what is connecting to the server and what the connections are
>     doing.
> 
>      >
>      >
>      >
>      > 2019년 4월 2일 (화) 오후 11:30, Adrian Klaver
>     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>>님이 작성:
>      >
>      >     On 4/1/19 10:03 PM, 김준형 wrote:
>      >      >
>      >      > 보낸사람: *김준형* <wnsuddlsla@gmail.com
>     <mailto:wnsuddlsla@gmail.com>
>      >     <mailto:wnsuddlsla@gmail.com <mailto:wnsuddlsla@gmail.com>>
>     <mailto:wnsuddlsla@gmail.com <mailto:wnsuddlsla@gmail.com>
>      >     <mailto:wnsuddlsla@gmail.com <mailto:wnsuddlsla@gmail.com>>>>
>      >      > Date: 2019년 4월 2일 (화) 오후 2:02
>      >      > Subject: Re: Postgresql with nextcloud in Windows Server
>      >      > To: Adrian Klaver <adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>
>      >     <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>
>      >      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>
>      >     <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>>>
>      >      >
>      >      >
>      >      > Thanks for your reply and I hope this answers can help
>     your questions
>      >      >
>      >      > 1) The nextcloud is running on PostgreSQL server. Cloud
>     system needs
>      >      > PostgreSQL server.
>      >
>      >     Well I got that backwards. Forget that Nextcloud is an
>     ownCloud fork.
>      >
>      >      >
>      >      > 2) Nextcloud system try to connect PostgreSQL server all time.
>      >      > 2019-03-27 20:46:59.396 LOG:  connection received:
>      >     host=xxx.xxx.xxx.xxx
>      >      > port=xxxx
>      >      > 2019-03-27 20:46:59.403 LOG:  connection authorized:
>     user=user_name
>      >      > database=db_name
>      >      > 2019-03-27 20:46:59.463 LOG:  disconnection: session time:
>      >     0:00:00.067
>      >      > user=user_name database=db_name host=xxx.xxx.xxx.xxx port=xxxx
>      >      > this connections repeat almost per 10sec.
>      >      > Other clients well... use this PostgreSQL but not so
>     much.(almost 30
>      >      > people use this PostgreSQL include nextcloud system users)
>      >
>      >     There is nothing unusual about the above, just shows a client
>      >     successfully connecting and then disconnecting. I set up an
>     ownCloud
>      >     server years ago as a test and as I remember it was
>     constantly talking
>      >     to the Postgres server. In postgresql.conf you could set
>      >     log_statement =
>      >     'all' to see what is actually being done by the client(s).
>     Just be
>      >     aware
>      >     this will generate a lot of logs so you probably want to do
>     this for
>      >     short period only.
>      >
>      >      >
>      >      >
>      >      > 3) Yes. log files doesn't shows problems clearly. I just
>     checked log
>      >      > files and saw difference when server couldn't connected.
>      >      > 2019-03-27 20:46:59.396 LOG:  connection received:
>      >     host=xxx.xxx.xxx.xxx
>      >      > port=xxxx
>      >      > 2019-03-27 20:46:59.403 LOG:  connection authorized:
>     user=user_name
>      >      > database=db_name
>      >      > this log repeated and no disconnection log.
>      >
>      >     This shows a successful connection. The disconnection maybe much
>      >     further
>      >     in the future or has not happened at all. To see current
>     connections
>      >     select from pg_stat_activity:
>      >
>      >
>     www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW
>     <http://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW>
>      >   
>       <http://www.postgresql.org/docs/11/monitoring-stats.html#PG-STAT-ACTIVITY-VIEW>
>      >
>      >      >
>      >      > 4) After problem occur, if try to connect to windows
>     server, windows
>      >      > remote access shows time-out error. PostgreSQL server also
>     too.
>      >
>      >     What problem occurs?
>      >     Where is the Windows server?
>      >
>      >      >
>      >      > 5) Before the server doesn't work normally, there is no
>     problem
>      >     to use
>      >      > PostgreSQL (even nextcloud system, too.)
>      >      >
>      >      > 6) No, It doesn't work. PostgreSQL service status doesn't
>     changed.
>      >
>      >     Not sure you know that if you cannot connect to the Windows
>     server?
>      >
>      >      >
>      >      > 7) When I restart server, I check PostgreSQL data and I
>     see some
>      >     schema
>      >      > data disappeared(only data). log files says 'db system was not
>      >     properly
>      >      > shut down' so 'automatic recover in progress' when
>     PostgreSQL server
>      >      > started after Windows Server get restarted.
>      >      > I think this 'not properly shut down' causes windows
>     server cold
>      >      > booting.(I need to turn on the server quickly for some
>     reason and my
>      >      > server spends a lot of time to restart.)
>      >
>      >     To me it looks like the OS is crashing and bringing the
>     Postgres server
>      >     down with it. There is a chance it is the other way around.
>     To figure
>      >     this out I would suggest looking at what is the below just
>     before/at
>      >     time of crash/after restart:
>      >
>      >     1) The Windows system logs
>      >
>      >     2) The Postgres log
>      >
>      >      >
>      >      > 2019년 4월 2일 (화) 오후 1:21, Adrian Klaver
>      >     <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >      > <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>
>      >     <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>>>님이 작성:
>      >      >
>      >      >     On 4/1/19 6:21 PM, 김준형 wrote:
>      >      >      > Hi Community, I have problem so I wanna help from
>     PostgreSQL
>      >      >     community.
>      >      >      > My problem is using PostgreSQL with nextcloud(cloud
>      >     system) and
>      >      >     It can
>      >      >      > make no more connection to server. I checked log
>     files and
>      >     knew
>      >      >     reason.
>      >      >      > PostgreSQL prevented to disconnecting connection
>     but still
>      >     tried to
>      >      >      > connect PostgreSQL server. This problem blocks to
>     connect
>      >     server and
>      >      >      > can't stop PostgreSQL service. What I can do is just
>      >     restart the
>      >      >     server
>      >      >      > and recover PostgreSQL data(I don't know why some data
>      >     disappear.).
>      >      >      > Can I get some help about this problem?
>      >      >
>      >      >     Not sure I entirely understand the above, so to help
>     can you
>      >     answer the
>      >      >     following:
>      >      >
>      >      >     1) The Postgres server is running on nextcloud, correct?
>      >      >
>      >      >     2) The client you are using to connect to the server is on
>      >     nextcloud
>      >      >     also or somewhere else?
>      >      >
>      >      >     3) The log files do not show a problem, is this correct?
>      >      >
>      >      >     4) Do you see an error message on the client end when
>     you try to
>      >      >     connect?
>      >      >
>      >      >     5) Have you looked at your pg_hba.conf to see if allows
>      >     connections
>      >      >     from
>      >      >     your client?
>      >      >
>      >      >     6) Not sure what you mean when you say you cannot stop the
>      >     service, but
>      >      >     that you can restart it?
>      >      >
>      >      >     7) How do you know that data has disappeared?
>      >      >
>      >      >      >
>      >      >      > My OS and PostgreSQL version is
>      >      >      > PostgreSQL 10.5
>      >      >      > Windows Server 2012 R2
>      >      >      >
>      >      >
>      >      >
>      >      >     --
>      >      >     Adrian Klaver
>      >      > adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com> <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>
>      >     <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com> <mailto:adrian.klaver@aklaver.com
>     <mailto:adrian.klaver@aklaver.com>>>
>      >      >
>      >
>      >
>      >     --
>      >     Adrian Klaver
>      > adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>     <mailto:adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>>
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 4/11/19 1:45 AM, 김준형 wrote:
> 
> 1.  It spends more resources but I think this setting endure that problem.
> Actually, after this setting, Windows server(include PostgreSQL server) 
> endures that problem more.
> But I know it's not a solution.
> 
> 2. No. MS-SQL SERVER, PostgreSQL Server, and Tomcat.
> What do you think of appropriate value of max_wal_size?

Have no idea, mostly because the root cause of your issue has not been 
established.
> 
> 3. I'll check these links until next weekend.
> Maybe I'll spend long time to adapt this suggestions.
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com