4

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.

  • Is the use of links causing the problem here? Or is it normal practice to use links with tablespaces? Can you try making your tablespaces without links and then run pg_basebackup? Are the directories really empty? (Check that first...) – Colin 't Hart Mar 13 '15 at 18:24
  • Good point, I followed the instructions:-PostgreSQL makes use of symbolic links to simplify the implementation of tablespaces. This means that tablespaces can be used only on systems that support symbolic links. docs. – Paulwin Mar 13 '15 at 18:51
  • Well, when I recreate the table-spaces and specify the LOCATION parameter as the actual location on disk instead of the name of the symbolic link - I receive the error/warning: pg_basebackup: directory "D:\app\tablespace\tbs1" exists but is not empty. Which is obviously true, but why is it being reported? It makes it seem that only an empty set of tablespaces can be backed up - which cannot be the case. – Paulwin Mar 16 '15 at 13:56
  • 3
    I think depesz.com/2014/03/04/… will help you. – Colin 't Hart Mar 17 '15 at 9:52
4

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.

  • Per Colin's comment, the -T option can be used to map tablespaces to new locations. – Rusty Lemur Oct 28 at 22:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.