When there is no WAL compression, the check is checking ARCHIVE_UNCOMPRESS_BIN value.
This value is set to "gunzip" by default.

So if ARCHIVE_COMPRESS is set to "no", we need to set ARCHIVE_UNCOMPRESS_BIN to empty to be able to check the archived WAL presence.

Upgrade the check in the following part:

                        if [ -n "$ARCHIVE_UNCOMPRESS_BIN" ]; then
                            [[ "$output" != "nagios" ]] && info "uncompressing $first_wal_file to measure its size"

                            wal_segsize=$($ARCHIVE_UNCOMPRESS_BIN -c $tmpwal 2>/dev/null| wc -c)
                            if [[ -z "$wal_segsize" ]] || [[ "$wal_segsize" == 0 ]]; then
                                die "Could not uncompress the WAL to check its size"
                            fi
                        else
                            [[ "$output" != "nagios" ]] && info "assuming WAL segments are not compressed"
                            wal_segsize=$(cat $tmpwal 2>/dev/null| wc -c)
                            if [[ -z "$wal_segsize" ]] || [[ "$wal_segsize" == 0 ]]; then
                                die "Could not get the size of the WAL segment"
                            fi
                        fi

First check for the ARCHIVE_COMPRESS value in first line.

Update CONTRIBUTING.md to explain local circleci installation and usage.

• Install snap manager:
  sudo apt install snapd

• Install docker and circleci snaps:

sudo snap install circleci docker
sudo snap connect circleci:docker docker

Note: With snap packages, the docker command will use the Docker snap, not any version of Docker you may have previously installed. For security purposes, snap packages can only read/write files from within $HOME.

• Test the install:

export PATH=$PATH:/snap/bin
circleci

• Setup your environment:
  • visit https://circleci.com/account/api and create a new token
  • run the setup command:

$ circleci setup
✔ CircleCI API Token: ****************************************
API token has been set.
✔ CircleCI Host: https://circleci.com
CircleCI host has been set.
Setup complete.
Your configuration has been saved to /home/me/snap/circleci/160/.circleci/cli.yml.

Trying an introspection query on API to verify your setup... Ok.
Trying to query our API for your profile name... Hello, Me.

• Run the test locally:

circleci local execute shellcheck

Pour le moment j'ai une erreur :

$ circleci local execute shellcheck
Downloading latest CircleCI build agent...
Docker image digest: sha256:2e3d8ff7707b34b815fc6174ae5f6fcd8384f7a166a219bad29ac5ffc139f475
docker: Error response from daemon: AppArmor enabled on system but the docker-default profile could not be loaded: running `/sbin/apparmor_parser apparmor_parser --version` failed with output: Failed to load features from '/usr/share/apparmor-features/features': No such file or directory

error: exit status 1.

Le redémarrage de apparmor n'y a rien changé. Peut-être qu'un reboot ??? je retesterais plus tard.

MacOS uses a FreeBSD version of "sed", which does not understand "--" to mark the end of the parameter list. The normal backup operations seem to work fine, but listing the backups does not work. Removing "--" from sed parameter lists in list_pitr resolves the issue.

Hi,
it seems that the command "pitrery restore" failed in some cases.
Pitrery seems to add the symbol "?" to the first argument of the command /usr/lib/pitrery/restore_pitr
If I initialize the variable with "opts=()" before "case $action in" in the script /usr/bin/pitrery => it works, else it does'nt work

Version of Pitrery : 1.8
Distribution : Centos 6.4 (Final)

On some servers with RHEL 6.4, it works in all cases.

Thanks in advance.
Lionel

The check -A option assumes that max_seg = 254 or 255, but in v11 it is different:

With wal-segsize=32M, last segment ends with 7F, hence:

postgres@pegase:~/11/pagode$ /opt/pitrery/pitrery -f /etc/postgresql/11/pagode/pitrery.conf check -A
2018-10-20 15:37:43 CEST INFO: checking local archives in /PGDATA2/PITR/pagode/archived_xlog
2018-10-20 15:37:43 CEST INFO: oldest backup is: /PGDATA2/PITR/pagode/2018.10.20_13.06.35
2018-10-20 15:37:43 CEST INFO: start wal file is: 00000001000000000000003C
2018-10-20 15:37:43 CEST INFO: listing WAL files
2018-10-20 15:37:43 CEST INFO: first WAL file checked is: 00000001000000000000003C.gz
2018-10-20 15:37:43 CEST INFO: start WAL file found
2018-10-20 15:37:44 CEST ERROR: missing WAL file: 000000010000000000000080
2018-10-20 15:37:44 CEST INFO: next found is: 000000010000000100000000
2018-10-20 15:37:45 CEST ERROR: missing WAL file: 000000010000000100000080
2018-10-20 15:37:45 CEST INFO: next found is: 000000010000000200000000
...

With wal-segsize=1024 (1 Gb), we stop at 03:

sudo -iu postgres  /opt/pitrery/pitrery -f /etc/postgresql/11/maousse/pitrery.conf check -A
2018-10-20 17:42:28 CEST INFO: checking local archives in /BACKUPS/postgresql_PITR/maousse/archived_xlog
2018-10-20 17:42:28 CEST INFO: oldest backup is: /BACKUPS/postgresql_PITR/maousse/2018-10-20T17:34:31+0200
2018-10-20 17:42:28 CEST INFO: start wal file is: 000000010000000700000001
2018-10-20 17:42:28 CEST INFO: listing WAL files
2018-10-20 17:42:28 CEST INFO: first WAL file checked is: 000000010000000700000001.gz
2018-10-20 17:42:28 CEST INFO: start WAL file found
2018-10-20 17:42:28 CEST ERROR: missing WAL file: 000000010000000700000004
2018-10-20 17:42:28 CEST INFO: next found is: 000000010000000800000000
2018-10-20 17:42:28 CEST ERROR: missing WAL file: 000000010000000800000004
2018-10-20 17:42:28 CEST INFO: next found is: 000000010000000900000000
2018-10-20 17:42:28 CEST INFO: last WAL file checked is: 000000010000000900000000.gz
2018-10-20 17:42:28 CEST INFO: missing count is: 504

