Native driver - table not added to publication

[dfillingham]

Hi there,

Is my understanding correct that when using the native driver and include_schema_regex, that any tables created within that schema will get auto-added to the publication?

Having an issue where I've installed and configured the extension and the DDL is replicating to the subscriber, but the table isn't being auto-added to the publication.

I ran the suggested debug query from the docs:

SELECT sc.set_name, n.nspname
FROM pg_namespace n
INNER JOIN pgl_ddl_deploy.set_configs sc
  ON nspname !~* pgl_ddl_deploy.exclude_regex()
  AND n.nspname ~* sc.include_schema_regex
ORDER BY sc.set_name, n.nspname;

which correctly gave me:

      set_name      |   nspname
--------------------+--------------
 prod2              | myschema

If I create a table on the provider and populate rows ie:

create table myschema.test1 (name text);
insert into myschema.test1 (name) values ('david');

I can then see the table gets created on the subscriber, but no data is sent:

select * from myschema.test1;
 name
------
(0 rows)

On the provider, it seems that the table isn't added to the publication:

select * from pg_publication_tables where tablename = 'mytest';
 pubname | schemaname | tablename
---------+------------+-----------
(0 rows)

Any ideas?

[jfinzel]

@dfillingham your understanding is correct. We will investigate this hopefully this week. Thanks!

[jfinzel]

@dfillingham I believe your only issue is your table has no primary key. Primary keys are required for a table to be added to replication under this system. Can you please try again with this and let me know:

create table myschema.test1 (name text primary key);

Below you can see it works for me:

test_logical=# CREATE PUBLICATION test;
CREATE PUBLICATION
test_logical=# INSERT INTO pgl_ddl_deploy.set_configs (set_name, include_schema_regex, driver)
VALUES ('test', 'myschema', 'native');
INSERT 0 1
test_logical=#
test_logical=#
test_logical=# SELECT pgl_ddl_deploy.deploy('test');
NOTICE:  table "tmp_objs" does not exist, skipping
NOTICE:  event trigger "auto_rep_ddl_create_2_test" does not exist, skipping
NOTICE:  event trigger "auto_rep_ddl_drop_2_test" does not exist, skipping
NOTICE:  event trigger "auto_rep_ddl_unsupp_2_test" does not exist, skipping
NOTICE:  function pgl_ddl_deploy.auto_rep_ddl_create_2_test() does not exist, skipping
NOTICE:  function pgl_ddl_deploy.auto_rep_ddl_drop_2_test() does not exist, skipping
NOTICE:  function pgl_ddl_deploy.auto_rep_ddl_unsupp_2_test() does not exist, skipping
 deploy
--------
 t
(1 row)

test_logical=# SELECT * FROM pg_publication_tables WHERE pubname = 'test';
 pubname |   schemaname   | tablename
---------+----------------+-----------
 test    | pgl_ddl_deploy | queue
(1 row)

test_logical=# CREATE SCHEMA myschema;
CREATE SCHEMA
test_logical=# CREATE TABLE myschema.test1 (name text primary key);
CREATE TABLE
test_logical=# SELECT * FROM pg_publication_tables WHERE pubname = 'test';
 pubname |   schemaname   | tablename
---------+----------------+-----------
 test    | pgl_ddl_deploy | queue
 test    | myschema       | test1
(2 rows)

[dfillingham]

Whoops, it even says so right in the postgres docs.

Yes with a primary key it's working correctly, thanks for the help :)