Skip site navigation (1) Skip section navigation (2)

Re: Pg_upgrade speed for many tables

From: Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Jeff Janes <jeff(dot)janes(at)gmail(dot)com>,Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Magnus Hagander <magnus(at)hagander(dot)net>,PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Pg_upgrade speed for many tables
Date: 2012-11-05 21:33:16
Message-ID: 20121105213316.GF12444@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Bruce Momjian escribió:
> On Mon, Nov  5, 2012 at 04:14:47PM -0500, Robert Haas wrote:
> > On Mon, Nov 5, 2012 at 4:07 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> > > Or have options for pg_dump and pg_restore to insert "set
> > > synchronous_commit=off" into the SQL stream?
> > 
> > It would be kind of neat if we had a command that would force all
> > previously-asynchronous commits to complete.  It seems likely that
> > very, very few people would care about intermediate pg_dump states, so
> > we could do the whole dump asynchronously and then do "FORCE ALL
> > COMMITS;" or whatever at the end.
> 
> Actually, I had assumed that a session disconnection forced a WAL fsync
> flush, but now I doubt that.  Seems only server shutdown does that, or a
> checkpoint.  Would this work?
> 
> 	SET synchronous_commit=on;
> 	CREATE TABLE dummy(x int);
> 	DROP TABLE dummy;

AFAIR any transaction that modifies catalogs gets sync commit forcibly,
regardless of the setting.  And sync commit means you get to wait for
all previous transactions to be flushed as well.  So simply creating a
temp table ought to do the trick ...

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services


In response to

Responses

pgsql-hackers by date

Next:From: Bruce MomjianDate: 2012-11-05 21:35:47
Subject: Re: install zic binary
Previous:From: Bruce MomjianDate: 2012-11-05 21:29:49
Subject: Re: Pg_upgrade speed for many tables

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group