On Wed, Jun 7, 2023 at 4:30 PM Jeremy Schneider <schnei...@ardentperf.com> wrote:
> On 6/7/23 2:12 PM, Don Seiler wrote: > > On the logical replication front, the concern is with the initial data > > import that happens when the subscription is created (by default). I > > know that you can tell the subscription to not copy data and instead use > > pg_dump and a replication slot snapshot to achieve this manually. > > However I'm unable to explain (to myself) why this is better than just > > having the subscription do it upon creation. Given that I can create > > pub/sub sets for individual tables for parallel operations, I'm curious > > what advantages there are in using pg_dump to do this import. > > FWIW, I think the place this feature shines the most is when you can > safely leverage things like storage-level snapshots. Sometimes that > means you can get a copy of a multi-TB database almost instantly if the > storage or filesystem does copy-on-write, for example database lab > (postgres.ai) which uses ZFS. > > Another thing I can think of is that while it's true you can create > multiple pub/sub sets, I'm not sure you can reduce the number of sets > later. So if you were concerned about having too many slots doing > decoding on the source, then you might want the flexibility of pg_dump > (or perhaps restoring a backup) to get more parallelism while having > more control over how many slots will be used later. > > In your case, the whole setup is hopefully temporary, so maybe these > particular concerns aren't as relevant to you. > > This is just what comes to mind... probably there's a few more things > I'm not thinking of and hopefully others will chime in. :) > Yes my setup would be temporary, just as long as needed to complete the migration to a new host. One other use case is using pg_dump/restore to move older data in time-based partitions (assuming data isn't modified for older partition periods). Those older partitions would just need to be copied but wouldn't need any ongoing pub/sub/replication set up for them. So you'd have both in use in that case. Thanks, Don. -- Don Seiler www.seiler.us
