Hello,

I successfully update the main db and one replicated db with the following:

cluster name = slony_rep1

node 1 admin conninfo = 'dbname = MyDB host = localhost user = slony1
password = Ejhfg33EdddsufhErR76 port = 6234';
node 2 admin conninfo = 'dbname = MyDB host = 86.88.5.4 user = slony1
password = Ejhfg33EdddsufhErR76 port = 6234';

EXECUTE SCRIPT
(
    SQL = 'ALTER TABLE operators RENAME COLUMN firstname TO lastname;',
    EVENT NODE = 1
);




However, I have 1 main db to 2 replicated db. The second replicated db has
the following cluster name and node information:

cluster name = slony_rep2

node 1 admin conninfo = 'dbname = MyDB host = localhost user = slony2
password = Ejhfg33EdddsufhErR76 port = 6234';
node 2 admin conninfo = 'dbname = MyDB host = 86.88.5.17 user = slony2
password = Ejhfg33EdddsufhErR76 port = 6234';


Do I need to run slonik twice with different cluster and node indo? That
does not seem right. After running the first time, the main db and the
first replicated db will be consistent but not the second replicated db.
Also, if I run it a second time, wouldn't the main db already be updated
hence the sql statements used for the original update will surely fail?


Thanks again.

On Sat, Oct 10, 2015 at 2:51 AM, Scott Marlowe <scott.marlowe at gmail.com>
wrote:

> On Fri, Oct 9, 2015 at 11:09 PM, Sung Hsin Lei <sungh.lei at gmail.com>
> wrote:
> >
> >
> > On Sat, Oct 10, 2015 at 12:50 AM, Scott Marlowe <scott.marlowe at gmail.com
> >
> > wrote:
> >>
> >> On Fri, Oct 9, 2015 at 9:34 PM, Sung Hsin Lei <sungh.lei at gmail.com>
> wrote:
> >> > Hello,
> >> >
> >> > I have a 10gig database on the Master which I backed up and restored
> on
> >> > the
> >> > Slave. Database activity is low. When I setup Slony replication from
> the
> >> > Master to the Slave, it would take hours before new information from
> the
> >> > Master would be updated into the Slave. Postgres would be also VERY
> slow
> >> > for
> >> > the first few hours. However, after the initial wait period, the Slave
> >> > would
> >> > update within a second or so and Postgres speed would return to
> normal.
> >> > I'm
> >> > assuming that Slony is taking time to verify the initial database
> >> > integrity
> >> > between the Master and the Slave. Am i right? I would like to know if
> >> > there's a way to have the updates start within the first few minutes
> of
> >> > the
> >> > original setup.
> >>
> >> In a normal slony sub, all you need on the slave is the schema. If
> >> there's data there it'll get truncated / deleted, so there's no reason
> >> to spend time copying it over.
> >>
> >> IF and only IF you are willing to take your master offline from your
> >> application (i.e. stop writes going to it) you can copy over the data
> >> and then subscribe with no copy. If the master is not taken "offline"
> >> from an application perspective then a subscribe with no copy will
> >> result in your master and slave not having the same data (which is
> >> bad).
> >
> >
> > Thanks for the response.
> >
> > How about when you change the db schema such as adding/removing/renaming
> > tables, columns and dependencies? We usually stop the replication, delete
> > the cluster from both Master and Slave, make the changes on both Master
> and
> > Slave and run the setup from scratch. This would also freeze postgres and
> > take several hours for replication to start. Isn't there a way to make it
> > faster considering that all data is already in the Slave?
>
> Yes, what you are looking for is the EXECUTE SCRIPT command. See this page:
> http://slony.info/adminguide/2.2/doc/adminguide/stmtddlscript.html
>
> >
> > Also, how do you subscribe with no copy?
> >
>
> There's an argument for the slonik subscribe command called "omit copy".
> See:
> http://slony.info/adminguide/2.2/doc/adminguide/stmtsubscribeset.html
>
> Note that all of this assumes you're running slony 2.2.latest. If
> you're running on slony 1.2 etc PLEASE consider upgrading.
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.slony.info/pipermail/slony1-general/attachments/20151029/f6f0c522/attachment-0001.htm