Re: Fwd: Postgresql with nextcloud in Windows Server

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?

3. I'll check these links until next weekend.
Maybe I'll spend long time to adapt this suggestions.

2019년 4월 9일 (화) 오후 11:26, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com
> > <mailto: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>
> > > <mailto: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>>
> > <mailto: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>>
> > > > <mailto: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
> >
> > >
> > <
> 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
> >>
> > > > <mailto: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>>
> > > <mailto: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>
> > <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
>