Can not restore to specific time with a remote barman backup server
1,408 views
Skip to first unread message
Simon Iong
Jul 9, 2014, 10:30:32 AM7/9/14
to pgba...@googlegroups.com
Hi, All:
I setup a remote server, and install barman in that server, keep it simple, just call it "barman", and I have postgres database located in another VM, call "syb-db". I have already setup everything as Document section mentions. and backup is working too.
What I try to do is to restore "postgres" instance back to a specific time. Let's have more detail here:
"Time in +8 UTC" | "My Action"
21:45:51 | Last time backup
21:46:54 | I did some change to Table A
21:48:00 | accidentally drop table B
As 21:48:00 is an accident, I manage to restore back to the time after change to Table A.
And this database is low traffic, after drop table B, and at time around 21:49, I issue a restore command:
>barman recover --target-time="2014-07-09 21:47:00.000" --remote-ssh-command="ssh -p 223 postgres@stg-db" syb-db 20140709T214551 /var/lib/postgresql/9.3/main
Starting remote restore for server syb-db using backup 20140709T214551Destination directory: /var/lib/postgresql/9.3/mainDoing PITR. Recovery target time: '2014-07-09 21:47:00'Copying the base backup.Copying required wal segments.Generating recovery.confThe archive_command was set to 'false' to prevent data losses.Your PostgreSQL server has been successfully prepared for recovery!Please review network and archive related settings in the PostgreSQLconfiguration file before starting the just recovered instance.WARNING: Before starting up the recovered PostgreSQL server,please review also the settings of the following configurationoptions as they might interfere with your current recovery attempt:data_directory = '/var/lib/postgresql/9.3/main'external_pid_file = '/var/run/postgresql/9.3-main.pid'hba_file = '/etc/postgresql/9.3/main/pg_hba.conf'ident_file = '/etc/postgresql/9.3/main/pg_ident.conf'ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
Since I know there's a bug, which cause rsync recovery.conf remotely failed, so I manage to create one in postgres VM, just paste the content of recovery.conf below:
#recovery.conf
restore_command = 'cp barman_xlog/%f %p'recovery_end_command = 'rm -fr barman_xlog'recovery_target_time = '2014-07-09 21:47:00.000'
And then I try to start the postgresql server.
But Table B still DROP!!!
I manage to figure out what happend, try to look up the log located in /var/log/postgresql/postgresql-9.3-main.log
note: The log timestamp is using UTC time
2014-07-09 13:56:37 GMT LOG: database system is shut down2014-07-09 14:05:42 GMT LOG: database system was interrupted; last known up at 2014-07-09 13:45:51 GMT2014-07-09 14:05:42 GMT LOG: creating missing WAL directory "pg_xlog/archive_status"2014-07-09 14:05:42 GMT LOG: starting point-in-time recovery to 2014-07-09 21:47:00+002014-07-09 14:05:42 GMT LOG: restored log file "000000010000000000000011" from archive2014-07-09 14:05:42 GMT LOG: redo starts at 0/110000902014-07-09 14:05:42 GMT LOG: consistent recovery state reached at 0/110000B82014-07-09 14:05:42 GMT LOG: restored log file "000000010000000000000012" from archivecp: cannot stat 'barman_xlog/000000010000000000000013': No such file or directory2014-07-09 14:05:42 GMT LOG: redo done at 0/120108B02014-07-09 14:05:42 GMT LOG: last completed transaction was at log time 2014-07-09 13:48:15.315947+002014-07-09 14:05:42 GMT LOG: restored log file "000000010000000000000012" from archivecp: cannot stat 'barman_xlog/00000002.history': No such file or directory2014-07-09 14:05:42 GMT LOG: selected new timeline ID: 2cp: cannot stat 'barman_xlog/00000001.history': No such file or directory2014-07-09 14:05:42 GMT LOG: incomplete startup packet2014-07-09 14:05:42 GMT LOG: archive recovery complete2014-07-09 14:05:43 GMT FATAL: the database system is starting up2014-07-09 14:05:43 GMT LOG: database system is ready to accept connections2014-07-09 14:05:43 GMT LOG: autovacuum launcher started
For someone may need the output of #barman diagnose
{
"global": {
"config": {
"barman_home": "/var/lib/barman",
"barman_user": "barman",
"log_file": "/var/log/barman/barman.log"
},
"system_info": {
"kernel_ver": "Linux barman.example.com 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux",
"python_ver": "Python 2.7.6",
"release": "Distributor ID:\tUbuntu\nDescription:\tUbuntu 14.04 LTS\nRelease:\t14.04\nCodename:\ttrusty",
"rsync_ver": "rsync version 3.1.0 protocol version 31",
"ssh_ver": "usage: ssh [-1246AaCfgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]"
}
},
"servers": {
"syb-db": {
"backups": {
"20140709T010608": {
"backup_id": "20140709T010608",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 01:06:08 2014",
"begin_wal": "000000010000000000000002",
"begin_xlog": "0/2000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": null,
"end_time": null,
"end_wal": null,
"end_xlog": null,
"error": "failure issuing stop of the backup (concurrent exclusive backups are not allowed)",
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27580585,
"status": "FAILED",
"tablespaces": null,
"timeline": 1,
"version": 90304
},
"20140709T011153": {
"backup_id": "20140709T011153",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 01:11:53 2014",
"begin_wal": "000000010000000000000005",
"begin_xlog": "0/5000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": null,
"end_time": null,
"end_wal": null,
"end_xlog": null,
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27580596,
"status": "STARTED",
"tablespaces": null,
"timeline": 1,
"version": 90304
},
"20140709T011343": {
"backup_id": "20140709T011343",
"backup_label": "None",
"begin_offset": null,
"begin_time": null,
"begin_wal": null,
"begin_xlog": null,
"config_file": null,
"end_offset": null,
"end_time": null,
"end_wal": null,
"end_xlog": null,
"error": "failure detecting data directory (could not connect to server: Connection refused)",
"hba_file": null,
"ident_file": null,
"mode": "default",
"pgdata": null,
"server_name": "syb-db",
"size": null,
"status": "FAILED",
"tablespaces": null,
"timeline": null,
"version": null
},
"20140709T011749": {
"backup_id": "20140709T011749",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 01:17:49 2014",
"begin_wal": "000000010000000000000007",
"begin_xlog": "0/7000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": null,
"end_time": null,
"end_wal": null,
"end_xlog": null,
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27587192,
"status": "STARTED",
"tablespaces": null,
"timeline": 1,
"version": 90304
},
"20140709T012650": {
"backup_id": "20140709T012650",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 01:26:50 2014",
"begin_wal": "00000001000000000000000B",
"begin_xlog": "0/B000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": "184",
"end_time": "Wed Jul 9 01:26:56 2014",
"end_wal": "00000001000000000000000B",
"end_xlog": "0/B0000B8",
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27580601,
"status": "DONE",
"tablespaces": null,
"timeline": 1,
"version": 90304
},
"20140709T111913": {
"backup_id": "20140709T111913",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 11:19:13 2014",
"begin_wal": "00000001000000000000000D",
"begin_xlog": "0/D000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": "184",
"end_time": "Wed Jul 9 11:19:19 2014",
"end_wal": "00000001000000000000000D",
"end_xlog": "0/D0000B8",
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27587197,
"status": "DONE",
"tablespaces": null,
"timeline": 1,
"version": 90304
},
"20140709T132819": {
"backup_id": "20140709T132819",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 13:28:19 2014",
"begin_wal": "00000001000000000000000E",
"begin_xlog": "0/E000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": "184",
"end_time": "Wed Jul 9 13:28:25 2014",
"end_wal": "00000001000000000000000E",
"end_xlog": "0/E0000B8",
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27551277,
"status": "DONE",
"tablespaces": null,
"timeline": 1,
"version": 90304
},
"20140709T205534": {
"backup_id": "20140709T205534",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 20:55:34 2014",
"begin_wal": "000000020000000000000010",
"begin_xlog": "0/10000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": "184",
"end_time": "Wed Jul 9 20:55:40 2014",
"end_wal": "000000020000000000000010",
"end_xlog": "0/100000B8",
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27567304,
"status": "DONE",
"tablespaces": null,
"timeline": 2,
"version": 90304
},
"20140709T214551": {
"backup_id": "20140709T214551",
"backup_label": "None",
"begin_offset": "40",
"begin_time": "Wed Jul 9 21:45:51 2014",
"begin_wal": "000000010000000000000011",
"begin_xlog": "0/11000028",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"end_offset": "184",
"end_time": "Wed Jul 9 21:45:56 2014",
"end_wal": "000000010000000000000011",
"end_xlog": "0/110000B8",
"error": null,
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"mode": "default",
"pgdata": "/var/lib/postgresql/9.3/main",
"server_name": "syb-db",
"size": 27434875,
"status": "DONE",
"tablespaces": null,
"timeline": 1,
"version": 90304
}
},
"config": {
"active": true,
"backup_directory": "/var/lib/barman/syb-db",
"backup_options": "exclusive_backup",
"bandwidth_limit": null,
"barman_home": "/var/lib/barman",
"basebackups_directory": "/var/lib/barman/syb-db/base",
"compression": null,
"conninfo": "host=stg-db user=postgres port=2346",
"custom_compression_filter": null,
"custom_decompression_filter": null,
"description": "Main PostgreSQL Database For Suiyinbao",
"immediate_checkpoint": false,
"incoming_wals_directory": "/var/lib/barman/syb-db/incoming",
"lock_file": "/var/lib/barman/syb-db/syb-db.lock",
"minimum_redundancy": 1,
"name": "syb-db",
"network_compression": false,
"post_backup_script": null,
"pre_backup_script": null,
"retention_policy": "window 4",
"retention_policy_mode": "auto",
"ssh_command": "ssh postgres@stg-db -p 223",
"tablespace_bandwidth_limit": null,
"wal_retention_policy": "simple-wal 4",
"wals_directory": "/var/lib/barman/syb-db/wals"
},
"status": {
"archive_command": "rsync -a -e \"ssh -p 223\" %p barman@barman:/var/lib/barman/syb-db/incoming/%f",
"archive_mode": "on",
"config_file": "/etc/postgresql/9.3/main/postgresql.conf",
"current_xlog": "000000020000000000000013",
"data_directory": "/var/lib/postgresql/9.3/main",
"hba_file": "/etc/postgresql/9.3/main/pg_hba.conf",
"ident_file": "/etc/postgresql/9.3/main/pg_ident.conf",
"last_shipped_wal": "000000010000000000000012",
"pgespresso_installed": false,
"server_txt_version": "9.3.4"
},
"system_info": {
"kernel_ver": "Linux stg-db.abc.info 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 UTC 2014 x86_64 x86_64 x86_64 GNU/Linux",
"python_ver": "Python 2.7.6",
"release": "Distributor ID:\tUbuntu\nDescription:\tUbuntu 14.04 LTS\nRelease:\t14.04\nCodename:\ttrusty",
"rsync_ver": "rsync version 3.1.0 protocol version 31",
"ssh_ver": "usage: ssh [-1246AaCfgKkMNnqsTtVvXxYy] [-b bind_address] [-c cipher_spec]"
}
}
}
}
Simon Iong
Jul 18, 2014, 6:37:28 AM7/18/14
to pgba...@googlegroups.com
After I few days of investigation, I found that there's should be a issue from my Time Zone setting in barman recover command,
My time zone is UTC+8, and default postgres database time zone is "GMT",
So after upgrade barman to version 1.3.3.alpha.1, and change the target-time in GMT time zone, It fix everything! Thanks again!
>barman recover --target-time="2014-07-09 21:47:00.000" --remote-ssh-command="ssh -p 223 postgres@stg-db" syb-db 20140709T214551 /var/lib/postgresql/9.3/main
>barman recover --target-time="2014-07-09 13:47:00 GMT" --remote-ssh-command="ssh -p 223 postgres@stg-db" syb-db 20140709T214551 /var/lib/postgresql/9.3/main
harish guntur
Feb 27, 2020, 6:12:15 PM2/27/20
to Barman, Backup and Recovery Manager for PostgreSQL
Thanks, Simon, in my case I have to convert the time myself then the restore is succsesfull! Thanks much for the hint!
Reply all
Reply to author
Forward
0 new messages