W
W
wadomon2019-01-28 18:37:11
PostgreSQL
wadomon, 2019-01-28 18:37:11

How to do ALTER TABLE for postgres pglogical?

How to do ALTER TABLE for postgres pglogical on logical replication?
A command like
ALTER TABLE aaa_plc_banned_domains ADD Phone2 CHARACTER VARYING(20);
Maybe something like
SELECT pglogical.replicate_ddl_command('ALTER TABLE aaa_plc_banned_domains ADD Phone2 CHARACTER VARYING(20)');
but writes error
ERROR: relation "aaa_plc_banned_domains" does not exist
CONTEXT: during execution of queued SQL statement: ALTER TABLE aaa_plc_banned_domains ADD Phone2 CHARACTER VARYING(20)

Answer the question

In order to leave comments, you need to log in

2 answer(s)
W
wadomon, 2019-01-31
@wadomon

I asked it myself, I'm answering it myself :) The command passing alter is
SELECT pglogical.replicate_ddl_command(command :='ALTER TABLE public.stats ADD Phone CHARACTER VARYING(20)',replication_sets:=ARRAY['insert_update']);
delete phone column
SELECT pglogical.replicate_ddl_command(command :='ALTER TABLE public.stats DROP Phone',replication_sets:=ARRAY['insert_update']);

M
Melkij, 2019-01-28
@melkij

https://github.com/2ndQuadrant/pglogical/blob/REL2...
Try querying with the full table name.
Probably in the public schema you have a table:

SELECT pglogical.replicate_ddl_command('ALTER TABLE public.aaa_plc_banned_domains ADD Phone2 CHARACTER VARYING(20)');

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question