Thread: where is pg_resetxlog ?

where is pg_resetxlog ?

From
Kevin Kempter
Date:
Hi all;

I'm trying to restore from a tar of the filesystem on a debian box and I get
xlog errors.

I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would
I find pg_resetxlog on a debian box that was installed via the deb packages ?
or how do I get it ?




Thanks in advance.. here's my log startup messages in case it helps:


Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [1-1] time:2009-07-25
18:40:17.446 UTC database: LOG:  could not load root certificate file
"root.crt": no SSL error reported
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [1-2] time:2009-07-25
18:40:17.446 UTC database: DETAIL:  Will not verify client certificates.
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [2-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  database system was shut down at 2009-07-23
01:18:04 UTC
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [3-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  could not open file
"pg_xlog/00000001000004B6000000C7" (log file 1206, segment
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [3-2]  199): No such file or
directory
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [4-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  invalid primary checkpoint record
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [5-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  could not open file
"pg_xlog/00000001000004B6000000C7" (log file 1206, segment
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [5-2]  199): No such file or
directory
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [6-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  invalid secondary checkpoint record
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16043]: [7-1] time:2009-07-25
18:40:17.653 UTC database: PANIC:  could not locate a valid checkpoint record
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [2-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  startup process (PID 16043) was terminated by
signal 6: Aborted
Jul 25 18:40:17 ux-dbs01-atl2 postgres[16042]: [3-1] time:2009-07-25
18:40:17.653 UTC database: LOG:  aborting startup due to startup process
failure


Re: where is pg_resetxlog ?

From
Scott Marlowe
Date:
On Sat, Jul 25, 2009 at 12:55 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
> Hi all;
>
> I'm trying to restore from a tar of the filesystem on a debian box and I get
> xlog errors.
>
> I suspect I need to run pg_resetxlog but I cannot find it anywhere, where would
> I find pg_resetxlog on a debian box that was installed via the deb packages ?
> or how do I get it ?

Wait, if you're restoring a backup, to a freshly initted db, then you
shouldn't be getting any kind of pg_xlog errors.  If you are, then
there's likely something wrong with your server that pg_resetxlog
isn't going to fix in the long term.  Are you running on windows with
anti-virus software or have some other kind of possible problem that
could be causing a problem with the postmaster writing to the hard
drives?

Re: where is pg_resetxlog ?

From
Kevin Kempter
Date:
On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
>
> Kempter<kevink@consistentstate.com> wrote:
> > Hi all;
> >
> > I'm trying to restore from a tar of the filesystem on a debian box and I
> > get xlog errors.
> >
> > I suspect I need to run pg_resetxlog but I cannot find it anywhere, where
> > would I find pg_resetxlog on a debian box that was installed via the deb
> > packages ? or how do I get it ?
>
> Wait, if you're restoring a backup, to a freshly initted db, then you
> shouldn't be getting any kind of pg_xlog errors.  If you are, then
> there's likely something wrong with your server that pg_resetxlog
> isn't going to fix in the long term.  Are you running on windows with
> anti-virus software or have some other kind of possible problem that
> could be causing a problem with the postmaster writing to the hard
> drives?

we're not restoring from a pg_dump. We were in the process of moving the db to
a new server. We brought the db down (on host A) and did an rsync of all the
db dir's (including tablespace dir's) to host B.

Then bad things were done to host A and we want to get back to where we
started. So, we stopped the db on host A and rsync'ed the files back from host
B to host A.  Now when I try and start the db I see all these tx sement errors
in the log.

We're actually ok if we loose anything that was in the pg_xlog dir.

Thoughts?

Re: where is pg_resetxlog ?

From
Scott Marlowe
Date:
On Sat, Jul 25, 2009 at 1:08 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
> On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
>> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
>>
>> Kempter<kevink@consistentstate.com> wrote:
>> > Hi all;
>> >
>> > I'm trying to restore from a tar of the filesystem on a debian box and I
>> > get xlog errors.
>> >
>> > I suspect I need to run pg_resetxlog but I cannot find it anywhere, where
>> > would I find pg_resetxlog on a debian box that was installed via the deb
>> > packages ? or how do I get it ?
>>
>> Wait, if you're restoring a backup, to a freshly initted db, then you
>> shouldn't be getting any kind of pg_xlog errors.  If you are, then
>> there's likely something wrong with your server that pg_resetxlog
>> isn't going to fix in the long term.  Are you running on windows with
>> anti-virus software or have some other kind of possible problem that
>> could be causing a problem with the postmaster writing to the hard
>> drives?
>
> we're not restoring from a pg_dump. We were in the process of moving the db to
> a new server. We brought the db down (on host A) and did an rsync of all the
> db dir's (including tablespace dir's) to host B.
>
> Then bad things were done to host A and we want to get back to where we
> started. So, we stopped the db on host A and rsync'ed the files back from host
> B to host A.  Now when I try and start the db I see all these tx sement errors
> in the log.
>
> We're actually ok if we loose anything that was in the pg_xlog dir.

Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
just the base directory?  You really need to do an rsync of
everything, not most everything.

Re: where is pg_resetxlog ?

From
Kevin Kempter
Date:
On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
> On Sat, Jul 25, 2009 at 1:08 PM, Kevin
>
> Kempter<kevink@consistentstate.com> wrote:
> > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
> >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
> >>
> >> Kempter<kevink@consistentstate.com> wrote:
> >> > Hi all;
> >> >
> >> > I'm trying to restore from a tar of the filesystem on a debian box and
> >> > I get xlog errors.
> >> >
> >> > I suspect I need to run pg_resetxlog but I cannot find it anywhere,
> >> > where would I find pg_resetxlog on a debian box that was installed via
> >> > the deb packages ? or how do I get it ?
> >>
> >> Wait, if you're restoring a backup, to a freshly initted db, then you
> >> shouldn't be getting any kind of pg_xlog errors.  If you are, then
> >> there's likely something wrong with your server that pg_resetxlog
> >> isn't going to fix in the long term.  Are you running on windows with
> >> anti-virus software or have some other kind of possible problem that
> >> could be causing a problem with the postmaster writing to the hard
> >> drives?
> >
> > we're not restoring from a pg_dump. We were in the process of moving the
> > db to a new server. We brought the db down (on host A) and did an rsync
> > of all the db dir's (including tablespace dir's) to host B.
> >
> > Then bad things were done to host A and we want to get back to where we
> > started. So, we stopped the db on host A and rsync'ed the files back from
> > host B to host A.  Now when I try and start the db I see all these tx
> > sement errors in the log.
> >
> > We're actually ok if we loose anything that was in the pg_xlog dir.
>
> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
> just the base directory?  You really need to do an rsync of
> everything, not most everything.

we rsync'ed EVERYTHING (the entire directory and all sub-dirs)

Re: where is pg_resetxlog ?

From
"Daniel Verite"
Date:
    Kevin Kempter wrote:

> I suspect I need to run pg_resetxlog but I cannot find it anywhere, where
> would
> I find pg_resetxlog on a debian box that was installed via the deb packages
> ?
> or how do I get it ?

It's under /usr/lib/postgresql/8.3/bin

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage: http://www.manitou-mail.org

Re: where is pg_resetxlog ?

From
Tom Lane
Date:
Kevin Kempter <kevink@consistentstate.com> writes:
> On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
>> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
>> just the base directory?  You really need to do an rsync of
>> everything, not most everything.

> we rsync'ed EVERYTHING (the entire directory and all sub-dirs)

Those log entries are proof positive that you forgot pg_xlog.

Maybe you had pg_xlog/ symlinked to someplace else on the old server
and forgot to replicate that arrangement on the new one?

            regards, tom lane

Re: where is pg_resetxlog ?

From
Scott Marlowe
Date:
On Sat, Jul 25, 2009 at 1:30 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
> On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
>> On Sat, Jul 25, 2009 at 1:08 PM, Kevin
>>
>> Kempter<kevink@consistentstate.com> wrote:
>> > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
>> >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
>> >>
>> >> Kempter<kevink@consistentstate.com> wrote:
>> >> > Hi all;
>> >> >
>> >> > I'm trying to restore from a tar of the filesystem on a debian box and
>> >> > I get xlog errors.
>> >> >
>> >> > I suspect I need to run pg_resetxlog but I cannot find it anywhere,
>> >> > where would I find pg_resetxlog on a debian box that was installed via
>> >> > the deb packages ? or how do I get it ?
>> >>
>> >> Wait, if you're restoring a backup, to a freshly initted db, then you
>> >> shouldn't be getting any kind of pg_xlog errors.  If you are, then
>> >> there's likely something wrong with your server that pg_resetxlog
>> >> isn't going to fix in the long term.  Are you running on windows with
>> >> anti-virus software or have some other kind of possible problem that
>> >> could be causing a problem with the postmaster writing to the hard
>> >> drives?
>> >
>> > we're not restoring from a pg_dump. We were in the process of moving the
>> > db to a new server. We brought the db down (on host A) and did an rsync
>> > of all the db dir's (including tablespace dir's) to host B.
>> >
>> > Then bad things were done to host A and we want to get back to where we
>> > started. So, we stopped the db on host A and rsync'ed the files back from
>> > host B to host A.  Now when I try and start the db I see all these tx
>> > sement errors in the log.
>> >
>> > We're actually ok if we loose anything that was in the pg_xlog dir.
>>
>> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
>> just the base directory?  You really need to do an rsync of
>> everything, not most everything.
>
> we rsync'ed EVERYTHING (the entire directory and all sub-dirs)

OK, on my laptop, in the /var/lib/postgresql/8.3/main there's a bunch
of dirs that look like this:

base    pg_clog       pg_subtrans  pg_twophase  pg_xlog
postmaster.pid  server.crt
global  pg_multixact  pg_tblspc    PG_VERSION   postmaster.opts
root.crt        server.key

Did you rsync the base dir only, or all of the directories above?
Cause if you just rsynced base, you need the rest of them, as well.

Re: where is pg_resetxlog ?

From
Kevin Kempter
Date:
On Saturday 25 July 2009 20:05:14 Scott Marlowe wrote:
> On Sat, Jul 25, 2009 at 1:30 PM, Kevin
>
> Kempter<kevink@consistentstate.com> wrote:
> > On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
> >> On Sat, Jul 25, 2009 at 1:08 PM, Kevin
> >>
> >> Kempter<kevink@consistentstate.com> wrote:
> >> > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
> >> >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
> >> >>
> >> >> Kempter<kevink@consistentstate.com> wrote:
> >> >> > Hi all;
> >> >> >
> >> >> > I'm trying to restore from a tar of the filesystem on a debian box
> >> >> > and I get xlog errors.
> >> >> >
> >> >> > I suspect I need to run pg_resetxlog but I cannot find it anywhere,
> >> >> > where would I find pg_resetxlog on a debian box that was installed
> >> >> > via the deb packages ? or how do I get it ?
> >> >>
> >> >> Wait, if you're restoring a backup, to a freshly initted db, then you
> >> >> shouldn't be getting any kind of pg_xlog errors.  If you are, then
> >> >> there's likely something wrong with your server that pg_resetxlog
> >> >> isn't going to fix in the long term.  Are you running on windows with
> >> >> anti-virus software or have some other kind of possible problem that
> >> >> could be causing a problem with the postmaster writing to the hard
> >> >> drives?
> >> >
> >> > we're not restoring from a pg_dump. We were in the process of moving
> >> > the db to a new server. We brought the db down (on host A) and did an
> >> > rsync of all the db dir's (including tablespace dir's) to host B.
> >> >
> >> > Then bad things were done to host A and we want to get back to where
> >> > we started. So, we stopped the db on host A and rsync'ed the files
> >> > back from host B to host A.  Now when I try and start the db I see all
> >> > these tx sement errors in the log.
> >> >
> >> > We're actually ok if we loose anything that was in the pg_xlog dir.
> >>
> >> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
> >> just the base directory?  You really need to do an rsync of
> >> everything, not most everything.
> >
> > we rsync'ed EVERYTHING (the entire directory and all sub-dirs)
>
> OK, on my laptop, in the /var/lib/postgresql/8.3/main there's a bunch
> of dirs that look like this:
>
> base    pg_clog       pg_subtrans  pg_twophase  pg_xlog
> postmaster.pid  server.crt
> global  pg_multixact  pg_tblspc    PG_VERSION   postmaster.opts
> root.crt        server.key
>
> Did you rsync the base dir only, or all of the directories above?
> Cause if you just rsynced base, you need the rest of them, as well.

