[Bucardo-general] Fwd: Migrating to Amazon Postgres RDS

[Chris Keane]

On Thu, Mar 6, 2014 at 10:54 AM, Greg Sabino Mullane <greg at endpoint.com>wrote:

>
> No. You need session_replication_role, or you need to alter the system
> tables. Both need superuser access. The only way around is to use
> security definer, as you indicated above. You want to avoid modifying
> pg_class if at all possible. That way was ugly. :)
>
>
OK, so I spent the afternoon exchanging emails with the fine folks at AWS,
and the support engineer I was working with is going to back the RDS
engineers to see if and when "something " can be added. I spent some time
walking through the postgres source to see if there were side effects to
setting session_replication_role but to the extent that grep could guide me
it looks to do exactly what it should and no more. So I can't imagine why
an RDS engineer would think it was a bad idea.

But, planning in case they do say no, and assuming I want to continue to
use my lovely and expensive RDS instance I'm thinking of ways to get around
the problem.
Say, this:

1. All triggers, both application and bucardo, get a snippet at the top to
check if session_user = bucardo and if yes, immediately return. This is a
way poor man's way to stop triggers firing on bucardo-related updates.

2. I somehow need to set an update order on goats so that foreign-key
constrained fields get updated in the right order. I notice that herdmap
has a field 'priority' and find_goats() uses it to return a list of goats
in priority order. If I massaged the priority field so that source table
fields get updated before foreign key reference tables, and assuming some
related data gets inserted into a few tables, would the replication happen
in herdmap.priority order?

OK, OK, so it makes me feel a bit queasy even considering some of this, but
bucardo is critical/core technology in our business and I'd really like to
make it play nice with AWS RDS. The optimal solution is to get Amazon to
un-bork postgres RDS but I'd like to have a contingency plan in case they
say "no" or even "yes, but not until next year"

Chris.



-- 
*Chris Keane* * Track Intelligence Inc *  +1 (650) 703 5523 (cell)
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.endcrypt.com/pipermail/bucardo-general/attachments/20140306/21fef11b/attachment.html>