Re: Slony v. DBMirror

threshar(at)torgo(dot)978(dot)org (Jeff -) writes:
> On May 5, 2005, at 5:03 PM, Grant McLean wrote:
>>
>> Why would you need to take anything down to run pg_dump? And surely
>> bringing a slave up to speed using Slony would be much slower than
>> dump/restore?
>>
>
> You'd need to stop client access to PG to prevent changes from
> occuring between when you take the dump & when you restore on the
> slave and hten fire up dbmirror. Although it might work if you
> install the dbmirror triggers, then dump & restore.
>
> Slony uses the COPY interface to read/load data. This is the same
> method used by pg_dump so the only throttle will be the network.

There unfortunately is another throttle, at this point.

If you use pg_dump to copy a database from here to there, the
processing takes place thus:

drop index i_a on a;
drop index ii_a on a;
drop table a;

create table a ( stuff );

copy a from stdin;
...
\.

create index i_a on a (id);
create index ii_a on a (txn_date);

In that arrangement, all the data is copied, then the indexes are
generated.

The Slony-I arrangement presently rearranges it thus:

drop index i_a on a;
drop index ii_a on a;
drop table a;

create table a ( stuff );

create index i_a on a (id);
create index ii_a on a (txn_date);

copy a from stdin;
...
\.

The indices are constructed concurrently with loading the data, which
isn't nearly as fast as creating the indices afterwards.

Once Slony-I 1.1 is out, I want to start looking at how to regenerate
the indexes rather than "building into them," so as to use the
following approach:

-- Start with schema complete with indexes

drop index i_a on a;
drop index ii_a on a;

copy a from stdin;
...
\.

create index i_a on a (id);
create index ii_a on a (txn_date);

That would indeed improve performance at set creation time.

What I need, for that, is a way of grabbing all the index definitions
for the table. One way to do that would be to run "pg_dump -s -t a",
though I'd rather have a method that uses the connection I already
have to the database.

This may involve some more-or-less involved queries on pg_index,
unless the pg_indexes view is available on all versions of PG of
interest...
--
(format nil "~S(at)~S" "cbbrowne" "acm.org")
http://www.ntlug.org/~cbbrowne/sap.html
Rules of the Evil Overlord #78. "I will not tell my Legions of Terror
"And he must be taken alive!" The command will be: ``And try to take
him alive if it is reasonably practical.''"
<http://www.eviloverlord.com/>