[Bucardo-general] Large database and best options.

David Christensen david at endpoint.com
Thu Oct 10 16:42:20 UTC 2019


> On Oct 10, 2019, at 10:49 AM, Mike Zupan <mike at zcentric.com> wrote:
> 
> I have about a 1.7 TB database I need to try to move to RDS. It also has around 190 tables.
> 
> Is the best way to setup 1 sync job like this
> 
> bucardo add db source_db dbhost=$SOURCE_HOST dbport=$SOURCE_PORT dbname=$SOURCE_DATABASE dbuser=$SOURCE_USERNAME dbpass=$SOURCE_PASSWORD
> bucardo add db dest_db dbhost=$DEST_HOST dbport=$DEST_PORT dbname=$DEST_DATABASE dbuser=$DEST_USERNAME dbpass=$DEST_PASSWORD
> bucardo add all tables --herd=copying_herd
> bucardo add sync the_sync relgroup=copying_herd dbs=source_db:source,dest_db:target onetimecopy=2
> 
> Or setup many smaller sync jobs that have about 10 tables per sync job? Or even 1 sync job per table?

Depending on the rate of change, I’d consider the following:

- create schema on target database
- create new sync with autokick=0 (this will start collecting delta rows to collect changes, but not start any data copying itself)
- do a parallel data-only pg_dump to target to maximize speed of load.  You will likely want/need to set session_replication_role = replica in order to allow for potential FK violations as data is copied over, but know that at the end of this process it’ll be eventually consistent.
- once all data has loaded on the remote side, then `bucardo kick mysync` to transfer all delta rows and get things up-to-date with any changes during the copy process.
- set the sync to autokick=1 to start the typical triggered sync-based approach.

I say considering the rate of change, because you don’t want to overrun your existing database server with huge numbers of delta rows if it will generate a lot of changes as it’s copying, but if you have space or volume of changes is within reason then you’re probably fine.

HTH,

David
-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: Message signed with OpenPGP
URL: <https://bucardo.org/pipermail/bucardo-general/attachments/20191010/8aa3efd6/attachment.sig>


More information about the Bucardo-general mailing list