We have covered this briefly before, but its an important enough concept to cover again in more detail.
Problem: You are running out of disk space on the drive you keep PostgreSQL data on
Create a new tablespace on a separate drive and move existing tables to it, or create a new tablespace and use for future tables.
What is a tablespace and how to create a tablespace
A tablespace in PostgreSQL is similar to a tablespace in Oracle and a filegroup in SQL Server. It segments a piece of physical disk space
for use by the PostgreSQL process for holding data. Below are steps to creating a new tablespace. Tablespaces have existed since PostgreSQL 8.0.
While it is possible to create a table index on a different tablespace from the table, we won't be covering that.
Below are steps to creating one
First create a folder on an available disk in your filesystem using an filesystem server administrative login
Next give full rights to the postgres server account (the one the daemon process runs under) or you can change the owner of the folder
to the postgres account (in linux you can use chown postgres and on windows just use the administrative properties panel for that folder.
Then launch psql or pgadmin III and connect as a super user
You move existing tables with the ALTER TABLE command. You can do the below replacing the my... variables with your specific one. mytableschema if
you are not using schemas for logical partitioning would simply be public.
Again connect via psql or PgAdmin III
ALTER TABLE mytableschema.mytable SET TABLESPACE mynewtablespace
Setting default location for new tables
As of PostgreSQL 8.2, PostgreSQL provides many options for setting the default location of newly created tables
You can default location based on the database using: ALTER DATABASE mydatabase SET default_tablespace = mynewtablespace If you happened to have used mixed case when creating your database, you will need to quote the database name e.g ALTER DATABASE "My_Database" SET default_tablespace = mynewtablespace;
based on the user creating the table. ALTER ROLE someuser SET default_tablespace = mynewtablespace;
Or temporarily for current session while you are creating a batch of tables using SET default_tablespace = mynewtablespace;
Since PostgreSQL 8.3 you can use the GUC temp_tablespaces to create temporary objects there, too. This also allows to specify locations where temporary files for sorting and so on can be located and separated from database storage. Multiple temp tablespaces can be configured for "balancing".