Zero Downtime Upgrade

[Akheel Ahmed]

Hi Group

What tools exist besides bucardo, londiste and rubyrep for logical replication 
to do a zero/near zero downtime upgrade?

Thanks in advance

create all indexes and triggers in a schema after bulk load

[Akheel Ahmed]

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

Re: create all indexes and triggers in a schema after bulk load

[Akheel Ahmed]

thanks. will give it a try

On 17/11/2021 5:16 pm, hubert depesz lubaczewski wrote:

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

Sorting difference in version 10 vs 13

[Akheel Ahmed]

Hi


I have performed logical replication from a 10.18 to 13.4.

in 13.4, order by asc gives underscores higher than other characters. 
all the LC parameters are identical across both instances.


Am I missing anything else?


Help appreciated.

Thanks

Re: Sorting difference in version 10 vs 13

[Akheel Ahmed]

Both are Amazon AWS Aurora instances. 10.x is on intel/amd64. 13.x is on 
their ARM based graviton.



select version();
   version
-
 PostgreSQL 13.4 on aarch64-unknown-linux-gnu, compiled by 
aarch64-unknown-linux-gnu-gcc (GCC) 7.4.0, 64-bit

(1 row)


select version();
 version
--
 PostgreSQL 10.18 on x86_64-pc-linux-gnu, compiled by 
x86_64-pc-linux-gnu-gcc (GCC) 7.4.0, 64-bit

(1 row)


On 21/12/2021 3:12 pm, Tom Lane wrote:

Akheel Ahmed  writes:

in 13.4, order by asc gives underscores higher than other characters.
all the LC parameters are identical across both instances.
Am I missing anything else?

OS-level differences, perhaps?

https://wiki.postgresql.org/wiki/Locale_data_changes

regards, tom lane