Cannot restart postgresql after recover if we put files on Amazon S3 or in archive

[dth...@telecomdesign.fr]

Hello,

after a backup, I tried to restore database, and that works only if I restore from local backup. When I put files on amazon S3, or in an archive, I cannot restart database from restored files. I start PostgreSQL with pg_ctl -w -D ./recover/server/ start.

I select files to put in archive with command :
$ barman list-file server latest
the trouble exist with target standalone and full. To not disrupt current backup, I created, for barman, a new server with name restored_server, copy /etc/barman.d/restored_server.conf from server.conf, just changing the name, and extract files in backup/restored_server. With commands :
$ barman list-backup restored_server
$ barman show-backup restored_server latest
I see the backup, and all seems correct. But after command
$ barman recover restored_server latest ./recover/server
it misses all wals : recover/server/pg_xlog is empty, it contains only empty directory archive_status, although backup/restored_server/wals doesn't. I manually copy them from the backup, but postgreSQL still does not start. Log says it misses the wal 00...xx, but if see the result of barman show-backup, the last wal is 00...xx-1.

I try to restore with PITR = END XLOG (get from show-backup), by removing recover/server/backup_label files, but my restored database doesn't start. I edited backup.info file to replace server_name by restored_server, same result, pg_xlog is still empty. I had also some other troubles : bad rights on few directories and files, and directory pg_tblspc doesn't exist, but I quickly fixed them.
The backup is correct : when I execute command
$ barman recover server ./recover/server
from the original backup, not the restored, postgreSQL start.

Is it possible to extract an archived backup in another directory that those used for backup, or should extract archive of backup files in same directory that they come ? For my try, I don't want disrupt the current backup by overwritten files with those of my archive...

Thanks for your help.

[Gabriele Bartolini]

Hi,

On Wed, 4 Dec 2013 08:30:48 -0800 (PST), dth...@telecomdesign.fr
wrote:

> backup/restored_server/wals doesn't. I manually copy them from the
> backup, but postgreSQL still does not start. Log says it misses the
> wal 00...xx, but if see the result of barman show-backup, the last
> wal
> is 00...xx-1.

Currently, Barman is missing the backup import/export feature.
Therefore, yes, only local (as in from Barman's standpoint) backup is
currently supported. We have added the list-files option so that you can
save backups and WAL files on external data sources, but then you might
need to know how PITR works - especially if you are using WAL
compression.

The first step of improving this, will be released in the next version.
We have added the XLOG database repair function - basically it scans all
WAL files that exist in a WAL archive for a server and rebuilds the XLOG
database. This way, I suspect, your process would work.

> Is it possible to extract an archived backup in another directory
> that those used for backup, or should extract archive of backup files
> in same directory that they come ? For my try, I don't want disrupt
> the current backup by overwritten files with those of my archive...

It is not currently possible, but it can be implemented. My idea was to
point to external sources, so that - for example - when a backup is out
of retention policies, it could be placed in a different location (tape,
S3, etc.). And when you type list-backup, you see those backups too. We
have not yet gone into much details for this feature, but if you are
interested in sponsoring its open source development, please contact us
privately.

Cheers,
Gabriele

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

[dth...@telecomdesign.fr]

I finally managed to restore my backup from Amazon S3 and restart PostgreSQL with restored data. Here the steps I followed :

• Get back files from Amazon S3 in a new directory inside barman backup directory
• Copy and rename configuration file of original server, and edit to change name
• Check backup with barman list-backup restored_server and barman show_backup restored_server latest
• Restore database : barman recover restored_server latest path/to/database
• Change rights on few files :
• chmod 700 path/to/database
• chmod 640 path/to/database/server.key
• Manually copy wal files :
• cp path/to/backup/restored_server/wals/00...xx/00xx* path/to/database/pg_xlog
• Eventually, uncompress files (adapt to your compression command) :
• for wal in `ls path/to/database/pg_xlog/00*`; do bzip2 -d -q path/to/database/pg_xlog/$wal; mv path/to/database/pg_xlog/$wal.out path/to/database/pg_xlog/$wal ; done
• create two folders :
• mkdir path/to/database/pg_tblspc
• mkdir path/to/database/pg_twophase
• edit postgresql.conf : according your configuration, change path for data_directory, hba_file, ident_file, external_pid_file and unix_socket_directory
• Start PostgreSQL :
• pg_ctl -w -D path/to/database start

I didn't try for a distant recovery with barman recover --remote-ssh-command ..., but I think there is not a lot of differences with this.

Denis

[Iñigo Quintana]

Hello.

Getting Barman catalog partial  copy from S3 and perform a recover seems to work this way too:

1) barman version:                             1.5.1-1.pgdg14.04+1              all          Backup and Recovery Manager for PostgreSQL

