Comprehensive data protection for all workloads
Post Reply
mkretzer
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by 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
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by 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
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by 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
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by HannesK »

okay, that would be "streaming replication" then :-)
mkretzer
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by mkretzer »

Ok, if i read documentation correctly replication is possible synchronously and asynchronously, correct? Or are there Limits caused by Veeam?
HannesK
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by HannesK »

correct, streaming replication can be asynchronous or synchronous. There are no known limits from our side except the ones listed above.
mkretzer
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by 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
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by 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
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by 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
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by mkretzer »

Hannes, thank you - what do you mean with "switch manually for log shipping" - on the Veeam side or do you mean failover?
HannesK
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by 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
Service Provider
Posts: 325
Liked: 23 times
Joined: Oct 09, 2012 2:30 pm
Full Name: Maso
Contact:

[MERGED] V12 backup postgresql cluster

Post by 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
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by 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
Veeam Software
Posts: 27
Liked: never
Joined: Mar 08, 2021 12:17 pm
Full Name: Nigel Tan
Contact:

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

Post by 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
Veeam Legend
Posts: 1139
Liked: 387 times
Joined: Dec 17, 2015 7:17 am
Contact:

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

Post by mkretzer »

I also would find support for Patroni clusters very useful!
Tom Hagever
Novice
Posts: 8
Liked: never
Joined: Sep 26, 2023 4:42 pm
Full Name: Tom babai
Contact:

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

Post by 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
Product Manager
Posts: 14181
Liked: 2815 times
Joined: Sep 01, 2014 11:46 am
Full Name: Hannes Kasparick
Location: Austria
Contact:

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

Post by 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
Post Reply

Who is online

Users browsing this forum: albertwt, ericlaiys78 and 40 guests