PostgreSQL – How to Make default_tablespace Work Properly

postgresql

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?

I made the following script, with it's output following, to show the problem I am having :

#!/bin/bash
#
export OWNER=yourself
export OBJECT=screwy
export OTHER=not${OBJECT}
export PWD='seecret'
#
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OWNER};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP DATABASE IF EXISTS ${OTHER};"
sudo -u postgres psql -tc "DROP TABLESPACE IF EXISTS ${OBJECT};"
sudo -u postgres psql -tc "DROP ROLE IF EXISTS ${OWNER};"
#
str=$(cat /etc/lsb-release | grep DESC)
echo "Linux version is : ${str#*=}"
echo "Postgres version is :"
sudo -u postgres psql -qtc "SELECT version();"
#
echo "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}'"
sudo -u postgres psql -tc "CREATE ROLE ${OWNER} WITH CREATEDB LOGIN PASSWORD '${PWD}';"
echo "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "CREATE TABLESPACE ${OBJECT} OWNER ${OWNER} LOCATION '/home/${OWNER}/data';"
sudo -u postgres psql -tc "SELECT 'Tablespace \"' || spcname || '\" is owned by ' || usename \
                             FROM pg_catalog.pg_tablespace, pg_catalog.pg_user \
                            WHERE spcname='${OBJECT}' AND usesysid = spcowner;"
echo "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
sudo -u postgres psql -tc "CREATE DATABASE ${OWNER} TABLESPACE ${OBJECT};"
#
echo "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
sudo -u postgres psql -tc "ALTER ROLE ${OWNER} SET DEFAULT_TABLESPACE TO ${OBJECT};"
echo "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
sudo -u postgres psql -tc "GRANT CREATE ON TABLESPACE ${OBJECT} TO ${OWNER};"
#
echo "## As ${OWNER}"
echo "*:*:*:yourself:${PWD}" | cat > ~/.pgpass
chmod 600 ~/.pgpass
#
echo "SHOW DEFAULT_TABLESPACE;"
psql -tc "SHOW DEFAULT_TABLESPACE;"
#
echo "CREATE DATABASE ${OBJECT};"
psql -tc "CREATE DATABASE ${OBJECT};"
#
echo "CREATE DATABASE ${OTHER};"
psql -tc "CREATE DATABASE ${OTHER} TABLESPACE ${OBJECT};"
#
echo "## As postgres"
sudo -u postgres psql -qtc "SELECT 'Database \"' || d.datname || '\" is in tablespace \"' || t.spcname || '\" in disk directory \"' || pg_tablespace_location(t.oid) || '\".' FROM pg_database d \
                         LEFT JOIN pg_catalog.pg_tablespace t \
                                ON t.oid = d.dattablespace \
                             WHERE datistemplate = false \
                              AND datname IN ('${OTHER}', '${OBJECT}', '${OWNER}');"

When I execute it, it looks like this :

DROP DATABASE
DROP DATABASE
DROP DATABASE
DROP TABLESPACE
DROP ROLE
Linux version is : "Ubuntu 14.04 LTS"
Postgres version is :
 PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.2-16ubuntu6) 4.8.2, 64-bit

CREATE ROLE yourself WITH CREATEDB LOGIN PASSWORD 'seecret'
CREATE ROLE
CREATE TABLESPACE screwy OWNER yourself LOCATION '/home/yourself/data';
CREATE TABLESPACE
 Tablespace "screwy" is owned by yourself

CREATE DATABASE yourself TABLESPACE screwy;
CREATE DATABASE
ALTER ROLE yourself SET DEFAULT_TABLESPACE TO screwy;
ALTER ROLE
GRANT CREATE ON TABLESPACE screwy TO yourself;
GRANT
##  As yourself
SHOW DEFAULT_TABLESPACE;
 screwy

CREATE DATABASE screwy;
CREATE DATABASE
CREATE DATABASE notscrewy TABLESPACE screwy;
CREATE DATABASE
## As postgres
 Database "yourself" is in tablespace "screwy" in disk directory "/home/yourself/data".
 Database "screwy" is in tablespace "pg_default" in disk directory "".
 Database "notscrewy" is in tablespace "screwy" in disk directory "/home/yourself/data".

My question is, "Why isn't database 'screwy' created in tablespace 'screwy'?

Best Answer

Per the PostgreSQL tablespaces documentation:

If a database is created without specifying a tablespace for it, it uses the same tablespace as the template database it is copied from.

Per the CREATE DATABASE documentation:

By default, the new database will be created by cloning the standard system database template1.

You could either alter the template1 database's tablespace to screwy, or create a new template database that has a tablespace of screwy, then specify that new database as a template when creating your screwy database.