Thread: 20220226-Clarification regarding delay time of PostgeSQL starting up

20220226-Clarification regarding delay time of PostgeSQL starting up

From
"Techsupport"
Date:

Hi to all,       

We are using PostgreSQL 12.3, it is running on the Windows Server.

Database data directory is configured in the network shared path. Unexpectedly it was disconnected and up again.

PgAgent is not connected, when I check the Log, it showing

FATAL:  the database system is starting up

FATAL:  the database system is starting up

……………………………

Other than that, there is no other errors available. For nearly 7 hours database is not connected, after that it’s getting connected. Why it is taking too long time to up. Totally our database has 3.5 TB of data.

Suggest me, how to start the server quickly.

Thanks,

Karthick Ramu

 

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
Julien Rouhaud
Date:
Hi,

On Sat, Feb 26, 2022 at 07:51:45PM +0530, Techsupport wrote:
> Hi to all,
>
> We are using PostgreSQL 12.3, it is running on the Windows Server.

So, you're using a version released almost 2 years ago, missing 8 minor
releases worth of bug fixes.  In general, if you have a problem you should just
blindly update to the latest minor version, otherwise it's highly unlikely that
anyone will look at your problem.

That being said...
>
> Database data directory is configured in the network shared path.
> Unexpectedly it was disconnected and up again.
>
> PgAgent is not connected, when I check the Log, it showing
>
> FATAL:  the database system is starting up
>
> FATAL:  the database system is starting up
>
> ...........
>
> Other than that, there is no other errors available.

Really?  This error is displayed if the server is doing some automatic recovery
after an unclean shutdown.  Could be an unrecoverable error like IO error on
the WALs, or just anything fixed in the last 2 years.

You should check for any message with PANIC/FATAL log level too.

> For nearly 7 hours database is not connected, after that it's getting
> connected. Why it is taking too long time to up. Totally our database has 3.5
> TB of data.

Was it 7 uninterrupted hours or did your database keeps being stopped in an
unclean way for 7 hours?  More details from the logs would help, you could also
see https://wiki.postgresql.org/wiki/Guide_to_reporting_problems.  But above
all you should consider updating to the latest minor version as soon as
possible.



Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
Laurenz Albe
Date:
On Sat, 2022-02-26 at 19:51 +0530, Techsupport wrote:
> Database data directory is configured in the network shared path. Unexpectedly it was disconnected and up again.

If you talking about a Windows CIFS (Network Share), you may be out of luck.
That is known to be an unreliable file system that cannot be used with a database.
A crash has likely corrupted your database.
(But we cannot tell for certain, because you didn't show any interesting messages.)

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com




RE: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
"Techsupport"
Date:
Hi,

Thanks for your reply.
Is it a good practice to upgrade / update database on production on every
year ?

In my log
 > FATAL:  the database system is starting up 
 Log showing continuously, other than that there is not PANIC log in the log
file, which is under the log folder. 

Thanks,
  


-----Original Message-----
From: Julien Rouhaud [mailto:rjuju123@gmail.com] 
Sent: Saturday, February 26, 2022 8:27 PM
To: Techsupport
Cc: pgsql-general@lists.postgresql.org
Subject: Re: 20220226-Clarification regarding delay time of PostgeSQL
starting up

Hi,

On Sat, Feb 26, 2022 at 07:51:45PM +0530, Techsupport wrote:
> Hi to all,
>
> We are using PostgreSQL 12.3, it is running on the Windows Server.

So, you're using a version released almost 2 years ago, missing 8 minor
releases worth of bug fixes.  In general, if you have a problem you should
just blindly update to the latest minor version, otherwise it's highly
unlikely that anyone will look at your problem.

That being said...
>
> Database data directory is configured in the network shared path.
> Unexpectedly it was disconnected and up again.
>
> PgAgent is not connected, when I check the Log, it showing
>
> FATAL:  the database system is starting up
>
> FATAL:  the database system is starting up
>
> ...........
>
> Other than that, there is no other errors available.

Really?  This error is displayed if the server is doing some automatic
recovery after an unclean shutdown.  Could be an unrecoverable error like IO
error on the WALs, or just anything fixed in the last 2 years.

You should check for any message with PANIC/FATAL log level too.

> For nearly 7 hours database is not connected, after that it's getting 
> connected. Why it is taking too long time to up. Totally our database 
> has 3.5 TB of data.

Was it 7 uninterrupted hours or did your database keeps being stopped in an
unclean way for 7 hours?  More details from the logs would help, you could
also see https://wiki.postgresql.org/wiki/Guide_to_reporting_problems.  But
above all you should consider updating to the latest minor version as soon
as possible.





Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
"David G. Johnston"
Date:
On Sat, Feb 26, 2022 at 11:34 AM Techsupport <techsupport@sardonyx.in> wrote:

Is it a good practice to upgrade / update database on production on every
year ?


Patches (updates) are released roughly every three months.  You should apply them as soon after they are released as feasible.

We (I) tend to reserve the word upgrade to mean a major version release change.  Releases are supported for roughly 5 years.

David J.

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
Julien Rouhaud
Date:
On Sun, Feb 27, 2022 at 12:04:19AM +0530, Techsupport wrote:
> Hi,
>
> Thanks for your reply.
> Is it a good practice to upgrade / update database on production on every
> year ?

It's a good practice to update all the software you rely on each time a minor
version is released, especially if those contains security fixes.  For postgres
new minor version are released at least every 3 months, and sometimes contain
security fixes.

> In my log
>  > FATAL:  the database system is starting up
>  Log showing continuously, other than that there is not PANIC log in the log
> file, which is under the log folder.

There has to be something else in your logs.  Your system can't go from normal
processing to "the database system is starting up" without showing any evidence
of some kind of problem.  Maybe in the event logs if it's not in your postgres
logs?



Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
Mladen Gogala
Date:
On 2/26/22 10:17, Laurenz Albe wrote:
If you talking about a Windows CIFS (Network Share), you may be out of luck.
That is known to be an unreliable file system that cannot be used with a database.
A crash has likely corrupted your database.
(But we cannot tell for certain, because you didn't show any interesting messages.)

Yours,
Laurenz Albe

Thanks for letting me know, I didn't know that. Are there any such limitations with NFS and NAS devices like NetApp?

Regards

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

Re: 20220226-Clarification regarding delay time of PostgeSQL starting up

From
Mladen Gogala
Date:
On 2/26/22 13:34, Techsupport wrote:
Thanks for your reply.
Is it a good practice to upgrade / update database on production on every
year ?

No, it is not a good practice. Database upgrade must be treated as a project which will result in non-negligible downtime. You should upgrade to the new version if the new version brings about features that you need. Here is the list of new features, introduced in the PostgreSQL 14:

https://www.postgresql.org/docs/14/release-14.html#id-1.11.6.7.5

If you need any of the listed features, you should upgrade. If not, you can safely skip the release. Upgrade is something that has business repercussions, inconveniences the database users and should be subjected to the same cost-benefit analysis as every other major IT move. Upgrade policy is usually set by the management, not by the DBA, much to chagrin of the DBA personnel. I used to be a DBA for a very long time and I know.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com