I'm using hasura migration guide to sync two servers - DEV and PROD. Before we manually transferred the changes (as in 'using UI to copy all the changes'), so now databases are 90% similar.
We decided to set up proper migrations, but based on my tests doing an initial sync requires a 'clean slate'.
Example of the problem:
We have users table on both DEV and PROD. On DEV there is additional field age.
We do
1 hasura migrate create --init (on dev)
2 hasura migrate apply --endpoint PRODUCTION
We get error relation \"users\" already exists.
The question is - how can we sync the DBs without cleaning PROD first?
You're currently receiving that issue since running migrate apply is trying to execute on tables which already exist.
If you use the --skip-execution flag you can mark all of your relevant migrations as completed in the PRODUCTION environment and the migrate apply as usual to apply the new migration.
More information is available in the CLI documentation:
https://hasura.io/docs/latest/graphql/core/hasura-cli/hasura_migrate_apply.html
After re-reading the question to clarify - creating the initial migration using create --init will create a snapshot of your database as it is now (won't diff between STAGING and PRODUCTION).
To migrate this between STAGING and PRODUCTION you'd need to manually change the initial migration created to match staging and prod, and then manually create an incremental migration to bring PRODUCTION in line with STAGING.
After this, if you're working with Hasura Console through the CLI (using https://hasura.io/docs/latest/graphql/core/hasura-cli/hasura_console.html) -- it will automatically create future incremental migrations for you in the directory.
As an aside - you can also create resilient migrations manually as well using IF NOT EXISTS (these aren't automatically created by Hasura, but you can edit their SQL file migrations).
For example:
ALTER TABLE users
ADD COLUMN IF NOT EXISTS age INT
Edit 2: One other tool which I came upon which may be helpful is Migra (for Postgres, outside of Hasura). It can help with diff-ing your dev and production databases to help create the initial migration state: https://github.com/djrobstep/migra