Small Database Migration

For smaller environments that can tolerate a little downtime, use a simple pg_dump/pg_restore process. Here are the steps.

  1. Save the database structure from your development database:

    1. pg_dump \
    2.    --format=plain \
    3.    --no-owner \
    4.    --schema-only \
    5.    --file=schema.sql \
    6.    --schema=target_schema \
    7.    postgres://user:pass@host:5432/db

  2. Connect to the Citus cluster using psql and create the schema:

    1. \i schema.sql

  3. Run your create_distributed_table and create_reference_table statements. If you get an error about foreign keys, it’s generally due to the order of operations. Drop foreign keys before distributing tables and then re-add them.

  4. Put the application into maintenance mode, and disable any other writes to the old database.

  5. Save the data from the original production database to disk with pg_dump:

    1. pg_dump \
    2.    --format=custom \
    3.    --no-owner \
    4.    --data-only \
    5.    --file=data.dump \
    6.    --schema=target_schema \
    7.    postgres://user:pass@host:5432/db

  6. Import into Citus using pg_restore:

    1. # remember to use connection details for Citus,
    2. # not the source database
    3. pg_restore  \
    4.    --host=host \
    5.    --dbname=dbname \
    6.    --username=username \
    7.    data.dump
    9. # it'll prompt you for the connection password

  7. Test application.

  8. Launch!