Aidan Van Dyk wrote:
> On Sat, Apr 9, 2011 at 7:03 AM, Bruce Momjian wrote:
> > Bruce Momjian wrote:
> >> Alvaro Herrera wrote:
> >> >
> >> > Why is it important to have the original pg_clog files around? ?Since
> >> > the transactions in question are below the freeze horizon, surely the
> >> > tu
On Sat, Apr 09, 2011 at 09:05:42AM -0400, Aidan Van Dyk wrote:
> On Sat, Apr 9, 2011 at 7:03 AM, Bruce Momjian wrote:
> > Bruce Momjian wrote:
> >> Alvaro Herrera wrote:
> >> >
> >> > Why is it important to have the original pg_clog files around? ?Since
> >> > the transactions in question are belo
On Sat, Apr 9, 2011 at 7:03 AM, Bruce Momjian wrote:
> Bruce Momjian wrote:
>> Alvaro Herrera wrote:
>> >
>> > Why is it important to have the original pg_clog files around? Since
>> > the transactions in question are below the freeze horizon, surely the
>> > tuples that involve those transaction
Bruce Momjian wrote:
> Alvaro Herrera wrote:
> >
> > Why is it important to have the original pg_clog files around? Since
> > the transactions in question are below the freeze horizon, surely the
> > tuples that involve those transaction have all been visited by vacuum
> > and thus removed if the
Alvaro Herrera wrote:
>
> Why is it important to have the original pg_clog files around? Since
> the transactions in question are below the freeze horizon, surely the
> tuples that involve those transaction have all been visited by vacuum
> and thus removed if they were leftover from aborted tran
bricklen wrote:
> On Fri, Apr 8, 2011 at 10:01 PM, bricklen wrote:
> > Update on the status of the steps we took, which were:
> > - test on a hot standby by bringing it live, running the script,
> > determing the missing clog files, copying them into the live (hot
> > standby) pg_clog dir
> >
> >
On Fri, Apr 8, 2011 at 10:01 PM, bricklen wrote:
> Update on the status of the steps we took, which were:
> - test on a hot standby by bringing it live, running the script,
> determing the missing clog files, copying them into the live (hot
> standby) pg_clog dir
>
> Now, on the master, copied the
On Fri, Apr 8, 2011 at 9:28 PM, Alvaro Herrera
wrote:
>
> Why is it important to have the original pg_clog files around? Since
> the transactions in question are below the freeze horizon, surely the
> tuples that involve those transaction have all been visited by vacuum
> and thus removed if they
Why is it important to have the original pg_clog files around? Since
the transactions in question are below the freeze horizon, surely the
tuples that involve those transaction have all been visited by vacuum
and thus removed if they were leftover from aborted transactions or
deleted, no? So you
bricklen wrote:
> On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian wrote:
> > Stephen Frost wrote:
> > -- Start of PGP signed section.
> >> bricklen,
> >>
> >> * bricklen (brick...@gmail.com) wrote:
> >> > Now, is this safe to run against my production database?
> >>
> >> Yes, with a few caveats. ?On
bricklen,
* bricklen (brick...@gmail.com) wrote:
> Thanks guys, I really appreciate your help. For the vacuum freeze, you
> say database-wide, should I run vacuumdb -a -v -F ? Will freezing the
> other tables in the cluster help (not sure how that works with
> template0/1 databases?)
Yes, using t
On Fri, Apr 8, 2011 at 8:07 PM, Bruce Momjian wrote:
> Stephen Frost wrote:
> -- Start of PGP signed section.
>> bricklen,
>>
>> * bricklen (brick...@gmail.com) wrote:
>> > Now, is this safe to run against my production database?
>>
>> Yes, with a few caveats. One recommendation is to also increa
Stephen Frost wrote:
-- Start of PGP signed section.
> bricklen,
>
> * bricklen (brick...@gmail.com) wrote:
> > Now, is this safe to run against my production database?
>
> Yes, with a few caveats. One recommendation is to also increase
> autovacuum_freeze_max_age to 5 (500m), which will
bricklen,
* bricklen (brick...@gmail.com) wrote:
> Now, is this safe to run against my production database?
Yes, with a few caveats. One recommendation is to also increase
autovacuum_freeze_max_age to 5 (500m), which will hopefully
prevent autovacuum from 'butting in' and causing issues
On Fri, Apr 8, 2011 at 7:20 PM, bricklen wrote:
> On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost wrote:
>> bricklen,
>>
>> * bricklen (brick...@gmail.com) wrote:
>>> I looked deeper into our backup archives, and it appears that I do
>>> have the clog file reference in the error message "DETAIL: C
On Fri, Apr 8, 2011 at 7:11 PM, Stephen Frost wrote:
> bricklen,
>
> * bricklen (brick...@gmail.com) wrote:
>> I looked deeper into our backup archives, and it appears that I do
>> have the clog file reference in the error message "DETAIL: Could not
>> open file "pg_clog/04BE": No such file or di
bricklen,
* bricklen (brick...@gmail.com) wrote:
> I looked deeper into our backup archives, and it appears that I do
> have the clog file reference in the error message "DETAIL: Could not
> open file "pg_clog/04BE": No such file or directory."
Great! And there's no file in pg_clog which matche
Hi Stephen,
On Fri, Apr 8, 2011 at 6:57 PM, Stephen Frost wrote:
> bricklen,
>
> * bricklen (brick...@gmail.com) wrote:
>> I've been noticing in my logs for the past few days the message you
>> note in the wiki. It seems to occur during a vacuum around 7:30am
>> every day. I will be running the s
bricklen,
* bricklen (brick...@gmail.com) wrote:
> I've been noticing in my logs for the past few days the message you
> note in the wiki. It seems to occur during a vacuum around 7:30am
> every day. I will be running the suggested script shortly, but can
> anyone tell me in how bad of shape my db
On Fri, Apr 8, 2011 at 4:51 PM, bricklen wrote:
> I've been noticing in my logs for the past few days the message you
> note in the wiki. It seems to occur during a vacuum around 7:30am
> every day. I will be running the suggested script shortly, but can
> anyone tell me in how bad of shape my db
On Fri, Apr 8, 2011 at 4:00 PM, Jeff Davis wrote:
> On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote:
>> A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4.
>> These releases will remove the need for the above script by correctly
>> updating all TOAST tables in the migrated
On Fri, 2011-04-08 at 15:03 -0400, Bruce Momjian wrote:
> A fix will be included in upcoming Postgres releases 8.4.8 and 9.0.4.
> These releases will remove the need for the above script by correctly
> updating all TOAST tables in the migrated databases.
You might want to clarify that the fix may
Bruce Momjian wrote:
> Tom Lane wrote:
> > Josh Berkus writes:
> > >> -- It will not lock any tables but will generate I/O.
> >
> > > add:
> > > IMPORTANT: Depending on the size and configuration of your database,
> > > this script may generate a lot of I/O and degrade database performance.
> > >
Tom Lane wrote:
> Josh Berkus writes:
> >> -- It will not lock any tables but will generate I/O.
>
> > add:
> > IMPORTANT: Depending on the size and configuration of your database,
> > this script may generate a lot of I/O and degrade database performance.
> > Users should execute this script dur
Josh Berkus writes:
>> -- It will not lock any tables but will generate I/O.
> add:
> IMPORTANT: Depending on the size and configuration of your database,
> this script may generate a lot of I/O and degrade database performance.
> Users should execute this script during a low traffic period and w
> -- It will not lock any tables but will generate I/O.
add:
IMPORTANT: Depending on the size and configuration of your database,
this script may generate a lot of I/O and degrade database performance.
Users should execute this script during a low traffic period and watch
the database load.
On Fri, Apr 08, 2011 at 12:16:50PM -0700, Jeff Davis wrote:
> On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote:
> > > 1. Make relfrozenxid go backward to the right value. There is currently
> > > no mechanism to do this without compiling C code into the server,
> > > because (a) VACUUM FREEZE wi
Bruce Momjian wrote:
> I am worried if I mention pg_dump that people will think pg_dump is
> broken, when in fact it is only the --binary-upgrade mode of pg_dump
> that is broken.
>
> I adjusted the wording of the last paragraph slighly to be clearer, but
> hopefully not confuse.
>
> We don't act
Stephen Frost wrote:
-- Start of PGP signed section.
> * Bruce Momjian (br...@momjian.us) wrote:
> > Yes, I like your version better; I did adjust the wording of the last
> > sentence to mention it is really the release, not the new pg_upgrade,
> > which fixes the problem because the fixes are in
On Fri, 2011-04-08 at 13:35 -0400, Noah Misch wrote:
> > 1. Make relfrozenxid go backward to the right value. There is currently
> > no mechanism to do this without compiling C code into the server,
> > because (a) VACUUM FREEZE will never move the relfrozenxid backward; and
> > (b) there is no way
* Bruce Momjian (br...@momjian.us) wrote:
> Yes, I like your version better; I did adjust the wording of the last
> sentence to mention it is really the release, not the new pg_upgrade,
> which fixes the problem because the fixes are in pg_dump, and hence a
> new pg_upgrade binary will not work;
Stephen Frost wrote:
-- Start of PGP signed section.
> Bruce,
>
> * Bruce Momjian (br...@momjian.us) wrote:
> > OK, here is a draft email announcement:
>
> Couple suggestions (also on IRC):
Yes, I like your version better; I did adjust the wording of the last
sentence to mention it is really th
Bruce,
* Bruce Momjian (br...@momjian.us) wrote:
> OK, here is a draft email announcement:
Couple suggestions (also on IRC):
> ---
A bug has been discovered in all released versions of pg_upgrade and
(formerly) pg_migrator.
Bruce Momjian wrote:
> OK, now that I have committed the fixes to git, I think it is time to
> consider how we are going to handle this fix for people who have already
> used pg_upgrade, or are using it in currently released versions.
>
> I am thinking an announce list email with this query would
Bruce Momjian wrote:
> New version; I made some other small adjustments:
>
> -- This script fixes data in pre-PG 9.0.4 and pre-8.4.8
> -- servers that was upgraded by pg_upgrade and pg_migrator.
> -- Run the script using psql for every database in the cluster
> -- except '
On Fri, Apr 08, 2011 at 10:05:01AM -0700, Jeff Davis wrote:
> On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote:
> > > Right, VACUUM FREEZE. I now see I don't need to set
> > > vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has:
> > >
> > > if (n->options & VACOPT_FREEZE)
Jeff Davis wrote:
> On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote:
> > > Right, VACUUM FREEZE. I now see I don't need to set
> > > vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has:
> > >
> > > if (n->options & VACOPT_FREEZE)
> > > n->freeze_min_age = n->freeze_tabl
Jeff Davis wrote:
> On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote:
> > One concern I have is that existing heap tables are protecting clog
> > files, but once those are frozen, the system might remove clog files not
> > realizing it has to freeze the heap tables too.
>
> I don't understan
Bruce Momjian wrote:
> Bruce Momjian wrote:
> > OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
> > the two reported pg_upgrade problems he saw via IRC. It seems toast
> > tables have xids and pg_dump is not preserving the toast relfrozenxids
> > as it should. Heap tables h
On Fri, 2011-04-08 at 07:08 -0400, Noah Misch wrote:
> > Right, VACUUM FREEZE. I now see I don't need to set
> > vacuum_freeze_table_age if I use the FREEZE keyword, e.g. gram.y has:
> >
> > if (n->options & VACOPT_FREEZE)
> > n->freeze_min_age = n->freeze_table_age = 0;
>
> True; it jus
On Thu, 2011-04-07 at 22:21 -0400, Bruce Momjian wrote:
> One concern I have is that existing heap tables are protecting clog
> files, but once those are frozen, the system might remove clog files not
> realizing it has to freeze the heap tables too.
I don't understand. Can you elaborate?
Regards
Noah Misch wrote:
> On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote:
> > Noah Misch wrote:
> > > 1) The pg_class.relfrozenxid that the TOAST table should have received
> > > ("true relfrozenxid") is still covered by available clog files. Fixable
> > > with some combination of pg_clas
On Thu, Apr 07, 2011 at 10:21:06PM -0400, Bruce Momjian wrote:
> Noah Misch wrote:
> > 1) The pg_class.relfrozenxid that the TOAST table should have received
> > ("true relfrozenxid") is still covered by available clog files. Fixable
> > with some combination of pg_class.relfrozenxid twiddling and
Noah Misch wrote:
> On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote:
> > Bruce Momjian wrote:
> > > OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
> > > the two reported pg_upgrade problems he saw via IRC. It seems toast
> > > tables have xids and pg_dump is n
Jeff Davis wrote:
> On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote:
> > So I think we have four possible approaches to correct databases:
> >
> > 1) SELECT * to set the hint bits
> > 2) VACUUM to set the hint bits
> > 3) VACUUM FREEZE to remove the old xids
> > 4) some comp
On Thu, 2011-04-07 at 20:14 -0400, Bruce Momjian wrote:
> So I think we have four possible approaches to correct databases:
>
> 1) SELECT * to set the hint bits
> 2) VACUUM to set the hint bits
> 3) VACUUM FREEZE to remove the old xids
> 4) some complicated function
>
> I
Bruce Momjian wrote:
> > > Yes, it will be reasonable.
> > >
> > >> That means that VACUUM FREEZE of the toast table, if there are no
> > >> concurrent transactions, will freeze all of the tuples; and the
> > >> newFrozenXid should always be seen as newer than the existing (and
> > >> wrong) relfro
Robert Haas wrote:
> On Thu, Apr 7, 2011 at 5:52 PM, Bruce Momjian wrote:
> > Jeff Davis wrote:
> >> On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote:
> >> > > Any idea how to correct existing systems? ?Would VACUUM FREEZE of just
> >> > > the toast tables work?
> >> >
> >> > VACUUM FREEZE will
On Thu, Apr 7, 2011 at 5:52 PM, Bruce Momjian wrote:
> Jeff Davis wrote:
>> On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote:
>> > > Any idea how to correct existing systems? Would VACUUM FREEZE of just
>> > > the toast tables work?
>> >
>> > VACUUM FREEZE will never set the relfrozenxid backw
Jeff Davis wrote:
> On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote:
> > > Any idea how to correct existing systems? Would VACUUM FREEZE of just
> > > the toast tables work?
> >
> > VACUUM FREEZE will never set the relfrozenxid backward. If it was never
> > preserved to begin with, I assume
On Thu, 2011-04-07 at 12:38 -0700, Jeff Davis wrote:
> > Any idea how to correct existing systems? Would VACUUM FREEZE of just
> > the toast tables work?
>
> VACUUM FREEZE will never set the relfrozenxid backward. If it was never
> preserved to begin with, I assume that the existing value could
Kevin Grittner wrote:
> Bruce Momjian wrote:
>
> > all we need to do is set those hint bits before the clog gets
> > remove, so maybe just a SELECT * would do the trick!
>
> Does that mean that those experiencing the problem are failing to do
> the vacuumdb run which is recommended in the pg_u
Bruce Momjian wrote:
> all we need to do is set those hint bits before the clog gets
> remove, so maybe just a SELECT * would do the trick!
Does that mean that those experiencing the problem are failing to do
the vacuumdb run which is recommended in the pg_upgrade instructions?
-Kevin
--
S
Bruce Momjian wrote:
> Jeff Davis wrote:
> > On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote:
> > > I want to avoid anything that requires a compile because they are hard
> > > for many sites to install so TransactionIdPrecedes() is out. We will
> > > need to do this in PL/pgSQL probably.
>
Jeff Davis wrote:
> On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote:
> > I want to avoid anything that requires a compile because they are hard
> > for many sites to install so TransactionIdPrecedes() is out. We will
> > need to do this in PL/pgSQL probably.
>
> PL/pgSQL can't see dead row
On Thu, 2011-04-07 at 17:06 -0400, Bruce Momjian wrote:
> I want to avoid anything that requires a compile because they are hard
> for many sites to install so TransactionIdPrecedes() is out. We will
> need to do this in PL/pgSQL probably.
PL/pgSQL can't see dead rows, so that would not be correc
Bruce Momjian wrote:
> Robert Haas wrote:
> > >> Well, that won't work, because VACUUM can't be executed in a transaction
> > >> block or function.
> > >
> > > Good point.
> > >
> > > The only bright part of this is that missing clog will throw an error so
> > > we are not returning incorrect data,
Robert Haas wrote:
> >> Well, that won't work, because VACUUM can't be executed in a transaction
> >> block or function.
> >
> > Good point.
> >
> > The only bright part of this is that missing clog will throw an error so
> > we are not returning incorrect data, and hopefully people will report
> >
Jeff Davis wrote:
> On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote:
> > OK, so the only other idea I have is to write some pretty complicated
> > query function that does a sequential scan of each toast table and pulls
> > the earliest xmin/xmax from the tables and use that to set the
> > r
Jeff Davis wrote:
> On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote:
> > OK, so the only other idea I have is to write some pretty complicated
> > query function that does a sequential scan of each toast table and pulls
> > the earliest xmin/xmax from the tables and use that to set the
> > r
On Thu, 2011-04-07 at 15:46 -0400, Bruce Momjian wrote:
> OK, so the only other idea I have is to write some pretty complicated
> query function that does a sequential scan of each toast table and pulls
> the earliest xmin/xmax from the tables and use that to set the
> relfrozenxid (pretty complica
Robert Haas wrote:
> ISTM we need to force a minor release once we are sure this has
> been corrected. We had also probably put out an announcement
> warning people that have already used pg_upgrade of possible data
> corruption. I'm not sure exactly what the language around that
> should be, bu
On Thu, Apr 7, 2011 at 3:46 PM, Bruce Momjian wrote:
> Jeff Davis wrote:
>> > I have added a personal regression test to show which
>> > pg_class.relfrozenxid values are not preserved, and with this patch the
>> > only ones not preserved are toast tables used by system tables, which
>> > are not c
Jeff Davis wrote:
> > I have added a personal regression test to show which
> > pg_class.relfrozenxid values are not preserved, and with this patch the
> > only ones not preserved are toast tables used by system tables, which
> > are not copied from the old cluster (FirstNormalObjectId = 16384). I
On Thu, Apr 07, 2011 at 12:16:55PM -0400, Bruce Momjian wrote:
> Bruce Momjian wrote:
> > OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
> > the two reported pg_upgrade problems he saw via IRC. It seems toast
> > tables have xids and pg_dump is not preserving the toast relf
On Thu, 2011-04-07 at 12:16 -0400, Bruce Momjian wrote:
> Bruce Momjian wrote:
> > OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
> > the two reported pg_upgrade problems he saw via IRC. It seems toast
> > tables have xids and pg_dump is not preserving the toast relfrozenxi
On 4/7/11 9:16 AM, Bruce Momjian wrote:
> OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
>> the two reported pg_upgrade problems he saw via IRC.
BTW, just for the release notes, RhodiumToad == Andrew Gierth.
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Bruce Momjian wrote:
> OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
> the two reported pg_upgrade problems he saw via IRC. It seems toast
> tables have xids and pg_dump is not preserving the toast relfrozenxids
> as it should. Heap tables have preserved relfrozenxids, bu
OK, thanks to RhodiumToad on IRC, I was able to determine the cause of
the two reported pg_upgrade problems he saw via IRC. It seems toast
tables have xids and pg_dump is not preserving the toast relfrozenxids
as it should. Heap tables have preserved relfrozenxids, but if you
update a heap row bu
69 matches
Mail list logo
