pg_back should include configuration files.

It could as simple than CONFIG_DIRECTORIES_TO_BACKUP="/etc/postgresql/ /var/lib/postgres/scripts" and a tar.

A copy from pg_settings is another option (@tilkow a done some things about it) but (in my mind) overkill, or the output from show all.

I see 5 different files in output. Do we have an option to have a tar file or any compression which includes all these 5 files ?

Good to see version 2 having package/binary releases and overall getting better.
There is just one feature from 1.x that's missing for me and that is the -q flag to run pg_back in "quiet" mode.
I normally run pg_back as a cron job and have mailing set up, so any output on STDOUT or STDERR will be sent to my inbox. However I only want to be notified about critical errors and want to suppress info messages like INFO: dumping instance configuration.

I tried sudo -u postgres pg_back > /dev/null but still seeing terminal output, as the program only outputs to STDERR.
Once I redirected STDERR to STDOUT sudo -u postgres pg_back > /dev/null 2>&1 I got the desired output. This however also discards any potential error and wont trigger an email (no output) if pg_back encounters a problem.

To fix this I propose the following changes:

1. Only output errors/warnings to STDERR and information, debug or verbose output to STDOUT
2. A new CLI flag to omit any non critical output e.g. only errors/warnings to STDERR

I've just compiled from master to test the encryption (Linux ubuntu 20.04, golang-1.16 installed)

• With nothing about encryption in the pg_back2.conf, this works (I get .age files):

/opt/pg_back2/pg_back2.bin.master --encrypt --cipher-pass AZERTYUIOP -vc /etc/postgresql/14/defo/pg_back2.conf

• This fails with the env variable (exported or not):

$ export PGBK_PASSPHRASE=AZERTYIUOP
$ echo $PGBK_PASSPHRASE
AZERTYUIOP

$ /opt/pg_back2/pg_back2.bin.master --encrypt  -vc /etc/postgresql/14/defo/pg_back2.conf
...
2021/12/20 19:34:48.665811 FATAL: some error encountered in postprocessing: encryption failed: could not encrypt /PGDATA2/DUMPS/defo14/pg_globals_2021-12-20_19-34-48.sql: failed to create recipient from password: passphrase can't be empty

• It works with this in the conf file:

encrypt = true
cipher_passphrase = AZERTYUIOP

• The encryption does not seem to work if defined only on DB level this way:

[pgbench5p10]
encrypt = true
cipher_passphrase = AZERTYUIOP

That's understandable but, IMHO, should be documented and raise an error

• I find there is an incoherence between --cipher-pass and cipher-passhphrase = ...: it's easy to confuse both, especially as the README.md speaks only the 1st one.
  (And there is no error, but this is for another issue)

Hi, I tried to add sftp upload, but without success. The following error is generated:
FATAL: some error encountered in postprocessing: could not open sftp session: ssh: subsystem request failed

The configuration file:
upload = sftp

sftp_host = nas-srv1
sftp_port = 22
sftp_user = admin
sftp_password = secretpassword
sftp_directory = /share/backups
sftp_identity = ~/.ssh/id_rsa
sftp_ignore_hostkey = false

I can connect to remote server without problems with the command: ssh admin@nas-srv1

What am I doing wrong?
Thanks,
Paolo

It would be really nice if someone created a helm chart for this, as all charts available are not nearly as good as this one. This is useful to reduce the number of local helm charts with a chart that is maintained.

Hi,
I downloaded the pg_back_2.0.1_linux_amd64.deb file from the release page and install it on Ubuntu 21.10 using:

sudo dpkg -i ./pg_back_2.0.1_linux_amd64.deb

Looks like pg_back does not recognize that upload option is set and as result does not upload backups to cloud (gcs in my case). There is no errors about misconfiguration.

But it does work if I compile the binary using:

docker run --rm -v "$PWD":/go/bin golang:1.16 go get github.com/orgrim/pg_back

In this case everything is working with the exact same config file I used previously.

It will be great, if we can have backup history with informations like: size, date, database name, purge status and upload status in a CSV file.

In v11, if I understand correctly, some information from pg_db_role_setting that were dumped by pg_dumpall -g are now saved by pg_dump and can restored only with pg_restore --create... but you need --create with an pg_dump -Fp, or you lose this info.

