4 ms·

I don't know Softlayer too well, but Postgres is better every day. 1. Install Postgres 9.3, the version after the replication fix. 2. Tune Postgres until it r

I don't know Softlayer too well, but Postgres is better every day. 1. Install Postgres 9.3, the version after the replication fix. 2. Tune Postgres until it reaches a reasonable speed (lots of tutorials available, primarily just assigning it more RAM) 3. Create a second instance, do a backup, restore backup on second instance 4. Turn on streaming replication, ensure WALs are being received by second instance 5. If you're paranoid, use synchronous replication, which won't finish a transaction until it's been committed on the primary and a streaming secondary. I'm not our DBA, but I believe this covers the primary setup. You will have to test whether Postgres correctly moves into a new history chain on migration as support for this via WALSender is pretty new I believe.

erichurkman 13y ago
Also, with Postgres memory tuning: you may have to change kernel parameters to permit more shared memory. Make sure you make these changes persistent between reboots! For example, in FreeBSD, you can set the parameters at runtime via sysctl, but unless you also modify /etc/sysctl.conf, they won't be applied after a reboot. It's not fun scrambling to figure out what your kernel parameters were after you've applied a security update and rebooted your machine. Not fun at all. Test out reboots.
- jeltz 13y ago
  In 9.3 or later you should not have to do this.
rosser 13y ago
I am our DBA, and, though brief, this is a reasonable list. A caveat, though: be careful with synchronous replication; it actually increases your chances of having an outage. If your slave goes down with sync rep, the master will no longer accept writes. (To mitigate that risk, have multiple slaves. But that can further increase the latency sync rep introduces, as now the master has to wait for all the slaves to report back a successful write.)
- hahainternet 13y ago
  Good point, I believe if you list multiple sync rep targets it only requires one of them, but I would have to go check that properly :) Still, Postgres in a hot standby configuration is relatively simple, still high performance and an actual real database that doesn't need tens of grand in licensing. You really can't go much wrong with it.
  - sandGorgon 13y ago
    thanks! just to be clear, you do mean host standby with streaming replication right ? And why did you specifically recommend 9.3 - I cant google for anything specific to standby fixes. The authentication defaults for postgres and mysql are vastly different - I am always tempted to move all authentication to md5 (pretty much the same as mysql). Am I doing it wrong ?
    - rosser 13y ago
      You want either 9.3.2 or 9.2 >= 9.2.5. Before either of those, there was a bug in the streaming replication code such that, on failover, the last few outstanding transactions on the master might not be replayed on the slave, resulting in data loss. And, no, you aren't doing it wrong. You generally want to use MD5 auth.
      - hahainternet 13y ago
        The reason I recommend 9.3 specifically is that when a migration occurs, Postgres switches to a new historical branch. As of 9.2 this change gets written only to the WAL directory, and you must use something like Ceph / Gluster in order to have these migration files available to each replicating server. In 9.3 at some point this is slated for inclusion in the WALSender. This allows you to do full streaming replication without the requirement for a cluster filesystem to hold your binary logs.
        sandGorgon 13y ago

erichurkman 13y ago

Also, with Postgres memory tuning: you may have to change kernel parameters to permit more shared memory. Make sure you make these changes persistent between reboots! For example, in FreeBSD, you can set the parameters at runtime via sysctl, but unless you also modify /etc/sysctl.conf, they won't be applied after a reboot. It's not fun scrambling to figure out what your kernel parameters were after you've applied a security update and rebooted your machine. Not fun at all. Test out reboots.

jeltz 13y ago
In 9.3 or later you should not have to do this.

rosser 13y ago

I am our DBA, and, though brief, this is a reasonable list. A caveat, though: be careful with synchronous replication; it actually increases your chances of having an outage. If your slave goes down with sync rep, the master will no longer accept writes. (To mitigate that risk, have multiple slaves. But that can further increase the latency sync rep introduces, as now the master has to wait for all the slaves to report back a successful write.)

hahainternet 13y ago
Good point, I believe if you list multiple sync rep targets it only requires one of them, but I would have to go check that properly :) Still, Postgres in a hot standby configuration is relatively simple, still high performance and an actual real database that doesn't need tens of grand in licensing. You really can't go much wrong with it.
- sandGorgon 13y ago
  thanks! just to be clear, you do mean host standby with streaming replication right ? And why did you specifically recommend 9.3 - I cant google for anything specific to standby fixes. The authentication defaults for postgres and mysql are vastly different - I am always tempted to move all authentication to md5 (pretty much the same as mysql). Am I doing it wrong ?
  - rosser 13y ago
    You want either 9.3.2 or 9.2 >= 9.2.5. Before either of those, there was a bug in the streaming replication code such that, on failover, the last few outstanding transactions on the master might not be replayed on the slave, resulting in data loss. And, no, you aren't doing it wrong. You generally want to use MD5 auth.
    - hahainternet 13y ago
      The reason I recommend 9.3 specifically is that when a migration occurs, Postgres switches to a new historical branch. As of 9.2 this change gets written only to the WAL directory, and you must use something like Ceph / Gluster in order to have these migration files available to each replicating server. In 9.3 at some point this is slated for inclusion in the WALSender. This allows you to do full streaming replication without the requirement for a cluster filesystem to hold your binary logs.
      - sandGorgon 13y ago