RE: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

[Avi Weinberg <AviW@xxxxxxxxx>]

Thanks for the reply,

 

My question was, what will happen if I have one destination table which gets data from many source tables.  What is the best way to handle changes in the structure of SOME of the source tables, while other source tables remain in the old format.

Maybe in some cases where the type was changed it may be able to work with source tables of different types, but what if column was renamed in one source table but the column remained with the old name in the other source table?  What column name should the destination table have?  Do I need to duplicate the column to have both old and new names?

 

From: Vijaykumar Jain [mailto:vijaykumarjain.github@xxxxxxxxx]
Sent: Sunday, July 4, 2021 6:53 PM
To: Avi Weinberg <AviW@xxxxxxxxx>
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Logical Replication - Single Destination Table With Multiple Source Tables - How to Handle Structure Changes

 

On Sun, 4 Jul 2021 at 15:53, Avi Weinberg <AviW@xxxxxxxxx> wrote:

I'm using logical replication to copy data from multiple tables to a single destination table.  At times the structure of the source table needs to change.  However, not all source table will have their structure updated at the same time.  Let's assume, for example, a column type needs to be changed (but solution needs to work for column addition, deletion, rename etc.).  What is the preferable approach:

 

1. To add another column to the destination table where its type will be the new type.  Source tables, that have the new column type, will write to the new column.  While source tables with old column type will write to the old column.  A view will do the proper casting and will show a single column to user.
2. Add a new table (versioned: table_name_v2) where source tables that have a new structure will write to the new destination table, while old source tables will write to the old destination table.   A view with UNION and casting will combine all tables.
3. A better way?

 

Does the below not work for all alter table changes on publisher.

I have been testing on a demo setup pg14beta, and subscribers are able to catch up fine.

 

on publisher (port 5001)

postgres=# alter table t alter COLUMN name type bigint using name::bigint;

 

on subscriber (port 5002)

postgres=# alter table t alter COLUMN name type bigint using name::bigint;

ALTER TABLE

postgres=# alter subscription mysub refresh publication;

 

this should work for all the cases for ddl changes right.

 

-- demo 

 

-- create table on pub and sub

postgres@db:~/playground/logical_replication$ psql -p 5001

psql (14beta1)

Type "help" for help.

postgres=# create table t(id int primary key, name text);

CREATE TABLE

postgres=# \q

 

postgres@db:~/playground/logical_replication$ psql -p 5002

psql (14beta1)

Type "help" for help.

postgres=# create table t(id int primary key, name text);

CREATE TABLE

postgres=# \q

 

-- insert dummy data to check sub rx changes

postgres@db:~/playground/logical_replication$ psql -p 5001

psql (14beta1)

Type "help" for help.

postgres=# insert into t values (1, 1::text);

INSERT 0 1

postgres=# \q

 

postgres@db:~/playground/logical_replication$ psql -p 5002

psql (14beta1)

Type "help" for help.

postgres=# table t;

 id | name

----+------

(0 rows)

postgres=# alter subscription mysub refresh publication; -- this is because i dropped table with publication enabled

ALTER SUBSCRIPTION

postgres=# table t;

 id | name

----+------

  1 | 1

(1 row)

postgres=# \q

 

-- alter table alter column change type on pub

postgres@db:~/playground/logical_replication$ psql -p 5001

psql (14beta1)

Type "help" for help.

postgres=# alter table t alter COLUMN name type bigint using name::bigint;

ALTER TABLE

postgres=# \q

 

-- alter table alter column change type on sub

postgres@db:~/playground/logical_replication$ psql -p 5002

psql (14beta1)

Type "help" for help.

postgres=# alter table t alter COLUMN name type bigint using name::bigint;

ALTER TABLE

postgres=# \q

 

-- insert new data based on new column type

postgres@db:~/playground/logical_replication$ psql -p 5001

psql (14beta1)

Type "help" for help.

postgres=# insert into t values (2, 100000000000000);

INSERT 0 1

postgres=# \q

 

-- check new data on sub

postgres@db:~/playground/logical_replication$ psql -p 5002

psql (14beta1)

Type "help" for help.

postgres=# table t;

 id |      name

----+-----------------

  1 |               1

  2 | 100000000000000

(2 rows)

postgres=# \q

 

 

--alter table alter col type on pub and insert data

postgres@db:~/playground/logical_replication$ psql -p 5001

psql (14beta1)

Type "help" for help.

postgres=# alter table t alter COLUMN name type text using name::text;

ALTER TABLE

postgres=# insert into t values (3, 'three');

INSERT 0 1

postgres=# \q

 

--alter table alter col type on sub, changes will not come till refresh pub

postgres@db:~/playground/logical_replication$ psql -p 5002

psql (14beta1)

Type "help" for help.

postgres=# table t;

 id |      name

----+-----------------

  1 |               1

  2 | 100000000000000

(2 rows)

postgres=#  alter table t alter COLUMN name type text using name::text;

ALTER TABLE

postgres=# table t;

 id |      name

----+-----------------

  1 | 1

  2 | 100000000000000

(2 rows)

postgres=# alter subscription mysub refresh publication; -- refresh would get back changes 

ALTER SUBSCRIPTION

postgres=# table t;

 id |      name

----+-----------------

  1 | 1

  2 | 100000000000000

  3 | three

(3 rows)

 

or

have I misunderstood the question? 

 

--

Thanks,

Vijay

Mumbai, India

IMPORTANT - This email and any attachments is intended for the above named addressee(s), and may contain information which is confidential or privileged. If you are not the intended recipient, please inform the sender immediately and delete this email: you should not copy or use this e-mail for any purpose nor disclose its contents to any person.