I rsync'd the /var/lib/postgresql/8.3/main dir. However several of the sub-
dirs were soft links (pg_xlog, server.cert, root.cert and server.key) so I
unfortunately did not get the data from the links' real location since they
all pointed to outside of the /var/lib/postgresql/8.3/main tree




Re: where is pg_resetxlog ?

From
Scott Marlowe
Date:
On Sat, Jul 25, 2009 at 9:48 PM, Kevin
Kempter<kevink@consistentstate.com> wrote:
> On Saturday 25 July 2009 20:05:14 Scott Marlowe wrote:
>> On Sat, Jul 25, 2009 at 1:30 PM, Kevin
>>
>> Kempter<kevink@consistentstate.com> wrote:
>> > On Saturday 25 July 2009 13:23:54 Scott Marlowe wrote:
>> >> On Sat, Jul 25, 2009 at 1:08 PM, Kevin
>> >>
>> >> Kempter<kevink@consistentstate.com> wrote:
>> >> > On Saturday 25 July 2009 13:02:52 Scott Marlowe wrote:
>> >> >> On Sat, Jul 25, 2009 at 12:55 PM, Kevin
>> >> >>
>> >> >> Kempter<kevink@consistentstate.com> wrote:
>> >> >> > Hi all;
>> >> >> >
>> >> >> > I'm trying to restore from a tar of the filesystem on a debian box
>> >> >> > and I get xlog errors.
>> >> >> >
>> >> >> > I suspect I need to run pg_resetxlog but I cannot find it anywhere,
>> >> >> > where would I find pg_resetxlog on a debian box that was installed
>> >> >> > via the deb packages ? or how do I get it ?
>> >> >>
>> >> >> Wait, if you're restoring a backup, to a freshly initted db, then you
>> >> >> shouldn't be getting any kind of pg_xlog errors.  If you are, then
>> >> >> there's likely something wrong with your server that pg_resetxlog
>> >> >> isn't going to fix in the long term.  Are you running on windows with
>> >> >> anti-virus software or have some other kind of possible problem that
>> >> >> could be causing a problem with the postmaster writing to the hard
>> >> >> drives?
>> >> >
>> >> > we're not restoring from a pg_dump. We were in the process of moving
>> >> > the db to a new server. We brought the db down (on host A) and did an
>> >> > rsync of all the db dir's (including tablespace dir's) to host B.
>> >> >
>> >> > Then bad things were done to host A and we want to get back to where
>> >> > we started. So, we stopped the db on host A and rsync'ed the files
>> >> > back from host B to host A.  Now when I try and start the db I see all
>> >> > these tx sement errors in the log.
>> >> >
>> >> > We're actually ok if we loose anything that was in the pg_xlog dir.
>> >>
>> >> Did you rsync the pg_xlog and pg_clog (i.e. EVERYTHING under data/) or
>> >> just the base directory?  You really need to do an rsync of
>> >> everything, not most everything.
>> >
>> > we rsync'ed EVERYTHING (the entire directory and all sub-dirs)
>>
>> OK, on my laptop, in the /var/lib/postgresql/8.3/main there's a bunch
>> of dirs that look like this:
>>
>> base    pg_clog       pg_subtrans  pg_twophase  pg_xlog
>> postmaster.pid  server.crt
>> global  pg_multixact  pg_tblspc    PG_VERSION   postmaster.opts
>> root.crt        server.key
>>
>> Did you rsync the base dir only, or all of the directories above?
>> Cause if you just rsynced base, you need the rest of them, as well.
>
> I rsync'd the /var/lib/postgresql/8.3/main dir. However several of the sub-
> dirs were soft links (pg_xlog, server.cert, root.cert and server.key) so I
> unfortunately did not get the data from the links' real location since they
> all pointed to outside of the /var/lib/postgresql/8.3/main tree

I'd do it again with -L