Migrating a PostgreSQL Database From Google Cloud to DigitalOcean | by Meriç Melike Yılmaz | Mar, 2022

At walbit, we began off our utility in Google Cloud the place we arrange our structure with Google Cloud Run, Google Storage, Google Compute Engine, and for our database with Cloud SQL, particularly with a managed PostgreSQL 13 occasion. We weren’t very pleased with Google’s providers and determined to change to DigitalOcean, and this information will undergo step-by-step how we moved our manufacturing database to DO. Be aware that the downtime for this course of was about 10 minutes, and we shut down our service through the migration to forestall knowledge loss.

On the time of this writing, our engineering workforce exists of three full-stack builders, with little information of DevOps. So we depend on documentation like these (or these of the cloud platforms) to get our apps up and working. If in case you have extra expertise in DevOps or have the assets to rent somebody who does, you most likely don’t want this text.

Let’s start with a few of the the reason why we wished to change from GCP to DO:

Minimal setup is simply too pricey on GCP

Originally of our journey, we didn’t have lots of customers, nor a giant finances. We additionally didn’t need to create a small compute engine and handle the database ourselves as managed databases have lots of benefits when it comes to upkeep, scalability, and uptime. So we used the smallest setup in GCP that made sense, and we’d scale up as our userbase and visitors grew. And never solely that, we wanted a second database for our sandbox surroundings and even the minimal setup was too pricey for us. For reference, originally of 2022, it price about 60$ a month for the setup beneath whereas we had been doing our closed beta which had little visitors.

GCP Billing Construction Did Not Work For Us

With Google Cloud Platform, except you’re a comparatively huge buyer (having paid 2500$ to GCP month-to-month for the final 3 months) it’s important to give in to a fee setting referred to as ‘computerized funds’. Google payments you and prices your card originally of every billing cycle (month), but in addition when your steadiness hits a sure ‘threshold’. This threshold was for us about 200$ apparently, and we had been not likely checking the billing standing/display screen of GCP often. It occurred a number of instances that we hit the edge and Google charged our bank card. Right here comes the worst half: Google blocks entry to your providers as quickly as your fee is declined. On the web page the place potential customers would usually see your touchdown web page, you see an unsightly error message that there was an issue with the billing account related to this service. We had been utilizing a digital card with our GCP account and had been solely opening up the steadiness after we had been going to make a fee to have extra management over our balances, however apparently, that didn’t work for Google. In fact, after these incidents, we instantly opened up the steadiness of the associated card and retried the funds, and the providers had been up and working once more, however these minutes had been sufficient for us to lose face in entrance of our clients and drive us loopy.

We talked to GCP’s Billing Help about this and so they may solely counsel us to ‘regulate our utilization’, and we didn’t need to work with that, so right here we’re 🤷‍♀️ (We labored with DigitalOcean earlier than and so they gracefully warn you for a few weeks after your fee is declined earlier than your providers are shut down)

DigitalOcean Docs Are Extra Newbie-Pleasant

As said above, our workforce contains 3 full-stack devs and no DevOps wizards 🧙‍♀️ Thus we want some steerage when navigating round cloud suppliers and establishing our structure. In addition to Google is attempting to construct a neighborhood and create content material for GCP, DigitalOcean’s personal docs and weblog posts have all the time been very informative and straightforward to make use of. Possibly for this level it’s additionally useful that DO doesn’t have so many various providers as GCP, so it’s simpler to work with what we get.

Now we’ll undergo step-by-step how we migrated our up-and-running database in Google Cloud Platform to DigitalOcean inside 10 minutes and saved utilizing our providers as earlier than. Earlier than you go on, you’ll need the next:

• An up and working managed PostgreSQL database on GCP
• A DigitalOcean account and a workforce account to create our situations in

We are going to observe these steps for our migration course of:

1. Create new Database in DO
2. Cease our providers in GCP
3. Take a dump of our DB in GCP
4. Restore the dump into our new DB in DO
5. Change connection parameters in our providers to make use of new DB
6. Restart our providers

Earlier than we go forward, it is sensible so that you can decide the place it’s important to change your connection parameters (API settings, GCP’s Secret Supervisor, .env information, and so on) since step 5 can be affecting the downtime of the service. In our case, we’ll exchange a connection string in Secret Supervisor and in a couple of .env information in some compute engines which were working some background duties.

1. Create PostgreSQL Database Occasion in DigitalOcean

You possibly can observe DigitalOcean’s guide for creating a PSQL Database cluster here. However to sum up, it’s merely:

