Re: where is pg_resetxlog ?

On Sat, Jul 25, 2009 at 1:30 PM, Kevin
Kempter<kevink(at)consistentstate(dot)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(at)consistentstate(dot)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(at)consistentstate(dot)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.