Recovery of tablespaces on remote server

[gap]

Hi,

i want to recover my db server on a remote host (which will be my slave in future). I prepared everything (ssh keys on both hosts) and started the remote recovery with this command:

barman recover --remote-ssh-command "ssh postgres@remotehost" SERVERNAME BACKUPID /var/lib/pgsql/data

I get an error saying the tablespaces could not be created:
unable to prepare 'MYTABLESPACE' tablespace
mkdir execution failed

The tablespaces directory is in /psql/data in a new directory (for future data separation due to performance improvements). I assumed postgres is the user who should be able to create new directories for tablespaces? Don't know what raises the error.

Thanks for help and advice,
Gaby 

 

[gap]

Hi,

solution for this problem is to add -p to mkdir command in /barman/fs.py file.

regards,
gaby

[gap]

So, with the solution to create the tablespaces by addind -p to the mkdir line (ca line 79 in /barman/fs.py) worked for the restoring process, but the symbolic couldn't be created. So that my /var/lib/pgsql/data/tablespaces directory is empty and all data is written into pg_tblspc instead. Still have to work out how to use the barman restore correctly to create symbolic links to my tablespaces.

Any hints?

Thanks,
Gaby

[gap]

Hi,
unfortunately, I still couldn't find a proper solution for restoring my database server on a remote host with many tablespaces located in the data directory.

I just found:" Version 1.3.1 fixes an important bug on recovery that was affecting only those users having tablespaces created inside the PGDATA directory. This behaviour was introduced in version 1.3.0."
I am using barman 1.3.3, so that the issue should be solved. When using the --tablespace option in the recovery command I get following error:
barman: error: unrecognized arguments:

I have more than one tablespace which I want to restore, so when using --tablespace I just added all pairs (NAME:LOCATION) comma-separated. I used the same list of tablespaces I get from the output:
barman list_backup MYSERVER

Are there any suggestions what I am doing wrong or should consider? Any help is really appreciated!

regards,
Gaby

[gap]

Hi,

I tested different scenarios for recovery on remote host:

1. without tablespaces:

→ works fine. Creates all subdirectories.

2. tablespace test1 in postgresql data directory but withput being specified in the recovery command:

→ not working, data directory empty

error message:

unable to prepare 'test1' tablespace (destination '/var/lib/pgsql/data/tablespaces/test1'): mkdir execution failed

Traceback (most recent call last):

File "/usr/lib64/python2.6/site-packages/barman/backup.py", line 433, in recover

cmd.create_dir_if_not_exists(location)

File "/usr/lib64/python2.6/site-packages/barman/fs.py", line 79, in create_dir_if_not_exists

raise FsOperationFailed('mkdir execution failed')

FsOperationFailed: mkdir execution failed

3. tablespace test1 in postgresql data directory with being specified in the recovery command:

a) barman recover --remote-ssh-command "ssh postgres@REMOTEHOST" main BACKUPID /var/lib/pgsql/data --tablespace test1:/var/lib/pgsql/data/tablespaces/test1

→ not working, data directory empty

error message:

unable to prepare 'test1' tablespace (destination '/var/lib/pgsql/data/tablespaces/test1'): mkdir execution failed

Traceback (most recent call last):

File "/usr/lib64/python2.6/site-packages/barman/backup.py", line 433, in recover

cmd.create_dir_if_not_exists(location)

File "/usr/lib64/python2.6/site-packages/barman/fs.py", line 79, in create_dir_if_not_exists

raise FsOperationFailed('mkdir execution failed')

FsOperationFailed: mkdir execution failed

b) barman recover --tablespace test1:/var/lib/pgsql/data/tablespaces/ --remote-ssh-command "ssh postgres@REMOTEHOST" main BACKUPID /var/lib/pgsql/data

→ data directory created and tablespace (empty) directory but no symlink in pg_tblspc , data directly in pg_tblc

message:

Destination directory: /var/lib/pgsql/data

24646, test1, /var/lib/pgsql/data/tablespaces/

4. Tablespace "test3" not in postgres home data directory, but in a separate directory with being specified in the recovery command:

→ data directory created and tablespace (empty) directory but no symlink in pg_tblspc , data directly in pg_tblc

message:

barman recover --tablespace test3:/data/tablespaces/test3 --remote-ssh-command "ssh postgres@REMOTEHOST" main BACKUPID /var/lib/pgsql/data

Starting remote restore for server main using backup BACKUPID

Destination directory: /var/lib/pgsql/data

unable to prepare 'test3' tablespace (destination '/data/tablespaces/test3'): mkdir execution failed

Its a bit disappointing. I'm posting now the 5th message about the problem I got and didn't receive any response of the forum admins or developers. I really would like to use the tool especially with the new incremental backup in 1.4.0, but I couldn't fix my issues with remote recovery of tablespaces. It seems the problem is not only accurring when tablespaces are put in postgres home directory. The recovery command can't put symlinks in teh pg_tblspc but rather puts all data there.

