Postgresql – Postgres 9.1 wait events

In the slony docs they have this third sequence as an example of a structured switchover [http://slony.info/documentation/failover.html#AEN839]:

lock set (id = 1, origin = 1);
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2, wait on=1);

Anyway, I've always used the alt-perl tools with the following, probably well-known, syntax. This moves set2 origin from node 1 to node 2, ie node 2 will become provider.

/usr/local/slony/bin/slonik_move_set set2 1 2 | /usr/local/pgsql/bin/slonik

As you state the script use this sequence of commands: lock set, sync, wait for event, move set. This has worked flawlessly.

What might take some time is the locking and sync. The locking might have to wait for any long running transactions to finish. If the sync is lagging all nodes has to be synchronized when doing a slonik_movet_set.

It seems reasonable to do a sync after the lock as way to confirm the sync between nodes. You could add the sync to your slonik script, ie:

lock set (id = 1, origin = 1);
sync (id = 1);
wait for event (origin = 1, confirmed = 2, wait on=1);    
move set (id = 1, old origin = 1, new origin = 2);
wait for event (origin = 1, confirmed = 2, wait on=1);

Also notice id of the confirming node and that you can specify a timeout for the wait for event command, default is 600 sec [http://slony.info/documentation/stmtwaitevent.html]

max_fsm_pages was eliminated in version 8.4. Free space tracking has gotten a lot smarter and more automatic.

Performance of deletes is going to depend on a lot of things.

• If there are any foreign keys which reference this table, you should check whether there is an index on the referencing column(s). Without that, each DELETE will cause a scan of the referencing table, to check that you're not causing any rows to be orphaned.

• Like other database modifications, it is generally more efficient to group actions into larger transactions where reasonable, since there is overhead to each database transaction.

• If you do have many transactions, you might want to consider setting synchronous_commit to off on the connection you're using for that work.

• If you are deleting a significant fraction of the table, and you can do this during some maintenance window, you might consider dropping indexes not needed to find the rows to be deleted, and create them again after the mass delete.