Heroku to amazon rds migration

Setting Up the RDS Instance

1. Log into AWS
2. Switch your Location to US-East-1 (top right of screen)
3. Search through Services for RDS and create a database (Make sure you’re still at the right location)
   1. Make sure the database version you are creating matches (POWR Prod is on v11.9)
   2. DB Instance Identifier is the name you want to use to identify the RDS instance on AWS (This is NOT the database name)
   3. Create username and password, and make sure to save that information somewhere you have access to
   4. DB Instance Size:
   5. Storage:
   6. VPC: This decides who/what will be able to access the RDS instance (default for now)
   7. In “Additional Connectivity Configuration”, make sure you enable Public Access
   8. For VPC Security Group, select default for now, we will come back to this laster
   9. DB port can be 5432
   10. DB Name Set in Additional Configurations, make sure the rest of the configurations are as you want, nothing too important to note

Setting Up the Replication Instance

1. Log into AWS
2. Switch your Location to US-East-1 (top right of screen)
3. Search through Services and Search for Database Migration Services
4. On the left sidebar, Go to “Replication Instances” and create a new instance
5. As this instance is going to handle all of the data migration, you’ll want a fairly large Instance Class, otherwise you’ll run into memory issues when migrating data
6. Make sure Publicly Accessible is marked so you can connect to Heroku Database
7. Make sure rest of configurations are appropriate (double-check your Storage Size), nothing else important to note

Setting Up Endpoints

1. Log into AWS
2. Switch your Location to US-East-1 (top right of screen)
3. Search through Services and Search for Database Migration Services
4. On the left sidebar, Go to “Endpoints” and we’re going to create a source and a target endpoint
5. Source: You’ll need to refer to Heroku Credentials, but here’s the mapping
   1. DMS <==> Heroku
   2. Server Name <==> Host
   3. Port <==> Port
   4. SSL ==> Require
   5. User Name <==> User
   6. Password <==> Password
   7. Database Name <==> Database
6. Target: You’ll need to refer to the RDS credentials you created, but it should be straightforward
   1. Make sure “Select RDS DB Instance” is selected
   2. Most of the info should be auto-populated; fill out username and password
   3. If you didn’t create a database name in the RDS instance, your Database Name is postgres
   4. NOTE: the endpoint identifier can’t have the same name your RDS database identifier, otherwise you’ll run into issues
7. Make sure to Test each endpoint before creation.

Setting Up Database Migration Task

1. On the left sidebar, Go to “Database Migration Tasks”
2. Migration Type:
   1. Migrate Existing Data: If you only need to dump the data over
   2. Migrate existing data and replicate ongoing changes: Dumps data over and syncs any new changes
   3. Replicate ongoing changes: Continues to replicate data changes from source to target (requires superuser permissions)
   4. NOTE: once this is set, you’re unable to change this for the task
3. In Table Mapping Section, you’ll need to add a new selection rule
   1. Heroku Schema is “public"
   2. If you leave “%” as the table name, it’ll get all tables
   3. Otherwise, if you fill out the table name, it’ll only migrate that table
   4. You can further add column filters if you need to break down a table migration into smaller chunks
4. Every other option should be straightforward

Setting up Database Access

1. Search through Services and Search for VPC
2. Find Security Groups and click on it
3. Create a new Security Group
4. The VPC you want to select should be the same VPC as you have on your RDS instance. Review your RDS Instance properties if you need to find it
5. Add new Inbound Rules
   1. Type will depend on your purpose, I’m going to go through how to set up permissions for an analyst to get permission to connect on their computer through psql
   2. Set Type to Postgres
   3. Set port range to the same port as your RDS Instance (should probably be 5432)
   4. Change source to “My IP” and it should auto populate with your current IP Address
      1. NOTE: Should be obvious, but don’t do this on a public network
      2. You can go ahead and add more IPs as needed
   5. Go ahead and add the rule and create the group when you’re done
6. Search through Services and Search for RDS
7. Select your RDS instance and “Modify” your properties
8. Go to the Connectivity Section and under “Security Group”, add your newly created group
9. Save changes (There should be an option that asks when you want to apply the changes. Select immedately if you want to be able to access the db now)

STEPS

1. Create VPC Security Group
2. Create RDS instance
3. Create schema copy from production
4. Apply Schema to RDS instance
5. Remove Foreign Key Constraints from following tables
   1. app_activations
   2. user_verification_callback_infos
   3. user_verifications
   4. sms_promos
   5. partnerstack_users

