Subject: Re: VACUUMs take twice as long across all nodes
From: Andreas Kostyrka <andreas@kostyrka.org>
To: Andrew Sullivan <ajs@crankycanuck.ca>
Cc: pgsql-performance@postgresql.org
In-Reply-To: <20061029153404.GF14585@phlogiston.dyndns.org>
References: <20061026095827.9e85e1c0.gdh@laterooms.com>
	<20196.1161874041@sss.pgh.pa.us>
	<20061026160609.5e94e581.gdh@laterooms.com>
	<20061026191728.GD26892@nasby.net>
	<20061026213556.174bd7b5.gdh@laterooms.com>
	<20061026220937.GA6866@phlogiston.dyndns.org>
	<20061027102025.8183c5d9.gdh@laterooms.com>
	<20061029145825.GB14585@phlogiston.dyndns.org>
	<20061029150826.80afe324.gdh@laterooms.com>
	<20061029153404.GF14585@phlogiston.dyndns.org>
Content-Type: multipart/signed; micalg=pgp-sha1;
	protocol="application/pgp-signature";
	boundary="=-dJUSDnBfFZ1++seT32J8"
Date: Sun, 29 Oct 2006 17:24:33 +0100
Message-Id: <1162139073.7606.1.camel@andi-lap>
Mime-Version: 1.0


--=-dJUSDnBfFZ1++seT32J8
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

Am Sonntag, den 29.10.2006, 10:34 -0500 schrieb Andrew Sullivan:
> On Sun, Oct 29, 2006 at 03:08:26PM +0000, Gavin Hamill wrote:
> >=20
> > This is interesting, but I don't understand.. We've done a full restore
> > from one of these pg_dump backups before now and it worked just great.
> >=20
> > Sure I had to DROP SCHEMA _replication CASCADE to clear out all the
> > slony-specific triggers etc., but the new-master ran fine, as did
> > firing up new replication to the other nodes :)
> >=20
> > Was I just lucky?
>=20
> Yes.  Slony alters data in the system catalog for a number of
> database objects on the replicas.  It does this in order to prevent,
> for example, triggers from firing both on the origin and the replica.=20
> (That is the one that usually bites people hardest, but IIRC it's not
> the only such hack in there.)  This was a bit of a dirty hack that
> was supposed to be cleaned up, but that hasn't been yet.  In general,
> you can't rely on a pg_dump of a replica giving you a dump that, when
> restored, actually works.

Actually, you need to get the schema from the master node, and can take
the data from a slave. In mixing dumps like that, you must realize that
there are two seperate parts in the schema dump: "table definitions" and
"constraints". Do get a restorable backup you need to put the table
definitions stuff before your data, and the constraints after the data
copy.

Andreas

>=20
> A
>=20

--=-dJUSDnBfFZ1++seT32J8
Content-Type: application/pgp-signature; name=signature.asc
Content-Description: Dies ist ein digital signierter Nachrichtenteil

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.2 (GNU/Linux)

iD8DBQBFRNXBHJdudm4KnO0RAk1tAJ0fWXqhElwNX8hdjnhQWhC3nV51zwCfec5R
SmVCZyDP1rpraHtO9wnZjvA=
=kA2v
-----END PGP SIGNATURE-----

--=-dJUSDnBfFZ1++seT32J8--