Sync local and remote metabase instances

sync.md

Create backup of metabase using pg_dump

First run

pg_dump -Fc -h localhost metabase > metabase-1.pgdump

Restore on prod, restore.sh

#!/bin/bash
# Restores given pgdump file using metabase database to rds

if [[ "$#" -ne 1 ]]; then
    echo "Usage: $0 <pgdump file>"
    exit 1
fi

# Enter root user creds here
export PGHOST=""
export PGUSER=""
export PGPASSWORD=""
export PGDATABASE="postgres"

psql -c "ALTER DATABASE metabase OWNER TO $PGUSER"
psql -c "DROP DATABASE IF EXISTS metabase"
psql -c "CREATE DATABASE metabase"
psql -c "ALTER DATABASE metabase OWNER TO metabase"
pg_restore --no-owner --role metabase -d metabase "$1"

psql -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO metabase"
psql -c "GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO metabase"
# check this a bit more, perm issue sometimes

# NOTE: Might need to restart metabase

bash restore.sh metabase-1.pgdump

Goto project root install pgsync create following .pgsync.yml

from: postgres://localhost:5432/metabase
to: postgres://user:password@remote_host/metabase?sslmode=require

to_safe: true

# exclude settings because need to preserve site-url
exclude:
  - login_history
  - settings

Subsequent runs

From now on, run

pgsync --defer-constraints

Sometimes might need to add --overwrite.
Try stopping both metabase instances before sync

Enjoy!