2) we sync the local Barma catalog into S3 this way to preserve files dates:  s3cmd sync -p /barman/localhost s3://${BUCKETNAME}/barman/ 

3) To get back the catalog into another AWS machine and be able perform the barman recover:

3.1)  Get files from S3  where $2 is the last base backup subfolder name, for example 20151209T130715 . In the scripts, we are only getting the files form S3 older than the last selected basebackup

 

#!/bin/bash

set -x # To debug the script

set -e # Enables checking of all commands. To execute it on production.

# Usage: get last barman backups from S3 to local -> getbarmanlastbackupinfo.sh "BUCKETNAME" "last backup subfolder in S3://bucketname/barman/localhost/base/20151125T093644"

# for example  getbarmanlastbackupinfo.sh "BUCKETNAME/barman/" "20151125T093644"

#

# Check this link inspiration http://shout.setfive.com/2011/12/05/deleting-files-older-than-specified-time-with-s3cmd-and-bash/

if [[ $2 ]]

then

s3cmd get -p --force s3://$1/localhost/base/$2/backup.info /tmp/

begintime=`grep begin_time /tmp/backup.info | cut -d'=' -f2 | cut -d':' -f1-2`

s3cmd --recursive ls s3://$1 | while read -r line;

do

createDate=`echo $line|awk {'print $1" "$2'}`

createDate=`date -d"$createDate" +%s`

#olderThan=`date -d"-$2" +%s`

#olderThan=$2

olderThan=`date -d"$begintime" +%s`

if [[ $createDate -ge $olderThan ]]

then 

fileName=`echo $line|awk {'print $4'}`

echo $fileName

if [[ $fileName != "" ]]

then

localname=`echo "$fileName" | cut -d'/' -f5-20`

s3cmd sync -p "$fileName" /barman/$localname

fi

fi

done;

else

echo 'Usage: getbarmanlastbackupinfo.sh "BUCKETNAME/barman/" "last backup subfolder name YYYYMMDDTHH24MMSS"'

echo 'see last backup subfolder name  in "s3://BUCKETNAME/barman/localhost/base/LASTBACKUP/" "foldername YYYYMMDDTHH24MMSS"'

echo "EXIT CODE 1"

fi

 3.2) Do the Barman recover (the postgresql database must be down)

Comment in postgresql.conf

 

#archive_mode = on  

#archive_command = 'rsync -a %p barman@localhost:/barman/localhost/incoming/%f'

#archive_timeout=600

 

barman rebuild-xlogdb localhost  <-- it is not even necessary

barman recover --remote-ssh-command="ssh postgres@localhost" --target-time "2015-12-09 14:52:59" localhost 20151209T130715 /var/lib/postgresql/9.3/main

 

Starting remote restore for server localhost using backup 20151209T130715

Destination directory: /var/lib/postgresql/9.3/main

Doing PITR. Recovery target time: '2015-12-09 14:52:59'

Copying the base backup.

Copying required WAL segments.

Generating recovery.conf

**** EXCEPTION: unable to create the archive_status directory '/var/lib/postgresql/9.3/main/pg_xlog/archive_status': mkdir execution failed*** 

 

 

 3.3) With postgres user:

Recovery after copyng from S3, it is mandatory to create these directories:

cd /var/lib/postgresql/9.3

chmod 700 main

cd /var/lib/postgresql/9.3/main

mkdir pg_tblspc

mkdir pg_xlog

mkdir pg_xlog/archive_status

mkdir pg_stat

mkdir pg_twophase

 3.4) service postgresql start

I have done it several times in works!!! 

Best regards

[Iñigo Quintana]

Hello again,

After reading this thread https://groups.google.com/forum/#!topic/pgbarman/Loib6IatXsk I have decided to improve the shell scripts wirh a Barman backup export "barman list-files localhost $LASTBASEBACKUP --target full"

1) Copy to S3 with a cron every 15 minutes, notice in the script both barman files + barman export are being sync

#!/bin/bash

set -x # To debug the script

set -e # Enables checking of all commands. To execute it on production.

# Syncronizes barman backups and WALs into S3

# Usage: barmansync.sh /var/log/nagiosbarmansync.log /var/log/barmansync.log

