Thread: where is pg_resetxlog ?
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
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