3 ms·

Regarding replication, the logical replication in pg10 should make this significantly easier. Between that and "Quorum Commit for Synchronous Replication" I'm r

Regarding replication, the logical replication in pg10 should make this significantly easier. Between that and "Quorum Commit for Synchronous Replication" I'm really excited to see how these pan out in production. Pg doesn't normally hype or talk about (or let into production) things that don't work.

snuxoll 9y ago
Logical replication really isn't going to fix the pain points I have with replication from a DevOps/Sysadmin perspective. The biggest issue with replication in PostgreSQL is restoring a failed master, pg_rewind provides the low-level plumbing to do this and it's somewhat integrated into repmgr now - but it's far from being easy to use compared to something like SQL Server Availability Groups. Being the sole Linux sysadmin / PostgreSQL DBA in my organization means I have to take responsibility for this since the tools are complex enough I can't easily throw them over to our Windows admins or SQL Server DBA's in an emergency. This is partially an issue with staffing, but if the tooling was a little easier to understand and robust enough in fully-automatic operation I could just leave common troubleshooting steps in a runbook, but right now when replication breaks it REALLY breaks.
- X86BSD 9y ago
  You know what's even easier? Zfs snapshots and zfs send/recv.
  - snuxoll 9y ago
    ZFS snapshots don't handle the same use-case as streaming replication, I've got backups handled quite nicely with barman.
- normaljoe 9y ago
  That's because they are Windows admins and not nix admins. They probably don't have a good understanding of the command line and if they do not the rich nix command line. I have a single pg admin in current project and he can throw most tasks to the Oracle (running on Linux) admins or even junior Linux admins. While "complex" they are still fairly basic to command line admins. Anything with less than 3 pipes or one regex should be simple to command line *nix person. Granted to excludes you including a complex AWK script or inline perl execution on the command line instead of in a file like a normal sane person.
manigandham 9y ago
The major issue with logical replication is the lack of DDL replication, meaning any changes to your table structure will cause issues until the replicas are updated to match. Also an all tables subscription will not automatically pickup new tables, it needs to be refreshed too. At this stage, I'd recommend against using logical replication unless absolutely necessary because it doesn't really do what people would normally expect it to.

snuxoll 9y ago

Logical replication really isn't going to fix the pain points I have with replication from a DevOps/Sysadmin perspective. The biggest issue with replication in PostgreSQL is restoring a failed master, pg_rewind provides the low-level plumbing to do this and it's somewhat integrated into repmgr now - but it's far from being easy to use compared to something like SQL Server Availability Groups. Being the sole Linux sysadmin / PostgreSQL DBA in my organization means I have to take responsibility for this since the tools are complex enough I can't easily throw them over to our Windows admins or SQL Server DBA's in an emergency. This is partially an issue with staffing, but if the tooling was a little easier to understand and robust enough in fully-automatic operation I could just leave common troubleshooting steps in a runbook, but right now when replication breaks it REALLY breaks.

X86BSD 9y ago
You know what's even easier? Zfs snapshots and zfs send/recv.
- snuxoll 9y ago
  ZFS snapshots don't handle the same use-case as streaming replication, I've got backups handled quite nicely with barman.
normaljoe 9y ago
That's because they are Windows admins and not nix admins. They probably don't have a good understanding of the command line and if they do not the rich nix command line. I have a single pg admin in current project and he can throw most tasks to the Oracle (running on Linux) admins or even junior Linux admins. While "complex" they are still fairly basic to command line admins. Anything with less than 3 pipes or one regex should be simple to command line *nix person. Granted to excludes you including a complex AWK script or inline perl execution on the command line instead of in a file like a normal sane person.

manigandham 9y ago

The major issue with logical replication is the lack of DDL replication, meaning any changes to your table structure will cause issues until the replicas are updated to match. Also an all tables subscription will not automatically pickup new tables, it needs to be refreshed too. At this stage, I'd recommend against using logical replication unless absolutely necessary because it doesn't really do what people would normally expect it to.