More

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

    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';

    Recent Articles

    spot_img

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox