This is specific to rails but this will get you 99% of the way
Before we start, I’m sure there’s an easier way but I’m not really sure how, so if someone can please comment on a MUCH better and cleaner way then please do. I am a complete noob but I was able to get this far…
Also, this is only for databases that are freshly made that you’re migrating to. We will be dropping the fresh database!!!
First make the database you want to migrate to, in my case I had a render.com database that I connect my fly apps to
Get the external url example:
postgres://user_name:password@location/target_db
Get the url of the database that you want to pull from
postgres://user_name:password@location/source_db
From there I use the
simply fly launch and set the secrets of SOURCE_DATABASE_URI and TARGET_DATABASE_URI
fly launch
Don’t create DB
fly secrets set SOURCE_DATABASE_URI=postgres://user_name:password@location/source_db
fly secrets set TARGET_DATABASE_URI=postgres://user_name:password@location/target_db
fly deploy
Cool now we’ll move the database over by running from the terminal
fly ssh console -C "migrate"
Cool now try assigning the new DATABASE_URL to your app
fly secrets set DATABASE_URL=postgres://user_name:password@location/target_db
You’ll probably hit some errors. Mine for example was it wasn’t detecting the database despite it being made and migrated. So, this is what the next part for:
Let that run and then psql into the TARGET DATABASE
psql postgres://user_name:password@location/target_db
So this is where things get a little weird which is why I’m writing this guide. This doesn’t create the database. This migrator may not copy over the data the database you want, but you now have the data that you need on the actual postgres instance. So to move the data over to the right database, we’ll drop the made database and copy it from the migrated data
First we need to take a look at the names and owners of the databases, do this with
\l+
Alright you should see your migrated database and the newly created database. If you only see one and you didn’t hit any errors then check and see if it’s running.
You’ll see something like this
What we’re going to do is drop the new database and create it again from the template. In order for us to drop it, we need to be out of it so if you see your database name at the command line entry ie: database_dropping_name=> then connect to the database you’re migrating from with
\c database_name_you_want_to_copy_from
For me my target is climate_coolers_db_fra and my source is climate_coolers_db and I need to set the owner climate_coolers_db_fra_user
If these things would be variables here’s what mine would be (but don’t use this plz, this isn’t a copy-paste part)
database_name_you_want_to_copy_to = climate_coolers_db_fra
database_name_you_want_to_copy_from = climate_coolers_db
owner_of_deleted_database = climate_coolers_db_fra_user
Please note what we’re doing below is dropping a database and cloning it from the migrated db, this is for fresh databases only!
SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity
WHERE pg_stat_activity.datname = 'database_name_you_want_to_copy_to' AND pid <> pg_backend_pid();
DROP DATABASE database_name_you_want_to_copy_to;
CREATE DATABASE database_name_you_want_to_copy_to WITH TEMPLATE database_name_you_want_to_copy_from OWNER owner_of_deleted_database;
Here’s what all of the operations look like:
From there the database should be migrated successfully. Test it out!
\c your_new_database
SELECT * FROM table_name LIMIT 50;
Once everything is good to go, drop the old database cause you don’t need it anymore
DROP DATABASE your_old_database;
If you’re on rails then you can set the DATABASE_URL to the new thing
fly secrets set DATABASE_URL=postgres://user_name:password@location/your_new_database
Alright, that’s my write-up. It’s dirty as hell so if someone would like to provide some better docs on this please please do because this was a nightmare to get set up.
Cheers
