Quick Tour of Postgres Logical Replication - Misc Tips and Tricks (Part 4)

August 21, 2026

This post was written for Blaugust 2026

Some misc tips and tricks, plus some things I found annoying while experimenting with this that didn’t really fit into the previous blog posts.

Read only tables on the sub

You want the subscriber’s app to read replicated data but never write to it. Otherwise an accidental INSERT or UPDATE on the subscriber creates divergence from the publisher.

We can create a read-only role for the subscriber’s application. The application connects as this role, so it can query replicated data but never modify it:

 1-- on the subscriber
 2
 3CREATE ROLE app_ro WITH LOGIN PASSWORD 'readonly';
 4
 5GRANT USAGE ON SCHEMA public TO app_ro;
 6
 7GRANT SELECT ON
 8    public.groups,
 9    public.users,
10    public.groups_users
11TO app_ro;

Now any accidental write from app_ro fails with:

ERROR: permission denied for table users

Row filters can turn UPDATE into DELETE 🫠

oh boy this is a shitty one, brace yourself:

A row filter can turn an UPDATE into a DELETE on the subscriber. For example:

1CREATE PUBLICATION users_pub
2FOR TABLE users WHERE (enabled);

Now suppose this happens on the publisher:

1UPDATE users SET enabled = false WHERE id = 1;

If that update changes the row from matching to not matching the filter, PostgreSQL sends a DELETE for that row to the subscriber.

This can create a nasty FK problem. The publisher didn’t actually delete users.id = 1, so its ON DELETE CASCADE never ran. The subscriber may therefore be left with groups_users.user_id = 1 pointing at a user that was deleted by replication.

Normally, the subscriber’s FK triggers don’t run during replication apply, so the orphan can slip through. If you deliberately enable or enforce those triggers, the replicated DELETE can instead fail with an FK violation.

You cannot rely on PostgreSQL’s internal referential integrity triggers to manage this specific state transition over logical replication. You must manage the deletion of dependent rows explicitly within your application logic or through a different database design strategy 💀

Schema Drift

Logical replication does NOT replicate schema changes. This means you need to keep the publisher and subscriber schemas compatible yourself.

One particularly nasty case is adding a column to the publication when the subscriber doesn’t have that column.

For example, if you add avatar_url to users on the publisher and that column is included in the publication, but the subscriber’s users table doesn’t have avatar_url, the logical replication apply worker will fail when it tries to apply a change containing that column.

Fear not tho. PostgreSQL will keep restarting the apply worker, and once you fix the subscriber’s schema, replication will resume and catch up on the queued changes.

I know, keeping the schemas in sync is a royal pain in the ass and super error-prone. pglogical, which we’ll look at below, can help with that.

Avoiding name collisions

If the subscriber already has its own users table, you can’t also have a replicated users table with the same name in the same schema.

So, we have two straightforward options:

Prefix the tables: name the replicated tables replica_users, replica_groups, and replica_groups_users on both sides.

Or:

Use a separate schema: put the replicated tables in a dedicated schema like upstream, while the subscriber’s own tables stay in public. It’s kinda nice since they’re in the same database, you can then join public.users and upstream.users no prob.

Alternatively, pglogical can map replicated tables into a different schema. See below 👇

pglogical

PostgreSQL’s native logical replication requires the tables to have the same names on both sides. pglogical lets you remap names.

For example:

Publisher	Subscriber
`users`	`replica_users`
`groups`	`replica_groups`
`groups_users`	`replica_groups_users`

Another thing with pglogical is that it can replicate schema changes. For example, if you want to add a column to users, run the schema change through pglogical on the publisher:

1SELECT pglogical.replicate_ddl_command(
2    'ALTER TABLE users ADD COLUMN avatar_url TEXT;'
3);

This executes the ALTER TABLE on the publisher and replicates the same change to the subscriber. You don’t need to run the ALTER TABLE separately on the publisher or subscriber.

Native logical replication doesn’t replicate schema changes, so you’d need to apply the same change manually on the subscriber.

Worth noting that pglogical doesn’t automatically replicate every possible schema change, so check if it fits your use case before setting up all this crap. 💩

What happens if the subscriber goes offline for an absurd amount of time?

Like we said in part 2 , the replication slot on the publisher survives a restart. So when the subscriber reconnects, it can pick up where it left off, assuming the required WAL is still available.

So basically: subscriber reconnects → slot survives → catches up.

Unless the required WAL is no longer available.

The real problem here is that while the subscriber is offline, the publisher keeps accepting writes and the replication slot prevents the required WAL from being recycled. That retained WAL can keep growing and eventually eat up your disk.

PostgreSQL has max_slot_wal_keep_size to limit how much WAL a replication slot can retain, but if the subscriber stays offline long enough to lose the WAL it needs, you’ll need to re-sync it rather than simply letting it catch up 💀

Initial sync isn’t instant

This may sound kinda obvious, but creating a subscription doesn’t give you an instant point-in-time copy. PostgreSQL establishes a consistent snapshot on the publisher, then copies each table while ongoing replication continues. The snapshot is consistent, but obviously the subscriber only has a partial copy until the initial copy finishes.

For a large tables, the initial copy can take a very long time, and you pay that cost again every time you drop and re-create a subscription to recover from a lost slot.