*Are* indexes supposed to be migrated over or are they regenerated on the target post table data copy?

[joetynan]

So, I think I may be misunderstanding what's going on with what I was originally thinking how this functions. I have a few tables that have extremely large indices, and it looks like the migration is copying them over instead of just doing the table and TOAST data. Is this the expected operation? From where you describe the switchover process, it seems to imply that indices are NOT supposed to be copied over?

[shayonj]

If you don't mind sharing the logs for which sql command you are talking about?

Also are you passing --recreate-indices-post-copy?

[joetynan]

ah, I am not, at least not when I execute a start-sync (didn't see that option in the readme?)

the command I am using is pg_easy_replicate start-sync --group-name example --schema public --tables "table1"

should I be appending a --recreate-indices-post-copy to that?

[shayonj]

OK, got it. So recreate-indices-post-copy is only opt-in. It's not typically needed because if you pass --copy-schema during bootstrap, then it should be copying over the entire schema of all the tables in the replication to the target database for you.

recreate-indices-post-copy was introduced to make the initial sync/COPY during replication faster by dropping the indexes right before starting the sync, since every write from the COPY doesn't need to rebuild the indexes each time. Then right before switchover, it will add the indexes again and perform the switchover.

So, if you are not using that option, then you shouldn't see any indexes being attempted to be added or deleted during or after sync or switchover.

That said, if you are using --copy-schema, then yes, indexes are supposed to be migrated to the target database as well.

[joetynan]

ah! OK. Yeah, I was using --copy-schema with bootstrap, but otherwise I wasn't leveraging that during the start-sync.

So, if I want to copy over the table data and that's complete, will I need to manually rebuild the indexes post switchover?

[joetynan]

another confusing aspect:

• Truncated TARGET table to clean it all out so that both table data and indices are empty
• Re-bootstrapped for the intended group name, did NOT use --copy-schema (since the schema is already there)
• Attempted to restart sync for just the one table in the intended group, but with --recreate-indices-post-copy

Got an error: PG::InsufficientPrivilege: ERROR: must be owner of index {index name}. However, config_check passes initially using the connection strings I have for both source and target, AND I verified that the user in each string is the owner of the table on their respective databases.

I tried dropping the index on the TARGET that it was erroring out on, and it throws a new error, PG::UndefinedObject: ERROR: index "{index name}" does not exist

[shayonj]

So, if I want to copy over the table data and that's complete, will I need to manually rebuild the indexes post switchover?

Nope, you don't need to, postgres takes care of that and pg_easy_replicate runs an ANALYZE in the end too.

[joetynan]

I'm assuming that even though it's not recreating the indexes until post copy, that doesn't mean that indexes will remain at zero until that time, correct? I do see indexes growing on the target (now that I got it replicating without any index errors, that was a schema mismatch), albeit rather slowly.

[joetynan]

OK, so I have run into an issue and I can't ID what the root cause is here. I created a blank target database, and started over from scratch. I did a config_check, then bootstrap with special user role set, with --copy-schema. I can verify that all tables AND their blank indexes are populated over OK. I then did a single table sync and specified --recreate-indices-post-copy at the end. It failed to set up the replication with the error Starting sync failed: PG::InsufficientPrivilege: ERROR: must be owner of index {index_name}.

Looking up higher in the output, I see this line where it fails on the DROP INDEX CONCURRENTLY: {"name":"pg_easy_replicate","hostname":"53ea78498912","pid":1,"level":50,"time":"2025-01-09T22:04:52.764+00:00","v":0,"msg":"PG::InsufficientPrivilege: ERROR: must be owner of index contacts_events_idx_on_values: DROP INDEX CONCURRENTLY public.{index-name};","version":"0.3.8"}

I verified that it's the same owner name on both source and target databases, on the same table in those databases, and that the owner is the same for both indices on both source and target.

[joetynan]

Just in case I was crazy, I did a psql \di on both source and target databases, and ownership names matched. Just in case, I ran a ALTER INDEX (name) OWNER TO (table owner) on the index, and repeated the sync attempt, and got the same error. Could this be using, or trying to use, the pger generated role to delete that table instead of the connection string role?

[joetynan]

one manual workaround for this is to drop the indexes manually on the target and then initiate replication - that seems to work totally fine.