Database Reference
In-Depth Information
Adding/Removing tablespaces
Tablespaces allow us to store PostgreSQL data across different devices. We might want to
do that for performance, administrative ease, or your database might just have run out of
disk space.
Getting ready
Before we can create a useful tablespace, we need to prepare the underlying devices in
production-ready form.
Think carefully about the speed, volume, and robustness of the disks you are about to use.
Make sure they are configured correctly. Those decisions will affect your life for the next few
months and years.
Disk performance is a subtle issue that most people think can be decided in a few seconds.
We recommend you read the
Performance
section of this topic, as well as additional topics on
this topic.
Once you've done all that, then you can create a directory for our tablespace.
The directory must:
F
Be empty
F
Be owned by the PostgreSQL owning userid
F
Be specified with an absolute path name
On Linux/Unix systems, you shouldn't use a mount point directly. Create a subdirectory and
use that instead. That simplifies ownership and avoids some filesystem-specific issues, such
as having lost+found directories.
It also needs to follow sensible naming conventions, so we clearly identify which tablespace
goes with which server. Do not be tempted to use something simple, such as data, because
it will make later administration more difficult. Be especially careful that test or development
servers do not and cannot get confused with production systems.
How to do it...
Once you've created your directory, adding the tablespace is simple, as follows:
CREATE TABLESPACE new_tablespace
LOCATION '/usr/local/pgsql/new_tablespace';
And, the command to remove the tablespace is also simple, which is:
DROP TABLESPACE new_tablespace;
