I have a question about restoring with old backup files.

[fkdl...@gmail.com]

Hi all,

I have a question about restoring with barman.

 

I set up barman for  backup on live server.

And I also set up replication with hot_standby mode on replication server.

Finally, the wal files for Archive is stored on backup server and I back up database on backup server.

So, there are 3 servers.

 

configure in Live server(127.0.0.1 = backup server):

wal_level = ‘hot_standby’

archive_mode = on

archive_command = ‘rsync -a %p bar...@127.0.0.1:/var/lib/barman/main/incoming/%f ’

 

I wanted to restore the old backup file(02/10/15) is located on back server to backup server.

So, I installed postgres 9.4 on backup server and tried to restore with barman.

 

The steps for restoring on backup server:

Step1)

barman recover main 20150210T184424 /var/lib/postgresql/9.4/main

Step2)

--## recovery.conf

restore_command = 'cp /var/lib/postgresql/9.4/main/pg_xlog/%f %p'

recovery_target_time = '2015-02-10 18:44:30 UTC'    --##) end_time=2015-02-10 18:44:43

Step3)

--## backup_label

…..

begin_time=2015-02-10 18:44:24.779381

begin_wal=000000010000002000000036

…..

 

cp /var/lib/barman/main/wals/0000000100000020/000000010000002000000036 /var/lib/postgresql/9.4/main/pg_xlog/

Step4)

service postgresql start

 

And then I got the message below.

-------------------------------------------------------------------------------------------------------------------------------------

2015-03-19 05:03:51 UTC [42871-1] LOG:  database system was interrupted; last known up at 2015-02-10 18:44:24 UTC

2015-03-19 05:03:51 UTC [42871-2] LOG:  starting point-in-time recovery to 2015-02-10 18:44:30+00

2015-03-19 05:03:51 UTC [42871-3] LOG:  invalid magic number D075 in log segment 000000010000002000000036, offset 0

2015-03-19 05:03:51 UTC [42871-4] LOG:  invalid primary checkpoint record

2015-03-19 05:03:51 UTC [42871-5] LOG:  invalid secondary checkpoint record

2015-03-19 05:03:51 UTC [42871-6] PANIC:  could not locate a valid checkpoint record

2015-03-19 05:03:51 UTC [42870-1] LOG:  startup process (PID 42871) was terminated by signal 6: Aborted

2015-03-19 05:03:51 UTC [42870-2] LOG:  aborting startup due to startup process failure

-------------------------------------------------------------------------------------------------------------------------------------

 

And I tried to restore the 20150211T182603 backup file with command below.

---------------------------------------------------------------------------------------------------------

barman recover main 20150211T182603 /var/lib/postgresql/9.4/main

service postgresql start

---------------------------------------------------------------------------------------------------------

I completed without any error.

 

Difference between first and second is

In first case, there was no wal file is needed for restoring (000000010000002000000036)  

in pg_xlog after restoring.

So I copied wal file to pg_xlog.

In second case, there is wal file file is needed for restoring in pg_xlog after restoring.

So I didn’t copy any wal file.

 

I’m wondering why second case failed and how I can restore without any error.

 

Additionally, I tried with pg_resetxlog.

After installing postgres-xc for pg_resetxlog, postgres was down.

When I try command “server postgresql start” It was not working. There was no reaction.

I don’t know why.

 

Could you guys help me?

 

Thanks,

[Gabriele Bartolini]

Hello,

  could you please provide the 'end_time' of the backup you are failing to restore?

Thanks,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support
 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

--
--
You received this message because you are subscribed to the "Barman for PostgreSQL" group.
To post to this group, send email to pgba...@googlegroups.com
To unsubscribe from this group, send email to
pgbarman+u...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/pgbarman?hl=en?hl=en-GB

---
You received this message because you are subscribed to the Google Groups "Barman, Backup and Recovery Manager for PostgreSQL" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pgbarman+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[fkdl...@gmail.com]

