Category: postgresql

How do you check if logical replication is running on a postgres instance?

  1. Check if logical replication is enabled in the PostgreSQL configuration file:Open the PostgreSQL configuration file (postgresql.conf), usually located in the PostgreSQL data directory. Look for the wal_level parameter. Logical replication requires at least wal_level to be set to logical or replica. If it’s set to logical, then logical replication is enabled. You can find the location of the PostgreSQL data directory by querying the pg_settings system catalog table: SELECT name, setting FROM pg_settings WHERE name = 'data_directory';
  2. Check if logical replication slots are being used:Logical replication uses replication slots to keep track of the status of replication. You can check if any replication slots are in use by querying the pg_replication_slots system catalog table: SELECT slot_name, plugin, slot_type, database FROM pg_replication_slots; If there are slots with slot_type as logical, then logical replication is being used.
  3. Check if there are any active subscriptions:Logical replication involves publishers and subscribers. Publishers publish changes to the replication stream, and subscribers consume these changes. To see if there are any active subscriptions, you can query the pg_subscription system catalog table:SELECT * FROM pg_subscription; If there are subscriptions listed here, then logical replication is in use.
  4. Check if there are any replication connections:Logical replication typically involves connections from publishers to subscribers. You can check if there are any active replication connections by querying the pg_stat_replication system view:SELECT * FROM pg_stat_replication; If there are entries listed here, then logical replication connections are active.

By following these steps, you can determine if logical replication is running on your PostgreSQL instance.

Difference between users, groups, and roles on Postgres and Redshift

Postgres

We’ll start here, because Postgres generally sets the standard for relational databases.

In Postgres 9, users and groups were replaced with roles (Postgres have been very kind, and have maintained user and group as aliases for role for backwards compatibility). The difference between users and roles? Simply put, users can now be considered as roles with the ability to login.

  • Membership: Any role can be a member of another role, so you can create a hierarchical structure with as many tiers as you’d like.
  • Ownership: Any role can own relations.
  • Privileges: Any role can have privileges granted to it.
  • Inheritance: Up to you! Roles can be configured such that any members automatically inherit the privileges of the role, or can be configured to force a member to explicitly change their role in order to use those privileges.

Redshift

Redshift forked from Postgres somewhere around version 8. As such, roles look more like an old-school Postgres database rather than the role-based world of Postgres 9 – on Redshift, users and groups are separate entities.

  • Membership: Only a user can be a member of groups, i.e. a group cannot be a member of other groups, so you can only have a two-tiered structure.
  • Ownership: Users, rather than groups, own relations.
  • Privileges: Both users and groups can have privileges granted to them.
  • Inheritance: Users automatically inherit all privileges of any groups they are a member of.