On Sun, 4 Jul 2021 at 15:53, Avi Weinberg <a...@gilat.com> 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
