where is pg_resetxlog ?

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?

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?

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.

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)

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

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

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.

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

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