Again: I am running on SLES11SP3 with barman 1.3.3, maybe its some dependencies which are not correct? Could someone help me with this?

Cheers,
gaby

[gap]

so, finally...my problem was really OS depending. I already had problems with rsync and had to refine the package for barman backups. I should have realized earlier that the recovery could be depending on it too and that I need to install rsync_myversion on the remote host as well.

now the tablespaces are symlinked and everything works fine.

cheers,
gaby

[Giulio Calacoci]

Hi Gaby,

I'm Happy you have solved your problem.

As you realized all the local and remote copy operations are rsync dependent.

I've opened a bug on Suse Bugzilla for this issue (you can find it here: https://bugzilla.suse.com/show_bug.cgi?id=898513),
they accepted the report as bug, anyway I don't think that the problem will be fixed soon.

Actually I suggest you to install your version of the rsync package on all the servers you are planning to use barman with,
especially if you want take advantage of the new incremental backup feature.

Incremental backup relies heavily on rsync and you want to be sure that rsync works as expected.

Regards
Giulio

--

--

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.

-- 
 Giulio Calacoci - 2ndQuadrant Italia
 PostgreSQL Training, Services and Support
 giulio....@2ndQuadrant.it | www.2ndQuadrant.it 

[gap]

Hi,

yes, I will install it on all servers and start testing the new features with the adapted rsync and hope it will work fine.
Thanks for the bug report.

cheers,
gaby

[gap]

Hi,

unfortunately I still can't recover tablespaces in the postgresql home directory. I know it doesn't make a lot of sense putting tablespaces in this directory but at the moment we have some servers with this setup already in production. I wrote before that barman was fixed for this special case with release 1.3.1. So I guess its SUSE-specific and related to rsync as well?

The error message is:
EXCEPTION: unable to prepare 'test1' tablespace (destination '/var/lib/pgsql/data/tablespaces/test1'): mkdir execution failed

barman.log:
2015-02-20 12:23:58,596 [2431] barman.backup ERROR: unable to prepare 'test1' tablespace (destination '/var/lib/pgsql/data/tablespaces/test1'): mkdir execution failed

Traceback (most recent call last):

  File "/usr/lib64/python2.6/site-packages/barman/backup.py", line 452, in recover

    cmd.create_dir_if_not_exists(location)
  File "/usr/lib64/python2.6/site-packages/barman/fs.py", line 79, in create_dir_if_not_exists
    raise FsOperationFailed('mkdir execution failed')
FsOperationFailed: mkdir execution failed

cheers,
gaby

[Giulio Calacoci]

Hi Gaby,
 
If I understand the issue, this time is not a rsync/SUSE problem.
Barman cannot create the destination directory for your test1 tablespace.
That happens because the '/var/lib/pgsql/data/tablespaces' does not exists when you start your recovery.
For a matter of security, we don't want barman to be able to create full paths of directories.
So barman expects the destination (for the basebackup and for the tablespaces) to exist.
 
Please create the '/var/lib/pgsql/data/tablespaces' directory, then try to recover the backup again.
 
Cheers
Giulio

[gap]

Hi Giulio,

thanks, you are right. This works. Nevertheless I will have to change the paths and place them somewhere else.

cheers,
Gaby

[Gabriele Bartolini]

Hi Gaby,

  this is at least a documentation bug. Either we specify this better in the documentation or we enable the transparent creation of subdirectories.

  Any suggestions? I see no issues whatsoever with permitting to create subdirectories, but I'd like to hear your opinion as well.

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director

 PostgreSQL Training, Services and Support

 gabriele....@2ndQuadrant.it | www.2ndQuadrant.it

[gap]

Hi Gabriele,

it would be nice to have this feature in barman if security issues are not a big concern.
For all db server in production which I won't alter in the next weeks I changed the /usr/lib64/python2.6/site-packages/barman/fs.py file and added the "-p" option to mkdir command. This seemed to work after I changed my rsync package (SUSE related issue).
But in future cases I will place the tablespaces in a directory outside of postgres home anyway.

When I searched in the forum for this issue I saw that other people had some struggles too and were missing this feature. So it could be worth including it.

thanks for your help,
Gaby

On Friday, February 20, 2015 at 3:03:34 PM UTC+1, Gabriele Bartolini wrote:

Hi Gaby,

  this is at least a documentation bug. Either we specify this better in the documentation or we enable the transparent creation of subdirectories.

  Any suggestions? I see no issues whatsoever with permitting to create subdirectories, but I'd like to hear your opinion as well.

Cheers,

Gabriele

--
 Gabriele Bartolini - 2ndQuadrant Italia - Managing Director
 PostgreSQL Training, Services and Support

 gabriele.bartolini@2ndQuadrant.it | www.2ndQuadrant.it