Re: My Database getting dropped anonymously

On Fri, Jun 21, 2024 at 12:29 AM Sekar S <sekar(dot)newtran(at)gmail(dot)com> wrote:

> Hi,
>
> I'm new the PostgreSQL. I have setup my PostgreSQL instance in Azure
> cloud.
>
> VM & OS - Linux, Ubuntu 22.04, Size - Standard B2s (2 vcpus, 4 GiB
> memory)
> PostgreSQL - psql (14.12 (Ubuntu 14.12-0ubuntu0.22.04.1))
>
> Past few days, I'm seeing repeated instances of my Database getting
> dropped which is weird.
> When I checked the logs in the /var/log/postgresql/postgresql-14-main.log,
> here is what it shows.
>
> 2024-06-20 23:17:20.030 UTC [91103] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.031 UTC [91151] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.041 UTC [91147] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.061 UTC [91102] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.061 UTC [91121] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.072 UTC [91073] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.081 UTC [91119] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-20 23:17:20.312 UTC [90636] LOG: background worker "logical
> replication launcher" (PID 90643) exited with exit code 1
> 2024-06-20 23:17:20.334 UTC [90638] LOG: shutting down
> 2024-06-20 23:17:20.504 UTC [90636] LOG: database system is shut down
> 2024-06-21 03:13:29.202 UTC [99847] LOG: starting PostgreSQL 14.12
> (Ubuntu 14.12-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc
> (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
> 2024-06-21 03:13:29.202 UTC [99847] LOG: listening on IPv4 address
> "0.0.0.0", port 5432
> 2024-06-21 03:13:29.202 UTC [99847] LOG: listening on IPv6 address "::",
> port 5432
> 2024-06-21 03:13:29.231 UTC [99847] LOG: listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5432"
> 2024-06-21 03:13:29.282 UTC [99848] LOG: database system was shut down at
> 2024-06-20 23:17:20 UTC
> 2024-06-21 03:13:29.393 UTC [99847] LOG: database system is ready to
> accept connections
> 2024-06-21 03:23:58.922 UTC [99987] nectar_dvpr(at)nectardev_db01 FATAL:
> password authentication failed for user "nectar_dvpr"
> 2024-06-21 03:23:58.922 UTC [99987] nectar_dvpr(at)nectardev_db01 DETAIL:
> Connection matched pg_hba.conf line 93: "host all all
> 0.0.0.0/0 md5"
> 2024-06-21 03:23:59.033 UTC [99988] nectar_dvpr(at)nectardev_db01 FATAL:
> password authentication failed for user "nectar_dvpr"
> 2024-06-21 03:23:59.033 UTC [99988] nectar_dvpr(at)nectardev_db01 DETAIL:
> Connection matched pg_hba.conf line 93: "host all all
> 0.0.0.0/0 md5"
> 2024-06-21 03:24:42.305 UTC [100002] postgres(at)template0 FATAL: database
> "template0" is not currently accepting connections
> 2024-06-21 03:24:58.029 UTC [99996] nectar_dvpr(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-21 03:25:00.673 UTC [100011] postgres(at)postgres ERROR: cannot
> drop the currently open database
> 2024-06-21 03:25:00.673 UTC [100011] postgres(at)postgres STATEMENT: DROP
> DATABASE postgres;
> 2024-06-21 03:25:08.314 UTC [100022] postgres(at)postgres ERROR: database
> "nectardev_db01" is being accessed by other users
> 2024-06-21 03:25:08.314 UTC [100022] postgres(at)postgres DETAIL: There is
> 1 other session using the database.
> 2024-06-21 03:25:08.314 UTC [100022] postgres(at)postgres STATEMENT: DROP
> DATABASE nectardev_db01;
> 2024-06-21 03:26:35.111 UTC [100039] nectar_dvpr(at)nectardev_db01 ERROR:
> relation "mas_party" does not exist at character 15
> 2024-06-21 03:26:35.111 UTC [100039] nectar_dvpr(at)nectardev_db01
> STATEMENT: select * from mas_party
> 2024-06-21 03:29:24.233 UTC [100087] postgres(at)nectardev_db01 ERROR:
> relation "mas_party" does not exist at character 15
> 2024-06-21 03:29:24.233 UTC [100087] postgres(at)nectardev_db01 STATEMENT:
> select * from mas_party;
> 2024-06-21 03:35:16.573 UTC [99847] LOG: received fast shutdown request
> 2024-06-21 03:35:16.584 UTC [99847] LOG: aborting any active transactions
> 2024-06-21 03:35:16.585 UTC [100053] postgres(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-21 03:35:16.593 UTC [100047] postgres(at)postgres FATAL:
> terminating connection due to administrator command
> 2024-06-21 03:35:16.603 UTC [100039] nectar_dvpr(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-21 03:35:16.613 UTC [100010] nectar_dvpr(at)nectardev_db01 FATAL:
> terminating connection due to administrator command
> 2024-06-21 03:35:16.654 UTC [99847] LOG: background worker "logical
> replication launcher" (PID 99854) exited with exit code 1
> 2024-06-21 03:35:16.746 UTC [99849] LOG: shutting down
> 2024-06-21 03:35:16.902 UTC [99847] LOG: database system is shut down
> 2024-06-21 03:35:17.912 UTC [100172] LOG: starting PostgreSQL 14.12
> (Ubuntu 14.12-0ubuntu0.22.04.1) on x86_64-pc-linux-gnu, compiled by gcc
> (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
> 2024-06-21 03:35:17.912 UTC [100172] LOG: listening on IPv4 address
> "0.0.0.0", port 5432
> 2024-06-21 03:35:17.912 UTC [100172] LOG: listening on IPv6 address "::",
> port 5432
> 2024-06-21 03:35:17.932 UTC [100172] LOG: listening on Unix socket
> "/var/run/postgresql/.s.PGSQL.5432"
> 2024-06-21 03:35:18.008 UTC [100173] LOG: database system was shut down
> at 2024-06-21 03:35:16 UTC
> 2024-06-21 03:35:18.093 UTC [100172] LOG: database system is ready to
> accept connections
>
>
> I have not issued any database drop command. I'm also using pgAdmin4
> (8.6) in my windows desktop to work on Azure cloud database. I tried to
> find solution through google but not found any answer why this is
> happening. I'm at the verge of switching other databases if this issue not
> solved.
>

As if it's Postgresql's fault that someone else has permission to drop that
database.

> I request your help to solve this issue and would be grateful for that.
>
> *Issues facing:*
> 1) Database instance getting stopped after a period of time whereas I want
> the database to be always on.
>

Same time every morning (or week)? Or random?

Maybe you have a forgotten job from the testing phase that drops the
database

2) Database is getting dropped (happened multiple times in the last one
> week). This needs to be fixed.
>

You haven't locked it down tight enough.