• Select ‘Databases’ throughout the ‘Create’ dropdown menu from high proper
• Select PostgreSQL as engine and its model, and choose specs you want to on your DB. The plans begin from 15$/month relying on the specification and you may all the time enhance the dimensions later, select fastidiously!

We are going to go along with PSQL 13 as it’s the model we use in GCP, and with a 15$ cluster to start with, and can later improve it to our wants. Be aware that you need to take note of the disk dimension and ensure the information you’ll migrate will match into the brand new cluster.

Transfer via the ‘Getting Began’ steps that DigitalOcean brings up relying in your desired configurations. For Prohibit inbound connections step, we won’t add any assets in the interim as a result of our backend API continues to be working in GCP Cloud Run and we want it to entry to the DB in DO. Once we later transfer all our structure to DO, we will replace this setting to permit solely the events that truly want entry to our DB to make it secure.

On the finish, DO will give you a connection string, or with the direct command to Migrate an present database. DigitalOcean will routinely create a default database (defaultdb) and default admin person (doadmin) for our system and the offered connection settings use these DB and person data. We want to replace these although, and we will accomplish that within the Customers & Databases tab beneath the managed database web page in DO. After that once you return to the overview tab, you will get up to date connection strings or configurations on your new person or DB title.

2. Cease the Providers

At this step, we’ll quickly disable our service in an effort to keep away from any knowledge loss, as a result of till we take the dump and import it into the brand new database, we might quite not have any knowledge written into the database in an effort to keep away from dropping that knowledge. So we select to make the app quickly unavailable as an alternative.

In our case, we’ll disable our Cloud Run instance indirectly, and cease our celery staff at the moment working in compute engine situations. We’ll allow them again as quickly as the brand new DB has all the information and the connection strings are up to date. So beginning at this level we’re in a rush to complete the steps as shortly as attainable to reduce the downtime in our utility 🙌

3. Export GCP Database

For this, it’s essential have Cloud Storage up and working and we have already got a number of buckets for our walbit web site and statics. We are going to use one among our present buckets to export the SQL file, however you can even create a brand new bucket for this following this tutorial. In a few minutes, Google will create an export file on your database within the storage bucket of your alternative. Obtain it in your pc so you’ll be able to run the command to revive the DB within the subsequent step.

Alternatively, you’ll be able to connect with your database utilizing CloudSQL Auth Proxy and run a pg_dump command to create a dump of your database. We selected to make use of Export knowledge to Cloud Storage possibility already offered by Google as it’s simpler.

4. Restore the dump into our new DB in DO

Within the earlier step you need to have downloaded an SQL file as a dump of your DB, and now we’ll use it to revive the DB configurations and the information into our newly created database in DigitalOcean. Change the parameters with your individual within the following command and run it to attain this:

PGPASSWORD=[psql-password] psql -U [psql-username] -h [psql-host] -p [psql-port] -d [database] --set=sslmode=require -f [path-to-sql-dump]

The variables password, username, host, port and database can be offered for you in DigitalOcean interface. Test the database ‘s Overview tab and you need to see it on high proper beneath Connection Particulars. Make sure that Public Community possibility is chosen and that you’re checking the Connection parameters.

When the above command finishes working, your new database is an actual reproduction of your earlier database (possibly aside from the dimensions) and is prepared for use.

5. Change connection parameters in our providers to make use of new DB

Now we’ll change the database utilized in our functions utilizing the brand new connection string from DO. In DB’s overview tab beneath connection particulars, we will get and duplicate the entire connection string. Our (Django) apps work with the connection string in order that’s ok for us, however make sure that to verify if it’s important to regulate every other settings or variables in your utility code.

In our case, to perform this step we up to date the DATABASE_URL config in our .env information (for particular person staff for some background duties) and GCP’s Secret Supervisor (for our API performing at Cloud Run) with the brand new connection string and all was good!

6. Restart our providers

Now that the brand new DB is up and working and we up to date the connection parameters to level to our new DB, we will merely restart our providers and make our app useful once more.

For our case, this entire operation and the downtime took about 10 minutes, but it surely may have been shorter as we needed to mess around with some flags and variables in between. Our database was a comparatively small one although, so the dumping and restoring operations took a few minutes every. In databases stuffed with extra knowledge that is prone to take an extended time.

Bonus: Optimize & Safe Your Database

On this tutorial we did the fundamentals and moved the prevailing database from one cloud supplier to the opposite, however there could be extra to do to have a well-functioning database relying on the wants of your utility. Make sure that to verify the next factors and docs so that you benefit from your managed DO database.