pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedstarting postgres with an empty px_xlog folder
10+ messages / 7 participants
[nested] [flat]
* starting postgres with an empty px_xlog folder
@ 2012-06-23 12:01 Mike Broers <mbroers@gmail.com>
2012-06-23 12:49 ` Re: starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
2012-06-23 14:45 ` Re: starting postgres with an empty px_xlog folder Ray Stell <stellr@vt.edu>
0 siblings, 2 replies; 10+ messages in thread
From: Mike Broers @ 2012-06-23 12:01 UTC (permalink / raw)
To: pgsql-admin
Hello,
We shut down our postgres 8.3 server last night cleanly for some hosted
services maintenance. When we got our server back, it didnt have the
pg_xlog mount with files and now when we start the server, it complains:
2012-06-23 06:06:04 CDT [18612]: [1-1] user=,db= LOG: database system was
shut down at 2012-06-22 23:56:16 CDT
2012-06-23 06:06:04 CDT [18612]: [2-1] user=,db= LOG: could not open file
"pg_xlog/0000000100000A0100000040" (log file 2561, segment 64): No such
file or
directory
2012-06-23 06:06:04 CDT [18612]: [3-1] user=,db= LOG: invalid primary
checkpoint record
2012-06-23 06:06:04 CDT [18612]: [4-1] user=,db= LOG: could not open file
"pg_xlog/0000000100000A0100000040" (log file 2561, segment 64): No such
file or
directory
2012-06-23 06:06:04 CDT [18612]: [5-1] user=,db= LOG: invalid secondary
checkpoint record
2012-06-23 06:06:04 CDT [18612]: [6-1] user=,db= PANIC: could not locate a
valid checkpoint record
2012-06-23 06:06:04 CDT [18609]: [1-1] user=,db= LOG: startup process (PID
18612) was terminated by signal 6: Aborted
2012-06-23 06:06:04 CDT [18609]: [2-1] user=,db= LOG: aborting startup due
to startup process failure
Since we had a clean shut down is there an easy or relatively clean way to
start back up? the pg_xlog folder is there, just with no files. i wouldnt
think it would be needed for a clean restart.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 12:01 starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
@ 2012-06-23 12:49 ` Mike Broers <mbroers@gmail.com>
2012-06-23 13:36 ` Re: starting postgres with an empty px_xlog folder Raghavendra <raghavendra.rao@enterprisedb.com>
1 sibling, 1 reply; 10+ messages in thread
From: Mike Broers @ 2012-06-23 12:49 UTC (permalink / raw)
To: pgsql-admin
I was able to duplicate the error in a test environment and get the
database started back up wtih the pg_resetxlog $PGDATA command, but i am
concerned about the fallout and not sure if i need to use any of the option
switches. We also have archived log files up to the point of server
shutdown if that provides a better option.
On Sat, Jun 23, 2012 at 7:01 AM, Mike Broers <mbroers@gmail.com> wrote:
> Hello,
>
> We shut down our postgres 8.3 server last night cleanly for some hosted
> services maintenance. When we got our server back, it didnt have the
> pg_xlog mount with files and now when we start the server, it complains:
>
> 2012-06-23 06:06:04 CDT [18612]: [1-1] user=,db= LOG: database system was
> shut down at 2012-06-22 23:56:16 CDT
> 2012-06-23 06:06:04 CDT [18612]: [2-1] user=,db= LOG: could not open file
> "pg_xlog/0000000100000A0100000040" (log file 2561, segment 64): No such
> file or
> directory
> 2012-06-23 06:06:04 CDT [18612]: [3-1] user=,db= LOG: invalid primary
> checkpoint record
> 2012-06-23 06:06:04 CDT [18612]: [4-1] user=,db= LOG: could not open file
> "pg_xlog/0000000100000A0100000040" (log file 2561, segment 64): No such
> file or
> directory
> 2012-06-23 06:06:04 CDT [18612]: [5-1] user=,db= LOG: invalid secondary
> checkpoint record
> 2012-06-23 06:06:04 CDT [18612]: [6-1] user=,db= PANIC: could not locate
> a valid checkpoint record
> 2012-06-23 06:06:04 CDT [18609]: [1-1] user=,db= LOG: startup process
> (PID 18612) was terminated by signal 6: Aborted
> 2012-06-23 06:06:04 CDT [18609]: [2-1] user=,db= LOG: aborting startup
> due to startup process failure
>
>
> Since we had a clean shut down is there an easy or relatively clean way to
> start back up? the pg_xlog folder is there, just with no files. i wouldnt
> think it would be needed for a clean restart.
>
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 12:01 starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
2012-06-23 12:49 ` Re: starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
@ 2012-06-23 13:36 ` Raghavendra <raghavendra.rao@enterprisedb.com>
0 siblings, 0 replies; 10+ messages in thread
From: Raghavendra @ 2012-06-23 13:36 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; +Cc: pgsql-admin
>
> 2012-06-23 06:06:04 CDT [18612]: [2-1] user=,db= LOG: could not open file
> "pg_xlog/0000000100000A0100000040" (log file 2561, segment 64): No such
> file or
> directory
Don't do pg_resetlogs. If you have archives, please find for the file which
has pointed in logs copy it to $PGDATA/pg_xlog location and start the
server. Sometime, server will throw the sequence of files like 41,42....,
not existing, you need to copy all of them to pg_xlog location and start it.
---
Regards,
Raghavendra
EnterpriseDB Corporation
Blog: http://raghavt.blogspot.com/
On Sat, Jun 23, 2012 at 6:19 PM, Mike Broers <mbroers@gmail.com> wrote:
> I was able to duplicate the error in a test environment and get the
> database started back up wtih the pg_resetxlog $PGDATA command, but i am
> concerned about the fallout and not sure if i need to use any of the option
> switches. We also have archived log files up to the point of server
> shutdown if that provides a better option.
>
>
>
> On Sat, Jun 23, 2012 at 7:01 AM, Mike Broers <mbroers@gmail.com> wrote:
>
>> Hello,
>>
>> We shut down our postgres 8.3 server last night cleanly for some hosted
>> services maintenance. When we got our server back, it didnt have the
>> pg_xlog mount with files and now when we start the server, it complains:
>>
>> 2012-06-23 06:06:04 CDT [18612]: [1-1] user=,db= LOG: database system
>> was shut down at 2012-06-22 23:56:16 CDT
>>
>> 2012-06-23 06:06:04 CDT [18612]: [3-1] user=,db= LOG: invalid primary
>> checkpoint record
>> 2012-06-23 06:06:04 CDT [18612]: [4-1] user=,db= LOG: could not open
>> file "pg_xlog/0000000100000A0100000040" (log file 2561, segment 64): No
>> such file or
>> directory
>> 2012-06-23 06:06:04 CDT [18612]: [5-1] user=,db= LOG: invalid secondary
>> checkpoint record
>> 2012-06-23 06:06:04 CDT [18612]: [6-1] user=,db= PANIC: could not locate
>> a valid checkpoint record
>> 2012-06-23 06:06:04 CDT [18609]: [1-1] user=,db= LOG: startup process
>> (PID 18612) was terminated by signal 6: Aborted
>> 2012-06-23 06:06:04 CDT [18609]: [2-1] user=,db= LOG: aborting startup
>> due to startup process failure
>>
>>
>> Since we had a clean shut down is there an easy or relatively clean way
>> to start back up? the pg_xlog folder is there, just with no files. i
>> wouldnt think it would be needed for a clean restart.
>>
>>
>>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 12:01 starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
@ 2012-06-23 14:45 ` Ray Stell <stellr@vt.edu>
1 sibling, 0 replies; 10+ messages in thread
From: Ray Stell @ 2012-06-23 14:45 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; +Cc: pgsql-admin
On Sat, Jun 23, 2012 at 07:01:08AM -0500, Mike Broers wrote:
> Hello,
>
> We shut down our postgres 8.3 server last night cleanly for some hosted
> services maintenance. When we got our server back, it didnt have the
> pg_xlog mount with files
what happened to make the files not available? Seems like you may want
to know the answer to this question for future reference.
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
@ 2012-06-23 19:45 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-24 07:15 ` Re: starting postgres with an empty px_xlog folder Craig Ringer <ringerc@ringerc.id.au>
0 siblings, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2012-06-23 19:45 UTC (permalink / raw)
To: mbroers@gmail.com; pgsql-admin
Mike Broers wrote:
> Mike Broers wrote:
>> We shut down our postgres 8.3 server last night cleanly for some
>> hosted services maintenance. When we got our server back, it didnt
>> have the pg_xlog mount with files and now when we start the
>> server, it complains
>> Since we had a clean shut down is there an easy or relatively
>> clean way to start back up? the pg_xlog folder is there, just with
>> no files. i wouldnt think it would be needed for a clean restart.
It is.
> I was able to duplicate the error in a test environment and get the
> database started back up wtih the pg_resetxlog $PGDATA command, but
> i am concerned about the fallout and not sure if i need to use any
> of the option switches. We also have archived log files up to the
> point of server shutdown if that provides a better option.
I would make a copy of the directory tree of the database cluster
while the server is stopped before attempting any recovery.
http://wiki.postgresql.org/wiki/Corruption
The archived WAL files, may provide a better option that
pg_resetxlog.
If it were my data, I would be inclined to use pg_dump to create a
fresh version of the database after recovery, although if you really
have a full set of archived WAL files and the server starts up
without any indications of problems in the server log, you might be
OK without doing that.
As previously stated, make sure you understand how this happened, so
you can make sure it doesn't happen again. The contents of the
pg_xlog directory are an integral part of your database cluster.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 19:45 Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-24 07:15 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-24 08:15 ` Re: starting postgres with an empty px_xlog folder Stuart Bishop <stuart@stuartbishop.net>
2012-06-25 14:45 ` Re: starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
0 siblings, 2 replies; 10+ messages in thread
From: Craig Ringer @ 2012-06-24 07:15 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: mbroers@gmail.com; pgsql-admin
On 06/24/2012 03:45 AM, Kevin Grittner wrote:
> As previously stated, make sure you understand how this happened, so
> you can make sure it doesn't happen again. The contents of the
> pg_xlog directory are an integral part of your database cluster.
People not backing up pg_xlog, deleting its contents, etc happens often
enough that I wonder if it should have a prominent 00_README or
00_WARNING_DONT_DELETE file created by initdb - or simply be renamed to
something scarier like "base_txrecords".
The annoyance of a readme is that it'd need translation.
People are used to logs being disposable. Anyone who's been responsible
for a database should ideally know better than to assume that
*transation* logs are disposable, but everyone has to learn sometime,
and not everybody does so by reading TFM (unfortunately).
--
Craig Ringer
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 19:45 Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-24 07:15 ` Re: starting postgres with an empty px_xlog folder Craig Ringer <ringerc@ringerc.id.au>
@ 2012-06-24 08:15 ` Stuart Bishop <stuart@stuartbishop.net>
1 sibling, 0 replies; 10+ messages in thread
From: Stuart Bishop @ 2012-06-24 08:15 UTC (permalink / raw)
To: Craig Ringer <ringerc@ringerc.id.au>; +Cc: pgsql-admin
On Sun, Jun 24, 2012 at 2:15 PM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 06/24/2012 03:45 AM, Kevin Grittner wrote:
>>
>> As previously stated, make sure you understand how this happened, so
>> you can make sure it doesn't happen again. The contents of the
>> pg_xlog directory are an integral part of your database cluster.
>
>
> People not backing up pg_xlog, deleting its contents, etc happens often
> enough that I wonder if it should have a prominent 00_README or
> 00_WARNING_DONT_DELETE file created by initdb - or simply be renamed to
> something scarier like "base_txrecords".
This doesn't help when your backup skips them because the files exist
on another disk partition. Out of sight, out of mind.
If pg_basebackup defaulted to --xlog and its use was encouraged these
mistakes might be less common.
--
Stuart Bishop <stuart@stuartbishop.net>
http://www.stuartbishop.net/
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 19:45 Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-24 07:15 ` Re: starting postgres with an empty px_xlog folder Craig Ringer <ringerc@ringerc.id.au>
@ 2012-06-25 14:45 ` Mike Broers <mbroers@gmail.com>
2012-06-25 15:19 ` Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 1 reply; 10+ messages in thread
From: Mike Broers @ 2012-06-25 14:45 UTC (permalink / raw)
To: pgsql-admin
Ultimately the hosting service restored the files that they had not brought
over during their maintenance migration and we started up ok. So that was
a relief.
We had archived log files but it did not appear that the archive
destination was caught up with the xlog the cluster was complaining about.
Given that the database server was shut down cleanly, and all other data
besides pg_xlog was available as expected (not corrupted), what would have
been the problem with pg_resetxlogs?
On Sun, Jun 24, 2012 at 2:15 AM, Craig Ringer <ringerc@ringerc.id.au> wrote:
> On 06/24/2012 03:45 AM, Kevin Grittner wrote:
>
>> As previously stated, make sure you understand how this happened, so
>> you can make sure it doesn't happen again. The contents of the
>> pg_xlog directory are an integral part of your database cluster.
>>
>
> People not backing up pg_xlog, deleting its contents, etc happens often
> enough that I wonder if it should have a prominent 00_README or
> 00_WARNING_DONT_DELETE file created by initdb - or simply be renamed to
> something scarier like "base_txrecords".
>
> The annoyance of a readme is that it'd need translation.
>
> People are used to logs being disposable. Anyone who's been responsible
> for a database should ideally know better than to assume that *transation*
> logs are disposable, but everyone has to learn sometime, and not everybody
> does so by reading TFM (unfortunately).
>
> --
> Craig Ringer
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 19:45 Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-24 07:15 ` Re: starting postgres with an empty px_xlog folder Craig Ringer <ringerc@ringerc.id.au>
2012-06-25 14:45 ` Re: starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
@ 2012-06-25 15:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-25 15:44 ` Re: starting postgres with an empty px_xlog folder Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Kevin Grittner @ 2012-06-25 15:19 UTC (permalink / raw)
To: Mike Broers <mbroers@gmail.com>; pgsql-admin
Mike Broers <mbroers@gmail.com> wrote:
> Ultimately the hosting service restored the files that they had
> not brought over during their maintenance migration and we started
> up ok. So that was a relief.
+1
> We had archived log files but it did not appear that the archive
> destination was caught up with the xlog the cluster was
> complaining about.
>
> Given that the database server was shut down cleanly, and all
> other data besides pg_xlog was available as expected (not
> corrupted), what would have been the problem with pg_resetxlogs?
Frankly, the odds would have been pretty good that you would have
come up without lost data or a corrupted database; but it's a matter
of the degree of confidence in that. Startup and shutdown code, by
its nature, is not exercised as heavily as most PostgreSQL code.
Startup after using a data recovery utility is even less heavily
exercised. Less frequently executed code is more likely to have
subtle bugs which only show up in rare corner cases. I like to
minimize my risk.
-Kevin
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: starting postgres with an empty px_xlog folder
2012-06-23 19:45 Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-24 07:15 ` Re: starting postgres with an empty px_xlog folder Craig Ringer <ringerc@ringerc.id.au>
2012-06-25 14:45 ` Re: starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
2012-06-25 15:19 ` Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2012-06-25 15:44 ` Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Scott Marlowe @ 2012-06-25 15:44 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: Mike Broers <mbroers@gmail.com>; pgsql-admin
On Mon, Jun 25, 2012 at 9:19 AM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
> Mike Broers <mbroers@gmail.com> wrote:
>
>> Ultimately the hosting service restored the files that they had
>> not brought over during their maintenance migration and we started
>> up ok. So that was a relief.
>
> +1
>
>> We had archived log files but it did not appear that the archive
>> destination was caught up with the xlog the cluster was
>> complaining about.
>>
>> Given that the database server was shut down cleanly, and all
>> other data besides pg_xlog was available as expected (not
>> corrupted), what would have been the problem with pg_resetxlogs?
>
> Frankly, the odds would have been pretty good that you would have
> come up without lost data or a corrupted database; but it's a matter
> of the degree of confidence in that. Startup and shutdown code, by
> its nature, is not exercised as heavily as most PostgreSQL code.
> Startup after using a data recovery utility is even less heavily
> exercised. Less frequently executed code is more likely to have
> subtle bugs which only show up in rare corner cases. I like to
> minimize my risk.
>
> -Kevin
Agreed 100%. Also, this brings up the question of how your server
setup is documented by the hosting providor. If you've got remote
drives, virtual drives or whatever, if needs to be documented there in
such a way they can move you without losing things. Or you'll have to
remind them each time you have a separate mount point that needs to
get transferred, but that's error prone.
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2012-06-25 15:44 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-06-23 12:01 starting postgres with an empty px_xlog folder Mike Broers <mbroers@gmail.com>
2012-06-23 12:49 ` Mike Broers <mbroers@gmail.com>
2012-06-23 13:36 ` Raghavendra <raghavendra.rao@enterprisedb.com>
2012-06-23 14:45 ` Ray Stell <stellr@vt.edu>
2012-06-23 19:45 Re: starting postgres with an empty px_xlog folder Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-24 07:15 ` Craig Ringer <ringerc@ringerc.id.au>
2012-06-24 08:15 ` Stuart Bishop <stuart@stuartbishop.net>
2012-06-25 14:45 ` Mike Broers <mbroers@gmail.com>
2012-06-25 15:19 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-06-25 15:44 ` Scott Marlowe <scott.marlowe@gmail.com>
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