Re: Fwd: Postgresql with nextcloud in Windows Server

Thanks for your answers.

1) First time, I set that value to 200. I think it is connection issue, so
increase max_connection = 1200.
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.

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

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(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>>님이 작성:
> >
> > On 4/1/19 10:03 PM, 김준형 wrote:
> > >
> > > 보낸사람: *김준형* <wnsuddlsla(at)gmail(dot)com
> > <mailto:wnsuddlsla(at)gmail(dot)com> <mailto:wnsuddlsla(at)gmail(dot)com
> > <mailto:wnsuddlsla(at)gmail(dot)com>>>
> > > Date: 2019년 4월 2일 (화) 오후 2:02
> > > Subject: Re: Postgresql with nextcloud in Windows Server
> > > To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > > <mailto:adrian(dot)klaver(at)aklaver(dot)com
> > <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> > <mailto:adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com
> >>
> > >
> >
> >
> > --
> > Adrian Klaver
> > adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>