Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

How to move from Amazon RDS to a dedicated PostgreSQL server

layer0.authentise.com

Post reply on HN
Print view View original

41–50 of 73 posts

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#41

Post by lexandstuff »

craigkerstiens wrote: post #3

Overall a nice post. It doesn't quite get into all the options for pg_dump as there's a number of other flags that can be useful to reduce downtime. It's also probably worth taking a look at Amazon Migration Service as well (though I haven't personally used it or heard from others that have yet) - https://aws.amazon.com/dms/ . One tool they did miss for continuous archiving is WAL-E, which tends to be the one most us…

Unfortunately, DMS is quite limited for Postgres. We trialled it but found these two limitations deal breakers:

* "AWS DMS doesn't support change processing on Amazon RDS for PostgreSQL. You can only do a full load."

* "AWS DMS doesn't map some PostgreSQL data types, including the JSON data type. The JSON is converted to CLOB."

http://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source....

Hopefully it improves soon, since it's an otherwise awesome tool.

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#42

Post by voltagex_ »

mrmondo wrote: post #35
aftabh wrote: post #33

>To my surprise, PostgreSQL v9.5 on Ubuntu 14.04 does not default to unicode schema. PostgreSQL does (if unicode character set was specified when PostgreSQL database cluster is initialized first time); it's Ubuntu OS which creates PostgreSQL database cluster with ASCII character set (encoding) after PostgreSQL's installation. > Here is the snippet that I had to use: ... Instead of resorting to those hacks, follow the…

Ubuntu has lots of poor defaults and this is a good example of one.

Wouldn't this be worth raising a bug report for?

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#43

Post by markonen »

jbrantly wrote: post #5

Not that it necessarily should, but the article did not really address why they were moving away from RDS. Was it cost, performance, other issues? I've been trying to compare RDS for Postgres to other offerings like Compose or Heroku but have come up surprisingly dry on comparisons.

In my personal comparison, a huge factor for RDS and against Heroku is something that I have never seen mentioned in these comparisons: you can run multiple databases on a single RDS instance and you can't do that with Heroku.

My company has a bunch of microservices that each need a Postgres database, but none of them are particularly high traffic. We do however need high availability guarantees for all of them, and so Heroku's pricing starts at $200/mo per database.

With RDS, we get adequate performance for a similar total cost (around $300/mo), but get to run about a dozen separate high-availability databases on that same instance, each with their own usernames and passwords. That means significant savings.

(of course you could just share a single Heroku Postgres database and user account between these services, perhaps separating them by scheme, but that wasn't really to my taste.)

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#44

Post by elktea »

peteretep wrote: post #37
dboreham wrote: post #36

Earlier quoted context omitted.

Using the distro-supplied packages for PG (or any database server) is typically not the best choice for a production deployment.

> Using the distro-supplied packages for PG > (or any database server) Or almost any of your application environment, really.

Distro supplied packages are a handy way of receiving timely security fixes without keeping too close an eye on recent CVEs + application changelogs.

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#45

Post by yeukhon »

qaq wrote: post #38
Rezo wrote: post #31

Earlier quoted context omitted.

When it comes to data, trust is key. Think about it this way: OCZ has on paper SSDs that are simultaneously both faster and cheaper than, say, Intel. But I wouldn't put a OCZ drive into the lowest budged ricer gaming PC I could imagine (let's just say that have a bit of a reputation, to put it lightly), and I should be rightfully fired if I suggested putting one in a server. Yes, your service might be massively faste…

You do not have an option to host anything larger then 6TB on RDS. I guess what we are trying to figure out is if there is a need for service for people who have needs beyond those RDS is addressing.

Not only disk space, you are limited to just two nodes (one primary and one standby). Of course you can create read replica, but that if you are write intensive, you can end up consuming all of your pools.

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#46

Post by JimDabell »

whyleyc wrote: post #32
eliribble wrote: post #16

Earlier quoted context omitted.

I'm the decision maker for the company in question - Authentise. It was a combination of cost and the fact that we need to support an in-datacenter install option for our customers. RDS is really great if you need all of the failover and backup options it provides and you don't have the manpower to invest in it. That's how we got our services started. But, as our expertise and automation grew it ended up being a mode…

Thanks for the background info, very helpful. How has on-prem install gone for you guys ? There was an article on HN yesterday suggesting it was a royal PITA (for ops and support), would be interesting to hear about your experience. Also FYI, your homepage has a 2015 copyright notice on it. No big deal, but thought you might like to know since it could put off particularly "nit picky" types of customer :)

> Also FYI, your homepage has a 2015 copyright notice on it. No big deal, but thought you might like to know since it could put off particularly "nit picky" types of customer :)

If that's when the work was created, then that's correct. Copyright notices aren't there to tell you what year it is today. They are there to tell you when the work was created. If they change it to 2016 when the work was really created in 2015, then that's an invalid copyright notice and equivalent to no notice at all.

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#47

Post by Roritharr »

dboreham wrote: post #36
aftabh wrote: post #33

>To my surprise, PostgreSQL v9.5 on Ubuntu 14.04 does not default to unicode schema. PostgreSQL does (if unicode character set was specified when PostgreSQL database cluster is initialized first time); it's Ubuntu OS which creates PostgreSQL database cluster with ASCII character set (encoding) after PostgreSQL's installation. > Here is the snippet that I had to use: ... Instead of resorting to those hacks, follow the…

Using the distro-supplied packages for PG (or any database server) is typically not the best choice for a production deployment.

I would like to hear more about that. Our Server Team swears on using CentOS Packages with Backports for everything for security purposes. This slows us a bit down, but so far we haven't had any security or audit related issues. How do you deal with CVEs and these staying audited and secure?

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#48

Post by mrmondo »

voltagex_ wrote: post #42
mrmondo wrote: post #35

Earlier quoted context omitted.

Ubuntu has lots of poor defaults and this is a good example of one.

Wouldn't this be worth raising a bug report for?

Probably, uphill battle though IMO, it's not like Ubuntu has a good reputation of sane defaults or quality packing or timely security updates.

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#49

Post by mrmondo »

Roritharr wrote: post #47
dboreham wrote: post #36

Earlier quoted context omitted.

Using the distro-supplied packages for PG (or any database server) is typically not the best choice for a production deployment.

I would like to hear more about that. Our Server Team swears on using CentOS Packages with Backports for everything for security purposes. This slows us a bit down, but so far we haven't had any security or audit related issues. How do you deal with CVEs and these staying audited and secure?

We use CentOS but PostgreSQL's official packages from their repo, just like we did when we were Debian based. http://yum.postgresql.org/

Re: How to move from Amazon RDS to a dedicated PostgreSQL server

#50

Post by merb »

yeukhon wrote: post #45
qaq wrote: post #38

Earlier quoted context omitted.

You do not have an option to host anything larger then 6TB on RDS. I guess what we are trying to figure out is if there is a need for service for people who have needs beyond those RDS is addressing.

Not only disk space, you are limited to just two nodes (one primary and one standby). Of course you can create read replica, but that if you are write intensive, you can end up consuming all of your pools.

actually you would shard than, which of course is possible with rds aswell. since you mostly shard at the application level. there are some users that use extensions but I guess calling the support of aws will get these extension in your rds instances.
Post reply on HN