Can I run CockroachDB as a drop-in replacement for Postgres?

[fwoodruff-ab]

services:
  db:
    container_name: db
    image: postgres:15
    restart: unless-stopped
    ports: 
      - "5432:5432"
    environment:
      - POSTGRES_USER=my_user
      - POSTGRES_PASSWORD=my_password
      - POSTGRES_DB=postgres
    volumes:
      - my-pgdata-db:/var/lib/postgresql/data
      - ./migrations/db_migration.sql:/docker-entrypoint-initdb.d/init.sql
 volumes:
    my-pgdata-db:

I have a vanilla docker-compose.yml file which runs some services against Postgres. Those services use github.com/jackc/pgx/v4 (among other Golang libraries) to talk to those databases, running migrations and then later making modifications and queries.
This link suggests that it might be possible to swap CockroachDB for Postgres without changing the application code.
If I swap postgres:15 for cockroachdb/cockroach:v24.1.2, what other changes should I make so that, as closely as possible, the application code still runs?

Notably, I would like to connect to CockroachDB without TLS but with password login.

[Yash-xoxo]

Yes, CockroachDB can work as a Postgres drop-in for basic use cases. Here's what to change:

services:
  db:
    container_name: db
    image: cockroachdb/cockroach:v24.1.2
    command: start-single-node --insecure
    restart: unless-stopped
    ports: 
      - "26257:26257"  # CockroachDB port
      - "8080:8080"    # Admin UI
    environment:
      - COCKROACH_DATABASE=postgres
      - COCKROACH_USER=my_user
      - COCKROACH_PASSWORD=my_password
    volumes:
      - my-cockroach-data:/cockroach/cockroach-data
      - ./migrations/db_migration.sql:/docker-entrypoint-initdb.d/init.sql

volumes:
  my-cockroach-data:

Key differences:

• Port 26257 instead of 5432
• --insecure flag disables TLS
• Connection string: postgresql://my_user:my_password@localhost:26257/postgres?sslmode=disable

Gotchas:

• Some Postgres features unsupported (sequences, triggers, certain index types)
• pgx/v4 works but check migration syntax compatibility
• Init scripts may need tweaking (CockroachDB's SQL dialect differs slightly)

Test your migrations - some DDL might need adjustments for CockroachDB's distributed nature.