GCP CloudSQL

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

​

Snapshots

​

From CloudSQL Postgres (Snapshots)

​

Quick Checklist

• Create a source user (pgstreamsource) with access to required schemas/tables.
• Decide how to handle roles:
  • Disabled → no special config.
  • Without passwords → no special config.
  • With passwords → enable cloudsql.pg_authid_select_role flag.
• 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 → grant pgstreamsource access to pg_authid by enabling the cloudsql.pg_authid_select_role database flag (via CloudSQL UI or API).
     • Reference: GCP documentation
   Without this, you’ll see:
   Copy

   Ask AI

   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:
   Copy

   Ask AI

   snapshot:
     schema:
       pgdump_pgrestore:
         roles_snapshot_mode: "disabled"
         no_owner: true
         no_privileges: true
   

   Example full configuration:
   Copy

   Ask AI

   source:
     postgres:
       url: "postgresql://pgstreamsource:password@<cloudsql_address>:5432/db"
       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 CloudSQL Postgres (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, set replication role).
• If using CloudSQL, disable disable_triggers.

​

Steps

• Schema ownership
  Copy

  Ask AI

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

• Database creation (if create_target_db is enabled)
  Copy

  Ask AI

  ALTER ROLE pgstreamtarget CREATEDB;
  

• Role creation (if role_snapshot_mode is enabled/no_passwords)
  Copy

  Ask AI

  ALTER ROLE pgstreamtarget CREATEROLE;
  

  ⚠️ pgstreamtarget must already hold any privileges it assigns (e.g., REPLICATION). If many privileges are needed, use the postgres user, which is a pseudo-superuser.
• Disable triggers (if disable_triggers is enabled)
  Copy

  Ask AI

  GRANT SET ON PARAMETER session_replication_role TO pgstreamtarget;
  

  Without this, pgstream fails with:
  Copy

  Ask AI

  permission denied to set parameter "session_replication_role"
  

  ⚠️ On CloudSQL, the postgres role cannot set session_replication_role. Always disable disable_triggers in this case.

​

Replication

​

From CloudSQL Postgres (Replication)

​

Quick Checklist

• Enable cloudsql.logical_decoding flag.
• Use postgres user for initialization.
• Grant cloudsqlreplica + replication privileges.
• Optionally transfer ownership to pgstreamsource for streaming.

​

Steps

1. Enable logical replication Enable the cloudsql.logical_decoding flag.
2. Replication phases
   • Initialization → requires postgres (superuser-like privileges).
   • Streaming → can switch to pgstreamsource (from the pgstream source URL).

​

Initialization

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

GRANT cloudsqlreplica TO postgres;
ALTER ROLE postgres REPLICATION;

​

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
GRANT cloudsqlreplica TO pgstreamsource;
ALTER ROLE pgstreamsource REPLICATION;

​

To CloudSQL Postgres (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