Home PostgreSQL pg_basebackup: error: directory “dir_abc” exists but is not empty OR ERROR: ...

pg_basebackup: error: directory “dir_abc” exists but is not empty OR ERROR: tablespace “ABC” is not empty

0
147

This error is a very common error if any DBA is trying to take pg_basebackup in the same server where your PostgreSQL Instance is running. You must have created one directory where your user tablespace(apart from pg_default and pg_global tablespace) is lying. We should never create user tablespace directory within “data” directory. If we try to do so, Postgres will show “WARNING: tablespace location should not be inside the data directory”. Hence, we end up creating a separate directory outside data folder.

When you take a pg_basebackup, you are taking full filesystem backup and you have to have same empty folder structure for tablespace as in your current PostgreSQL instance. Otherwise, you have to use tablespace-mapping option in pg_basebackup.

Solution to pg_basebackup Directory does not exist issue:

pg_basebackup: error: directory "/opt/PosgreSQL/13/user_tablespace" exists but is not empty
pg_basebackup: removing data directory "/opt/PosgreSQL/13/pgbasebackup/bck4"

Create a new directory say “/opt/PosgreSQL/13/user_tablespace_new” and put the same in tablespace_mapping

[[email protected] user_tablespace_new]$ pg_basebackup --host=localhost -p 5432 --wal-method=stream -r 1024M -R --format=t -z -P --tablespace-mapping=/opt/PosgreSQL/13/user_tablespace=/opt/PosgreSQL/13/user_tablespace_new -D /opt/PosgreSQL/13/pgbasebackup/bck4
202302/202302 kB (100%), 2/2 tablespaces

Solution to Drop tablespace issue:

Move all the databases and objects to pg_default tablespace and drop the tablespace.

Step1: Check list of objects resides in the tablespace. For my test case tablespace name is ‘SMS_TABLSPC’.

SELECT
ts.spcname,
cl.relname
FROM
pg_class cl
JOIN pg_tablespace ts ON cl.reltablespace = ts.oid
WHERE
ts.spcname = 'SMS_TABLSPC';

Step2: Change tablespace for table or database to pg_default using following commands.

alter table "Employee1" set tablespace pg_default;

alter database postgres set tablespace pg_default;

Step3: Drop user tablespace

drop tablespace 'SMS_TABLSPC';