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:
- 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.
- 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.
- 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
