Re: [17] CREATE SUBSCRIPTION ... SERVER

On Fri, Jan 5, 2024 at 1:34 PM Jeff Davis <pgsql(at)j-davis(dot)com> wrote:
>
> On Fri, 2024-01-05 at 12:49 +0530, Ashutosh Bapat wrote:
> > Can you please provide an example using postgres_fdw to create a
> > subscription using this patch. I think we should document it in
> > postgres_fdw and add a test for the same.
>
> There's a basic test for postgres_fdw in patch 0003, just testing the
> syntax and validation.
>
> A manual end-to-end test is pretty straightforward:
>
> -- on publisher
> create table foo(i int primary key);
> create publication pub1 for table foo;
> insert into foo values(42);
>
> -- on subscriber
> create extension postgres_fdw;
> create table foo(i int primary key);
> create server server1
> foreign data wrapper postgres_fdw
> options (host '/tmp', port '5432', dbname 'postgres');
> create user mapping for u1 server server1
> options (user 'u1');
> select pg_conninfo_from_server('server1','u1',true);
> create subscription sub1 server server1 publication pub1;
>
> I don't think we need to add an end-to-end test for each FDW, because
> it's just using the assembled connection string. To see if it's
> assembling the connection string properly, we can unit test with
> pg_conninfo_from_server().

Thanks for the steps.

I don't think we need to add a test for every FDW. E.g. adding a test
in file_fdw would be pointless. But postgres_fdw is special. The test
could further create a foreign table ftab_foo on subscriber
referencing foo on publisher and then compare the data from foo and
ftab_foo to make sure that the replication is happening. This will
serve as a good starting point for replicated tables setup in a
sharded cluster.

--
Best Wishes,
Ashutosh Bapat