0

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?

Amantel
  • 659
  • 1
  • 8
  • 18

2 Answers2

2

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

MartyMcfly
  • 60
  • 5
  • As far as I understand if I ran --skip-execution on single migration that I have (because I don't have any previous) it will skip a lot of stuff that I need to transfer - correct? Example: In DEV I have a new table. I do a _fresh_ initial migration and as per your suggestion I do `skip` with `apply` That table will not be transfered to PROD, right? – Amantel Mar 18 '22 at 16:26
  • 1
    Yes - after re-reading your question - after running the `--init` command Hasura will pretty much create a slightly modified `pg_dump` of your database as one snapshot SQL file. If you're making alteration between prod and staging using the initial dump, I would recommend copying the change to a new SQL migration manually and applying the skip to the initial migration. Any changes you make afterwards, you can use the CLI Console to create incremental migrations automatically. – MartyMcfly Mar 18 '22 at 16:30
  • Thanks, Marty! That's what I thought too. I will be pain to do copy stuff manually, but well - I reap what I sow. Anyway, please update you answer with your comment and I'll mark it accepted. – Amantel Mar 18 '22 at 16:46
  • One other tool I'll update the original post with as well is Migra (for Postgres, outside of Hasura). It can help with diff-ing the dev and production databases to help create the initial migration state: https://github.com/djrobstep/migra – MartyMcfly Apr 03 '22 at 21:24
1

It's a bit buried, but the section on migrations covers this scenario (you haven't been tracking/creating migrations and now need to initialize them for the first time):

https://hasura.io/docs/latest/graphql/core/migrations/migrations-setup.html#step-3-initialize-the-migrations-and-metadata-as-per-your-current-state

Hope this helps =)

Gavin Ray
  • 595
  • 1
  • 3
  • 10
  • Hi, Gavin! Thanks for the link, but the problem is that guide you mentioned works only if the server we are migrating to is 'clean'. Hasura's initial schema download (step 1) will create up.sql file where all the tables will be created as `CREATE TABLE` instead of `CREATE TABLE IF NOT EXISTS` – Amantel Mar 23 '22 at 17:51
  • You mark the migrations as already applied on the server (per the note at the bottom of the step) with `--skip-execution` This makes no changes to the server the migrations are being applied on, just sets the Hasura migration status to "these migrations/tables have already been created + run" – Gavin Ray Mar 23 '22 at 18:51
  • I think (based on other articles on hasura website) that by 'this server' they meant 'giver' server, not 'reciever' one. – Amantel Mar 24 '22 at 07:38
  • But if not, let's try with an example: table `users`. On DEV we have 3 fields - id, name, age. On PROD we have only id and name. I do first steps as per guide. If I do not add `--skip-execution` I end up with an error telling me that table already exists. If I do add `--skip-execution` I essentially tell hasura that servers are equal and it should not execute anything. E.g. hasura starts thinking that both DEV and PROD are equal, but in reality they are not. In that case following migrations _will work_ but I will loose 'age' field. – Amantel Mar 24 '22 at 07:38