Database Reference
In-Depth Information
Then use pg_restore :
pg_restore --dbname= mydb2 --section=pre-data --jobs=4 mydb.backup
Managing Disk Storage with Tablespaces
PostgreSQL uses tablespaces to ascribe logical names to physical locations on disk. Ini‐
tializing a PostgreSQL cluster automatically begets two tablespaces: pg_default , which
stores all user data, and pg_global , which stores all system data. These are located in
the same folder as your default data cluster. You're free to create tablespaces at will and
house them on any server disks. You can explicitly assign default tablespaces for new
objects by database. You can also move existing database objects to new ones.
Creating Tablespaces
To create a new tablespace, specify a logical name and a physical folder and make sure
that the postgres service account has full access to the physical folder. If you are on a
Windows server, use the following command (note the use of Unix-style forward slash‐
es):
CREATE TABLESPACE secondary LOCATION 'C:/pgdata94_secondary' ;
For Unix-based systems, you first must create the folder or define an fstab location, then
use this command:
CREATE TABLESPACE secondary LOCATION '/usr/data/pgdata94_secondary' ;
Moving Objects Between Tablespaces
You can shuffle database objects among different tablespaces. To move all objects in the
database to our secondary tablespace, we issue the following SQL command:
ALTER DATABASE mydb SET TABLESPACE secondary ;
To move just one table:
ALTER TABLE mytable SET TABLESPACE secondary ;
New in PostgreSQL 9.4 is the ability move a group of objects from one tablespace to
another. If the person running the command is a superuser, all objects will be moved.
If a nonsuperuser is running the statement, only the objects that she owns will be moved.
To move all objects from default tablespace to secondary:
ALTER TABLESPACE pg_default MOVE ALL TO secondary ;
During the move, your database or table will be locked.
Search WWH ::




Custom Search