pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgreSQL Service Unable to Start After Archive Log delete
12+ messages / 6 participants
[nested] [flat]
* PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 12:58 loganathan P <plogandba@gmail.com>
0 siblings, 2 replies; 12+ messages in thread
From: loganathan P @ 2026-09-10 12:58 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org; +Cc: loganathan P <plogandba@gmail.com>
Dear All,
Unfortunately, the OS housekeeping activity has been performed, and all
archive log files has been deleted from both the archive log and pg_wal
locations. We attempted to start the service, but it is unable to start due
to the error shown below. Please provide the solution.
starting PostgreSQL 15.18 on x86_64-pc-linux-gun, compiled by gcc (GCC)
8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
listening on IPv4 address "192.168.56.15", port 5423
listening on Unix socket "var/run/postgresql/.s.PGSQL.5423"
listening on Unix socket "/tmp/ .s.PGSQL.5423"
database system was shut down in recovery at 2026-09-10 11:10:40 IST
invalid primary checkpoint record
PANIC: could not locate a valid checkpoint record
startup process (PID 1031317) was terminated by signal 6: Aborted
terminating any other active sever processes
shutting down due to startup process failure
database system is shut down
Thanks.
Regards,
Loganathan P
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 13:53 Laurenz Albe <laurenz.albe@cybertec.at>
parent: loganathan P <plogandba@gmail.com>
1 sibling, 1 reply; 12+ messages in thread
From: Laurenz Albe @ 2026-09-10 13:53 UTC (permalink / raw)
To: loganathan P <plogandba@gmail.com>; pgsql-general@lists.postgresql.org
On Thu, 2026-09-10 at 18:28 +0530, loganathan P wrote:
> Unfortunately, the OS housekeeping activity has been performed, and all archive
> log files has been deleted from both the archive log and pg_wal locations.
> We attempted to start the service, but it is unable to start due to the error
> shown below. Please provide the solution.
>
> starting PostgreSQL 15.18 on x86_64-pc-linux-gun, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
> listening on IPv4 address "192.168.56.15", port 5423
> listening on Unix socket "var/run/postgresql/.s.PGSQL.5423"
> listening on Unix socket "/tmp/ .s.PGSQL.5423"
> database system was shut down in recovery at 2026-09-10 11:10:40 IST
> invalid primary checkpoint record
> PANIC: could not locate a valid checkpoint record
> startup process (PID 1031317) was terminated by signal 6: Aborted
> terminating any other active sever processes
> shutting down due to startup process failure
> database system is shut down
What you performed was not "the OS housekeeping activity", but the "destroy the
database activity". To use an analogy, I wouldn't consider making a hole in
the roof a housekeeping activity.
This is the time to restore your backup.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 14:17 loganathan P <plogandba@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 3 replies; 12+ messages in thread
From: loganathan P @ 2026-09-10 14:17 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org, loganathan P <plogandba@gmail.com>
Hi,
Backups are available only for 10-Sep-2026 at 02:00 AM IST. Please let us
know if any alternative solution is available.
The issue occurred on 10-Sep-2026 at 11:00 AM IST.
The OS team proceeded with the changes without prior discussion or approval.
Thanks.
Regards,
Loganathan P
On Thu, Sep 10, 2026 at 7:23 PM Laurenz Albe <laurenz.albe@cybertec.at>
wrote:
> On Thu, 2026-09-10 at 18:28 +0530, loganathan P wrote:
> > Unfortunately, the OS housekeeping activity has been performed, and all
> archive
> > log files has been deleted from both the archive log and pg_wal
> locations.
> > We attempted to start the service, but it is unable to start due to the
> error
> > shown below. Please provide the solution.
> >
> > starting PostgreSQL 15.18 on x86_64-pc-linux-gun, compiled by gcc (GCC)
> 8.5.0 20210514 (Red Hat 8.5.0-28), 64-bit
> > listening on IPv4 address "192.168.56.15", port 5423
> > listening on Unix socket "var/run/postgresql/.s.PGSQL.5423"
> > listening on Unix socket "/tmp/ .s.PGSQL.5423"
> > database system was shut down in recovery at 2026-09-10 11:10:40 IST
> > invalid primary checkpoint record
> > PANIC: could not locate a valid checkpoint record
> > startup process (PID 1031317) was terminated by signal 6: Aborted
> > terminating any other active sever processes
> > shutting down due to startup process failure
> > database system is shut down
>
> What you performed was not "the OS housekeeping activity", but the
> "destroy the
> database activity". To use an analogy, I wouldn't consider making a hole
> in
> the roof a housekeeping activity.
>
> This is the time to restore your backup.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 14:44 Adrian Klaver <adrian.klaver@aklaver.com>
parent: loganathan P <plogandba@gmail.com>
2 siblings, 1 reply; 12+ messages in thread
From: Adrian Klaver @ 2026-09-10 14:44 UTC (permalink / raw)
To: loganathan P <plogandba@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-general@lists.postgresql.org
On 9/10/26 7:17 AM, loganathan P wrote:
>
> Hi,
>
> Backups are available only for 10-Sep-2026 at 02:00 AM IST. Please let
> us know if any alternative solution is available.
Given:
"... all archive log files has been deleted from both the archive log
and pg_wal locations."
You are stuck with the 2:00 AM backup.
>
> The issue occurred on 10-Sep-2026 at 11:00 AM IST.
>
>
> The OS team proceeded with the changes without prior discussion or approval.
That is cause for a serious discussion.
>
> Thanks.
>
> Regards,
> Loganathan P
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 15:02 Oli Sennhauser <oli.sennhauser@fromdual.com>
parent: loganathan P <plogandba@gmail.com>
1 sibling, 2 replies; 12+ messages in thread
From: Oli Sennhauser @ 2026-09-10 15:02 UTC (permalink / raw)
To: loganathan P <plogandba@gmail.com>; pgsql-general@lists.postgresql.org
Hi Loganathan
I am a complete PostgreSQL noob! Reading the manual (after googling) I
found this: https://www.postgresql.org/docs/current/app-pgresetwal.html
When I tried to simulate your situation I ended up with this:
I broke it as you described:
2026-09-10 16:49:31.398 CEST [803224] LOG: starting PostgreSQL 18.4 on
x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
2026-09-10 16:49:31.399 CEST [803224] LOG: listening on IPv6 address
"::1", port 5436
2026-09-10 16:49:31.399 CEST [803224] LOG: listening on IPv4 address
"127.0.0.1", port 5436
2026-09-10 16:49:31.404 CEST [803224] LOG: listening on Unix socket
"/tmp/.s.PGSQL.5436"
2026-09-10 16:49:31.415 CEST [803230] LOG: database system was shut
down at 2026-09-10 16:48:53 CEST
2026-09-10 16:49:31.415 CEST [803230] LOG: invalid checkpoint record
2026-09-10 16:49:31.415 CEST [803230] PANIC: could not locate a valid
checkpoint record at 0/7000028
2026-09-10 16:49:31.465 CEST [803224] LOG: startup process (PID 803230)
was terminated by signal 6: Aborted
2026-09-10 16:49:31.465 CEST [803224] LOG: terminating any other active
server processes
2026-09-10 16:49:31.465 CEST [803224] LOG: shutting down due to startup
process failure
2026-09-10 16:49:31.466 CEST [803224] LOG: database system is shut down
Then I did the following:
dba@boss:/mnt/archivedir [postgres-18-t1, 5436]> pg_resetwal
--pgdata=/home/dba/database/postgres-18-t1/data
Write-ahead log reset
dba@boss:/mnt/archivedir [postgres-18-t1, 5436]> start
waiting for server to start.... done
server started
Error Log shows me, that database is up an running again:
2026-09-10 16:55:34.086 CEST [807005] LOG: starting PostgreSQL 18.4 on
x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
2026-09-10 16:55:34.087 CEST [807005] LOG: listening on IPv6 address
"::1", port 5436
2026-09-10 16:55:34.087 CEST [807005] LOG: listening on IPv4 address
"127.0.0.1", port 5436
2026-09-10 16:55:34.091 CEST [807005] LOG: listening on Unix socket
"/tmp/.s.PGSQL.5436"
2026-09-10 16:55:34.102 CEST [807011] LOG: database system was shut
down at 2026-09-10 16:55:18 CEST
2026-09-10 16:55:34.114 CEST [807005] LOG: database system is ready to
accept connections
And application started to work as well...
How consistent your data are now somebody else has to answer...
Hope this helps?
Regards,
Oli
--
FromDual - Neutral and vendor independent MariaDB, MySQL and PostgreSQL services.
FromDual GmbH Rebenweg 6
CH - 8610 Uster
Oli Sennhauser CEO / Senior Consultant
Phone: +41 44 500 58 20 Mobile: +41 79 830 09 33
oli.sennhauser@fromdual.com https://www.fromdual.com
Twitter: fromdual
Attachments:
OpenPGP_0xB58CF11D3C9DDEA9.asc (application/pgp-keys, 1.7K) download
OpenPGP_signature.asc (application/pgp-signature, 494B) download
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 16:06 Justin <zzzzz.graf@gmail.com>
parent: Oli Sennhauser <oli.sennhauser@fromdual.com>
1 sibling, 0 replies; 12+ messages in thread
From: Justin @ 2026-09-10 16:06 UTC (permalink / raw)
To: Oli Sennhauser <oli.sennhauser@fromdual.com>; +Cc: loganathan P <plogandba@gmail.com>; pgsql-general@lists.postgresql.org
On Thu, Sep 10, 2026 at 11:02 AM Oli Sennhauser <oli.sennhauser@fromdual.com>
wrote:
> Hi Loganathan
>
>
> I am a complete PostgreSQL noob! Reading the manual (after googling) I
> found this: https://www.postgresql.org/docs/current/app-pgresetwal.html
>
> When I tried to simulate your situation I ended up with this:
>
>
>
>
Loganathan, and Oli.
Yes you can use pg_resetwal to get PG to start to get more records that
would be lost otherwise.
CAN NOT STRESS THIS ENOUGH using pg_resetwal is the last ditch option
after exploring all other options. Use this to fetch what data can be
fetched. DO NOT USE a database that has used pg_resetwal for any other
activity other than getting missing records.
make a backup of the database before executing pg_walreset. There are
other options to get the data out of the PostgreSQL tables but the catalog
has to be intact. pg_class, pg_attribute, pg_type. loose those tables
the database is toast
Would like to see the DR and backup methods that was being used. If normal
housekeeping included purging WAL directories there is a very big
disconnect between the sysadmins and dbas
Best of luck
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 18:31 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 0 replies; 12+ messages in thread
From: Ron Johnson @ 2026-09-10 18:31 UTC (permalink / raw)
To: pgsql-general
On Thu, Sep 10, 2026 at 10:45 AM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:
> On 9/10/26 7:17 AM, loganathan P wrote:
>
>
> > The issue occurred on 10-Sep-2026 at 11:00 AM IST.
> >
> >
> > The OS team proceeded with the changes without prior discussion or
> approval.
>
> That is cause for a serious discussion.
And is why our company has a strict change control process. Heads roll
quickly when it causes a major incident.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 18:39 Ron Johnson <ronljohnsonjr@gmail.com>
parent: loganathan P <plogandba@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Ron Johnson @ 2026-09-10 18:39 UTC (permalink / raw)
To: pgsql-general
On Thu, Sep 10, 2026 at 10:17 AM loganathan P <plogandba@gmail.com> wrote:
>
> Hi,
>
> Backups are available only for 10-Sep-2026 at 02:00 AM IST. Please let us
> know if any alternative solution is available.
>
> The issue occurred on 10-Sep-2026 at 11:00 AM IST.
>
It appears that you all are doing pg_dump instead of using a PITR backup
method like PgBackrest, barman or pg_basebackup.
Changing your backup process should be added high on your TODO list, since
that would have given you backups up through about 10:30 to 10:45.
Upgrading from EOL v15 to a supported version should also be on that list.
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 20:29 loganathan P <plogandba@gmail.com>
parent: Oli Sennhauser <oli.sennhauser@fromdual.com>
1 sibling, 2 replies; 12+ messages in thread
From: loganathan P @ 2026-09-10 20:29 UTC (permalink / raw)
To: Justin <zzzzz.graf@gmail.com>; pgsql-general@lists.postgresql.org; +Cc: Oli Sennhauser <oli.sennhauser@fromdual.com>; loganathan P <plogandba@gmail.com>
Dear All,
As per the provided link, the pg_resetwal command was executed
successfully, and the services have been started. However, a *rollback
transaction error* is appearing in the Tomcat catalina.out logs. The below
error is also being reported, which indicates that database corruption may
have occurred.
PANIC: failed to add item to index page in "idx_item_sign"
ERROR: index "pg_toast_141429_index" contains unexpected zero page at block
36363
PANIC: failed to add item to index page in "idx_item_01" Call
getNextException to see other errors in the batch.
ERROR: index "pg_toast_141429_index" contains unexpected zero page at block
36363
WARNING: terminating connection because of crash of another server process
DETAIL: the postmaster has commanded this server process to roll back the
current transaction and exit, because another server process exited
abnormally and possibly corrupted shared memory.
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
Thanks.
Regards,
Loganathan P
On Thu, 10 Sept, 2026, 9:50 pm Justin, <zzzzz.graf@gmail.com> wrote:
> [image: Boxbe] <https://www.boxbe.com/overview; Justin (
> zzzzz.graf@gmail.com) added themselves to your Guest List
> <https://www.boxbe.com/approved-list?tc_serial=61917440713&tc_rand=1585156446&utm_source=stf&...;
> | Remove them
> <https://www.boxbe.com/anno?action=remove&tc_serial=61917440713&tc_rand=1585156446&utm_so...;
> | Block them
> <https://www.boxbe.com/anno?action=add&disp=b&tc_serial=61917440713&tc_rand=1585156446&am...;
>
>
> On Thu, Sep 10, 2026 at 11:02 AM Oli Sennhauser <
> oli.sennhauser@fromdual.com> wrote:
>
>> Hi Loganathan
>>
>>
>> I am a complete PostgreSQL noob! Reading the manual (after googling) I
>> found this: https://www.postgresql.org/docs/current/app-pgresetwal.html
>>
>> When I tried to simulate your situation I ended up with this:
>>
>>
>>
>>
> Loganathan, and Oli.
> Yes you can use pg_resetwal to get PG to start to get more records that
> would be lost otherwise.
>
> CAN NOT STRESS THIS ENOUGH using pg_resetwal is the last ditch option
> after exploring all other options. Use this to fetch what data can be
> fetched. DO NOT USE a database that has used pg_resetwal for any other
> activity other than getting missing records.
>
> make a backup of the database before executing pg_walreset. There are
> other options to get the data out of the PostgreSQL tables but the catalog
> has to be intact. pg_class, pg_attribute, pg_type. loose those tables
> the database is toast
>
> Would like to see the DR and backup methods that was being used. If normal
> housekeeping included purging WAL directories there is a very big
> disconnect between the sysadmins and dbas
>
> Best of luck
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 20:56 Justin <zzzzz.graf@gmail.com>
parent: loganathan P <plogandba@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Justin @ 2026-09-10 20:56 UTC (permalink / raw)
To: loganathan P <plogandba@gmail.com>; +Cc: pgsql-general@lists.postgresql.org, Oli Sennhauser <oli.sennhauser@fromdual.com>
HI Loganathan,
There is corruption specific to a toasted column index . Do NOT allow the
application to connect to the database. Application will most likely make
the corruption worse if it starts writing to the database
Do a pg_dump if that fails. then start doing pg_dumps one table at a time
to extract what you can.
SELECT * FROM xxxx PosgreSQL will just read the table out without using
indexes.
If you get toast table corruption, SELECT <all columns that are
not toasted> FROM XXXX
If you get table corruption that is to specific data pages then you need
to figure out WHERE clauses to avoid the corrupt datapage.
Can use a page inspector to look at the raw table data to maybe get the
corrupted datapages back or skip the corrupted pages .
https://www.postgresql.org/docs/current/pageinspect.html
>
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 21:21 Laurenz Albe <laurenz.albe@cybertec.at>
parent: loganathan P <plogandba@gmail.com>
1 sibling, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2026-09-10 21:21 UTC (permalink / raw)
To: loganathan P <plogandba@gmail.com>; Justin <zzzzz.graf@gmail.com>; pgsql-general@lists.postgresql.org; +Cc: Oli Sennhauser <oli.sennhauser@fromdual.com>
On Fri, 2026-09-11 at 01:59 +0530, loganathan P wrote:
> As per the provided link, the pg_resetwal command was executed successfully, and the services
> have been started. However, a rollback transaction error is appearing in the Tomcat catalina.out logs.
That's because pg_resetwal has the side effect of corrupting your database.
It is *not* a tool to fix database corruption.
It is a tool to get a corrupted server to somehow start so that you can
more easily salvage those data that can be salvaged.
Restore your backup.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
* Re: PostgreSQL Service Unable to Start After Archive Log delete
@ 2026-09-10 21:26 Laurenz Albe <laurenz.albe@cybertec.at>
parent: loganathan P <plogandba@gmail.com>
2 siblings, 0 replies; 12+ messages in thread
From: Laurenz Albe @ 2026-09-10 21:26 UTC (permalink / raw)
To: loganathan P <plogandba@gmail.com>; +Cc: pgsql-general@lists.postgresql.org
On Thu, 2026-09-10 at 19:47 +0530, loganathan P wrote:
> Backups are available only for 10-Sep-2026 at 02:00 AM IST.
> Please let us know if any alternative solution is available.
There is no good alternative to restoring your backup.
Particularly if you are not a PostgreSQL expert.
> The issue occurred on 10-Sep-2026 at 11:00 AM IST.
Count yourself lucky. You are only losing 9 hours of data.
That's better than staying down for days in a desperate attempt to
fix the data loss in some other fashion.
In the future, you should use a better backup strategy that allows
point-in-time-recovery to reduce the data loss.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 12+ messages in thread
end of thread, other threads:[~2026-09-10 21:26 UTC | newest]
Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-10 12:58 PostgreSQL Service Unable to Start After Archive Log delete loganathan P <plogandba@gmail.com>
2026-09-10 13:53 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-09-10 14:17 ` loganathan P <plogandba@gmail.com>
2026-09-10 14:44 ` Adrian Klaver <adrian.klaver@aklaver.com>
2026-09-10 18:31 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-10 18:39 ` Ron Johnson <ronljohnsonjr@gmail.com>
2026-09-10 21:26 ` Laurenz Albe <laurenz.albe@cybertec.at>
2026-09-10 15:02 ` Oli Sennhauser <oli.sennhauser@fromdual.com>
2026-09-10 16:06 ` Justin <zzzzz.graf@gmail.com>
2026-09-10 20:29 ` loganathan P <plogandba@gmail.com>
2026-09-10 20:56 ` Justin <zzzzz.graf@gmail.com>
2026-09-10 21:21 ` Laurenz Albe <laurenz.albe@cybertec.at>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox