Mostly idle server and base backups, missing xlog issue

[pellcorp]

I have a particular database that I am doing a base backup a few times a day. The issue is that often this database gets very little activity, so even with a checkpoint_timeout, xlogs are not being switched and so most base backups actually cannot be used to restore the db, cos the Xlog that was open when the base backup was created was not saved.

I am wondering if it would be ok to submit a merge request which optionally forces the xlog to switch after completing the base backup?

Im using postgresql 9.6 atm

For example, the db can go 2 days without generating an xlog after the last base backup was completed. So most of the current days backups are completely pointless.

[pellcorp]

Im currently using basebackup_mode = pipe

After taking a look at the code, im thinking that changing to basebackup_mode = local-tar might solve my problems...

[pellcorp]

Would the project accept a patch which adds the xlog switching for run_piped_basebackup?

Its just that if I switched to local-tar, id have to do quite a bit of testing, which took long enough for piped as is :-)

[pellcorp]

Ive got a potentially stupid question!

I have setup pghoard so it is on the same server as the standby, so I create base backups and get xlogs from the standby and my nodes setting looks like this:

"nodes": [
                {
                    "host": "127.0.0.1",
                    "password": "some password that is not this",
                    "port": 5432,
                    "user": "repuser"
                }
            ]

This suggests to me that doing a local-tar backup won't work, because the xlog switching has to be on the primary.

I chose to do it this way, because I figured I was offloading any processing overheads from pghoard onto the standby

Is it possible to retain this configuration, but use local-tar anyway? Im wondering if I can configure the nodes to actually point at my upstream primary server.

[pellcorp]

Ok, so I can see its catered for, if running on a standby the xlog is skipped.

[pellcorp]

I understand the lay of the land now, will close this issue. I will open a merge request if I come up with a simple enough change to allow the switch xlog stuff to work with piped base backup.