Re: Slony v. DBMirror - Mailing list pgsql-general

threshar@torgo.978.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@~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/>