0

For reference, I'm using PostgreSQL 14.3 with PGAdmin. I have 2 databases within the same cluster of databases (or server group if cluster isn't the right word).

So, I have 2 dummy DBs for the purposes of testing this (game and game sub, as publisher and subscriber respectively) and the queries for setting everything up is as follows:

Ran this query in both the publisher and subscriber database

CREATE TABLE IF NOT EXISTS player (
   playerid SERIAL PRIMARY KEY,
   playername VARCHAR (50) UNIQUE NOT NULL,
   playerstatus VARCHAR (50) NOT NULL,
   playerrole VARCHAR (50) NOT NULL,
   playerlevel BIGINT
);

Then I made multiple schema and remade the table so that I would have player.player, party.player, item.player, and public.player like so (using player schema as the example):

CREATE SCHEMA player;
ALTER TABLE player SET SCHEMA player;

Rinse and repeat until all of the schema have an identical 'player' table with no data yet. The publication DB and subscription DB both have this exact same set up, they're 1-to-1.

I then made sure to set the wal_level to logical in the publication DB with the following query:

ALTER SYSTEM SET wal_level = logical;

I even checked to be sure that it's that way using:

SELECT * FROM pg_settings WHERE name ='wal_level';

Once that was done, I set up a publication like so:

CREATE PUBLICATION player_publication FOR TABLE player.player, party.player, item.player, public.player

Then, since the 2 DBs are within the same cluster, I created a replication slot separately from the subscription:

SELECT pg_create_logical_replication_slot('gamesubslot', 'pgoutput');

CREATE SUBSCRIPTION player_subscription3
CONNECTION 'host=localhost port=5432 user=gameuser password=gameuser dbname=gamesub'
PUBLICATION player_publication 
WITH (create_slot=false, slot_name=gamesubslot);

Then, I tried to test it, to no avail. No amount of inserting data into any of the tables in the first (publisher) DB showed up in the subscription DB. I figured, maybe it's an issue of permissions? So then I tried:

GRANT ALL PRIVILEGES ON DATABASE game TO gameuser;
GRANT ALL PRIVILEGES ON DATABASE gamesub TO gameuser;
GRANT USAGE ON SCHEMA player TO gameuser;
GRANT USAGE ON SCHEMA party TO gameuser;
GRANT USAGE ON SCHEMA item TO gameuser;
GRANT USAGE ON SCHEMA public TO gameuser;
GRANT SELECT ON TABLE player.player TO gameuser;
GRANT SELECT ON TABLE party.player TO gameuser;
GRANT SELECT ON TABLE item.player TO gameuser;
GRANT SELECT ON TABLE public.player TO gameuser;

I do not understand what step I could possibly be missing. I've refreshed the subscription several times now, to no avail, I've inserted into all of the tables across all of the schema, I've created new publications and subscriptions. Is it just not possible to connect a database in the same cluster to another database? It shouldn't be a permissions issue, because I granted every permission under the sun EXPLICITLY to the user responsible for the subscription, it has superuser permissions I just want it to WORK at a basic level, which is to say replicating dummy data for a 5 column table. Also, yes, I have checked that every operation is being tracked, in PGAdmin I can see "With INSERT, UPDATE, DELETE, TRUNCATE".

In case maybe my own stupidity as at fault, here's a sample insert statement I'm using to populate the tables:

INSERT INTO public.player
VALUES (1, 'Name', 'Active', 'DPS', 10);

INSERT INTO player.player
VALUES (1, 'Name', 'Active', 'DPS', 10);

INSERT INTO party.player
VALUES (1, 'Name', 'Active', 'DPS', 10);

INSERT INTO item.player
VALUES (1, 'Name', 'Active', 'DPS', 10);

None of these rows will be replicated into the subscription DB.

Please help.

Skullruss
  • 63
  • 8
  • 2
    Do you have any errors in the Postgres log file? You can also check the state of the replicated tables through `pg_subscription_rel.srsubstate` (join that to `pg_subscription` and `pg_class`) –  Mar 01 '23 at 15:57
  • I can't tell which of your actions you did in which database. – jjanes Mar 01 '23 at 15:58
  • @jjanes I performed all of the actions on the publication db, should I have done them on both? To be specific, I performed all of the permission based actions on the publication db, and any table/schema actions on both. – Skullruss Mar 01 '23 at 16:04
  • The creating of the subscription needs to be done in the subscriber database. Whatever else might be going on should be reflected in the log file. Interpreting the log files isn't always easy, but ignoring it rarely makes things easier. – jjanes Mar 05 '23 at 16:29

0 Answers0