-rw------- 1 postgres postgres       225 oct.  20 17:34 000000010000000700000001.00D71A08.backup.gz
-rw------- 1 postgres postgres 197033693 oct.  20 17:34 000000010000000700000001.gz
-rw------- 1 postgres postgres  10202675 oct.  20 17:34 000000010000000700000002.gz
-rw------- 1 postgres postgres 196899273 oct.  20 17:35 000000010000000700000003.gz
-rw------- 1 postgres postgres 197077469 oct.  20 17:36 000000010000000800000000.gz
-rw------- 1 postgres postgres 197032180 oct.  20 17:37 000000010000000800000001.gz
-rw------- 1 postgres postgres 197191191 oct.  20 17:38 000000010000000800000002.gz
-rw------- 1 postgres postgres 144541154 oct.  20 17:40 000000010000000800000003.gz
-rw------- 1 postgres postgres 156853149 oct.  20 17:41 000000010000000900000000.gz
-rw------- 1 postgres postgres 156950672 oct.  20 17:43 000000010000000900000001.gz

On the other site, with wal-segsize=1 (1 Mo), we go up to 000FFF.

-rw------- 1 postgres postgres 128673 oct.  20 17:57 000000010000000000000FFD.gz
-rw------- 1 postgres postgres 128322 oct.  20 17:57 000000010000000000000FFE.gz
-rw------- 1 postgres postgres 130496 oct.  20 17:57 000000010000000000000FFF.gz
-rw------- 1 postgres postgres 145214 oct.  20 17:57 000000010000000100000000.gz
-rw------- 1 postgres postgres 149611 oct.  20 17:57 000000010000000100000001.gz
-rw------- 1 postgres postgres 134147 oct.  20 17:57 000000010000000100000002.gz
-rw------- 1 postgres postgres 128016 oct.  20 17:57 000000010000000100000003.gz
-rw------- 1 postgres postgres 127031 oct.  20 17:57 000000010000000100000004.gz
-rw------- 1 postgres postgres 116026 oct.  20 17:57 000000010000000100000005.gz
-rw------- 1 postgres postgres 133376 oct.  20 17:57 000000010000000100000006.gz
-rw------- 1 postgres postgres 130882 oct.  20 17:57 000000010000000100000007.gz
-rw------- 1 postgres postgres 126947 oct.  20 17:57 000000010000000100000008.gz
-rw------- 1 postgres postgres 154376 oct.  20 17:57 000000010000000100000009.gz

If I remove 000000010000000000000FFF.gz, check -A does not detect it.

