AWS

Documentation Index

Fetch the complete documentation index at: https://xata.io/docs/llms.txt

Use this file to discover all available pages before exploring further.

• pgstreamsource refers to the user provided in the pgstream source URL.
• pgstreamtarget refers to the user provided in the pgstream target URL.

​

Snapshots

​

From AWS RDS/Aurora Database (Snapshots)

​

Quick Checklist

• Create a source user (pgstreamsource) with access to required schemas/tables.
• For roles without passwords → no special config needed.
• Snapshot of roles with passwords is not supported.
• Update YAML config with correct snapshot settings.

​

Steps

1. User privileges Ensure the pgstreamsource user (from the pgstream source URL) can access the database schema and tables you need.
2. Roles handling
   • No roles → no changes required.
   • Roles without passwords → no changes required.
   • Roles with passwords → not supported by AWS. If you try to snapshot roles with passwords, you’ll see:
     pg_dumpall: error: query failed: ERROR:  permission denied for table pg_authid
     
3. Config when not snapshotting roles If roles are disabled or not manually managed, add the following to avoid failures:
   snapshot:
     schema:
       pgdump_pgrestore:
         roles_snapshot_mode: "disabled"
         no_owner: true
         no_privileges: true
   
   Example full configuration:
   source:
     postgres:
       url: "postgresql://pgstreamsource:password@:5432/db?sslmode=require"
       mode: snapshot
       snapshot:
         mode: full # schema + data
         tables: ["public.*"] # all tables in the public schema
         schema:
           mode: pgdump_pgrestore
           pgdump_pgrestore:
             roles_snapshot_mode: "disabled"
             no_owner: true
             no_privileges: true
   

​

To AWS RDS/Aurora Database (Snapshots)

​

Quick Checklist

• Create a target user (pgstreamtarget) for the pgstream target URL.
• Grant privileges for schema/database ownership.
• Grant optional privileges depending on features (create DB, create roles).
• Use disable_triggers: false (required for all non-rds_superuser users).

​

Steps

• Schema ownership
  ALTER DATABASE db OWNER TO pgstreamtarget;
  ALTER SCHEMA public OWNER TO pgstreamtarget;
  
• Database creation (if create_target_db is enabled)
  ALTER ROLE pgstreamtarget CREATEDB;
  
• Role creation (if roles_snapshot_mode is enabled/no_passwords)
  ALTER ROLE pgstreamtarget CREATEROLE;
  
  ⚠️ pgstreamtarget must already hold any privileges it assigns (e.g., REPLICATION).
• Disable triggers (required for AWS RDS/Aurora) ⚠️ AWS RDS/Aurora only allows setting session_replication_role for rds_superuser roles. You must disable trigger management in pgstream if using a non superuser role:
  target:
    postgres:
      disable_triggers: false # Required for AWS RDS/Aurora target databases when using non superuser roles
  
  ❌ If using disable_triggers: true and a role without enough privileges, pgstream will fail with:
  permission denied to set parameter "session_replication_role"
  

​

Replication

​

From AWS RDS/Aurora Database (Replication)

​

Quick Checklist

• Ensure logical replication is enabled (rds.logical_replication=1 in parameter group).
• Use the postgres user or a user with rds_superuser for initialization.
• Grant rds_replication role for replication privileges.
• Optionally use a different pgstreamsource user for streaming.

​

Steps

1. Enable logical replication Logical replication requires wal_level to be set to logical. Check current setting:
   SHOW wal_level;
   
   If not set to logical, update it: AWS RDS and Aurora require modifying the database parameter group:
   1. Go to the AWS RDS Console
   2. Navigate to Parameter groups in the left sidebar
   3. Create a new parameter group or select your existing custom parameter group
      • Note: You cannot modify the default parameter groups
   4. Search for and modify the rds.logical_replication parameter:
      • Set rds.logical_replication to 1 (enabled)
   5. Apply the parameter group to your RDS/Aurora instance:
      • Go to your database instance
      • Click Modify
      • Under Database options, select your custom parameter group
      • Apply the changes
   6. Reboot your database instance for the changes to take effect
   Verification: After the reboot, verify the setting:
   SHOW wal_level;  -- Should return 'logical'
   SHOW rds.logical_replication;  -- Should return 'on' or '1'
   
   ⚠️ Important notes:
   • Setting rds.logical_replication=1 automatically sets wal_level=logical
   • This change requires a database reboot
   • Logical replication may increase storage usage due to retained WAL files
   • Ensure you have adequate monitoring for replication slot lag
2. Replication phases
   • Initialization → requires elevated privileges to create schemas, event triggers, and replication slots.
   • Streaming → can use a dedicated pgstreamsource user.

​

Initialization

• Creates pgstream schema
• Creates replication slot (if missing)
• Creates event triggers/functions for schema changes

source:
  postgres:
    url: "postgresql://postgres:password@:5432/db?sslmode=require"
    mode: replication

-- The postgres user typically has these by default
-- If using a different user, grant:
GRANT rds_superuser TO pgstreamsource;
GRANT rds_replication TO pgstreamsource;

​

Streaming

• Keep using postgres, or
• Transfer ownership to a dedicated pgstreamsource role.

-- Allow ownership transfer
GRANT pgstreamsource TO current_user;

-- Transfer ownership
ALTER DATABASE db OWNER TO pgstreamsource;
ALTER SCHEMA pgstream OWNER TO pgstreamsource;
ALTER SCHEMA <schema> OWNER TO pgstreamsource;
ALTER TABLE pgstream.schema_log OWNER TO pgstreamsource;
ALTER TABLE pgstream.table_ids OWNER TO pgstreamsource;
ALTER SEQUENCE pgstream.xid_serial OWNER TO pgstreamsource;

-- Replication privileges (AWS RDS/Aurora specific)
GRANT rds_replication TO pgstreamsource;

​

To AWS RDS/Aurora Database (Replication)

​

Quick Checklist

• Ensure pgstreamtarget has schema/database ownership.
• If combined with snapshot → follow Snapshot target requirements.

​

Steps

ALTER DATABASE db OWNER TO pgstreamtarget;
ALTER SCHEMA <schema> OWNER TO pgstreamtarget;

​

Troubleshooting