Hi all
I setup Google Cloud SQL as a long term - low cost recorder for my Home-assistant data. This is useful if you want to do data analysis on long term/large volumes of data and don’t want the hassle of maintaining your own database server. I’ve also found that database queries return data faster from the cloud database than from my own local database sever (MySQL in Docker on a Synology). A writeup and example analysis code is in the repo below.
Cheers
It seems a great solution for data analysis, but I wouldn’t call it “low cost”. If I did the math right, you have to spend at minimum 15$/month for a 24/7 running db. For the price of the first year you can almost buy a dedicated Synology with 1TB storage.
EDIT: I forgot a question: any reason to chose PostgreSQL instead of MySQL?
The pricing is confusing, but from the table below looks reasonable. Frankly my plan is to see what the cost is after 1 month then report that, and decide if the cost is justified. Also when you first sign up to the cloud services you get something like $300 credit.
RE PostgreSQL instead of MySQL, I’ve used both but had a nice tutorial for PostgreSQL. Also there are some extensions for PostgreSQL.
This is only the Storage roicing. You have to add Instance pricing and Network pricing on top of that.
The cheapest db-f1-micro is 9$/month.
In the Google Cloud Console, billing options, you can set a monthly budget, check the current expense and have an alert if the budget is reached.
There is a free micro tier for the instance, although the Google Cloud console is so confusing
here is how, nice guide and very good blog
OK so when setting up your instance try this configuration:
Will report the ‘cost’ after a month, hopefully free
how do you set an external ip if mine is changing daily?
AUTHORISATION : It is important that you add the IP address which you will connect from, i.e. your home IP. Just google ‘whats my ip’ and add this address here.
Mine isnt changing. I dont pay for that but apparently its a feature of my ISP! Is there a setting to allow all IP?
apparently not, only ip
I don’t have enough experience with google cloud
Thanks for the GitHub page, enabled the Google Cloud DB yesterday. Any update on your monthly cost calculation?
@robmarkcole, Thanks for the update. Hmm. It’s not THAT expensive but I think it’s on the limit for just hosting a small hass DB.
I maybe will install my own SQL server on the “always-free” micro VM they are offering instead: https://cloud.google.com/free/docs/frequently-asked-questions?hl=en_US&_ga=2.105260995.-1948479717.1523394278#always-free
I’ve actually stopped using the cloud SQL now as I want to keep my free credit for other projects. Will you post a short write-up of your VM approach?
It’s 90€/year, or 110$ in USA money. It is expensive!!! With 160$ you can get a NAS + 1TB HDD.
Don’t forget to add the costs of electricity to the NAS setup
Yet I do prefer to run my DB locally in my QNAP