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