ALTER TABLE user_verifications DROP CONSTRAINT fk_rails_9822845834;
ALTER TABLE user_verification_callback_infos DROP CONSTRAINT fk_rails_4bd26dd782;
ALTER TABLE partnerstack_users DROP CONSTRAINT fk_rails_315745a260;
ALTER TABLE lead_responses DROP CONSTRAINT fk_rails_64e5c7a06e;
ALTER TABLE app_activations DROP CONSTRAINT fk_rails_381517648e;
ALTER TABLE snail_mails DROP CONSTRAINT fk_rails_8021a07ed1;
ALTER TABLE sms_promos DROP CONSTRAINT fk_rails_13ed37947a;
ALTER TABLE sms_promos DROP CONSTRAINT fk_rails_91cb86907d;
ALTER TABLE calendar_time_frames DROP CONSTRAINT fk_rails_c9596e1556;

1. Tables with JSON data need Full LOB — slows down entire migration, so might be needed as separate migration task
   1. app_form_responses
   2. app_infos
   3. versions
   4. pending_transactions
   5. admin_papertrails
   6. shopify_embeds
2. Create replication Instance
3. Create Endpoints
4. Create Migration Tasks and migrate data (ongoing replication)
5. Before the switch, need to update all sequences to max id (literally a minute before then a minute after)

