3

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

1 Answer 1

3

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.

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.