RE: Postgres Point in time Recovery (PITR),

Hello,

I am trying to setup barman for my test environment with the following steps but I m not able to start the backup. Please advice !

Postgres 11.2 streaming replication on docker container , Centos
Server A : pg1 (primary)
Server B : pg2 (Standby)

I am using pg2 as a Barman server. Barman 2.8 is installed on pg2
Also I have installed " yum install barman-cli-2.8-1.el6.noarch.rpm" on pg1 (postgres server) for 'barman-wal-archive.

@pg1

1 . Connect on the server pgsql and log into the postgres account:
sudo -i -u postgres
2 . createuser --interactive -P barman
3 . createuser barman
4 . Edit postgresql.conf , for
listen_addresses = '*' and sudo service postgresql restart
wal_level = replica
archive_mode = on
archive_command = 'barman-wal-archive pg2 pgsql %p'
5. host all all pg2/32 trust

@pg2 (Barman Server)
1 . switch to user barman and generate the keys: ssh-keygen -t rsa
2 . Copy the key to the user account postgres on pgsql: ssh-copy-id postgres(at)pg1
3 . Barman also requires access to the postgres account on the server pg2. Copy the key into the directory of the postgres user and test the connection:

ssh-copy-id postgres(at)localhost
ssh postgres(at)localhost -C true

4 . Once this is done, log in as postgres user on pg1and generate an SSH key: ssh-keygen -t rsa

5 . Copy the generated key to the list of authorized keys of the user barman on pg2: ssh-copy-id barman(at)pg2

6 . Test the connection to the server:

@barmaner server /etc/barman.d/pgsql.conf

[test]
; Human readable description
description = "Example of PostgreSQL Database (via SSH)"

ssh_command = ssh postgres(at)pg1
conninfo = host=pg1 user=barman dbname=postgres
backup_method = rsync
;reuse_backup = link
; Identify the standard behavior for backup operations: possible values are
; exclusive_backup (default), concurrent_backup
; concurrent_backup is the preferred method with PostgreSQL >= 9.6
backup_options = exclusive_backup

; Number of parallel workers to perform file copy during backup and recover
;parallel_jobs = 1
archiver = on
;archiver_batch_size = 50

barman(at)vipostgres-db-test:/etc/barman.d$ barman check pgsql
WARNING: No backup strategy set for server 'pgsql' (using default 'exclusive_backup').
WARNING: The default backup strategy will change to 'concurrent_backup' in the future. Explicitly set 'backup_options' to silence this warning.
Server pgsql:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: FAILED
directories: OK
retention policy settings: OK
backup maximum age: OK (no last_backup_maximum_age provided)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: OK (have 0 backups, expected at least 0)
ssh: FAILED (Connection failed using 'ssh postgres(at)vipostgres-cn-test -o BatchMode=yes -o StrictHostKeyChecking=no' return code 255)
not in recovery: OK
pg_receivexlog: FAILED
pg_receivexlog compatible: FAILED (PostgreSQL version: None, pg_receivexlog version: None)
receive-wal running: FAILED (See the Barman log file for more details)
archiver errors: OK

NOte: If I try to login mannulay on postgres from barman then it is aking for password :

ssh postgres(at)pg1
Failed to add the host to the list of known hosts (/tmp/.ssh/known_hosts).
========================== Authorized Access Notice ==================================================
This computer system is the property of Exponential Interactive Inc. Activities are actively monitored
and unauthorized access or use of this computer system is prohibited.
======================================================================================================
Password:

Thanks,

-----Original Message-----
From: Andreas Kretschmer <andreas(at)a-kretschmer(dot)de>
Sent: Friday, October 18, 2019 12:38 PM
To: pgsql-general(at)lists(dot)postgresql(dot)org; Daulat Ram <Daulat(dot)Ram(at)exponential(dot)com>; pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Re: Postgres Point in time Recovery (PITR),

On 18 October 2019 07:59:21 CEST, Daulat Ram <Daulat(dot)Ram(at)exponential(dot)com> wrote:
>Hello All,
>Can you please share some ideas and scenarios how we can do the PITR in
>case of disaster.
>
>
>Thanks,

Consider Barman.

--
2ndQuadrant - The PostgreSQL Support Company