Databases under 200GB¶
For smaller environments that can tolerate a little downtime, use a simple pg_dump/pg_restore process. Here are the steps.
Save the database structure:
pg_dump \ --format=plain \ --no-owner \ --schema-only \ --file=schema.sql \ --schema=target_schema \ postgres://user:pass@host:5432/db
Connect to the Citus cluster using psql and create the schema:
\i schema.sql
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.
Put the application into maintenance mode, and disable any other writes to the old database.
Save the data from the old database to disk with pg_dump:
pg_dump \ --format=custom \ --no-owner \ --data-only \ --file=data.dump \ --schema=target_schema \ postgres://user:pass@host:5432/db
Import into Citus using pg_restore:
# remember to use connection details for Citus, # not the source database pg_restore \ --host=host \ --dbname=dbname \ --username=username \ data.dump # it'll prompt you for the connection password
Test application.
Launch!