[Bucardo-general] how to RENAME tables in postgres, replicated by bucardo

[Markova, Nina (NRCan/RNCan)]

Thanks Greg!

I actually did by creating new tables in postgres, that were later added to bucardo's relgroup.
The old tables were removed from bucardo.

Nina

-----Original Message-----
From: bucardo-general-bounces at bucardo.org [mailto:bucardo-general-bounces at bucardo.org] On Behalf Of Greg Sabino Mullane
Sent: April 20, 2017 23:26
To: Markova, Nina (NRCan/RNCan)
Cc: 'bucardo-general at bucardo.org'
Subject: Re: [Bucardo-general] how to RENAME tables in postgres, replicated by bucardo

On Tue, Apr 18, 2017, Nina Markova wrote:

> I need to RENAME couple of tables in postgres, which are replicated 
> via BUCARDO on MASTER-MASTER setup.

That's a little tricky, as the table names are hard-coded in an few places. The easiest way is probably this:

1) Make sure nothing is modifying the table, so that we don't discard any deltas
2) Do a final kick of the sync, to clear out any deltas
3) Stop Bucardo
4) Rename the table via ALTER TABLE
5) Drop the 'bucardo_delta' trigger on the table: DROP TRIGGER bucardo_delta on xyz;
   (the other two triggers you may see are tablename-agnostic)
6) Drop and re-add the table via the 'bucardo' program:
   bucardo remove table public.abc
   bucardo add table public.xyz relgroup=foobar
7) Run validate sync to add the new bucardo_delta trigger:
   bucardo validate all
8) Start up Bucardo

If it's a real busy database and you cannot ensure the deltas will not build up, one could do it a more careful, but much more involved way, by renaming the trigger, adding the new one, copying delta rows from old delta table to the new one, then removing the old bucardo_delta trigger.

--
Greg Sabino Mullane greg at endpoint.com
End Point Corporation
PGP Key: 2529 DF6A B8F7 9407 E944  45B4 BC9B 9067 1496 4AC8