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

61–70 of 73 posts

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

#61

Post by dboreham »

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 the official PG packages from their repo on CentOS. It appears there is an apt repository for Ubuntu too: https://www.postgresql.org/download/linux/ubuntu/

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

#62

Post by erkkie »

qaq wrote: post #39
jbrantly wrote: post #30

Earlier quoted context omitted.

Exactly. I want my app server and my db server to be as close as possible. That's why services like Compose and ElephantSQL offer just that. So then it comes down to performance/price (value) between RDS and those.

Latency will be higher. We are considering if we do go forward: a) Partnerships with smaller providers where there is an option to colocate equipment b) Colocating as close to as possible to targeted providers. The main thing we are trying to figure out - if there is a need for service that has significantly higher tears then what Azure, AWS offers.

I would imagine people who need either more AWS can supply (or cheaper) but cannot pay for it, and also haven't figured out how to run it themselves, to be a very limited set. Happy to be proven wrong though and curious about what you will find out :) Is there a way to keep in loop?

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

#63

Post by _Codemonkeyism »

JimDabell wrote: post #46
whyleyc wrote: post #32

Earlier quoted context omitted.

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 20…

Heard this for the first time, so being really curious (I always have the most recent year in a range on my sites)

What if the page is dynamically created on the fly? What copyright should I have? If page contains snippets/work created in different years?

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

#64

Post by pjungwir »

aftabh wrote: post #57
approachingtraj wrote: post #55

Earlier quoted context omitted.

> Instead of resorting to those hacks, follow the PostgreSQL documentation[1] to do it the right way. The simplest way is to initialize your PostgreSQL database cluster as: This is actually poor advice. The right way on Ubuntu is to use the packages provided by PostgreSQL Development Group ( https://wiki.postgresql.org/wiki/Apt ), and in that setup you use pg_createcluster instead of initdb. The snippet in the origin…

> This is actually poor advice. The right way on Ubuntu is to use the packages provided by PostgreSQL Development Group ( https://wiki.postgresql.org/wiki/Apt ), and in that setup you use pg_createcluster instead of initdb. initdb is part of standard PostgreSQL utilities/tools[1] for initializing your PostgreSQL server that's why I suggested using it. On the other hand, I don't see any reference to pg_createcluster c…

Since we're talking about Ubuntu, I would use the Ubuntu tools. They make it very easy to maintain several Postgres clusters side-by-side, e.g. /var/lib/postgresql/9.4/main vs /var/lib/postgresql/9.5/main. You can also control which binaries you use with the PGCLUSTER variable, like this:

    PGCLUSTER=9.4/main pg_dump foo
    PGCLUSTER=9.5/main pg_dump foo

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

#65

Post by oskari »

markonen wrote: post #43
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…

FYI, the Aiven database service allows you to run multiple database per instance. HA plans for Aiven start at US$200/mo and are available in multiple clouds including AWS and Google, see https://aiven.io/postgresql#comparison

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

#66

Post by merb »

aftabh wrote: post #58
merb wrote: post #51

Earlier quoted context omitted.

actually that is not true for ubuntu 16.04 there it will use the locale setting: newly setup: List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres…

It's good news that they've changed the default PostgreSQL character set (encoding) from ASCII to UTF8 in Ubuntu 16.04. It'll definitely save a good amount of time of new PostgreSQL users who need UTF8 encoding as default.

No it's not the "default" the default is 'use locale settings'.

So LANG=de_DE.UTF-8 will load a de_DE.UTF-8 Database, while my LANG previously was en_US.UTF-8, i can change LANG to something different and it will create the cluster to the value I've set my LANG to.

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

#67

Post by qaq »

adwf wrote: post #59
qaq wrote: post #22

OK a bit of topic, but just curious we are considering rolling Postgres hosted service that would support much large instances up 50TB much higher IOPS 1,000,000+ lower latency (infiniband vs ethernet SAN) pricing would be in line with AWS RDS +/- optional ElasticSearch integration optional change notification pub/sub Would you guys be interested/think such service is a good idea? Would appreciate to learn what you g…

My priorities are: Reliability > Size ~= Price > Performance Reliability of a database is always no.1, without it, nothing else matters. So the customer must have enough access to the machine to setup replication and failover themselves (eg. using something like repmgr). Or have damn good guarantees that you're doing it well for them. ie. multiple replicas, verifiable backups, offsite backups, etc. Secondly is size.…

Thanx for your input many of the frustrations you outline is the exact motivation why we are considering building this service.

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

#68

Post by qaq »

erkkie wrote: post #62
qaq wrote: post #39

Earlier quoted context omitted.

Latency will be higher. We are considering if we do go forward: a) Partnerships with smaller providers where there is an option to colocate equipment b) Colocating as close to as possible to targeted providers. The main thing we are trying to figure out - if there is a need for service that has significantly higher tears then what Azure, AWS offers.

I would imagine people who need either more AWS can supply (or cheaper) but cannot pay for it, and also haven't figured out how to run it themselves, to be a very limited set. Happy to be proven wrong though and curious about what you will find out :) Is there a way to keep in loop?

The thing that is impractical to do on premise is "transparent scaling" say your load increases and you need a way bigger box that's the kind of problem the service can solve (take slave offline remount storage on a bigger box catchup on replication switch to slave repeat for former master). We are at the early stage just investigating if we should move forward so @andre1sk on twitter would prob the only place :)

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

#69

Post by throwaway2016a »

Amazon DMS (database migration service) actually works really well and allows you to do this without writing any code. You may need to change your Postgres config a bit.

DMS isn't just for moving two RDS it can also be used to move off or RDS or even move from one non-RDS server to another non-RDS server that isn't even on AWS.

It can also do Postres to MySQL, MySQL to Postgres, etc.

It also does continuous replication so once the copy is done it will keep up to date (yes, even if it is Postgres -> mySQL).

One gotcha, though, it does not bring over secondary keys so make sure to recreate them before sending traffic over.

But this article does provide some insight on how to configure Postgres to be similar to RDS in terms of functionality.

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

#70

Post by gingerlime »

oskari wrote: post #65
markonen wrote: post #43

Earlier quoted context omitted.

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…

FYI, the Aiven database service allows you to run multiple database per instance. HA plans for Aiven start at US$200/mo and are available in multiple clouds including AWS and Google, see https://aiven.io/postgresql#comparison

First to hear about Aiven and you guys released pg hoard. Right? Interesting offerings. Are you connected to upcloud? (Both are based in Helsinki from what I could gather)
Post reply on HN