Database Setup

This guide covers the database configuration and user setup required for Kasho. Choose your database type below.

PostgreSQL

PostgreSQL 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 kashoio/kasho:latest /app/scripts/prepare-dbs-pg.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. This is automatically handled by the Option A: Automated Setup above.

Recommended: Use Automated Setup The DDL logging setup involves event triggers with a two-phase capture mechanism. Running the automated setup script (Option A) is strongly recommended as it handles all the complexity correctly.

If you used the manual setup in Option B and need to add DDL logging separately, you can run just the DDL setup portion:


# Set environment variables
export PRIMARY_DATABASE_URL="postgresql://kasho:pass@primary:5432/db"
export PRIMARY_DATABASE_SU_USER="postgres"
export PRIMARY_DATABASE_SU_PASSWORD="postgres"
 
# Run DDL setup script
docker run --rm kashoio/kasho:latest /app/scripts/setup-ddl-logging-pg.sh

This creates:

kasho_ddl_log table to store DDL statements with their WAL LSN positions
Event triggers that capture DDL using PostgreSQL’s set_config/current_setting mechanism
Automatic cleanup of entries older than 7 days

MySQL

MySQL Database Requirements

Source MySQL Database

Your source database needs:

MySQL version: 8.0 or higher
Binary logging: Enabled with ROW format
GTID mode: Enabled for consistent replication
User permissions: REPLICATION SLAVE, REPLICATION CLIENT, and SELECT privileges

Target Database

Your target database needs:

MySQL version: 8.0 or higher
User permissions: ALL PRIVILEGES (required to bypass foreign key checks during replication)

Simpler DDL Replication Unlike PostgreSQL, MySQL automatically captures DDL statements in the binary log. No additional DDL logging setup is required.

Step 1: Configure MySQL for Binary Log Replication

Changing these settings requires a MySQL restart

Edit your my.cnf file:


[mysqld]
# Unique server identifier
server-id = 1
 
# Enable binary logging
log_bin = mysql-bin
 
# Full row images for accurate replication
binlog_row_image = FULL
 
# Enable GTID mode for consistent replication
gtid_mode = ON
enforce_gtid_consistency = ON

Note: binlog_format = ROW defaults in MySQL 8.0+ and the option is deprecated, so you don’t need to set it explicitly.

Restart MySQL 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="mysql://kasho:pass@primary:3306/db"
export REPLICA_DATABASE_URL="mysql://kasho:pass@replica:3306/db"
export PRIMARY_DATABASE_SU_USER="root"
export PRIMARY_DATABASE_SU_PASSWORD="root"
export REPLICA_DATABASE_SU_USER="root"
export REPLICA_DATABASE_SU_PASSWORD="root"
 
# Run setup script that configures both databases
docker run --rm kashoio/kasho:latest /app/scripts/prepare-dbs-mysql.sh

This script will:

Verify prerequisites on the primary (binlog enabled, GTID mode)
Create the kasho user on the primary with REPLICATION privileges
Create the kasho user on the replica with ALL PRIVILEGES

Option B: Manual Setup

If you prefer manual setup, follow these steps:

1. Verify Binary Logging

Connect as root and check:


-- Check current settings
SELECT
    @@binlog_format AS binlog_format,
    @@log_bin AS log_bin_enabled,
    @@binlog_row_image AS binlog_row_image,
    @@gtid_mode AS gtid_mode;
 
-- Expected values:
-- binlog_format = ROW
-- log_bin_enabled = 1
-- binlog_row_image = FULL
-- gtid_mode = ON

2. Create User on Source Database


-- Create user with replication privileges
CREATE USER 'kasho'@'%' IDENTIFIED BY 'your-secure-password';
 
-- Grant replication privileges
GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'kasho'@'%';
 
-- Grant SELECT on all tables for reading data
GRANT SELECT ON *.* TO 'kasho'@'%';
 
-- Apply privileges
FLUSH PRIVILEGES;

3. Create User on Target Database

Connect as root to your target database:


-- Create user with full privileges
CREATE USER 'kasho'@'%' IDENTIFIED BY 'your-secure-password';
 
-- Grant all privileges (needed to apply changes and bypass foreign key checks)
GRANT ALL PRIVILEGES ON *.* TO 'kasho'@'%' WITH GRANT OPTION;
 
-- Apply privileges
FLUSH PRIVILEGES;

No Publication Required Unlike PostgreSQL, MySQL doesn’t require creating a publication. The binary log automatically captures all changes.

Step 3: Grant User Privileges

MySQL uses GRANT statements for access control instead of a separate configuration file like PostgreSQL’s pg_hba.conf.

The user creation statements above already include 'kasho'@'%' which allows connections from any host. For production, you may want to restrict this:


-- Restrict to specific host/network
CREATE USER 'kasho'@'10.0.0.%' IDENTIFIED BY 'your-secure-password';
 
-- Or restrict to specific IP
CREATE USER 'kasho'@'192.168.1.100' IDENTIFIED BY 'your-secure-password';

Step 4: Test Connection

Verify the kasho user can connect:


# Test source database connection
mysql -h source-host -P 3306 -u kasho -p -e "SELECT 1"
 
# Test target database connection
mysql -h target-host -P 3306 -u kasho -p -e "SELECT 1"
 
# Verify binlog access on source
mysql -h source-host -P 3306 -u kasho -p -e "SHOW MASTER STATUS"

Security Consideration The replica user requires ALL PRIVILEGES. In production environments, consider using a dedicated database instance for the replica if security policies are a concern.

No DDL Setup Required MySQL automatically captures DDL statements (CREATE TABLE, ALTER TABLE, etc.) in the binary log. Unlike PostgreSQL, no additional event triggers or logging tables are needed.

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