PostgreSQL Log Backup & V12 - Replication / Cluster / HA working?

[mkretzer]

Since V12 is near release: we have alot of projects migrating from MS-SQL to PostgreSQL. The one thing missing with V11 for us was log backup for PostgreSQL.

Since we also plan to migrate some of our Always On Clusters we wonder: Is there a PosgreSQL cluster/replication/HA solution which will not break V12 log backup & restore?

[HannesK]

Hello Markus,
yes, log backup would work for streaming replication:
- primary node: log shipping enabled
- standby node(s): uncheck log shipping
- job needs to be adjusted if primary node changes
- Archive_command must be empty

For file-based replication log shipping is impossible for 3rd party tools and must be disabled:
- primary node: Archive_command is occupied
- standby node(s): Standby nodes are in recovery mode

If you want to replace SQL clusters... which additional technology do you plan to get a similar experience with Postgres? Patroni + vip-manager?

Best regards,
Hannes

[mkretzer]

Hi Hannes,

perfect backup is slightly more important to us than perfect clustering as we have VSphere HA against Hardware failures and synchronous SAN mirroring against disk and location failures, so HA is mainly for situations where the OS of the DB server fails.
Thats why i ask - we are searching for a "Veeam-Compatible" solution.

Markus

[HannesK]

okay, that would be "streaming replication" then

[mkretzer]

Ok, if i read documentation correctly replication is possible synchronously and asynchronously, correct? Or are there Limits caused by Veeam?

[HannesK]

correct, streaming replication can be asynchronous or synchronous. There are no known limits from our side except the ones listed above.

[mkretzer]

What about logical replication (which is the only mode as i understand to be able to support different versions and thus nearly downtime-free upgrades)?

[mkretzer]

Second question: Is there any HA solution like Patroni or Stolon that uses streaming replication and allows for automatic failover compatible with Veeam log backup?

[HannesK]

Hello Markus,
sorry for the delay... vacation time... I never looked at logical replication. As database schema and DDL are not replicated, only tables can be replicated and other restrictions, I'm not sure how many real-world us-cases exist for logical replication (from here)

I tested with Patroni back in the days and and one has to switch manually for log shipping. The software does not detect the primary node after a failover. Stolon seems to be only for Kubernetes, so that's out of scope here I guess.

Best regards,
Hannes

[mkretzer]

Hannes, thank you - what do you mean with "switch manually for log shipping" - on the Veeam side or do you mean failover?

[HannesK]

Hello,
on the Veeam side, you need to define which machine should be used for log shipping.

If a standby node is selected for log shipping, then it will fail because it's in recovery mode.

Best regards,
Hannes

[masonit]

Hi!

I wonder if it is supported in Veeam V12 to run application aware backup on 3 vsphere vms running a postgresql cluster?

\Maso

[HannesK]

Hello,
the user guide says "not supported", but I described above what's working.

What type of cluster technology are you using? That information would help us to decide which types of cluster to potentially support in future.

Best regards,
Hannes

[nantheless]

Hi Hannes,

I do have a partner requesting for the support of Patroni HA management for PostgreSQL cluster.
Is it possible to raise this as a FR?

[mkretzer]

I also would find support for Patroni clusters very useful!

[Tom Hagever]

Hey all,
I have many postgresql clusters in my environment, is there a solution for backing up the logs without adjusting the job once in a while when a primary db changes? Like is there a reason not to just back up all the cluster vms and their logs and use them accordingly once the restore is needed?

Also does anybody know what value does the archive_command get? Why does it have to be empty?

[HannesK]

Hello,

is there a solution for backing up the logs without adjusting the job once in a while when a primary db changes?

no, that's one of the reasons why it's unsupported. That manual configuration is not "customer ready".

rest of the conversation is here and I will continue to answer there

Best regards,
Hannes