Re: Fwd: Postgresql with nextcloud in Windows Server - Mailing list pgsql-general

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