SELECT setval('ab_criteria_id_seq', max(id)) FROM ab_criteria;
SELECT setval('ab_log_infos_id_seq', max(id)) FROM ab_log_infos;
SELECT setval('ab_logs_id_seq', max(id)) FROM ab_logs;
SELECT setval('add_on_subscriptions_id_seq', max(id)) FROM add_on_subscriptions;
SELECT setval('admin_notes_id_seq', max(id)) FROM admin_notes;
SELECT setval('admin_papertrails_id_seq', max(id)) FROM admin_papertrails;
SELECT setval('affiliate_infos_id_seq', max(id)) FROM affiliate_infos;
SELECT setval('affiliate_payouts_id_seq', max(id)) FROM affiliate_payouts;
SELECT setval('airtable_call_logs_id_seq', max(id)) FROM airtable_call_logs;
SELECT setval('airtable_call_record_syncs_id_seq', max(id)) FROM airtable_call_record_syncs;
SELECT setval('alternative_to_competitors_id_seq', max(id)) FROM alternative_to_competitors;
SELECT setval('americommerce_installs_id_seq', max(id)) FROM americommerce_installs;
SELECT setval('analytics_id_seq', max(id)) FROM analytics;
SELECT setval('app_accesses_id_seq', max(id)) FROM app_accesses;
SELECT setval('app_details_id_seq', max(id)) FROM app_details;
SELECT setval('app_feature_simple_copies_id_seq', max(id)) FROM app_feature_simple_copies;
SELECT setval('app_form_response_external_data_id_seq', max(id)) FROM app_form_response_external_data;
SELECT setval('app_form_responses_id_seq', max(id)) FROM app_form_responses;
SELECT setval('app_images_id_seq', max(id)) FROM app_images;
SELECT setval('app_slug_simple_copies_id_seq', max(id)) FROM app_slug_simple_copies;
SELECT setval('app_subscriptions_id_seq', max(id)) FROM app_subscriptions;
SELECT setval('app_template_categories_id_seq', max(id)) FROM app_template_categories;
SELECT setval('app_templates_id_seq', max(id)) FROM app_templates;
SELECT setval('apps_id_seq', max(id)) FROM apps;
SELECT setval('auto_videos_id_seq', max(id)) FROM auto_videos;
SELECT setval('beyond_shop_installs_id_seq', max(id)) FROM beyond_shop_installs;
SELECT setval('bigcommerce_embeds_id_seq', max(id)) FROM bigcommerce_embeds;
SELECT setval('bigcommerce_installs_id_seq', max(id)) FROM bigcommerce_installs;
SELECT setval('bigcommerce_widgets_id_seq', max(id)) FROM bigcommerce_widgets;
SELECT setval('blocklisteds_id_seq', max(id)) FROM blocklisteds;
SELECT setval('blog_users_id_seq', max(id)) FROM blog_users;
SELECT setval('blogs_id_seq', max(id)) FROM blogs;
SELECT setval('braintree_disputes_id_seq', max(id)) FROM braintree_disputes;
SELECT setval('business_booster_data_id_seq', max(id)) FROM business_booster_data;
SELECT setval('calendar_services_id_seq', max(id)) FROM calendar_services;
SELECT setval('calendar_time_frames_id_seq', max(id)) FROM calendar_time_frames;
SELECT setval('cancel_feedbacks_id_seq', max(id)) FROM cancel_feedbacks;
SELECT setval('canva_installs_id_seq', max(id)) FROM canva_installs;
SELECT setval('chat_controls_id_seq', max(id)) FROM chat_controls;
SELECT setval('chat_histories_id_seq', max(id)) FROM chat_histories;
SELECT setval('client_records_id_seq', max(id)) FROM client_records;
SELECT setval('comments_users_id_seq', max(id)) FROM comments_users;
SELECT setval('credit_cards_id_seq', max(id)) FROM credit_cards;
SELECT setval('credit_discount_links_id_seq', max(id)) FROM credit_discount_links;
SELECT setval('credit_pro_subscription_transactions_id_seq', max(id)) FROM credit_pro_subscription_transactions;
SELECT setval('daily_analytics_id_seq', max(id)) FROM daily_analytics;
SELECT setval('db_pool_data_id_seq', max(id)) FROM db_pool_data;
SELECT setval('demo_accesses_powr_token_id_seq', max(powr_token_id)) FROM demo_accesses;
SELECT setval('demos_id_seq', max(id)) FROM demos;
SELECT setval('digital_downloads_id_seq', max(id)) FROM digital_downloads;
SELECT setval('ecommerce_payments_id_seq', max(id)) FROM ecommerce_payments;
SELECT setval('ecwid_installs_id_seq', max(id)) FROM ecwid_installs;
SELECT setval('email_campaign_versions_id_seq', max(id)) FROM email_campaign_versions;
SELECT setval('email_platform_verifications_id_seq', max(id)) FROM email_platform_verifications;
SELECT setval('emails_id_seq', max(id)) FROM emails;
SELECT setval('epages_installs_id_seq', max(id)) FROM epages_installs;
SELECT setval('facebook_pages_id_seq', max(id)) FROM facebook_pages;
SELECT setval('feature_request_votes_id_seq', max(id)) FROM feature_request_votes;
SELECT setval('feature_requests_id_seq', max(id)) FROM feature_requests;
SELECT setval('feedbacks_id_seq', max(id)) FROM feedbacks;
SELECT setval('form_uploads_id_seq', max(id)) FROM form_uploads;
SELECT setval('general_copies_id_seq', max(id)) FROM general_copies;
SELECT setval('google_sheet_integrations_id_seq', max(id)) FROM google_sheet_integrations;
SELECT setval('hubspot_installs_id_seq', max(id)) FROM hubspot_installs;
SELECT setval('hubspot_next_charge_data_id_seq', max(id)) FROM hubspot_next_charge_data;
SELECT setval('hubspot_syncs_id_seq', max(id)) FROM hubspot_syncs;
SELECT setval('jumpseller_installs_id_seq', max(id)) FROM jumpseller_installs;
SELECT setval('last_viewed_url_filters_id_seq', max(id)) FROM last_viewed_url_filters;
SELECT setval('lead_responses_id_seq', max(id)) FROM lead_responses;
SELECT setval('leads_id_seq', max(id)) FROM leads;
SELECT setval('legacy_files_id_seq', max(id)) FROM legacy_files;
SELECT setval('numbers_data_id_seq', max(id)) FROM numbers_data;
SELECT setval('oauth_access_grants_id_seq', max(id)) FROM oauth_access_grants;
SELECT setval('oauth_access_tokens_id_seq', max(id)) FROM oauth_access_tokens;
SELECT setval('oauth_applications_id_seq', max(id)) FROM oauth_applications;
SELECT setval('oauth_providers_id_seq', max(id)) FROM oauth_providers;
SELECT setval('partner_payment_posts_id_seq', max(id)) FROM partner_payment_posts;
SELECT setval('partner_users_id_seq', max(id)) FROM partner_users;
SELECT setval('partnerstack_partners_id_seq', max(id)) FROM partnerstack_partners;
SELECT setval('partnerstack_users_id_seq', max(id)) FROM partnerstack_users;
SELECT setval('payment_notifications_id_seq', max(id)) FROM payment_notifications;
SELECT setval('pending_events_id_seq', max(id)) FROM pending_events;
SELECT setval('pending_transactions_id_seq', max(id)) FROM pending_transactions;
SELECT setval('phishing_blacklists_id_seq', max(id)) FROM phishing_blacklists;
SELECT setval('powr_ab_completed_id_seq', max(id)) FROM powr_ab_completed;
SELECT setval('powr_ratings_id_seq', max(id)) FROM powr_ratings;
SELECT setval('powr_tokens_id_seq', max(id)) FROM powr_tokens;
SELECT setval('powr_tokens_users_id_seq', max(id)) FROM powr_tokens_users;
SELECT setval('powrmail_url_statuses_id_seq', max(id)) FROM powrmail_url_statuses;
SELECT setval('powrmail_urls_id_seq', max(id)) FROM powrmail_urls;
SELECT setval('powrzilla_emails_id_seq', max(id)) FROM powrzilla_emails;
SELECT setval('powrzilla_unsubscriptions_id_seq', max(id)) FROM powrzilla_unsubscriptions;
SELECT setval('pricing_analytics_errors_id_seq', max(id)) FROM pricing_analytics_errors;
SELECT setval('pricing_analytics_id_seq', max(id)) FROM pricing_analytics;
SELECT setval('pro_subscriptions_id_seq', max(id)) FROM pro_subscriptions;
SELECT setval('pro_subscriptions_user_notifications_id_seq', max(id)) FROM pro_subscriptions_user_notifications;
SELECT setval('product_update_likes_id_seq', max(id)) FROM product_update_likes;
SELECT setval('product_updates_id_seq', max(id)) FROM product_updates;
SELECT setval('project_updates_id_seq', max(id)) FROM project_updates;
SELECT setval('promo_codes_id_seq', max(id)) FROM promo_codes;
SELECT setval('raw_app_form_responses_id_seq', max(id)) FROM raw_app_form_responses;
SELECT setval('raw_shopify_data_id_seq', max(id)) FROM raw_shopify_data;
SELECT setval('raw_wix_data_id_seq', max(id)) FROM raw_wix_data;
SELECT setval('recipient_lists_id_seq', max(id)) FROM recipient_lists;
SELECT setval('refund_reasons_id_seq', max(id)) FROM refund_reasons;
SELECT setval('response_counters_id_seq', max(id)) FROM response_counters;
SELECT setval('review_clicks_id_seq', max(id)) FROM review_clicks;
SELECT setval('review_records_id_seq', max(id)) FROM review_records;
SELECT setval('roles_id_seq', max(id)) FROM roles;
SELECT setval('search_analytics_id_seq', max(id)) FROM search_analytics;
SELECT setval('shopify_app_stores_id_seq', max(id)) FROM shopify_app_stores;
SELECT setval('shopify_billing_issues_id_seq', max(id)) FROM shopify_billing_issues;
SELECT setval('shopify_embeds_id_seq', max(id)) FROM shopify_embeds;
SELECT setval('shopify_events_id_seq', max(id)) FROM shopify_events;
SELECT setval('shopify_installs_id_seq', max(id)) FROM shopify_installs;
SELECT setval('shopify_partners_data_id_seq', max(id)) FROM shopify_partners_data;
SELECT setval('shopify_shops_id_seq', max(id)) FROM shopify_shops;
SELECT setval('simple_copies_id_seq', max(id)) FROM simple_copies;
SELECT setval('sms_messages_id_seq', max(id)) FROM sms_messages;
SELECT setval('sms_promos_id_seq', max(id)) FROM sms_promos;
SELECT setval('snail_mails_id_seq', max(id)) FROM snail_mails;
SELECT setval('subscription_discounts_id_seq', max(id)) FROM subscription_discounts;
SELECT setval('support_calls_id_seq', max(id)) FROM support_calls;
SELECT setval('surge_protector_tags_id_seq', max(id)) FROM surge_protector_tags;
SELECT setval('surge_protector_votes_id_seq', max(id)) FROM surge_protector_votes;
SELECT setval('surge_protectors_id_seq', max(id)) FROM surge_protectors;
SELECT setval('synonyms_id_seq', max(id)) FROM synonyms;
SELECT setval('temp_user_surveys_submitted_user_id_seq', max(submitted_user_id)) FROM temp_user_surveys;
SELECT setval('tickets_id_seq', max(id)) FROM tickets;
SELECT setval('tiny_urls_id_seq', max(id)) FROM tiny_urls;
SELECT setval('todo_tutorials_id_seq', max(id)) FROM todo_tutorials;
SELECT setval('topic_vertical_contents_id_seq', max(id)) FROM topic_vertical_contents;
SELECT setval('topics_id_seq', max(id)) FROM topics;
SELECT setval('transactions_id_seq', max(id)) FROM transactions;
SELECT setval('tutorial_configs_id_seq', max(id)) FROM tutorial_configs;
SELECT setval('unsubscribe_events_id_seq', max(id)) FROM unsubscribe_events;
SELECT setval('upload_responses_id_seq', max(id)) FROM upload_responses;
SELECT setval('user_banners_id_seq', max(id)) FROM user_banners;
SELECT setval('user_credits_id_seq', max(id)) FROM user_credits;
SELECT setval('user_discount_records_id_seq', max(id)) FROM user_discount_records;
SELECT setval('user_journey_steps_id_seq', max(id)) FROM user_journey_steps;
SELECT setval('user_mail_histories_id_seq', max(id)) FROM user_mail_histories;
SELECT setval('user_metrics_id_seq', max(id)) FROM user_metrics;
SELECT setval('user_notifications_id_seq', max(id)) FROM user_notifications;
SELECT setval('user_promo_codes_id_seq', max(id)) FROM user_promo_codes;
SELECT setval('user_sms_messages_id_seq', max(id)) FROM user_sms_messages;
SELECT setval('user_sparkpost_data_id_seq', max(id)) FROM user_sparkpost_data;
SELECT setval('user_verification_callback_infos_id_seq', max(id)) FROM user_verification_callback_infos;
SELECT setval('user_verifications_id_seq', max(id)) FROM user_verifications;
SELECT setval('users_id_seq', max(id)) FROM users;
SELECT setval('vcita_installs_id_seq', max(id)) FROM vcita_installs;
SELECT setval('vendasta_installs_id_seq', max(id)) FROM vendasta_installs;
SELECT setval('versions_id_seq', max(id)) FROM versions;
SELECT setval('verticals_id_seq', max(id)) FROM verticals;
SELECT setval('video_testimonial_clicks_id_seq', max(id)) FROM video_testimonial_clicks;
SELECT setval('video_tutorials_id_seq', max(id)) FROM video_tutorials;
SELECT setval('views_counts_id_seq', max(id)) FROM views_counts;
SELECT setval('webhooks_id_seq', max(id)) FROM webhooks;
SELECT setval('weebly_installs_id_seq', max(id)) FROM weebly_installs;
SELECT setval('whitelisteds_id_seq', max(id)) FROM whitelisteds;
SELECT setval('wix_app_store_infos_id_seq', max(id)) FROM wix_app_store_infos;
SELECT setval('wix_app_store_positions_id_seq', max(id)) FROM wix_app_store_positions;
SELECT setval('wix_app_stores_id_seq', max(id)) FROM wix_app_stores;
SELECT setval('wix_categories_id_seq', max(id)) FROM wix_categories;
SELECT setval('wix_installs_id_seq', max(id)) FROM wix_installs;
SELECT setval('wix_webhooks_id_seq', max(id)) FROM wix_webhooks;
SELECT setval('woo_commerce_app_store_positions_id_seq', max(id)) FROM woo_commerce_app_store_positions;
SELECT setval('woo_commerce_app_store_prices_id_seq', max(id)) FROM woo_commerce_app_store_prices;
SELECT setval('woo_commerce_app_stores_id_seq', max(id)) FROM woo_commerce_app_stores;
SELECT setval('woo_commerce_embeds_id_seq', max(id)) FROM woo_commerce_embeds;
SELECT setval('woo_commerce_installs_id_seq', max(id)) FROM woo_commerce_installs;
SELECT setval('zoom_installs_id_seq', max(id)) FROM zoom_installs;

Alternative View with slightly better formatting: https://www.evernote.com/shard/s645/client/snv?noteGuid=edf9bd7d-9bb6-4ab5-b199-289106a2e7a3¬eKey=15d6b0eaffbe0e7c&sn=https%3A%2F%2Fwww.evernote.com%2Fshard%2Fs645%2Fsh%2Fedf9bd7d-9bb6-4ab5-b199-289106a2e7a3%2F15d6b0eaffbe0e7c&title=Heroku%2Bto%2BAmazon%2BRDS%2BMigration