Обсуждение: create all indexes and triggers in a schema after bulk load

Hi Group

I am looking at doing a CDC-based upgrade using the following steps:

1. pg_dump schema on source, but exclude indexes and triggers

2. recreate schema on target

3. Do a full load from source to target

4. Recreate all the indexes and triggers on the target

The migration will automatically switch from full load to CDC when all tables are fully loaded.

Is there a way to just recreate all indexes and triggers using a separate pg_dump on source to capture the ddl and then run it on the target?

Please advise

Thanks

On Wed, Nov 17, 2021 at 05:13:35PM +1000, Akheel Ahmed wrote:
> Hi Group
> 
> I am looking at doing a CDC-based upgrade using the following steps:
> 
> 
> 1. pg_dump schema on source, but exclude indexes and triggers
> 
> 2. recreate schema on target
> 
> 3. Do a full load from source to target
> 
> 4. Recreate all the indexes and triggers on the target
> 
> The migration will automatically switch from full load to CDC when all
> tables are fully loaded.
> 
> Is there a way to just recreate all indexes and triggers using a separate
> pg_dump on source to capture the ddl and then run it on the target?

There is no need for separate pg_dump.

Just dump once, with -Fc, or (better) -Fd, and then use pg_restore
-l/-L.

depesz

thanks. will give it a try

On Wed, Nov 17, 2021 at 05:13:35PM +1000, Akheel Ahmed wrote:

Hi Group

I am looking at doing a CDC-based upgrade using the following steps:


1. pg_dump schema on source, but exclude indexes and triggers

2. recreate schema on target

3. Do a full load from source to target

4. Recreate all the indexes and triggers on the target

The migration will automatically switch from full load to CDC when all
tables are fully loaded.

Is there a way to just recreate all indexes and triggers using a separate
pg_dump on source to capture the ddl and then run it on the target?

There is no need for separate pg_dump.

Just dump once, with -Fc, or (better) -Fd, and then use pg_restore
-l/-L.

depesz