Will pg_back be compatible with this?

Running a dump on a slave server on a Debian 8 server with pg_back on version 1.3-dev, I got a warning about typeset :

Nov 10 00:00:01 bas4 pg_back[23603]: 2016-11-10 00:00:01 CET  INFO: pausing replication replay
Nov 10 00:00:01 bas4 pg_back[23603]: /usr/lib/postgresql/pg_back: 168: /usr/lib/postgresql/pg_back: typeset: not found
Nov 10 00:00:01 bas4 pg_back[23603]: 2016-11-10 00:00:01 CET  INFO: listing databases
Nov 10 00:00:01 bas4 pg_back[23603]: 2016-11-10 00:00:01 CET  INFO: dumping global objects

The typeset command is not available in sh (but it is in bash) :

# /bin/bash
# typeset
BASH=/bin/bash
BASHOPTS=checkwinsize:cmdhist:complete_fullquote:expand_aliases:extquote:force_fignore:hostcomplete:interactive_comments:progcomp:promptvars:sourcepath
BASH_ALIASES=()
(...)

# /bin/sh
# typeset
/bin/sh: 1: typeset: not found

Hi Orgrim,

The postgresql function you're using to check recovery status only works since PostGreSQL 9.x

# psql -V
psql (PostgreSQL) 8.4.20

# ./pg_back -c ./pg_back.conf
2016-02-05 10:28:58 CET  INFO: preparing to dump
2016-02-05 10:28:58 CET  INFO: target directory is /var/backups/postgresql
ERROR:  function pg_is_in_recovery() does not exist

May be there is an alternative to your commands for PgSQL earlier than 9
For now, I've simply replaced "die" with and echo on line 162
https://github.com/orgrim/pg_back/blob/master/pg_back#L162

Any unrecognized option in the .conf file should raise an error.

Eg, I've already lost some time for this :

encyrpt=true

