Tablespace in postgres

 Tablespace in postgres

video

PostgreSQL : What is a Tablespace in PostgreSQL Demo for tablespaces in PostgreSQL Create a new instance or initialize existing instance mkdir -p /postgres/data/instance3 pg_ctl -D /postgres/data/instance3 initdb or pg_ctl -D data initdb ls -lF data pg_ctl -D data start or By default, when you create objects (like tables, indexes) which need on-disk storage, the Postgres server creates the required files in $PGDATA. By default the Postgres server creates the required files in the default tablespace called pg_default. the location of which is the data directory $PGDATA. Location to place the physical files for postgresql objects. The Default Tablespaces Two tablespaces are automatically created when the database cluster is initialized. pg_global tablespace is used for shared system catalogs. pg_default tablespace is the default tablespace of the template1 and template0 databases The Default Tablespaces location. The location of the default tablespaces is the same as the data directory, or $PGDATA. by default objects gets storage into the pg_default tablespace, which corresponds to the data directory itself. Creating a new Tablespace $ mkdir -p /postgres/data/tbs2 postgres# create tablespace tbs2 location '/postgres/data/tbs2'; postgres# \db+ ls -l data/pg_tblspc/ within $PGDATA, Postgres creates a symbolic link Creating Objects create database db2 tablespace tbs2; \c db2 create table tab1 (a int); create table tab2 (a int) tablespace tbs2; create table tab3 (a int) tablespace pg_default; How to move objects from one tablespace to another alter table tab1 set tablespace pg_default; alter table all in tablespace tbs1 set tablespace pg_default; Tablespace Properties alter tablespace space2 set ( seq_page_cost=0.5, random_page_cost=0.5 ); Connection-Default Tablespace mkdir -p /postgres/data/newts2 CREATE TABLESPACE tsttbs2 LOCATION '/postgres/data/newts2'; CREATE TABLE foo2(i int) TABLESPACE tsttbs2; SET default_tablespace = tsttbs2; CREATE TABLE foo3(i int); How to Backup additional tablespace pg_basebackup --format=p --tablespace-mapping=/tmp/space2=/tmp/space2backup -D plainb Why is tablespace is used for Database growing then create a new tablespace and move objects from existing to new tablespace. High perfomrming disk move indexes and table to that . Location on disk where PostgreSQL stores data files containing database object. Map a logical name to a physical location on disk. Default tablespaces pg_default tablespace stores all user data. pg_global tablespace stores all global data. Advantages of using tablespaces you can create a new tablespace on a a New FS /Drive and use it . you can place the frequent access indexes or tables on devices that perform very fast.