update db service to optionally use barman for recovery

singhalkarun commented 4 months ago

Setting up Postgres with SSH for Barman

ENABLE_BARMAN: true/false BARMAN_IP: (required if ENABLE_BARMAN is true) ID_RSA: (required if ID_RSA is true) IDA_RSA_PUB: (required if IDA_RSA_PUB is true)

Go into a container and run command to start ssh server in postgres

How to verify if this is working successfully?

Setting up Barman

setup-barman - Makefile target - triggers script to setup barman (anyone who wants to setup barman will clone repo and run this target)

singhalkarun commented 3 months ago

GJS2162 commented 3 months ago

barman@root:~$ tail -f /var/log/barman/barman.log
2024-08-20 06:17:01,856 [2410] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:17:01,858 [2410] barman.utils INFO: Cleaning up lockfiles directory.
2024-08-20 06:17:02,029 [2411] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:17:02,029 [2411] barman.wal_archiver INFO: No xlog segments found from file archival for mydb.
2024-08-20 06:18:02,095 [2414] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:18:02,097 [2414] barman.utils INFO: Cleaning up lockfiles directory.
2024-08-20 06:18:02,253 [2415] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:18:02,253 [2415] barman.wal_archiver INFO: No xlog segments found from file archival for mydb.
2024-08-20 06:18:06,616 [2416] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:18:06,618 [2416] barman.server ERROR: Check 'WAL archive' failed for server 'mydb'
2024-08-20 06:19:01,366 [2487] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:19:01,369 [2487] barman.utils INFO: Cleaning up lockfiles directory.
2024-08-20 06:19:01,586 [2488] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:19:01,587 [2488] barman.wal_archiver INFO: No xlog segments found from file archival for mydb.
2024-08-20 06:19:17,405 [2547] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
2024-08-20 06:19:17,405 [2547] barman.server ERROR: Check 'WAL archive' failed for server 'mydb'

and

barman@root:~$ barman check mydb
WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
Server mydb:
        WAL archive: FAILED (please make sure WAL shipping is setup)

Currently getting this but in the past was getting

WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
Server mydb:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: FAILED
directories: OK
retention policy settings: 0K
backup maximum age: OK (no last_backup_maximum_age provided)
backup minimum size: OK (0 B)
wal maximum age: OK (no last_wal_maximum_age provided)
wal size: OK (0 B)
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@mydb -p 2222 -0 BatchMode=yes -o StrictHostKeyChecking=no' r
55)
systemid coherence: OK (no system Id available)
archiver errors: OK

and

barman@root:~/devops$ tail -f /var/log/barman/barman.log
2024-08-12 13:35:23,252 [4788]
barman.server ERROR: Check 'PostgreSQL' failed for server 'mydb'
2024-08-12 13:35:23, 346
[4788]
Command WARNING: postgres@mydb: Permission denied (publickey, keyboard-interactive) •
2024-08-12 13:35:23,347
[4788]
barman.server ERROR: Check 'ssh' failed for server 'mydb'
2024-08-12 13:35:48,998
[4791]
barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_
backup').
2024-08-12 13:35:48,998 [4791] barman.server ERROR: Check 'WAL archive' failed for server 'mydb'
2024-08-12 13:35:49,015 [4791] barman.postgres WARNING: Error retrieving PostgreSQL status: connection to server at "mydb" (192.168
•1.26), port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.65.1", user "barman", database "company", no encryption
2024-08-12 13:35:49,039 [4791] barman.backup_executor WARNING: Error retrieving PostgreSQL status: connection to server at "mydb" (
192.168.1.26), port 5432 failed: FATAL: no pg_hba.conf entry for host "192.168.65.1", user "barman", database "company", no encryp tion
2024-08-12 13:35:49,040 [4791] barman.server ERROR: Check 'PostgreSQL' failed for server 'mydb'
2024-08-12
13:35:49,139
[4791]
Command WARNING: postgres@mydb: Permission denied (publickey, keyboard-interactive).
2024-08-12
13:35:49,139
[47911
barman.server ERROR: Check 'ssh' failed for server 'mydb'
2024-08-12 13:36:02,227 [4797] barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_
backup') •
2024-08-12 13:36:02,230
[4797] barman.utils INFO: Cleaning up lockfiles directory.
2024-08-12 13:36:02,397
[4798]
barman.backup_executor WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_
backup').
2024-08-12 13:36:02,397 [4798] barman.wal_archiver INFO: No xlog segments found from file archival for mydb.

singhalkarun commented 3 months ago

Use sleep 10000000 command to start container so that container won't restart.

GJS2162 commented 3 months ago

barman@root:/etc/barman.d$ barman check mydb
WARNING: No backup strategy set for server 'mydb' (using default 'concurrent_backup').
Server mydb:
        WAL archive: FAILED (please make sure WAL shipping is setup)
        PostgreSQL: OK
        superuser or standard user with backup privileges: OK
        wal_level: OK
        directories: OK
        retention policy settings: OK
        backup maximum age: OK (no last_backup_maximum_age provided)
        backup minimum size: OK (0 B)
        wal maximum age: OK (no last_wal_maximum_age provided)
        wal size: OK (0 B)
        compression settings: OK
        failed backups: OK (there are 0 failed backups)
        minimum redundancy requirements: OK (have 0 backups, expected at least 0)
        ssh: OK (PostgreSQL server)
        systemid coherence: OK (no system Id stored on disk)
        archive_mode: OK
        archive_command: OK
        continuous archiving: OK
        archiver errors: OK

Now only WAL archive: FAILED (please make sure WAL shipping is setup) needs to be fixed

GJS2162 commented 3 months ago

Commands:

1. Make deploy
2. Change key manually on Mac 
3. docker exec -it -u postgres 69cb5186beaf /usr/local/bin/postgres -D /var/lib/postgresql/data &
4. Use touch ~/.hushlogin
5. Change this “ssh -q  postgres@mydb -p 2222” in /etc/barman.d/mydb.conf 
6. Barman check mydb

singhalkarun commented 3 months ago

Scenarios

[ ] If both barman and db has public ip
[ ] If both barman and db only have private ip

Samagra-Development / devops

update db service to optionally use barman for recovery #40

Setting up Postgres with SSH for Barman

Setting up Barman