Hi Gabriele,

Nice to meet you :)

Here is the backup info

I tried to restore with recovery_target_time = '2015-02-10 18:44:30 UTC'   in recovery.conf

backup.info

--------------------------------------------------------------------------------------------------------

begin_offset=40

begin_time=2015-02-10 18:44:24.779381

begin_wal=000000010000002000000036

begin_xlog=20/36000028

config_file=/etc/postgresql/9.4/main/postgresql.conf

end_offset=296

end_time=2015-02-10 18:44:43.627352

end_wal=000000010000002000000036

end_xlog=20/36000128

error=None

hba_file=/etc/postgresql/9.4/main/pg_hba.conf

ident_file=/etc/postgresql/9.4/main/pg_ident.conf

mode=default

pgdata=/var/lib/postgresql/9.4/main

server_name=main

size=934097788

status=DONE

tablespaces=None

timeline=1

version=90401

--------------------------------------------------------------------------------------------------------

backup_label

--------------------------------------------------------------------------------------------------------

START WAL LOCATION: 20/36000028 (file 000000010000002000000036)

CHECKPOINT LOCATION: 20/36000060

BACKUP METHOD: pg_start_backup

BACKUP FROM: master

START TIME: 2015-02-10 18:44:24 UTC

LABEL: Barman backup main 20150210T184424

--------------------------------------------------------------------------------------------------------

Thanks,

[Gabriele Bartolini]

Hello,

  nice to meet you too. Mistery solved.

  End time of the backup is higher than the requested recovery time, therefore Postgres cannot reach a consistent state. With that backup you can recover at any time from the end time (2015-02-10 18:44:43.627352) to the latest available WAL file.

  I hope this helps.

Thanks,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support
 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

--

[fkdl...@gmail.com]

Hi Gabriele,

Thank you for your reply.

You mean do I have to recover from the end time  (2015-02-10 18:44:43.627352).

I tried to recovery with following paramter value in recovery.conf

recovery.conf

restore_command = 'cp /mnt/disk1/postgresql/9.4/main/pg_xlog/%f %p'

recovery_target_time = '2015-02-10 18:45:00 UTC'

And I still got the message below.

--------------------------------------------------------------------------------------------------------------------------------------

* Starting PostgreSQL 9.4 database server                                                                                                                             

 * The PostgreSQL server failed to start. Please check the log output:

2015-03-24 21:44:16 UTC [18851-1] LOG:  database system was interrupted; last known up at 2015-02-10 18:44:24 UTC

2015-03-24 21:44:16 UTC [18851-2] LOG:  starting point-in-time recovery to 2015-02-10 18:45:00+00

2015-03-24 21:44:16 UTC [18851-3] LOG:  invalid magic number D075 in log segment 000000010000002000000036, offset 0

2015-03-24 21:44:16 UTC [18851-4] LOG:  invalid primary checkpoint record

2015-03-24 21:44:16 UTC [18851-5] LOG:  invalid secondary checkpoint record

2015-03-24 21:44:16 UTC [18851-6] PANIC:  could not locate a valid checkpoint record

2015-03-24 21:44:16 UTC [18850-1] LOG:  startup process (PID 18851) was terminated by signal 6: Aborted

2015-03-24 21:44:16 UTC [18850-2] LOG:  aborting startup due to startup process failure

----------------------------------------------------------------------------------------------------------------------------------------

Thanks,

Show trimmed content

[fkdl...@gmail.com]

Other recipients:

Hi Gabriele, Thank you for your reply. You mean do I have to recover from the end time (2015-02-10 18:44:43.627352). I tried to recovery with following paramter value in recovery.conf recovery.conf restore_command = 'cp /mnt/disk1/postgresql/9.

Hi Gabriele,

I solved this problem.

The backup file had a problem.

We upgraded from 9.3 to 9.4 during this time.

Thanks,