BUG #16003: pg_basebackup failed with error : directory "xxxxxxx" exists but is not empty

On Thu, Sep 12, 2019 at 4:41 AM PG Bug reporting form <
noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 16003
Logged by: sebastien ruiz
Email address: sebastien.ruiz@harmonie-mutuelle.fr
PostgreSQL version: 11.4
Operating system: Red Hat Enterprise Linux Server release 7.6
Description:

hello,
(sorry i'm french)

We have a problem when using pg_basebackup.
We tried to rebuild a slave (master/slave with streaming replication) using
pg_basebackup like this :
(executed on the slave server of course)
pg_basebackup -D /pgbd/IPMUTB6/admin -P -h PGSMUTB61 -p 5432 -U postgres
-X stream -v --waldir=/pgxlog/IPMUTB6/wal

But it failed :
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/17000028 on timeline 5
pg_basebackup: directory "/pgbd/IPMUTB6/data/dpaafb1_data" exists but is
not
empty
pg_basebackup: removing data directory "/pgbd/IPMUTB6/admin"
pg_basebackup: removing WAL directory "/pgxlog/IPMUTB6/wal"
pg_basebackup: changes to tablespace directories will not be undone

in fact it failed because we have an architecture of tablespace who is not
compatible with the pg_basebackup tests/controls.

This is how our tablespace architecture looks like :
postgres=# \db
List of tablespaces
Name | Owner | Location

-----------------------+----------+-----------------------------------------------
aafb1_dpaafb1_data | aafb1 |
/pgbd/IPMUTB6/data/dpaafb1_data/aafb1_data
aafb1_dpaafb1_idx | aafb1 |
/pgbd/IPMUTB6/idx/dpaafb1_idx/aafb1_idx
dpaafb1_data | postgres | /pgbd/IPMUTB6/data/dpaafb1_data
dpaafb1_idx | postgres | /pgbd/IPMUTB6/idx/dpaafb1_idx
dpccmb1_data | postgres | /pgbd/IPMUTB6/data/dpccmb1_data
dpccmb1_idx | postgres | /pgbd/IPMUTB6/idx/dpccmb1_idx
dptemb1_data | postgres | /pgbd/IPMUTB6/data/dptemb1_data
dptemb1_idx | postgres | /pgbd/IPMUTB6/idx/dptemb1_idx
pg_default | postgres |
pg_global | postgres |
tech_dpaafb1_data | tech |
/pgbd/IPMUTB6/data/dpaafb1_data/tech_data
tech_dpaafb1_idx | tech | /pgbd/IPMUTB6/idx/dpaafb1_idx/tech_idx
tech_dpccmb1_data | tech |
/pgbd/IPMUTB6/data/dpccmb1_data/tech_data
tech_dpccmb1_idx | tech | /pgbd/IPMUTB6/idx/dpccmb1_idx/tech_idx
tech_dptemb1_data | tech |
/pgbd/IPMUTB6/data/dptemb1_data/tech_data
tech_dptemb1_idx | tech | /pgbd/IPMUTB6/idx/dptemb1_idx/tech_idx
temp | postgres | /pgtemp/IPMUTB6/tmp
tempob1_dptemb1_data | tempob1 |
/pgbd/IPMUTB6/data/dptemb1_data/tempob1_data
tempob1_dptemb1_idx | tempob1 |
/pgbd/IPMUTB6/idx/dptemb1_idx/tempob1_idx
webccmb1_dpccmb1_data | webccmb1 |
/pgbd/IPMUTB6/data/dpccmb1_data/webccmb1_data
webccmb1_dpccmb1_idx | webccmb1 |
/pgbd/IPMUTB6/idx/dpccmb1_idx/webccmb1_idx

In summary, we have tablespace directories into tablespace directories.

exemple :
/pgbd/IPMUTB6/data/dpaafb1_data/aafb1_data is inside the directory
/pgbd/IPMUTB6/data/dpaafb1_data

And this point is not compatible with pg_basebackup usage.

My question :
is it possible to make pg_basebackup compatible with this situation ?
I'm sure that many entreprise have this kind of architecture.
We can't use pg_basebackup and it's really too bad for us.

Why do you have multiple tablespaces if they are in the same place? The
appropriate fix for that is to not do that.

And actually putting new tablespace directories inside other tablespaces is
*definitely* not the way it's supposed to be done.

As a workaround, you might be able to use pg_basebackup with -T to relocate
the tablespaces into a directory that is not in conflict.

//Magnus

On Thu, Sep 12, 2019 at 09:27:02AM -0400, Magnus Hagander wrote:

And actually putting new tablespace directories inside other tablespaces is
*definitely* not the way it's supposed to be done.

Yes.

As a workaround, you might be able to use pg_basebackup with -T to relocate
the tablespaces into a directory that is not in conflict.

I was wondering about that and that should work. Looking at sendDir()
in basebackup.c, we only accept links from the root of pg_tlbspc, so
all any links inside another tablespace would just be ignored. In
this case, the server would complain with a WARNING and "skipping
special file blah".
--
Michael

Hi Magnus,

Thanks for the reply.

We have tablespace directories inside other tablespaces because we have a technical schema (call »tech) into each database.
So we can’t put thoses tablespaces (for tech) anywhere else (they have the same name).
Well… it’s our choice. Probably it’s not a good choice but it’s done and it will be very difficult to change this actually.

But do you think it’s impossible for dev team to change pg_basebackup so that it works in this case ?

(by the way, i’m not sure it’s the right way to answer like this (reply this mail) for the comunication on this list. I don’t know how i can do otherwise…)

Cordialement,
[cid:image001.jpg@01D35FC5.0989FAF0]<https://www.harmonie-mutuelle.fr/&gt;

Sébastien RUIZ
Administrateur Bases de Données
Direction de la Production et des Infrastructures
Direction des Opérations et de la Transformation
[cid:image010.jpg@01D373FE.EBC9E180] 02 47 46 53 84 – Bât. A - Bureau 114
41, rue Fabienne Landy 37700 ST PIERRE DES CORPS

[cid:image005.gif@01D35FC5.0989FAF0]<https://www.linkedin.com/company/1634517/&gt;[cid:image006.gif@01D35FC5.0989FAF0]&lt;https://twitter.com/hmutuelle&gt;[cid:image007.gif@01D35FC5.0989FAF0]&lt;https://www.facebook.com/harmoniemutuelle/&gt;[cid:image008.gif@01D35FC5.0989FAF0]&lt;https://www.instagram.com/harmoniemutuelle/?hl=fr&gt;[cid:image009.gif@01D35FC5.0989FAF0]&lt;https://www.youtube.com/user/HarmonieMutuelle&gt;www.harmonie-mutuelle.fr&lt;https://www.harmonie-mutuelle.fr/&gt;

De : Magnus Hagander <magnus@hagander.net>
Envoyé : jeudi 12 septembre 2019 15:27
À : PG Bug reporting form <noreply@postgresql.org>
Cc : pgsql-bugs@lists.postgresql.org; RUIZ Sébastien <Sebastien.RUIZ@harmonie-mutuelle.fr>
Objet : Re: BUG #16003: pg_basebackup failed with error : directory "xxxxxxx" exists but is not empty

On Thu, Sep 12, 2019 at 4:41 AM PG Bug reporting form <noreply@postgresql.org<mailto:noreply@postgresql.org>> wrote:
The following bug has been logged on the website:

Bug reference: 16003
Logged by: sebastien ruiz
Email address: sebastien.ruiz@harmonie-mutuelle.fr<mailto:sebastien.ruiz@harmonie-mutuelle.fr>
PostgreSQL version: 11.4
Operating system: Red Hat Enterprise Linux Server release 7.6
Description:

hello,
(sorry i'm french)

We have a problem when using pg_basebackup.
We tried to rebuild a slave (master/slave with streaming replication) using
pg_basebackup like this :
(executed on the slave server of course)
pg_basebackup -D /pgbd/IPMUTB6/admin -P -h PGSMUTB61 -p 5432 -U postgres
-X stream -v --waldir=/pgxlog/IPMUTB6/wal

But it failed :
pg_basebackup: initiating base backup, waiting for checkpoint to complete
pg_basebackup: checkpoint completed
pg_basebackup: write-ahead log start point: 0/17000028 on timeline 5
pg_basebackup: directory "/pgbd/IPMUTB6/data/dpaafb1_data" exists but is not
empty
pg_basebackup: removing data directory "/pgbd/IPMUTB6/admin"
pg_basebackup: removing WAL directory "/pgxlog/IPMUTB6/wal"
pg_basebackup: changes to tablespace directories will not be undone

in fact it failed because we have an architecture of tablespace who is not
compatible with the pg_basebackup tests/controls.

This is how our tablespace architecture looks like :
postgres=# \db
List of tablespaces
Name | Owner | Location
-----------------------+----------+-----------------------------------------------
aafb1_dpaafb1_data | aafb1 |
/pgbd/IPMUTB6/data/dpaafb1_data/aafb1_data
aafb1_dpaafb1_idx | aafb1 |
/pgbd/IPMUTB6/idx/dpaafb1_idx/aafb1_idx
dpaafb1_data | postgres | /pgbd/IPMUTB6/data/dpaafb1_data
dpaafb1_idx | postgres | /pgbd/IPMUTB6/idx/dpaafb1_idx
dpccmb1_data | postgres | /pgbd/IPMUTB6/data/dpccmb1_data
dpccmb1_idx | postgres | /pgbd/IPMUTB6/idx/dpccmb1_idx
dptemb1_data | postgres | /pgbd/IPMUTB6/data/dptemb1_data
dptemb1_idx | postgres | /pgbd/IPMUTB6/idx/dptemb1_idx
pg_default | postgres |
pg_global | postgres |
tech_dpaafb1_data | tech |
/pgbd/IPMUTB6/data/dpaafb1_data/tech_data
tech_dpaafb1_idx | tech | /pgbd/IPMUTB6/idx/dpaafb1_idx/tech_idx
tech_dpccmb1_data | tech |
/pgbd/IPMUTB6/data/dpccmb1_data/tech_data
tech_dpccmb1_idx | tech | /pgbd/IPMUTB6/idx/dpccmb1_idx/tech_idx
tech_dptemb1_data | tech |
/pgbd/IPMUTB6/data/dptemb1_data/tech_data
tech_dptemb1_idx | tech | /pgbd/IPMUTB6/idx/dptemb1_idx/tech_idx
temp | postgres | /pgtemp/IPMUTB6/tmp
tempob1_dptemb1_data | tempob1 |
/pgbd/IPMUTB6/data/dptemb1_data/tempob1_data
tempob1_dptemb1_idx | tempob1 |
/pgbd/IPMUTB6/idx/dptemb1_idx/tempob1_idx
webccmb1_dpccmb1_data | webccmb1 |
/pgbd/IPMUTB6/data/dpccmb1_data/webccmb1_data
webccmb1_dpccmb1_idx | webccmb1 |
/pgbd/IPMUTB6/idx/dpccmb1_idx/webccmb1_idx

In summary, we have tablespace directories into tablespace directories.

exemple :
/pgbd/IPMUTB6/data/dpaafb1_data/aafb1_data is inside the directory
/pgbd/IPMUTB6/data/dpaafb1_data

And this point is not compatible with pg_basebackup usage.

My question :
is it possible to make pg_basebackup compatible with this situation ?
I'm sure that many entreprise have this kind of architecture.
We can't use pg_basebackup and it's really too bad for us.

Why do you have multiple tablespaces if they are in the same place? The appropriate fix for that is to not do that.

And actually putting new tablespace directories inside other tablespaces is *definitely* not the way it's supposed to be done.

As a workaround, you might be able to use pg_basebackup with -T to relocate the tablespaces into a directory that is not in conflict.

//Magnus

On Fri, Sep 13, 2019 at 4:28 AM RUIZ Sébastien <
Sebastien.RUIZ@harmonie-mutuelle.fr> wrote:

Hi Magnus,

Thanks for the reply.

We have tablespace directories inside other tablespaces because we have a
technical schema (call »tech) into each database.

So we can’t put thoses tablespaces (for tech) anywhere else (they have the
same name).

I don't understand that argument. The only *reason* to have multiple
tablespaces is to put them in different locations. If you put them in the
same location, why have them at all? Just move everything to the main
tablespace and remove them.

You can have an unlimited (more or less) number of schemas in the same
database, in the same tablespace. There is no connection whatsoever between
schemas and tablespaces.

Well… it’s our choice. Probably it’s not a good choice but it’s done and
it will be very difficult to change this actually.

But do you think it’s impossible for dev team to change pg_basebackup so
that it works in this case ?

Nothing is impossible. Worse case you could always have a fork created for
your specific environment. But compared to either of those options it will
be easier to actually fix your installation.

(by the way, i’m not sure it’s the right way to answer like this (reply
this mail) for the comunication on this list. I don’t know how i can do
otherwise…)

That is definitely the correct way.

//Magnus

Ok, thanks for the answer and the advice.
This thread can be closed.
best regards.

Cordialement,
[cid:image001.jpg@01D35FC5.0989FAF0]<https://www.harmonie-mutuelle.fr/&gt;

Sébastien RUIZ
Administrateur Bases de Données
Direction de la Production et des Infrastructures
Direction des Opérations et de la Transformation
[cid:image010.jpg@01D373FE.EBC9E180] 02 47 46 53 84 – Bât. A - Bureau 114
41, rue Fabienne Landy 37700 ST PIERRE DES CORPS

[cid:image005.gif@01D35FC5.0989FAF0]<https://www.linkedin.com/company/1634517/&gt;[cid:image006.gif@01D35FC5.0989FAF0]&lt;https://twitter.com/hmutuelle&gt;[cid:image007.gif@01D35FC5.0989FAF0]&lt;https://www.facebook.com/harmoniemutuelle/&gt;[cid:image008.gif@01D35FC5.0989FAF0]&lt;https://www.instagram.com/harmoniemutuelle/?hl=fr&gt;[cid:image009.gif@01D35FC5.0989FAF0]&lt;https://www.youtube.com/user/HarmonieMutuelle&gt;www.harmonie-mutuelle.fr&lt;https://www.harmonie-mutuelle.fr/&gt;

De : Magnus Hagander <magnus@hagander.net>
Envoyé : vendredi 13 septembre 2019 15:17
À : RUIZ Sébastien <Sebastien.RUIZ@harmonie-mutuelle.fr>
Cc : pgsql-bugs@lists.postgresql.org
Objet : Re: BUG #16003: pg_basebackup failed with error : directory "xxxxxxx" exists but is not empty

On Fri, Sep 13, 2019 at 4:28 AM RUIZ Sébastien <Sebastien.RUIZ@harmonie-mutuelle.fr<mailto:Sebastien.RUIZ@harmonie-mutuelle.fr>> wrote:
Hi Magnus,

Thanks for the reply.

We have tablespace directories inside other tablespaces because we have a technical schema (call »tech) into each database.
So we can’t put thoses tablespaces (for tech) anywhere else (they have the same name).

I don't understand that argument. The only *reason* to have multiple tablespaces is to put them in different locations. If you put them in the same location, why have them at all? Just move everything to the main tablespace and remove them.

You can have an unlimited (more or less) number of schemas in the same database, in the same tablespace. There is no connection whatsoever between schemas and tablespaces.

Well… it’s our choice. Probably it’s not a good choice but it’s done and it will be very difficult to change this actually.

But do you think it’s impossible for dev team to change pg_basebackup so that it works in this case ?

Nothing is impossible. Worse case you could always have a fork created for your specific environment. But compared to either of those options it will be easier to actually fix your installation.

(by the way, i’m not sure it’s the right way to answer like this (reply this mail) for the comunication on this list. I don’t know how i can do otherwise…)

That is definitely the correct way.

//Magnus