(and it won't encrypt anything)

or:

cipher_pass=AZERTYUIOP

hi,

it is a cool tool 👍
Just a note, it took me some time to configure the password. Can the password be provided by environment variable?
Also I think the tool requires superuser permissions which you don't get on AWS RDS or Azure postgres.

Helllo,

The current way to remove older backups is based on file age, it could be awesome to apply also a retention policy based on the number of backup.

Thank you.

The readme gives the following example:

pg_back -d /var/lib/pgsql/9.3/backups

Looking at the help text, shouldn't that be -b instead?

Hi,
The purge is not correct with PGBK_MIN_KEEP variable because it process two type of files (.dump and .sql) at the same time. It results in a wrond deletion of backups.

Let's say PGBK_MIN_KEEP=4 and you have 5 backups of "foo" db (so 10 files total because one sql and one dump file per backup)

• the good behaviour will be to delete the last backup (i.e: 2 files total) to keep the 4 most recent backups.
• the current behaviour deletes the last 6 older files (i.e: the 3 older backups) and the result after purge is 4 files (or 2 backups) left (i.e: the value of PGBK_MIN_KEEP).

(And I didn't mention the case where PGBK_MIN_KEEP is odd and backups are partially deleted because of that).

The solution is simple, just test for each type of file individually. I have forked the project and commit a solution here: tohtor@7560822

Regards,

PS: sorry if it's not clear enough, my mother tongue is french, it's hard to explain something like this in english.

$ which pg_dump
/usr/bin/pg_dump
$ /tmp/pg_back  -B /usr/bin/pg_dump -b /tmp
2022/06/17 11:46:34 WARN: failed to retrieve version of pg_dump: fork/exec /usr/bin/pg_dump/pg_dump: not a directory
2022/06/17 11:46:34 FATAL: provided pg_dump is older than 8.4, unable use it.

Could this be related to #78 ?

This error doesn't seem to happen with version 2.1.0 (installed via .deb package).

#pg_back -c pg_back.conf
INFO: dumping globals
ERROR: pg_dumpall: error: could not connect to database "postgres"
INFO: waiting for postprocessing to complete
FATAL: pg_dumpall -g failed: exit status 1

While trying manually to connect with pg_dumpall connects successfully

pg_dumpall -h localhost -p 5432 -U postgres -d postgres -W

Conf:
host = localhost
port = 5432
user = "postgres"
dbname = "postgres"

Password for the user postgres is stored in ~/.pgpass
Do we have a detailed log to see whats the exact error ?

pg_back is nice, but a newbie may not know in which order restore the dumps, which commands to use, especially in panic mode. Or ignore which base to restore. Or what to do with the different createdb.sql. Or what to do with the settings file.

Most people will ignore that the ALTER ROLE ... IN DATABASE ... must be searched and replayed.

A simple script, or even a basic README, should be generated to indicate which files are to be used and in which order.

Hello,
Latest version of pg_back (2.0.1) seems not compatible with postgresql 9.2 and lower.
Especially due to nonexistent "-d connstr" option with pg_dumpall.

2021/10/29 02:00:02 INFO: dumping globals
2021/10/29 02:00:02 ERROR: /usr/pgsql-9.1/bin/pg_dumpall: invalid option -- 'd'
2021/10/29 02:00:02 ERROR: Try "pg_dumpall --help" for more information.
2021/10/29 02:00:02 FATAL: pg_dumpall -g failed: exit status 1

Could you please add a disclaimer for older versions? Or take away this option?
Regards

It seems that the retention fails when the output format is plain.

Hypothesis : the .createdb.sql files are counted among the .sql files to keep.

Config :

bin_directory = /usr/lib/postgresql/14/bin
backup_directory = /PGDATA2/DUMPS/defo14
purge_older_than = 1s
purge_min_keep = 7
format = plain
with_templates = true
port = 14001
user = postgres
dbname = postgres
host=/var/run/postgresql

After 10 runs of, the result is:

$ ll template1*                   
-rw-rw-r-- 1 postgres postgres 386 Jan  7 13:49 template1_2022-01-07T13:49:08+01:00.createdb.sql
-rw-rw-r-- 1 postgres postgres 386 Jan  7 13:49 template1_2022-01-07T13:49:10+01:00.createdb.sql
-rw-rw-r-- 1 postgres postgres 545 Jan  7 13:49 template1_2022-01-07T13:49:10+01:00.sql
-rw-rw-r-- 1 postgres postgres 386 Jan  7 13:49 template1_2022-01-07T13:49:12+01:00.createdb.sql
-rw-rw-r-- 1 postgres postgres 545 Jan  7 13:49 template1_2022-01-07T13:49:12+01:00.sql
-rw-rw-r-- 1 postgres postgres 386 Jan  7 13:49 template1_2022-01-07T13:49:14+01:00.createdb.sql
-rw-rw-r-- 1 postgres postgres 545 Jan  7 13:49 template1_2022-01-07T13:49:14+01:00.sql

If format=custom, I get correctly:

$ ll template1*
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:45 template1_2022-01-07T13:45:19+01:00.dump
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:45 template1_2022-01-07T13:45:22+01:00.dump
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:45 template1_2022-01-07T13:45:36+01:00.dump
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:45 template1_2022-01-07T13:45:39+01:00.dump
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:45 template1_2022-01-07T13:45:42+01:00.dump
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:45 template1_2022-01-07T13:45:58+01:00.dump
-rw-rw-r-- 1 postgres postgres 1569 Jan  7 13:46 template1_2022-01-07T13:46:01+01:00.dump

Hello orgrim,

configuration

The /etc/pg_back/pg_back.conf file:

# pg_back configuration file

# PostgreSQL binaries path. Leave empty to search $PATH
bin_directory = /usr/pgsql-14/bin

# Where to store the dumps and other files. It can include the
# {dbname} keyword that will be replaced by the name of the database
# being dumped.
backup_directory = /pg_backups/pg_back

# Timestamp format to use in filenames of output files. Two values are
# possible: legacy and rfc3339. For example legacy is 2006-01-02_15-04-05, and
# rfc3339 is 2006-01-02T15:04:05-07:00. rfc3339 is the default, except on
# Windows where it is not possible to use the rfs3339 format in filename. Thus
# the only format on Windows is legacy: the option has no effect on Windows.
# timestamp_format = rfc3339

# PostgreSQL connection options. This are the usual libpq
# variables. dbname is the database used to dump globals, acl,
# configuration and pause replication. password is better set in
# ~/.pgpass
host = localhost
port = 5432
user = postgres

with_templates = false

# Format of the dump, understood by pg_dump. Possible values are
# plain, custom, tar or directory.
format = directory

# When the format is directory, number of parallel jobs to dumps (-j
# option of pg_dump)
parallel_backup_jobs = 4

# When using a compressed binary format, e.g. custom or directory, adjust the
# compression level between 0 and 9. Use -1 to keep the default level of pg_dump.
compress_level = 3

purge_older_than = 7

# When purging older dumps, always keep this minimum number of
# dumps. The default is 0, even if purge_older_than is 0 the dumps of
# the current run are kept, if one wants to remove all dumps and not
# keep anything, for example to just test for data corruption, then
# purge_older_than shall be a negative duration.
purge_min_keep = 1

# Number of pg_dump commands to run concurrently
jobs = 1

amed the same as the
# # database. These options take precedence over the global values
# [dbname]

# format =
# parallel_backup_jobs =
# compress_level =
# checksum_algorithm =
# purge_older_than =
# purge_min_keep =

# # List of schemas and tables to dump or exlude from the dump.
# # Inclusion and exclusion rules of pg_dump apply, as well as
# # pattern rules. Separate schema/table names with a semicolon
# schemas =
# exclude_schemas =

# tables =
# exclude_tables =

# Include or exclude large objects in the dump. Leave the option commented to
# keep the default behaviour, see pg_dump -b.
# with_blobs = true

# # inject these options to pg_dump. Use an empty value to cancel the
# # global value of pg_dump_options
# pg_dump_options =

And when I run pg_back I get this weird message:

[postgres@SLV-AURION-BDD ~]$ pg_back
2023/04/07 09:51:45 FATAL: Could load configuration file: key-value delimiter not found: amed the same as the

Install

I installed pg-back-2.1.1-x86_64.rpm because uname -m's output was x86_64.

sudo yum install https://github.com/orgrim/pg_back/releases/download/v2.1.1/pg-back-2.1.1-x86_64.rpm
sudo chown -R postgres:postgres /etc/pg_back

os-release

NAME="Rocky Linux"
VERSION="8.7 (Green Obsidian)"
ID="rocky"
ID_LIKE="rhel centos fedora"
VERSION_ID="8.7"
PLATFORM_ID="platform:el8"
PRETTY_NAME="Rocky Linux 8.7 (Green Obsidian)"
ANSI_COLOR="0;32"
LOGO="fedora-logo-icon"
CPE_NAME="cpe:/o:rocky:rocky:8:GA"
HOME_URL="https://rockylinux.org/"
BUG_REPORT_URL="https://bugs.rockylinux.org/"
ROCKY_SUPPORT_PRODUCT="Rocky-Linux-8"
ROCKY_SUPPORT_PRODUCT_VERSION="8.7"
REDHAT_SUPPORT_PRODUCT="Rocky Linux"
REDHAT_SUPPORT_PRODUCT_VERSION="8.7"

Robin,

Bonjour,

Utilisant pg_back avec plusieurs instances postgresql localement (sous Debian), je vous propose un timer systemd (à la place d'un cron + logrotate ) :

[email protected]

[Unit]
Wants=postgresql@%i.service
AssertPathExists=/etc/pg_back/%I/pg_back.conf
Description=Simple backup for PostgreSQL
Documentation=https://github.com/orgrim/pg_back
After=postgresql@%i.service
RequiresMountsFor=/etc/pg_back/%I /var/lib/postgresql/%I

[Service]
# Execute pre and post scripts as root, otherwise it does it as User=
PermissionsStartOnly=true
ExecStartPre=/usr/bin/install -m 0750 -o postgres -g postgres -d /var/backups/postgresql/%I

ExecStart=/usr/bin/pg_back -c /etc/pg_back/%I/pg_back.conf
User=postgres
Group=postgres

IOSchedulingClass=best-effort
IOSchedulingPriority=7
SyslogIdentifier=pg_back@%i
Type=oneshot

[Install]
WantedBy=multi-user.target

[email protected]

[Unit]
Description=Daily PostgreSQL backup

[Timer]
OnCalendar=*-*-* 4:00:00
AccuracySec=30m
Persistent=true

[Install]
WantedBy=timers.target

Exemple de consultation des logs:

# journalctl -u pg_back@12-main

Cordialement :)

This is ignored by pg_back (Debian Linux, pg_back 2.1.0 or compiled from master):

bin_directory = /usr/lib/postgresql/15/bin

DEBUG: pg_dump version is: 140002
2022/04/19 13:47:34.349841 DEBUG: processing input connection parameters
2022/04/19 13:47:34.349887 DEBUG: using pg_back as application_name
2022/04/19 13:47:34.349912 INFO: dumping globals
2022/04/19 13:47:34.349953 DEBUG: started encryption worker 0
2022/04/19 13:47:34.349967 DEBUG: started checksum worker 0
2022/04/19 13:47:34.349982 DEBUG: started upload worker 0
2022/04/19 13:47:34.350005 DEBUG: started checksum worker for encrypted files 0
2022/04/19 13:47:34.404027 DEBUG: pg_dumpall version is: 140002
2022/04/19 13:47:34.404126 DEBUG: running: /usr/bin/pg_dumpall -g -w -l postgres -d application_name=pg_back dbname=postgres host=/var/run/postgresql port=15001 sslmode=disable user=postgres -f /PGDATA2/DUMPS/defo-15/pg_globals_2022-04-19T13:47:34+02:00.sql

This seems to be as mistake in f01bbb4 on this line :

(I assume that you didn't drop the Linux support).

Hacking the function solves the problem.

I'll try it soon

"pg_back" is an invalid package name.

It does not comply with the Debian package naming rules :
https://www.debian.org/doc/debian-policy/ch-controlfields.html#source

Package names (both source and binary, see Package) must consist only of lower case letters (a-z), digits (0-9), plus (+) and minus (-) signs, and periods (.).

Looks like debian does not allow _ (underscore) in package names.

Not much of an issue most of the time, but I stumbled upon this debsums tool which was reporting a warning about this:

$ sudo debsums -ce
debsums: invalid package name 'pg_back'

There might be others out there who might be real picky...

Hi,

excludes_tables config parameter seems to not work properly. Here is what I tried and only the first table is excluded :

# tables =
exclude_tables = "pgbench_history;pgbench_accounts"


pg_restore --list postgres_2023-09-18T09\:10\:51Z.dump 
...
216; 1259 16394 TABLE public pgbench_accounts postgres
217; 1259 16397 TABLE public pgbench_branches postgres
215; 1259 16391 TABLE public pgbench_tellers postgres
4277; 0 16394 TABLE DATA public pgbench_accounts postgres
4278; 0 16397 TABLE DATA public pgbench_branches postgres
4276; 0 16391 TABLE DATA public pgbench_tellers postgres

Here now with another value for exclude_tables :

exclude_tables = "pgbench_tellers;pgbench_accounts;pgbench_branches"

pg_restore --list postgres_2023-09-18T09\:12\:44Z.dump 
...
216; 1259 16394 TABLE public pgbench_accounts postgres
217; 1259 16397 TABLE public pgbench_branches postgres
214; 1259 16388 TABLE public pgbench_history postgres
4275; 0 16394 TABLE DATA public pgbench_accounts postgres
4276; 0 16397 TABLE DATA public pgbench_branches postgres
4274; 0 16388 TABLE DATA public pgbench_history postgres

The version is :

pg_back -V
pg_back version 2.1.1

Same thing without double quote in the line.
Here is the command executed by pg_dump

/usr/bin/pg_dump -Fc -f /var/backups/postgresql/postgres_2023-09-18T09:48:06Z.dump -w -T pgbench_tellers -d application_name=pg_back dbname=postgres

Regards

hello,

how to create multiple pg_back.conf when we are multiple postgresql cluster ?

In some case of AccessExclusiveLocks on primary node, a internal loop waits for pausing replication when dumping on a standby node, with the following message:

The slave database has exclusive locks (vacuum full, truncate or other locking command) running on master
Resuming replication for 10s

(feature introduced in commit 3159de5)

If active session is still running during a large amount of time, it could be necessary to abort pg_back to avoid multiple executions on a daily routine, by adding a new parameter called PGBK_PAUSE_TIMEOUT to manage a properly exit of internal loop.

I tried to compile, I got the following error.
That disappeared after the install of go 1.17 (on Ubuntu 22.04).

The docker way of compiling has a similar problem.

/opt/pg_back2/pg_back2$ make 
go build -ldflags="-s -w" .
go build: when using gccgo toolchain, please pass linker flags using -gccgoflags, not -ldflags
# golang.org/x/sys/unix
/home/christ/go/pkg/mod/golang.org/x/[email protected]/unix/syscall.go:83:30: error: reference to undefined identifier ‘unsafe.Slice’
   83 |         return string(unsafe.Slice(p, n))
      |                              ^
/home/christ/go/pkg/mod/golang.org/x/[email protected]/unix/syscall_linux.go:2271:23: error: reference to undefined identifier ‘unsafe.Slice’
 2271 |         return unsafe.Slice((*byte)(unsafe.Pointer(uintptr(unsafe.Pointer(&fh.fileHandle.Type))+4)), n)
      |                       ^
/home/christ/go/pkg/mod/golang.org/x/[email protected]/unix/syscall_unix.go:118:21: error: reference to undefined identifier ‘unsafe.Slice’
  118 |         b := unsafe.Slice((*byte)(unsafe.Pointer(addr)), length)
      |                     ^
/home/christ/go/pkg/mod/golang.org/x/[email protected]/unix/sysvshm_unix.go:33:21: error: reference to undefined identifier ‘unsafe.Slice’
   33 |         b := unsafe.Slice((*byte)(unsafe.Pointer(addr)), int(info.Segsz))
      |                     ^
note: module requires Go 1.17
make: *** [Makefile:5 : pg_back] Erreur 2

Note: I don't know anything about go, just following the README

postgres@monolithe:~$ go get -u github.com/orgrim/pg_back
package embed: unrecognized import path "embed" (import path does not begin with hostname)

(Same problem on 2 Ubuntu 20.04 and 1 Debian 10.8 with gccgo-go)

PGBK_TIMESTAMP as a constant is useful when I need only ONE backup without a date.

1. With -Fc it works, but pg_back erases the previous backup while creating the new one. So in case of a failure, you have no backup anymore. This is not worse than a direct pg_dump, and this may be a feature if the backup space is scarse or backups are copied elsewhere. Perhaps does it need only a word in the documentation or a comment in pg_back.conf

2. More serious : with -Fd, this does not work. More precisely, it works once, and the 2nd time it fails this way:

$ sudo -iu postgres /opt/pg_back/pg_back -c /etc/postgresql/11/piggy/pg_back.conf 
2020-07-10 10:46:01 CEST  INFO: preparing to dump
2020-07-10 10:46:01 CEST  INFO: target directory is /PGDATA2/DUMPS/piggy
2020-07-10 10:46:01 CEST  INFO: listing databases
2020-07-10 10:46:01 CEST  INFO: dumping global objects into /PGDATA2/DUMPS/piggy/pg_global_X.sql
2020-07-10 10:46:01 CEST  INFO: saving output of SHOW ALL to /PGDATA2/DUMPS/piggy/pg_settings_X.out
2020-07-10 10:46:01 CEST  INFO: acquiring internal lock for postgres
2020-07-10 10:46:01 CEST  INFO: dumping database "postgres" into /PGDATA2/DUMPS/piggy/postgres_X.dump
pg_dump: [directory archiver] could not create directory "/PGDATA2/DUMPS/piggy/postgres_X.dump": File exists
2020-07-10 10:46:01 CEST  ERROR: pg_dump of database "postgres" failed
2020-07-10 10:46:01 CEST  INFO: acquiring internal lock for template1
2020-07-10 10:46:01 CEST  INFO: dumping database "template1" into /PGDATA2/DUMPS/piggy/template1_X.dump
pg_dump: [directory archiver] could not create directory "/PGDATA2/DUMPS/piggy/template1_X.dump": File exists
2020-07-10 10:46:01 CEST  ERROR: pg_dump of database "template1" failed
2020-07-10 10:46:01 CEST  INFO: purging old backups
2020-07-10 10:46:01 CEST  INFO: done

I didn't try to find the cause.

Config file:

$ sudo cat /etc/postgresql/11/piggy/pg_back.conf
# Configuration file for pg_back

# PostgreSQL binaries path. Leave empty to search $PATH
PGBK_BIN=/usr/lib/postgresql/11/bin

# Backup directory
PGBK_BACKUP_DIR=/PGDATA2/DUMPS/piggy

# The timestamp to add at the end of each dump file
#PGBK_TIMESTAMP='%Y-%m-%d_%H-%M-%S'
PGBK_TIMESTAMP='X'

# The time limit for old backups, in days
PGBK_PURGE=3

# The minimum number of backups to keep when purging or 'all' to keep
# everything (e.g. disable the purge)
PGBK_PURGE_MIN_KEEP=1

# Command-line options for pg_dump
# (Beware: on v11 and above, with "-Fp", you probably want to add "--create")
PGBK_OPTS="-Fd -Z9 -v"

# List of databases to dump (separator is space)
# If empty, dump all databases which are not templates
#PGBK_DBLIST="db1 db2"

# Exclude databases (separator is space)
PGBK_EXCLUDE="partitions_10000 partitions_none partitions_100 pgbench foreign_partitions_nombreuses"

# Include templates ("yes" or "no")
PGBK_WITH_TEMPLATES="yes"

# Connection options
PGBK_HOSTNAME=/var/run/postgresql
PGBK_PORT=11002
PGBK_USERNAME=postgres
PGBK_CONNDB=postgres

This is perfectly working configuration file for pg_back2 on Windows (2016):
(I had to use the long connection string to allow no-SSL connections, as SSL is not activated by default)

bin_directory = "C:\Program Files\PostgreSQL\13\bin"
dbname = " sslmode=disable host=localhost port=13666 user=postgres dbname=postgres password=yiu5"
backup_directory = "P:\dumps"
timestamp_format = legacy
purge_older_than = 1
purge_min_keep = 1
format = directory
pg_dump_options = -Z8 -j2
exclude_dbs = postgres, postgis_31_sample, postgres2
with_templates=true

[pgbench]
format = custom
pg_dump_options = '-Z9 -j1'

[template1]
format = plain

[template0]
format = custom
pg_dump_options = '-Z9 -j1'

Obviously I don't want the password in clear, so I tested but this does not work.

dbname = "sslmode=disable host=127.0.0.1 port=13666 user=postgres dbname=postgres passfile='p:/pg_back2/pgpass' "

All of these are perfectly fine connection strings that work, but fail with pg_back:

%P%\pg_dumpall  -g -d "sslmode=disable host=127.0.0.1 port=13666 user=postgres dbname=postgres passfile='p:/pg_back2/pgpass' "
%P%\pg_dumpall  -g -d "sslmode=disable host=127.0.0.1 port=13666 user=postgres dbname=postgresmd5 passfile='p:/pg_back2/pgpass' "
%P%\pg_dumpall  -g -d "postgresql://[email protected]:13666/postgres?sslmode=disable&passfile=p:/pg_back2/pgpass"

Errors are different, but it seems that the password are corrupted somewhere in the process:

P:\pg_back2>P:\pg_back2\pg_back.exe -v -c pg_back2.conf -d "postgresql://[email protected]:13666/postgres?sslmode=disable&password=yiu5" 
2021/04/03 19:02:59.893371 INFO: dumping globals
2021/04/03 19:02:59.893371 FATAL: pg_dumpall -g failed: illegal character in keyword

Instead of p:/pg_back2/pgpass, a pass.conf in %APPDATA%/postgresql does not work better.

I've tried quotes, no quotes, slash, antislash, nothing works.

It is not better when the target is an instance on Linux.

I've found no problem on pg_back2 for Linux.

I hope I've just made a stupid mistake.

Full debug log sent in private.

Hi,

It often happens that we'd like to copy the dump generated to an external location. It would be nice to have pre/post backup hooks to do that rather than having to build a complete script around pg_back.

Would it be something possible ?

Kind regards

Can we dump schemas seperately ?
Conf:
"# # List of schemas and tables to dump or exlude from the dump."
"# # Inclusion and exclusion rules of pg_dump apply, as well as"
"# # pattern rules. Separate schema/table names with a semicolon"
schemas = test
"# exclude_schemas ="

FATAL: could not validate pg_back.conf: unknown parameter in configuration file: schemas

Hi,

When querying the content of SHOW ALL, psql does not use the parameters from pg_back.conf.

So the pg_settings.out of my v12 instance contains ''server_version = 9.6.18'' because I have an old instance on 5432. Or querying SHOW ALL can fail because nothing runs on 5432.

Pull Request coming soon...

Document:

• use lagecy timestamp format in filenames:

D:\PGDATA\dump>pg_back.exe -b D:\PGDATA\dump\demo -p 55432 -h 127.0.0.1 -U postgres -d postgres 
2021/03/26 14:41:08 INFO: dumping globals
2021/03/26 14:41:08 ERROR: unrecognized win32 error code: 123pg_dumpall: erreur : n'a pas pu ouvrir le fichier de sauvegarde « D:\PGDATA\dump\demo\pg_globals_2021-03-26T14:41:08+01:00.sql » : Invalid argument
2021/03/26 14:41:08 FATAL: pg_dumpall -g failed: exit status 1

timestamp_format=legacy

• when putting backslahes in values of connection string, they must be doubled (see PostgreSQL docs: https://www.postgresql.org/docs/current/libpq-connect.html#LIBPQ-CONNSTRING)

Encrypting a single file is complicated, but it would be nice if pg_back could encrypt the dumps itself (GPG ?)

In an ideal case, the dump would not touch the disk un-encrypted (I have no idea how to deal with the directory format in this case).

• Big bytea fields (> a few 100MBs) cannot be exported (famous old limitation)
• pg_dumpbinary uses COPY ... TO ... (FORMAT binary) but has very few features.
• Is it possible that pg_back covers this case? A parameter may contain tables to be dumped with FORMAT binary. I suppose that coherence may be a problem (or use --snapshot ?) And it would need a separate restore script.

As mentioned in #33, a locked relation can block the execution pg_dump forever. We could end up with many pg_back processes stacking up if the script is schedule by cron or a similar tool.

A simple solution would be implement a per-database locking mecanism to avoid concurrent runs of pg_dump in this context.

Hi,
According to the doc, when PGBK_BIN is empty, it means search the PATH. In this particular case, the command pg_dumpacl is not detected:

You can test this yourself by using it with an other command:
PGBK_BIN=""; [ -x "${PGBK_BIN}ls" ] && echo "cmd found" || echo "cmd not found"

The test should use "command -v" to detect pg_dumpacl. I have forked the project and commit a correction here: tohtor@81e583e

Regards

Hi, like in subject add support for table and schema excludes per database.

Hello,

Requesting a signature (-S ) for a backup generated by the directory format does not work (file empty), and does not return an error.
Indeed, the command is executed on the directory and not on its files.

Regards

In addition to the N previous backups, it would be nice to keep N' weekly backups and N'' monthly backups, to be able to look back into the far past.

Hi, how can I use pg_back without backing up data?

I know this is a consequence of #24 . I open the issue because it may be useful to others with the same problem.

This does not work anymore :

PGBK_OPTS='-Fd -Z8 --jobs=2'

This seems to work:

PGBK_OPTS=("-Fd" "-Z8" "--jobs=2")

An error message to ease the upgrade would be nice (if no( is found in PGBK_OPTS, for example).

There is a "A list fo databases" in the README file. Should be "A list of databases".

Report from Alexandre Anriot. We guessed a PR would be a bit too much :)

the -Fd pg_dump option creates directories not files,
if the backup fails the
rm -f ${dump}
also fails, it should be changed to
rm -rf ${dump}
(as done in the cleanup section further below)

if the for example -T 'tmp*' is used in PGBK_OPTS, the table is not excluded, this only seems to work if eval is used.
instead of:
if ! ${PGBK_BIN}pg_dump $OPTS $PGBK_OPTS -f "${dump}" $db; then
this could work (not yet tested for all cases, also need to handle spaces in $dump)
if ! eval "${PGBK_BIN}pg_dump $OPTS $PGBK_OPTS -f ${dump} $db"; then