if [[ $2 ]]

then

NAGIOSLOG=$1

LOG=$2

> $NAGIOSLOG

BUCKETNAME=`grep SYNC_S3_BUCKET /etc/barman-backup.conf | cut -d'/' -f3`

DATETIME=`date +"%Y-%m-%d %T"`

echo "${DATETIME} Starting Barman sync" >> $LOG

LASTBASEBACKUP=`barman list-backup localhost | grep -v failed | head -1 | cut -d ' ' -f2`

barman list-files localhost $LASTBASEBACKUP --target full > /tmp/exp.$LASTBASEBACKUP

s3cmd sync -p /tmp/exp.$LASTBASEBACKUP s3://${BUCKETNAME}/barman/ >>$LOG

if [ $? -eq 0 ]; then

echo "${DATETIME} Barman export sync OK" >> $NAGIOSLOG

else

echo "${DATETIME} Barman export sync FAIL" >> $NAGIOSLOG

fi

s3cmd sync -p /barman/localhost s3://${BUCKETNAME}/barman/ >>$LOG

if [ $? -eq 0 ]; then

echo "${DATETIME} Barman sync OK" >> $NAGIOSLOG

else

echo "${DATETIME} Barman sync FAIL" >> $NAGIOSLOG

fi

DATETIME=`date +"%Y-%m-%d %T"`

echo "${DATETIME} Finishing Barman sync" >> $LOG

echo "******************************************" >> $LOG

echo " " >> $LOG

else

echo "Usage: barmansync.sh /var/log/nagiosbarmansync.log /var/log/barmansync.log"

echo "EXIT CODE 1"

fi

2) If your instance gets down and its not recoverable, launch a new amy and recover there the Barman PARTIAL catalog this way

#!/bin/bash

set -x # To debug the script

set -e # Enables checking of all commands. To execute it on production.

# Usage: get last barman backups from S3 to local -> getbarmanlastbackupexport.sh "BUCKETNAME" "last backup subfolder in S3://bucketname/barman/localhost/base/20151125T093644"

# for example  getbarmanlastbackupexport.sh "BUCKETNAME" "20151125T093644"

#

# Check this google groups inspiration https://groups.google.com/forum/#!topic/pgbarman/Loib6IatXsk)

if [[ $2 ]]

then

s3cmd get -p --force s3://$1/exp.$2 /tmp/

sed "s&/var/lib/barman/catalog/&&g" /tmp/exp.$2 > /tmp/exp.$2.barman 

for i in `cat /tmp/exp.$2.barman`

do

s3cmd get -p s3://$1/$i /barman/$i

done;

else

echo 'Usage: getbarmanlastbackupexport.sh "BUCKETNAME/barman/" "last backup subfolder name YYYYMMDDTHH24MMSS"'

echo 'see last backup subfolder name  in "s3://BUCKETNAME/barman/localhost/base/LASTBACKUP/" "foldername YYYYMMDDTHH24MMSS"'

echo "EXIT CODE 1"

fi

3) Do the Barman recover (the postgresql database must be down)

Comment in postgresql.conf

 

#archive_mode = on  

#archive_command = 'rsync -a %p barman@localhost:/barman/localhost/incoming/%f'

#archive_timeout=600

 

barman rebuild-xlogdb localhost 

barman recover --remote-ssh-command="ssh postgres@localhost" --target-time "2015-12-09 14:52:59" localhost 20151209T130715 /var/lib/postgresql/9.3/main

 

Starting remote restore for server localhost using backup 20151209T130715

Destination directory: /var/lib/postgresql/9.3/main

Doing PITR. Recovery target time: '2015-12-09 14:52:59'

Copying the base backup.

Copying required WAL segments.

Generating recovery.conf

**** EXCEPTION: unable to create the archive_status directory '/var/lib/postgresql/9.3/main/pg_xlog/archive_status': mkdir execution failed*** 

 

 

4) With postgres user:

Recovery after copyng from S3, it is mandatory to create these directories:

cd /var/lib/postgresql/9.3

chmod 700 main

cd /var/lib/postgresql/9.3/main

mkdir pg_tblspc

mkdir pg_xlog

mkdir pg_xlog/archive_status

mkdir pg_stat

mkdir pg_twophase

5) service postgresql start

Best regards

[Mohammad Mahzoun]

I get this error:

postgresql error PANIC: could not locate a valid checkpoint record

how should i solve it?

I restore it on another server.