Re: Fwd: Postgresql with nextcloud in Windows Server

On 4/1/19 10:03 PM, 김준형 wrote:
>
> 보낸사람: *김준형* <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>>
>
>
> 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(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>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com