Worse: in this case, check -A is stuck in a loop after complaining that a wal is missing (of course it's there):

$ sudo -iu postgres /opt/pitrery/pitrery -f /etc/postgresql/11/piggy/pitrery.conf check -A
2018-10-20 18:05:54 CEST INFO: checking local archives in /PGDATA2/PITR/piggy/archived_xlog
2018-10-20 18:05:54 CEST INFO: oldest backup is: /PGDATA2/PITR/piggy/2018.10.20_17.39.04
2018-10-20 18:05:54 CEST INFO: start wal file is: 000000010000000000000108
2018-10-20 18:05:54 CEST INFO: listing WAL files
2018-10-20 18:05:54 CEST INFO: first WAL file checked is: 000000010000000000000009.gz
2018-10-20 18:05:56 CEST INFO: start WAL file found
2018-10-20 18:05:56 CEST ERROR: missing WAL file: 000000010000000100000008

Config : pitrery 2.2-dev from this morning, Ubuntu 16.04, bash 4.3.48

sudo -iu postgres  /opt/pitrery/pitrery -c /etc/postgresql/10/abeille1/pitrery.conf check -A
INFO: checking local archives in /PGDATA2/PITR/abeilles/archived_xlog
INFO: oldest backup is: /PGDATA2/PITR/abeilles/2018.02.24_12.08.02
INFO: start wal file is: 000000010000000000000025
/opt/pitrery/pitrery: line 3362: 0x"00000001" : syntax error: invalid arithmetic operator (error token is ""00000001" ")

Everything is okay if I remove the " " in the $(( 0x".." )) strings.

The culprit is probably the bash version:

bash --version ; echo $(( 0x"00000001" ))
GNU bash, version 4.4.12(1)-release (x86_64-pc-linux-gnu)...
1

bash --version ; echo $(( 0x"00000001" ))
GNU bash, version 4.3.48(1)-release (x86_64-pc-linux-gnu)...
-bash: 0x"00000001"  : erreur de syntaxe : opérateur arithmétique non valable (le symbole erroné est ""00000001" ")

Same problem for bash 4.2 on CentOS 7

If the first WAL file to keep (aka $wal_file in purge_pitr.bash) is missing, then the check on line 395 would never succeed.
All the files or moved to the wal_purge_list... and purged.

postgres@srvA:/pg_xlog/9.4$ /usr/bin/pitrery  purge -N
2017-01-27 15:45:05 CET INFO: searching backups
2017-01-27 15:45:05 CET INFO: there are no backups to purge
2017-01-27 15:45:05 CET INFO: listing WAL files older than 000000010000004D0000005F
2017-01-27 15:45:05 CET INFO: wal_file: 000000010000004D0000005F
2017-01-27 15:45:05 CET INFO: found wals:
2017-01-27 15:45:05 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D0000005F.gz
2017-01-27 15:45:05 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D00000060.gz
2017-01-27 15:45:05 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D00000061.gz
2017-01-27 15:45:05 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D00000062.gz
2017-01-27 15:45:05 CET INFO: 0 old WAL file(s) to remove
2017-01-27 15:45:05 CET INFO: done
postgres@srvA:/pg_xlog/9.4$ mv /pg_backups/9.4/pitr/archived_xlog/000000010000004D0000005F.gz /tmp
postgres@srvA:/pg_xlog/9.4$ /usr/bin/pitrery  purge -N
2017-01-27 15:45:20 CET INFO: searching backups
2017-01-27 15:45:20 CET INFO: there are no backups to purge
2017-01-27 15:45:20 CET INFO: listing WAL files older than 000000010000004D0000005F
2017-01-27 15:45:20 CET INFO: wal_file: 000000010000004D0000005F
2017-01-27 15:45:20 CET INFO: found wals:
2017-01-27 15:45:20 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D00000060.gz
2017-01-27 15:45:20 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D00000061.gz
2017-01-27 15:45:20 CET INFO: /pg_backups/9.4/pitr/archived_xlog/000000010000004D00000062.gz
2017-01-27 15:45:20 CET INFO: 15 old WAL file(s) to remove
2017-01-27 15:45:20 CET INFO: Would purge 3 old WAL file(s):
2017-01-27 15:45:20 CET INFO:  First: 000000010000004D00000061.gz
2017-01-27 15:45:20 CET INFO:  Last: 000000010000004D00000062.gz
2017-01-27 15:45:20 CET INFO: done

Either add temp files to cleanup to a list in cleanup() or create a unique mktemp directory to save all files and remove this directory in cleanup()

The -c option can used to set the path to the configuration file or th compression command for tar method, depending on the context.

This can be confusing at first and it would be easier to use 2 differents letters, for instance:

• keep -c for the compression command of pitrery backup and pitrery restore
• use -f for the config file

In the meantime the -c config_file could be secretly maintained for backward compatibility

As discussed in private:

On Debian, with pitrery 3.1 (from apt.dalibo.org or github) and PG 12, the behaviour has changed:

• with pitrery restore, the postgresql.conf is modified (restore_command  & others added) and is stored in restored_config_files
• so an immediate restart fails, because there is a recovery.signal but no restore_command in /etc/postgresql/12/main/postgresql.conf or postgresql.auto.conf.

With PG11, a suitable recovery.conf is created and you can start PG immediately.

On CentOS and with PG12, the postgresql.conf is in PGDATA and is updated with restore_command, so an immediate start is possible.

I'd really prefer a clean postgresql.auto.conf with the same options as the old recovery.conf, that would solve this problem, and cleanly separate parameters added by pitrery from the original ones.

At least, as big warning should appear at the end of the restore if no postgresql.conf is present in PGDATA, explaining the operations left to the user (ie picking the useful lines or copying the postgresql.conf file from restored_config_files).

Hello,

Please remove my name from the web site : https://github.com/dalibo/pitrery/blob/gh-pages/_layouts/default.html

Thanks and regards

I get FATAL: Could not uncompress the WAL to check its size, although the WALs are not compressed.

ARCHIVE_UNCOMPRESS_BIN is of course empty in the conf file. The code seems to assume that it is always set.

Hello,

I am not sure it's the right place to ask this, my bad if it is not.

What is the impact of the new API for non exclusive backups on Pitrery [1] ? especially the need to keep a live connection to the database during the backup.

My shell skills are not top norch, so i am not sure if it's a problem.

Thank you.

[1] https://commitfest.postgresql.org/9/510/

pitrery is a set of complex scripts. I think it could be nice to add some basic tests to make sure we don't regress. For me it's the first step before doing some advanced refactoring or adding some new features.

I think we can start by writing some basic tests based on bats and shellcheck - then see how to automate those tests

The version 12 has changed how recovery configuration is managed:

• Move recovery.conf settings into postgresql.conf
• Do not allow multiple conflicting recovery_target* specifications -> it is too difficult to check that thoroughly in case include directives are used. PostgreSQL will make the check on startup.
• Cause recovery to advance to the latest timeline by default
• There will be no more recovery.conf file. The parameters will be listed in postgresql.conf and a recovery.signal or standby.signal will be used by the restored cluster to know what action to take.

Objectives:

• add a switch option / a configuration key to specify whether the restored cluster should be used as standby (creation of either the standby.signal file or the recovery.conf file)
• edit the postgresql.conf file to update the restore_command and, if needed, recovery_target_time configuration?
• others?

The target directory for archived WAL files is created in local mode but not SSH

While running pitrery backup against PostgresSQL 9.6 pitrery calls pg_start_backup in non-exlusive mode and prepares backup_label file on it own. So my archive dir looks something like this:

ls
backup_label  backup_timestamp  pgdata.tar.gz  PG_VERSION  tablespace_map  tblspc  tblspc_list`.

However, pitrery restore creates a data directory which does not have a backup_label file. The result of that is when postgresql instance is started against a restored data dir it tries to do crash recovery. While doing crash recovery it does not try to call restore_command to fetch the missing WAL segments from archive and immediately fails with PANIC.

This code branch claims that the label should be restored by pitrery restore, which seems to not happen:

# In non-exclusive mode, we have to write the backup_label files
    # ourselves. When using the tar storage, we cannot add the file to
    # PGDATA inside the tarball, so we just create files locally, and
    # copy them to the backup directory later. It is the job of the
    # restore to put them back in the correct location ($PGDATA) when
    # restoring.

pitr_mgr currently does not handle purge of .backup and .history files.

Capture the invoked command. In case *_xlog is used: warn about script name change

Hello,

The find command used within the list_backups() function does not follow symlinks: https://github.com/dalibo/pitrery/blob/master/pitrery#L412

To reproduce the problem, one must:

• backup over ssh
• create a symlink on the backup server that links to the real backup directory
• put the path to the symlink on the BACKUP_DIR parameter

Then, the pitrery list command fails with the following error:

FATAL: could not find any backups in /var/lib/pgsql/my_backup_symlink/ on 192.168.122.72

Even when there is backups into the directory:

-bash-4.1$ ssh 192.168.122.72 ls -l /var/lib/pgsql/my_backup_symlink/
total 4
drwx------ 3 postgres postgres 4096 May 18 15:48 2018.05.18_15.48.08

With -L or -follow options of find, this works, but may have non desirable side effects, like following links under subdirectories, so I'm unsure this would be the right fix.
readlink should be able to translate the symlink, but it would be necessary to execute it over ssh to get the full path name.

One other way to fix this could be to systematically add the / character at the end of the directory used within find, so even if that is a symlink, find would consider it as a directory.

Hello,

I think there's a typo in the error messages at lines 525 and 536. The sentence is affirmative where it should be negative.
echo "ERROR: could extract [...]" 1>&2
-> echo "ERROR: could not extract [...]" 1>&2

Regards,

Create a new configuration key in pitrery.conf in order to set the recovery_end_command on restore.

Returning 255 make the archiver process die for no purpose with the side effect of not updating the archive stats.

POSIX says exit code > 127 are here to tell the parent process the child was died on a signal, the archiver process respects this, but we don't. So we need to fix archive_xlog

See https://www.postgresql.org/message-id/flat/[email protected]#[email protected]

This line gives error when bash version is < 4.2. (in RH6 i.e.)

To solve it, replace it by echo "${out%?}". Successfully tested on RH6 and RH7.

Kind regards

Hello,

For a specific customer case, we had to limit the bandwidth used by Pitrery by 2 means 👍

• option --whole-file to disable the rsync delta algorithm and just transfer the whole data file if the modification timestamp has changed since last backup, the delta algorithm is quiet consuming over NFS
• option --bwlimit to limit the I/O bandwidth

Regards

Hello,

we have this usecase where we build a rpm from your sources and define a custom dir where our configuration file is stored. The thing is that this rpm is installed for several projects and we don't want to use the default pitrery.conf file but rather a project.conf for each. Future projects will arrive also at some point.

Would it be acceptable to have an env variable such as CONFIG allowing us to override the default configuration file ? Sort of the same as we could override other parameters at run time.

A snippet would be (in the main pitrery script) :

config="pitrery.conf"
if [ "$CONFIG" != "" ]
then
    config="$CONFIG"
fi

And an example of how we could run it :

CONFIG="custom.conf" pitrery check

Do you see anything that could go wrong with such a modification (even if not accepted in the codebase) ?

I know we could use other tricks like bash_aliases or others.

• That would be helpful on some configurations with different archive_xlogs

• That could be used by pitrery to check that the archive_xlog in archive_command has the same version.

Archiving a non-existing file with archive_xlog generates an error like this but creates an empty file in the archive location.

For example :
$ archive_xlog -C archive_server non-existing-file
gzip: non-existing-file: No such file or directory
ERROR: Unable to send compressed non-existing-file to xxx.xxx.xxx.xxx:/data/pgsql-9.2/archives

Its return is not 0, that's OK :
$ echo $?
1

Hello,

To reproduce the problem, one must configure pitrery to backup over ssh, and then do at least one backup using pitrery backup.

Then, the pitrery list command fails with the following error:

ERROR: acces denied to /var/lib/pgsql/pitr/2018.05.18_16.05.20

But as we can check, the permissions are correct:

-bash-4.1$ ssh 192.168.122.72 ls -ld /var/lib/pgsql/pitr/2018.05.18_16.05.20
drwx------ 3 postgres postgres 4096 May 18 16:05 /var/lib/pgsql/pitr/2018.05.18_16.05.20

It seems that on the list) action, the permission check and backup size computation are always done locally, even when backups are configured to be done over ssh.
See https://github.com/dalibo/pitrery/blob/master/pitrery#L1037-L1070

Archives were previously stored by default in $BACKUP_DIR/archive_xlog, now in $BACKUP_DIR/archive_wal. So, if $ARCHIVE_DIR was not explicitly set, pitrery will put the archives in a different directory than before. Archiving still succeeds, but a check returns an error, purge will not find old archives, and I suppose that a restore woud fail. That could lead to as big problem for an user that did not realize that pitrery was upgraded.

Suggestions:

• improve doc and add a migration checklist that suggest setting $ARCHIVE_DIR explicitly or renaming archive_xlog, running check ...
• Automatically create a symlink if ARCHIVE_DIR is not set and that the old default directory is present ? I would not automatically rename the existing archive_xlog.
• check: in case of a failure, try to detect the old default archive_xlog, and give a hint
• restore : if the old archive_xlog is detected, raise a warning and suggest a check

A check command was stuck in an infinite loop:

pitrery version: 3.1 from the github repo, updated today.

These are all the wals:

postgres@pegase:/PGDATA2/PITR/abeilles/archived_wal$ ls -l
total 53772
-rw-r----- 1 postgres postgres     239 May 12 10:33 000000010000000700000059.00000060.backup.bz2
-rw-r----- 1 postgres postgres    4050 May 12 10:33 000000010000000700000059.bz2
-rw-r----- 1 postgres postgres    6788 May 12 10:33 00000001000000070000005A.bz2
-rw-r----- 1 postgres postgres    3919 May 12 10:33 00000001000000070000005B.bz2
-rw-r----- 1 postgres postgres    4027 May 12 10:33 00000001000000070000005C.bz2
-rw------- 1 postgres postgres   54203 May 12 10:33 00000001000000070000005D.bz2
-rw------- 1 postgres postgres  650265 May 12 10:33 00000001000000070000005E.bz2
-rw------- 1 postgres postgres   15160 May 12 10:33 00000001000000070000005F.bz2
-rw------- 1 postgres postgres    3870 May 12 10:33 000000010000000700000060.bz2
-rw------- 1 postgres postgres    5202 May 12 10:33 000000010000000700000061.bz2
-rw------- 1 postgres postgres     236 May 12 10:33 000000010000000700000062.00000028.backup.bz2
-rw------- 1 postgres postgres    3968 May 12 10:33 000000010000000700000062.bz2
-rw------- 1 postgres postgres 2180618 May 12 10:33 000000010000000700000063.bz2
-rw------- 1 postgres postgres 2151197 May 12 10:33 000000010000000700000064.bz2
-rw------- 1 postgres postgres 2121074 May 12 10:33 000000010000000700000065.bz2
-rw------- 1 postgres postgres 2108571 May 12 10:33 000000010000000700000066.bz2
-rw------- 1 postgres postgres 2068492 May 12 10:33 000000010000000700000067.bz2
-rw------- 1 postgres postgres 2067396 May 12 10:33 000000010000000700000068.bz2
-rw------- 1 postgres postgres 2012586 May 12 10:33 000000010000000700000069.bz2
-rw------- 1 postgres postgres 1976326 May 12 10:33 00000001000000070000006A.bz2
-rw------- 1 postgres postgres 2017135 May 12 10:33 00000001000000070000006B.bz2
-rw------- 1 postgres postgres 2118591 May 12 10:33 00000001000000070000006C.bz2
-rw------- 1 postgres postgres 2121475 May 12 10:33 00000001000000070000006D.bz2
-rw------- 1 postgres postgres  798143 May 12 10:33 00000001000000070000006E.bz2
-rw------- 1 postgres postgres 2137230 May 12 10:33 00000001000000070000006F.bz2
-rw------- 1 postgres postgres 2084112 May 12 10:33 000000010000000700000070.bz2
-rw------- 1 postgres postgres 2078855 May 12 10:33 000000010000000700000071.bz2
-rw------- 1 postgres postgres 2051493 May 12 10:33 000000010000000700000072.bz2
-rw------- 1 postgres postgres 2034537 May 12 10:33 000000010000000700000073.bz2
-rw------- 1 postgres postgres 1997111 May 12 10:33 000000010000000700000074.bz2
-rw------- 1 postgres postgres 1988158 May 12 10:33 000000010000000700000075.bz2
-rw------- 1 postgres postgres 1986421 May 12 10:33 000000010000000700000076.bz2
-rw------- 1 postgres postgres 1942352 May 12 10:33 000000010000000700000077.bz2
-rw------- 1 postgres postgres 1899085 May 12 10:33 000000010000000700000078.bz2
-rw------- 1 postgres postgres 1882196 May 12 10:33 000000010000000700000079.bz2
-rw------- 1 postgres postgres 1854690 May 12 10:33 00000001000000070000007A.bz2
-rw------- 1 postgres postgres 1849765 May 12 10:33 00000001000000070000007B.bz2
-rw------- 1 postgres postgres 1825048 May 12 10:33 00000001000000070000007C.bz2
-rw------- 1 postgres postgres 1733856 May 12 10:33 00000001000000070000007C.partial.bz2
-rw------- 1 postgres postgres  859491 May 12 10:33 00000001000000070000007D.bz2

This is the result if bash -x /opt/pitrery/pitrery -f /etc/postgresql/10/abeille1/pitrery.conf check -B -A -g1d , as it goes into the infinite loop:

+ for wal_file in "${wal_list[@]}"
+ [[ /PGDATA2/PITR/abeilles/archived_wal/00000001000000070000007B.bz2 =~ .*\.backup.* ]]
++ basename -- /PGDATA2/PITR/abeilles/archived_wal/00000001000000070000007B.bz2
+ wal_file=00000001000000070000007B.bz2
+ [[ 00000001000000070000007B.bz2 =~ ^[0-9A-F]{24}.*$ ]]
+ '[' -z 1 ']'
+ ((  122 >= 255  ))
+ cur_seg=123
++ cut -b 1-8
+ tln=1
++ cut -b 9-16
+ wal=7
++ cut -b 17-24
+ seg=123
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  89 == 123  ))
+ [[ yes != \y\e\s ]]
+ chk_c=35
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  123 == 123  ))
+ '[' -n '' ']'
+ for wal_file in "${wal_list[@]}"
+ [[ /PGDATA2/PITR/abeilles/archived_wal/00000001000000070000007C.bz2 =~ .*\.backup.* ]]
++ basename -- /PGDATA2/PITR/abeilles/archived_wal/00000001000000070000007C.bz2
+ wal_file=00000001000000070000007C.bz2
+ [[ 00000001000000070000007C.bz2 =~ ^[0-9A-F]{24}.*$ ]]
+ '[' -z 1 ']'
+ ((  123 >= 255  ))
+ cur_seg=124
++ cut -b 1-8
+ tln=1
++ cut -b 9-16
+ wal=7
++ cut -b 17-24
+ seg=124
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  89 == 124  ))
+ [[ yes != \y\e\s ]]
+ chk_c=36
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  124 == 124  ))
+ '[' -n '' ']'
+ for wal_file in "${wal_list[@]}"
+ [[ /PGDATA2/PITR/abeilles/archived_wal/00000001000000070000007C.partial.bz2 =~ .*\.backup.* ]]
++ basename -- /PGDATA2/PITR/abeilles/archived_wal/00000001000000070000007C.partial.bz2
+ wal_file=00000001000000070000007C.partial.bz2
+ [[ 00000001000000070000007C.partial.bz2 =~ ^[0-9A-F]{24}.*$ ]]
+ '[' -z 1 ']'
+ ((  124 >= 255  ))
+ cur_seg=125
++ cut -b 1-8
+ tln=1
++ cut -b 9-16
+ wal=7
++ cut -b 17-24
+ seg=124
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  89 == 124  ))
+ [[ yes != \y\e\s ]]
+ chk_c=37
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  125 == 124  ))
+ ((  1 != 1  ))
+ '[' -z '' ']'
++ printf %08X%08X%08X 1 7 125
+ missing=00000001000000070000007D
+ [[ '' != \n\a\g\i\o\s ]]
+ error 'missing WAL file: 00000001000000070000007D'
++ now
++ '[' yes = yes ']'
+++ date '+%F %T %Z '
++ echo '2020-05-12 11:17:14 CEST '
+ echo '2020-05-12 11:17:14 CEST ERROR: missing WAL file: 00000001000000070000007D'
2020-05-12 11:17:14 CEST ERROR: missing WAL file: 00000001000000070000007D
+ out_rc=1
+ mc=1
+ ((  125 == 255  ))
+ cur_seg=126
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  126 == 124  ))
+ ((  1 != 1  ))
+ '[' -z 00000001000000070000007D ']'
+ mc=2
+ ((  126 == 255  ))
+ cur_seg=127
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  127 == 124  ))
+ ((  1 != 1  ))
+ '[' -z 00000001000000070000007D ']'
+ mc=3
+ ((  127 == 255  ))
+ cur_seg=128
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  128 == 124  ))
+ ((  1 != 1  ))
+ '[' -z 00000001000000070000007D ']'
+ mc=4
+ ((  128 == 255  ))
+ cur_seg=129
+ ((  1 == 1  ))
+ ((  7 == 7  ))
+ ((  129 == 124  ))
+ ((  1 != 1  ))
+ '[' -z 00000001000000070000007D ']'
+ mc=5
+ ((  129 == 255  ))

...and so on, with mc increasing indefinitely.

Since eeba3e3, the purge script was broken for bash lesser than 4.2.

The function qw was using the bashism: echo "${out::-1}"
bash 4.2 CHANGES gave this possible grammar:
"Negative length specifications in the ${var:offset:length} expansion, previously errors, are now treated as offsets from the end of the variable."

@tilkow proposed an elegant way to remove last space: echo ${out%?}

This should be compatible with bash 2.0+.

Thank you again @tilkow for the help!

Version : Pitrery 1.10
The cleanup phase in backup_pitr does not work when archiving is done over SSH.

backup_pitr fails to connect to remote ssh server because no user is provided in connection string. The current user is then used by ssh, resulting in a password prompt or a connection error (since the default user is unknown on remote server).

backup_pitr.cleanup() on lines 67 and 69 :

ssh $target "test -d \"$backup_dir\"" 2>/dev/null
...
ssh $target "rm -rf $backup_dir" 2>/dev/null

We can see that ssh_user is missing and only target host is passed as parameter.

I suggest fixing those lines as follow, to add the ssh_user :

ssh ${ssh_user:+$ssh_user@}$target "test -d \"$backup_dir\"" 2>/dev/null
...
ssh ${ssh_user:+$ssh_user@}$target "rm -rf $backup_dir" 2>/dev/null

Nicolas, as discussed this afternoon, check -A raised an error on one (and only one) wal that was present:
ERROR: missing WAL file: 0000000100000C7500000000

..archived_xlog$ ls -l | grep -A2 -B2 0000000100000C7500000000
-rw-r--r--. 1 john_doe somegroup  3313701 24 sept. 01:40 0000000100000C74000000FE.gz
-rw-r--r--. 1 john_doe somegroup  3303227 24 sept. 01:41 0000000100000C74000000FF.gz
-rw-r--r--. 1 john_doe somegroup  3283767 24 sept. 01:41 0000000100000C7500000000.gz
-rw-r--r--. 1 john_doe somegroup  3281353 24 sept. 01:41 0000000100000C7500000001.gz
-rw-r--r--. 1 john_doe somegroup  3255313 24 sept. 01:41 0000000100000C7500000002.gz

Rights are fine, and the unzipped file is 16 Mb big.
Maybe important: this is the first file following a ...FF in the archive_xlog.

Store inside the backup:

• the pitrery command line
• the pitrery configuration file
• the postgresql configuration files paths ('config_file' 'hba_file' 'ident_file')
• search for extra configuration files (include_if_exists, include file, include)
• pg_controldata ?

Hi,

We’ve implemented a Pitrery solution recently (2.2) and while testing it a few options came to my mind that doesn’t seem possible, so I wanted to check with you guys if that is possible. I was wondering if it’s possible to restore single database from the cluster from the backup and if we can change a name of the restored database. Would it be possible to restore single database to a different sever if we have backups available on that sever also?

Thank you for the help.

Hi.

We're backing up archive log and base backup to the separate mount point to the disks that are not directly attached to the server where PostgreSQL is running. Recently we experienced availability issues with that attached mount point and the same one was unavailable for a few hours. When it finally became available (re-mounted) I noticed there was few WAL files files that remained in the PostgreSQL WAL directory and I cannot see them in the backup location. Does Pitrery handles those types of issues and will they be backed up with the following archive log command or the upcoming base backup? Do we need to check the files and run archive command with file specification manually?

Do you think this would be the best option for backup or we should prefer to back it up locally and then copy it every hour or more often to the storage not attached to the server where PostgreSQL is running?

Thank you for your help.
N.

Hi,

When you're not using the appropriate user, you'll find this disturbing message:

path/to/backup		size_of_backup		ERROR: could find the "stop time" in the backup_label file !!! This backup may be imcomplete or corrupted !!!

I think the message isn't appropriate as it leads to thinking the backup is incomplete or corrupted (major pb) while it's just the user you used don't have permission to see those backups.

My configuration :

• Backup is sent on another machine
• I use pitrery list
• With the user postgres, it's ok
• With my home user, I got the message
• Version 1.10 (yes, sorry, it is quite old)

Have a nice day,

Lætitia

http://www.postgresql.org/docs/9.3/static/app-pgreceivexlog.html

Using pg_receivexlog means that backup server has the latest data, instead of whenever the WALs are recycled.

Config : CentOS 7, pitrery 2.2-dev including 355384d

After a 1st backup, check- A correctly finds the 1st WAL for the oldest backup. The oldest WAL in the archive is older than this.
After a purge, the oldest WAL in archive_xlog is the first of the oldest backup, but pitrery check -A does not find it.

It seems to me that the culprit is the continue line 3948: it short-cirtcuits the test on the start_wal, and $start_wal_file_found stays to 'no'.

-bash-4.2$ /opt/pitrery/pitrery -c /var/lib/pgsql/10/data/pitrery.conf backup
INFO: preparing directories in /var/lib/pgsql/BACKUPS/10/data/
...
INFO: backup directory is /var/lib/pgsql/BACKUPS/10/data//2018.04.06_14.16.25
INFO: done

-bash-4.2$ /opt/pitrery/pitrery -c /var/lib/pgsql/10/data/pitrery.conf check -A
INFO: checking local archives in /var/lib/pgsql/BACKUPS/10/data/archived_xlog
INFO: oldest backup is: /var/lib/pgsql/BACKUPS/10/data/2018.04.06_14.16.25
INFO: start wal file is: 00000001000000000000000E
INFO: listing WAL files
INFO: first WAL file checked is: 000000010000000000000001.gz
INFO: start WAL file found
INFO: last WAL file checked is: 00000001000000000000000F.gz
INFO: all archived WAL files found

-bash-4.2$ /opt/pitrery/pitrery -c /var/lib/pgsql/10/data/pitrery.conf backup
INFO: preparing directories in /var/lib/pgsql/BACKUPS/10/data/
...
INFO: backup directory is /var/lib/pgsql/BACKUPS/10/data//2018.04.06_14.17.41
INFO: done

-bash-4.2$ /opt/pitrery/pitrery -c /var/lib/pgsql/10/data/pitrery.conf purge
INFO: searching backups
INFO: there are no backups to purge
INFO: listing WAL files older than 00000001000000000000000E
INFO: 13 old WAL file(s) to remove
INFO: purging old WAL files
INFO: done

-bash-4.2$ /opt/pitrery/pitrery -c /var/lib/pgsql/10/data/pitrery.conf check -A
INFO: checking local archives in /var/lib/pgsql/BACKUPS/10/data/archived_xlog
INFO: oldest backup is: /var/lib/pgsql/BACKUPS/10/data/2018.04.06_14.16.25
INFO: start wal file is: 00000001000000000000000E
INFO: listing WAL files
INFO: first WAL file checked is: 00000001000000000000000E.gz
INFO: last WAL file checked is: 000000010000000000000012.gz
ERROR: start WAL file of the oldest backup not found

-bash-4.2$ ls -l /var/lib/pgsql/BACKUPS/10/data/archived_xlog/
total 204
-rw------- 1 postgres postgres   195  6 avril 14:16 00000001000000000000000E.00000028.backup.gz
-rw------- 1 postgres postgres 27135  6 avril 14:16 00000001000000000000000E.gz
-rw------- 1 postgres postgres 52440  6 avril 14:17 00000001000000000000000F.gz
-rw------- 1 postgres postgres 30205  6 avril 14:17 000000010000000000000010.gz
-rw------- 1 postgres postgres 27061  6 avril 14:17 000000010000000000000011.gz
-rw------- 1 postgres postgres   195  6 avril 14:17 000000010000000000000012.00000028.backup.gz
-rw------- 1 postgres postgres 27133  6 avril 14:17 000000010000000000000012.gz
-rw------- 1 postgres postgres 27062  6 avril 14:18 000000010000000000000013.gz

Backup are no more possible with PostgreSQL 9.6 when USE_ISO8601_TIMESTAMPS is enabled. Here is the error reported:

date: invalid date ‘2017-04-21 11:00:24 CEST|’
ERROR: could not format stop time to a directory name

The following patch solves the issue but there must be a better way to fix the bug:

diff --git a/backup_pitr.bash b/backup_pitr.bash
index 501e5b4..130463f 100755
--- a/backup_pitr.bash
+++ b/backup_pitr.bash
@@ -415,9 +415,11 @@ if (( $pg_version >= 90600 )) && (( ${BASH_VERSINFO[0]} >= 4 )); then
         # When the fd has some data, read everything and change
         # newlines to pipe characters, to pass through expansion of
         # newline to space, when stored as a string.
+        ret_str=''
         while read -t 1 -u ${COPROC[0]} line; do
-            [ -n "$line" ] && echo -n "$line|"
+            [ -n "$line" ] && ret_str="$ret_str$line|"
         done
+        echo -n $ret_str | sed 's/|$//'
         return 0
     }
 

Not sure whether this is a pitrery or PostgreSQL issue.

A backup was started on the 31st of December 2018. Not sure how long the backup went, but the backup label gives a stop time on the 31st of December 2019:

START WAL LOCATION: 7E8/45000028 (file 00000006000007E800000045)
CHECKPOINT LOCATION: 7E8/45000060
BACKUP METHOD: streamed
BACKUP FROM: master
START TIME: 2018-12-31 03:15:04 CET
LABEL: pitr_2018.12.31-03.15.02
STOP TIME: 2019-12-31 04:28:26 CET

The default restore_command generated by the restore action does not work when archiving is done over SSH.

restore_xlog fails to find the SSH user and host from the configuration file, when it is not provided on the command line. As of 1.9, the restore action no longer generates a restore_xlog command line including -h for restore_command in recovery.conf.

This make the restore fail when PostgreSQL starts.

The workaround is to set RESTORE_COMMAND in the configuration file to:

RESTORE_COMMAND="/path/to/restore_xlog -C <config_file> -h <archive_host> %f %p"

If a different SSH user is required, add -u:

RESTORE_COMMAND="/path/to/restore_xlog -C <config_file> -h <archive_host> -u <archive_user> %f %p"

Hello,
Currently, pitrery can backup from a standby, but only if PostgreSQL is at least in 9.6.
Do you have plans to support this feature for earlier versions of PostgreSQL ?

The key :

https://dl.dalibo.com/public/pitrery/debian/pitrery_1.12-1_all.deb.asc

Seems not working well ? I try different ways to add the key and go with the deb paquet but I get this error :

gpg pitrery_1.12-1_all.deb.asc
Detached signature.
Please enter name of data file: pitrery_1.12-1_all.deb.asc
gpg: Signature made Sun 20 Nov 2016 02:28:53 PM UTC using RSA key ID D664AA4D
gpg: Can't check signature: public key not found

root@pgsql-0a41:~# gpg --import pitrery_1.12-1_all.deb.asc
gpg: no valid OpenPGP data found.
gpg: Total number processed: 0

wget -q https://dl.dalibo.com/public/pitrery/debian/pitrery_1.12-1_all.deb.asc
apt-key add pitrery_1.12-1_all.deb.asc
gpg: no valid OpenPGP data found.

wget -O - https://dl.dalibo.com/public/pitrery/debian/pitrery_1.12-1_all.deb.asc  | \
sudo apt-key add -
gpg: no valid OpenPGP data found.

In the meantime, i will use the source code.
Thank you in advance.

Hi !

I try archiving the usual way :

#archive_command = 'test ! -f /var/lib/postgresql/9.6/backup/bobby/pitr_9.6-bobby/archived_xlog/%f && cp %p /var/lib/postgresql/9.6/backup/bobby/pitr_9.6-bobby/archived_xlog/%f'

No problem with this method.

Then I use pitrery binary to do the same thing :

archive_command = '/usr/bin/archive_xlog -C /etc/pitrery/pitr_96-bobby %p'

The following information are set in /etc/pitrery/pitr_96-bobby.conf

grep -v "#" /etc/pitrery/pitr_9.6-bobby.conf | grep ARCH
ARCHIVE_LOCAL="yes"
ARCHIVE_HOST=
ARCHIVE_USER=
ARCHIVE_DIR="$BACKUP_DIR/$BACKUP_LABEL/archived_xlog"
ARCHIVE_COMPRESS="yes"
ARCHIVE_OVERWRITE="yes"
ARCHIVE_CHECK="no"
ARCHIVE_FLUSH="no"

I get the following error :

ERROR: Not enough information to archive the segment
2017-05-10 16:08:11.896 UTC [8046] LOG:  archive command failed with exit code 1
2017-05-10 16:08:11.896 UTC [8046] DETAIL:  The failed archive command was: /usr/bin/archive_xlog -C /etc/pitrery/pitr_96-bobby.conf pg_xlog/00000001000000000000006F

First weird thing, why interpreting %p as pg_xlog/00000001000000000000006F while wal files are in this folder : /var/lib/postgresql/9.6/wal/bobby/files/00000001000000000000006xx

And why did I get this kind of error while "$ARCHIVE_LOCAL" is set to "yes" ?

# Sanity check. We need at least to know if we want to perform a local
# copy or have a hostname for an SSH copy
if [ "$ARCHIVE_LOCAL" != "yes" ] && [ -z "$ARCHIVE_HOST" ]; then
    error "Not enough information to archive the segment"
fi

I bypassed the problem by using the following line instead :

archive_command = '/usr/bin/archive_xlog -L -d /var/lib/postgresql/9.6/backup/bobby/pitr_9.6-bobby/archived_xlog/ -C /etc/pitrery/pitr_96-bobby.conf %p'

But maybe there is a better solution ?

Thanks in advance

For huge wal repositories, I sometimes wonder if pitrery is stuck or still working.

Adding a small return would be helpful, for example:

INFO: checked until 0000000100000DD30000000 and going on...

Luxury option: counting of WAL:

INFO: checked 123 WALs until 0000000100000DD30000000 and going on (about 10000 remaining)...

Hi,

I would like to restore a backup Postgres, i have a error

-bash-4.1$ pitrery restore
2017-09-14 12:01:33 CEST INFO: searching backup directory
2017-09-14 12:01:33 CEST INFO: searching for tablespaces information
2017-09-14 12:01:33 CEST INFO:
2017-09-14 12:01:33 CEST INFO: backup directory:
2017-09-14 12:01:33 CEST INFO: /var/lib/pgsql/9.4/backups//pitr/2017-09-14T11:17:47+0200
2017-09-14 12:01:33 CEST INFO:
2017-09-14 12:01:33 CEST INFO: destinations directories:
2017-09-14 12:01:33 CEST INFO: PGDATA -> /var/lib/pgsql/9.4/data/
2017-09-14 12:01:33 CEST INFO:
2017-09-14 12:01:33 CEST INFO: recovery configuration:
2017-09-14 12:01:33 CEST INFO: target owner of the restored files: postgres
2017-09-14 12:01:33 CEST INFO: restore_command = '/usr/bin/restore_xlog %f %p'
2017-09-14 12:01:33 CEST INFO:
2017-09-14 12:01:33 CEST INFO: checking if /var/lib/pgsql/9.4/data/ is empty
2017-09-14 12:01:33 CEST ERROR: /var/lib/pgsql/9.4/data/ is not empty. Contents won't be overwritten

can you help me?

Thank you

This problem is found on master & pg_12 :

Aftrer a sucessfull pitrery restore, I get a postgresql.confe file:

postgres@pegase:~/12/defo5$ ll restored_config_files/
total 68
-rw-r----- 1 postgres postgres  4939 Dec  9 10:04 pg_hba.conf
-rw-r----- 1 postgres postgres  1968 Dec  9 10:04 pg_ident.conf
-rw-r--r-- 1 postgres postgres 27501 Dec  9 10:04 postgresql.conf
-rw-r--r-- 1 postgres postgres 27104 Dec  9 10:04 postgresql.confe

The postgresql.confe file contains the same content as postgresql.conf until the restore_command included.

With bash -x, I get the following debug log:

++ date
+ echo '## Pitrery generated the lines below (Mon Dec  9 09:55:10 CET 2019)'
+ sed -ie 's/^[[:blank:]]*restore_command.*/#&/' /var/lib/postgresql/12/defo5/restored_config_files/postgresql.conf
+ echo 'restore_command = '\''restore_xlog -C /etc/postgresql/12/defo/pitrery.conf %f %p'\'''
+ sed -ie 's/^[[:blank:]]*recovery_target_time.*/#&/' /var/lib/postgresql/12/defo5/restored_config_files/postgresql.conf
+ '[' -n '' ']'

The  -e option of one or the other of the sed commands seems the culprit. I don't really understand its use, and it works better when removed.