postgreSQL Tablespaces and datafiles

• Each database has a separate subdirectory.
• Tablespaces can be created using the CREATE TABLESPACE command.
• The Tablespace directory must be created and the appropriate permissions must be set prior to running the CREATE TABLESPACE command.

Syntax:

    CREATE TABLESPACE tablespace_name [ OWNER user_name ] LOCATION 'directory'

  Use tablespace:

  Use the TABLESPACE keyword while creating tables and indexes to specify a   tablespace name.

  Syntax: 

            TABLESPACE <table space name>

• default_tablespace server parameter sets default tablespace.
• default_tablespace parameter can also be set using the SET command at the session level.temp_tablespaces parameter determines the placement of temporary tables and indexes and temporary files.
• temp_tablespaces can be a list of tablespace names.

Example:

show default_tablespace;

show temp_tablespaces;

How to set default table space?

• set default_tablespace <table-space>
• Alter tablespace name rename to new_name
• Alter tablespace name owenr to new_owner

 Drop table space:

drop tablespace <name>

create database in different table space:

create databse <db-name> tablespace="<table-space-name>

create database <db-name> <table-space-name>

create table space with location:

create tablespace <table-space-name> location '/home/mydbname'

also see below

what is postgreSQL

Why postgresSQL

History of PostgreSQL

postgreSQL - PSQL MATA Commands

postgreSQL what is schema?

postgreSQL Connecting to a Database

postgreSQL Create and access Database

How to create user in postgreSQL?

postgreSQL Grant/Revoke Privileges

postgreSQL Tablespaces and datafiles

postgreSQL How to start server and stop server?

postgreSQL perfomance

postgreSQL How to create cluster?

postgreSQL Queries

postgreSQL routine maintenance and proactive-maintanace.

postgresql-logging