Database Setup

This guide covers the PostgreSQL configuration and user setup required for Kasho.

Database Requirements

Source PostgreSQL Database

Your source database needs:

PostgreSQL version: 15 or higher
WAL level: Set to logical for replication
Replication slots: At least 2 available slots
Max WAL senders: At least 2
User permissions: REPLICATION privilege and SELECT on all tables

Target Database

Your target database needs:

PostgreSQL version: 15 or higher
User permissions: SUPERUSER privilege

SUPERUSER Required

The replica user requires SUPERUSER privileges to set session_replication_role = 'replica', which prevents triggers from firing during replication.

Step 1: Configure PostgreSQL for Logical Replication

Changing these settings requires a PostgreSQL restart

Edit your postgresql.conf file:


# Enable logical replication
wal_level = logical
 
# Allow replication connections
max_replication_slots = 10
max_wal_senders = 10

Restart PostgreSQL after making these changes.

Step 2: Setup Both Databases

Option A: Automated Setup (Recommended)

Use the provided setup script to configure both primary and replica databases:


# Set environment variables for both databases
export PRIMARY_DATABASE_URL="postgresql://kasho:pass@primary:5432/db"
export REPLICA_DATABASE_URL="postgresql://kasho:pass@replica:5432/db"
export PRIMARY_DATABASE_SU_USER="postgres"
export PRIMARY_DATABASE_SU_PASSWORD="postgres"
export REPLICA_DATABASE_SU_USER="postgres"
export REPLICA_DATABASE_SU_PASSWORD="postgres"
 
# Run setup script that configures both databases
docker run --rm kasho:latest /app/scripts/prepare-dbs.sh

This script will:

Verify prerequisites on the primary (wal_level = logical)
Create the kasho user on the primary with REPLICATION privileges
Create the kasho user on the replica with SUPERUSER privileges
Set up DDL logging on the primary (for schema change replication)
Create the publication on the primary

Option B: Manual Setup

If you prefer manual setup, follow these steps:

1. Verify WAL Level

Connect as a superuser and check:


-- Check current setting
SHOW wal_level;
 
-- Should return 'logical'

2. Create User on Source Database


-- Create role with replication and login privileges
CREATE ROLE kasho WITH REPLICATION LOGIN PASSWORD 'your-secure-password';
 
-- Grant read permissions
GRANT USAGE ON SCHEMA public TO kasho;
GRANT CREATE ON SCHEMA public TO kasho;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO kasho;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO kasho;
 
-- Grant future table permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO kasho;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON SEQUENCES TO kasho;

3. Create User on Target Database

Connect as a superuser to your target database:


-- Create role with SUPERUSER privileges
-- SUPERUSER is required to set session_replication_role = 'replica'
CREATE ROLE kasho WITH SUPERUSER LOGIN PASSWORD 'your-secure-password';
 
-- Grant full permissions (needs to apply changes)
GRANT USAGE ON SCHEMA public TO kasho;
GRANT CREATE ON SCHEMA public TO kasho;
GRANT ALL ON ALL TABLES IN SCHEMA public TO kasho;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO kasho;
 
-- Grant future table permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL ON TABLES TO kasho;
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT ALL ON SEQUENCES TO kasho;

4. Create Publication on Source


-- Create publication for all tables
CREATE PUBLICATION kasho_pub FOR ALL TABLES;

Do NOT create the replication slot manually. It will be created automatically during the bootstrap process.

Step 3: Configure pg_hba.conf

Ensure the kasho user can connect for replication. Add to pg_hba.conf:


# TYPE  DATABASE        USER        ADDRESS                 METHOD
host    replication     kasho       0.0.0.0/0               md5
host    all             kasho       0.0.0.0/0               md5

Reload PostgreSQL configuration:


pg_ctl reload

Step 4: Test Connection

Verify the kasho user can connect:


# Test source database connection
psql "postgresql://kasho:password@source-host:5432/source_db?sslmode=disable" -c "SELECT 1"
 
# Test target database connection  
psql "postgresql://kasho:password@target-host:5432/target_db?sslmode=disable" -c "SELECT 1"

Security Consideration

The replica user requires SUPERUSER privileges. In production environments, this should be carefully evaluated. Consider using a dedicated database instance for the replica if security policies restrict SUPERUSER access.

Step 5: DDL Replication Setup

In order to replicate schema changes (CREATE TABLE, ALTER TABLE, etc.), you must set up DDL logging:


-- Create DDL log table
CREATE TABLE IF NOT EXISTS kasho_ddl_log (
    id BIGSERIAL PRIMARY KEY,
    lsn pg_lsn NOT NULL,
    timestamp TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    username TEXT NOT NULL,
    database TEXT NOT NULL,
    ddl TEXT NOT NULL
);
 
-- Create cleanup function
CREATE OR REPLACE FUNCTION kasho_cleanup_ddl_log() RETURNS void AS $$
BEGIN
    DELETE FROM kasho_ddl_log WHERE timestamp < NOW() - INTERVAL '7 days';
END;
$$ LANGUAGE plpgsql;
 
-- Create DDL capture function
CREATE OR REPLACE FUNCTION kasho_log_ddl() RETURNS event_trigger AS $$
DECLARE
    current_lsn pg_lsn;
    ddl_text TEXT;
BEGIN
    SELECT pg_current_wal_lsn() INTO current_lsn;
    SELECT current_query() INTO ddl_text;
    
    INSERT INTO kasho_ddl_log (lsn, username, database, ddl)
    VALUES (current_lsn, current_user, current_database(), ddl_text);
    
    PERFORM kasho_cleanup_ddl_log();
END;
$$ LANGUAGE plpgsql;
 
-- Create event triggers
CREATE EVENT TRIGGER kasho_log_ddl_start 
ON ddl_command_start
EXECUTE FUNCTION kasho_log_ddl();
 
CREATE EVENT TRIGGER kasho_log_ddl_end 
ON ddl_command_end
EXECUTE FUNCTION kasho_log_ddl();

Next Steps

Once your databases are configured:

Continue with the Quick Start guide
Learn about Configuration Options
Set up the Bootstrap Process for existing data