Postgres pg_basebackup reports directory exists but is not empty

Question

I have postgreSQL 9.4.1 installed on a Win 2012 Server.

I am looking to setup replication between this and another server of the same spec. So I am following guidance on preparing the master database server for standby servers as per warm standby section 25.2.3 This involves using pg_basebackup.

The PostgreSQL instance on the master contains a database for one of our apps. This database has 3 schemas each of which have their own dedicated tablespace. On windows the tablespace locations were created by using the MKLINK command (as I believe the LOCATION parameter has to point to a symbolic link on windows) so - for example, :

mklink /j C:\tbs1 D:\app\tablespace\tbs1
mklink /j C:\tbs2 D:\app\tablespace\tbs2

So, using the above as an example a tablespace in postgreSQL was created using the following command:

CREATE TABLESPACE app_ops OWNER the_user LOCATION 'C:\tbs1';

Which is all fine. However when I wish to use the pg_basebackup tool, i received the following error/warning - with nothing being written to the backup location:

pg_basebackup -h localhost -U postgres -D C:\master_backup
pg_basebackup: directory "C:\tbs1" exists but is not empty

I don't understand why I am receiving this specific message.

The main point in many of the answers I have seen talks about ensuring that the tablespaces didn't exist within PG_DATA. However they don't in my case - PG_DATA is set in the executable path of the windows service as -D C:\Program Files\PostgreSQL\9.4\data, as can been seen:

"C:\Program Files\PostgreSQL\9.4\bin\pg_ctl.exe" runservice -N "postgresql-x64-9.4" -D "C:\Program Files\PostgreSQL\9.4\data" -w

I had looked at the tablespace-mapping parameter 'old=new', but this didn't change the result.

I appreciate that there are similar questions and guidance, however I do not seem to be able to find anything additional that may help. I have looked on dba/stackoverflow and www.postgresql.org I would list more of the places I have seen helpful info - but alas my reputation doesn't allow me to add any more

If there are any thoughts or ideas it would be greatly appreciated.

regards.

Answer 1

In my opinion it is not related to mklink or Windows issues.

Per pg_basebackup docs, the main data directory will be placed in the target directory, but all other tablespaces will be placed in the same absolute path as they have on the server.

-F format
--format=format

    Selects the format for the output. format can be one of the following:

    p
    plain

    Write the output as plain files, with the same layout as the current data directory and tablespaces. When the cluster has no
    additional tablespaces, the whole database will be placed in the
    target directory. If the cluster contains additional tablespaces, the
    main data directory will be placed in the target directory, but all
    other tablespaces will be placed in the same absolute path as they
    have on the server.

    This is the default format.

Solution: do not run it on the same server, or change backup format.

Answer 2

I faced the same problem in PG 14:

[postgres@postgres2 data]$ pg_basebackup -h postgres1 -U repuser -p 5432 -D /var/lib/pgsql/14/data -Fp -Xs -n -P -R -S master
pg_basebackup: error: directory "/var/lib/pgsql/14" exists but is not empty
pg_basebackup: data directory "/var/lib/pgsql/14/data" not removed at user's request

Unforetunately I created the tablespace directly under /var/lib/pgsql/14 on the primary node. After changing the tablespace folder out of PGDATA the problem was solved and changed to a simple permission error:

[postgres@postgres2 data]$ pg_basebackup -h postgres1 -U repuser -p 5432 -D /var/lib/pgsql/14/data -Fp -Xs -P -R -S master
pg_basebackup: error: could not create directory "/tablespace": Permission denied
pg_basebackup: removing contents of data directory "/var/lib/pgsql/14/data"