2

I tried to setup a simple master > slave replication using pglogical. The pglogical docs mention that one needs a primary key for replicating updates and deletes. Since I don't need neither one, I set up a replication set at the provider as follows:

SELECT pglogical.create_replication_set(
set_name := 'all_remote_tables', 
replicate_insert := true, 
replicate_update := false,
replicate_delete := false,
replicate_truncate := false);

Then I added my table:

SELECT pglogical.replication_set_add_table(
set_name := 'all_remote_tables',
relation := 'measurements',
synchronize_data := true);

My subscriber is set up as follows:

SELECT pglogical.create_subscription(
subscription_name := 'testsubscription1',
provider_dsn := 'host=$host port=$port dbname=$dbname user=$user password=$pw', 
replication_sets := array['all_remote_tables'],
synchronize_structure := true,
synchronize_data := true,
forward_origins := '{}');

With this setup and a table layout like this:

CREATE TABLE "measurements"(    
time        TIMESTAMP WITH TIME ZONE NOT NULL,   
value       NUMERIC,
sensor_id   TEXT);

only the structure is replicated.

As soon as I add a primary key:

CREATE TABLE "measurements"(            
id          SERIAL PRIMARY KEY, 
time        TIMESTAMP WITH TIME ZONE NOT NULL,   
value       NUMERIC,
sensor_id   TEXT);

everything is working as expected.

Since I want to store high frequency timeseries (using timescaledb) and will never have the need to update/delete single rows I don't see the benefits of a primary key here. Therefore I want to know if pglogical is not working without primary keys by design or if I did some misconfiguration.

The prebuilt replication set "default_insert_only" works without any primary key, but I want to use my own replication sets.

A little offtopic: would you even recommend using pglogical for simple multiple masters > one slave replication of specific tables (= aggregation)? Since postgres 10 logical replication is part of the core anyhow.

Tom
  • 91
  • 7

1 Answers1

1

You don't need custom replication set for that, there is preexisting default_insert_only set for this purpose. Check chapter "2.4 Replication Sets" in pglogical docs.

Jacek Chmielewski
  • 1,763
  • 4
  • 16
  • 18
  • 1
    That's true, I want to have full control over my replication sets, though. The documentation states `The "default_insert_only" only replicates INSERTs and is meant for tables that don't have primary key (see Limitations section for details).` I don't see a difference to my self defined replication set, still it won't work without a primary key. – Tom Feb 06 '18 at 11:16
  • 1
    The builtin "default_insert_only" replication set works as expected, even without a primary key. – Tom Feb 06 '18 at 11:23