threshar@xxxxxxxxxxxxx (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/> ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
