• Home
  • Services
    • DBA Support
    • DBA Consultancy Services
    • PostgreSQL Support
    • Website Maintenance
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum Database

    Greenplum Database

    $2,000.00 $1,500.00
    Read More
  • Company
    • FAQs
    • About Us
    • Contact
  • Events
  • Portfolio
  • Blogs
    • Blog – RayaFeeL
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
    • Blog – Medical Coding
      • Cart

        0

    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    RegisterLogin
    RayaFeeL
    • Home
    • Services
      • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
      • Website Maintenance
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00
      Read More
    • Company
      • FAQs
      • About Us
      • Contact
    • Events
    • Portfolio
    • Blogs
      • Blog – RayaFeeL
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
      • Blog – Medical Coding
        • Cart

          0

      Databases

      • Home
      • All courses
      • Databases
      • PostgreSQL Database
      CoursesIT & SoftwareDatabasesPostgreSQL Database
      • BASIC POSTGRESQL
        6
        • Lecture1.1
          PostgreSQL Brief History 30 min
        • Lecture1.2
          PostgreSQL Features 20 min
        • Lecture1.3
          PostgreSQL Monitoring Tools Overview 30 min
        • Lecture1.4
          PostgreSQL – Oracle Vs PostgreSQL 30 min
        • Lecture1.5
          PostgreSQL Data Types 30 min
        • Lecture1.6
          PostgreSQL Maximum Objects Size 30 min
      • VMWARE & POSTGRESQL INSTALLATION
        9
        • Lecture2.1
          Vmware Installation 30 min
        • Lecture2.2
          Creating Linux Virtual Machine 01 hour
        • Lecture2.3
          PostgreSQL Installation Types 30 min
        • Lecture2.4
          PostgreSQL GUI Installation 30 min
        • Lecture2.5
          PostgreSQL Text Mode Installation 30 min
        • Lecture2.6
          PostgreSQL Unattended Mode Installation 30 min
        • Lecture2.7
          Configure the network & Disk Partition 30 min
        • Lecture2.8
          How to install PostgreSQL10 On Linux Server ? 20 min
        • Lecture2.9
          PostgreSQL -11 Installation (rpm & source code) 30 min
      • POSTGRESQL DATABASE
        6
        • Lecture3.1
          Connect Postgres Server 10 min
        • Lecture3.2
          PostgreSQL startup / shutdown /restart the postgresql server 30 min
        • Lecture3.3
          PostgreSQL .bash_profile Set Up 30 min
        • Lecture3.4
          PostgreSQL Database Creation 30 min
        • Lecture3.5
          PostgreSQL Connect Database 30 min
        • Lecture3.6
          PostgreSQL Drop Database 30 min
      • POSTGRESQL TABLE
        16
        • Lecture4.1
          PostgreSQL Languages 30 min
        • Lecture4.2
          PostgreSQL Create Table 30 min
        • Lecture4.3
          PostgreSQL Select Table 30 min
        • Lecture4.4
          PostgreSQL Alter Table 30 min
        • Lecture4.5
          PostgreSQL Drop Table 30 min
        • Lecture4.6
          PostgreSQL Truncate Table 30 min
        • Lecture4.7
          PostgreSQL Rename 30 min
        • Lecture4.8
          PostgreSQL Comment 30 min
        • Lecture4.9
          PostgreSQL Insert 30 min
        • Lecture4.10
          PostgreSQL Update 30 min
        • Lecture4.11
          PostgreSQL Table Delete 30 min
        • Lecture4.12
          PostgreSQL Merge Table 30 min
        • Lecture4.13
          PostgreSQL UNION 30 min
        • Lecture4.14
          PostgreSQL UNION ALL 30 min
        • Lecture4.15
          PostgreSQL INTERSECT 30 min
        • Lecture4.16
          PostgreSQL DISTINCT 30 min
      • USER/OBJECTS PRIVILEGE AND ROLES ASIGNING
        3
        • Lecture5.1
          PostgreSQL Grant 30 min
        • Lecture5.2
          PostgreSQL Revoke 30 min
        • Lecture5.3
          Postgres user creation and restrict DDL statement & other database access 30 min
      • TRANSACTIONS - MVCC
        3
        • Lecture6.1
          PostgreSQL Commit 30 min
        • Lecture6.2
          PostgreSQL Rollback 30 min
        • Lecture6.3
          PostgreSQL Savepoint 30 min
      • POSTGRESQL USER/SCHEMA MANAGEMENT
        9
        • Lecture7.1
          PostgreSQL User Creation 30 min
        • Lecture7.2
          PostgreSQL User Creation Using Utility 30 min
        • Lecture7.3
          PostgreSQL Drop user 30 min
        • Lecture7.4
          PostgreSQL Drop User Using Utility 30 min
        • Lecture7.5
          PostgreSQL Password Changing and Views 30 min
        • Lecture7.6
          PostgreSQL Group/role 30 min
        • Lecture7.7
          Alter PostgreSQL User/Role/group 30 min
        • Lecture7.8
          PostgreSQL Schema 30 min
        • Lecture7.9
          PostgreSQL user creation and restrict DDL & database access 30 min
      • POSTGRESQL CONSTRAINTS
        6
        • Lecture8.1
          PostgreSQL Constraints 30 min
        • Lecture8.2
          PostgreSQL Primary Key 30 min
        • Lecture8.3
          PostgreSQL UNIQUE Constraint 30 min
        • Lecture8.4
          PostgreSQL CHECK Constraint 30 min
        • Lecture8.5
          PostgreSQL NOT NULL Constraint 30 min
        • Lecture8.6
          PostgreSQL Foreign Key 30 min
      • POSTGRESQL ADVANCE DATA TYPE
        5
        • Lecture9.1
          PostgreSQL DOMAIN Data Type 30 min
        • Lecture9.2
          PostgreSQL Alter Domain 30 min
        • Lecture9.3
          PostgreSQL Drop DOMAIN 30 min
        • Lecture9.4
          PostgreSQL Json Data Type 30 min
        • Lecture9.5
          PostgreSQL Sequence 30 min
      • POSTGRESQL VIEWS
        1
        • Lecture10.1
          How to Create PostgreSQL View 30 min
      • POSTGRESQL MONITORING OBJECT USUAGE/SIZE
        1
        • Lecture11.1
          How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database 30 min
      • POSTGRESQL DATABASE ARCHITECTURE
        4
        • Lecture12.1
          PostgreSQL Tablespace 30 min
        • Lecture12.2
          PostgreSQL UnderStanding Of Objects 30 min
        • Lecture12.3
          PostgreSQL Architecture 30 min
        • Lecture12.4
          PostgreSQL Query Flow 30 min
      • POSTGRESQL BACKUP AND RECOVERY
        13
        • Lecture13.1
          PostgreSQL Backup & Recovery Overview 30 min
        • Lecture13.2
          PostgreSQL Database Backup Tools – Pg_dump 30 min
        • Lecture13.3
          PostgreSQL Database Backup Tools – Pg_dumpall 30 min
        • Lecture13.4
          PostgreSQL Database Backup Tools – Pg_basebackup 30 min
        • Lecture13.5
          PostgreSQL COPY data From Text File 30 min
        • Lecture13.6
          PostgreSQL COPY data to Text File 30 min
        • Lecture13.7
          PostgreSQL Clone Table 20 min
        • Lecture13.8
          Postgres Database Schema Migration 30 min
        • Lecture13.9
          PostgreSQL Database clone/migration 30 min
        • Lecture13.10
          PostgreSQL Compress backup format 30 min
        • Lecture13.11
          PostgreSQL Archivelog 30 min
        • Lecture13.12
          PostgreSQL Point In Time Recovery 30 min
        • Lecture13.13
          Taking Backup On Postgres Slave (Standby) Server 30 min
      • POSTGRESQL PERFORMANCE TUNING
        5
        • Lecture14.1
          PostgreSQL Index 30 min
        • Lecture14.2
          PostgreSQL Reindex 30 min
        • Lecture14.3
          PostgreSQL PerformanceTuning 30 min
        • Lecture14.4
          Understanding Postgres VACUUM | VACUUM FULL | VACUUM ANALYZE 30 min
        • Lecture14.5
          Postgres Autovacuum Configuration/Setup 30 min
      • HIGH AVAILABILITY, LOAD BALANCING, AND REPLICATION
        11
        • Lecture15.1
          POSTGRESQL STREAMING REPLICATION 30 min
        • Lecture15.2
          SSL Streaming replication with PostgreSQL 10 30 min
        • Lecture15.3
          How to make Linux partition and configure the network on Redhat Linux7.3 30 min
        • Lecture15.4
          How To Fix Firewall Issues while connecting the other server? 30 min
        • Lecture15.5
          How to install the PostgreSQL10 On Linux ? 30 min
        • Lecture15.6
          How to Configure the cascade replication On PostgreSQL 10.3 ? 30 min
        • Lecture15.7
          How to add extra one slave an existing PostgreSQL cascade replication without down time ? 30 min
        • Lecture15.8
          PostgreSQL Switchover 30 min
        • Lecture15.9
          Postgres Failover 30 min
        • Lecture15.10
          Postgres Upgrade 30 min
        • Lecture15.11
          PostgreSQL Upgrade 9.5 to 11.3 . 30 min

        PostgreSQL Tablespace

         What is postgresql tablespace:

        • Tablespace is a logical storage to map a logical name(tbs1) to a physical location on disk(/u02/tbs1)
        • Postgres tablespaces are cluster level objects
        • User/superuser must have CREATE privilege create tablespaces, but they can assign ownership of tablespaces to Non-superusers/Non-CREATE privilege users
        • By default, the user who executed the CREATE TABLESPACE is the owner of the tablespace. The statement also allows assign the ownership of tablespace to another user specified in the OWNER clause.

        Condition:

        • The name of the tablespace should not begin with pg_, because these names are reversed for the system tablespaces. 
        • The directory must be empty and must be owned by the PostgreSQL system user. The directory must be specified by an absolute path name.
        • The location must not be on removable or transient storage, as the cluster might fail to function if the tablespace is missing or lost.
        Uses Of Tablespace : 

        • if a partition on which the cluster was initialized is out of space, you can create a new tablespace on a different partition
        • users can explicitly say that they want to store some specific tables/indexes in that new “linked folder” or one can also decide to make it the default for new objects or even move all old objects over to the new tablespace.
        • Tables, indexes, and entire databases can be assigned to particular tablespaces. 
        • Table partition – After version postgresql 10 , table partition is introduced based on tablespace this is one of the performance System in postgresql.
        • You can tune IO using tablespaces but before that you have to understand this parameter “seq_page_cost, random_page_cost and effective_io_concurrency“. 
        • you can easily move existing TABLES,INDEX, AND DATABASE  to a new tablespace.

        Two tablespaces are automatically created when the database cluster is initialized:

        1. pg_global : is used for shared system catalogs.
        2. pg_default : is the default tablespace of the template1 and template0 databases,default tablespace used for tables, indexes, and temporary files created within the database, if no TABLESPACE clause is given.whenever
          you create a table/Database without specifying a tablespace in the
          create table statement it will go to the pg_default tablespace.
        Syntax For Tablespace Creation:

        CREATE TABLESPACE tablespace_name
            [ OWNER { new_owner | CURRENT_USER | SESSION_USER } ]
            LOCATION 'directory'
            [ WITH ( tablespace_option = value [, ... ] ) ]

        How To create tablespace:
        Before creating tablespace you have to create OS level directory and you have to change the directory owner permission to postgres user

        1.creating tbs1 directory:
        cd /tab1/
        mkdir tbs1
        
        2.changing tbs1 permission to postgres user:
        chown -R postgres:postgres tbs1
        
        3.creating tablespace with name of  tbs1 ,tbs1 is a logical name you can change  whatever you want:
        CREATE TABLESPACE tbs1 LOCATION '/tab1/tbs1';
        
        4.Listing postgresql tablespace:
        postgres=# \db+
                                           List of tablespaces
            Name    |  Owner   |  Location  | Access privileges | Options |  Size   | Description
        ------------+----------+------------+-------------------+---------+---------+-------------
         pg_default | postgres |            |                   |         | 23 MB   |
         pg_global  | postgres |            |                   |         | 573 kB  |
         tbs1       | postgres | /tab1/tbs1 |                   |         | 0 bytes |
        (3 rows)

        Creating Tables With/Without Tablespace :
        Most “CREATE” SQL commands come with a “TABLESPACE” option using which you can specify the tablespace in which to create that SQL object. Let’s try a few:

        postgres=# create database dbname2 tablespace tbs1;
        CREATE DATABASE
        postgres=# \c dbname2
        You are now connected to database "dbname2" as user "nijam".
        dbname2=# create table t1 (a int);
        CREATE TABLE
        dbname2=# create table t2 (a int) tablespace tbs1;
        CREATE TABLE
        dbname2=# create table t3 (a int) tablespace tbs2;
        CREATE TABLE

        Here’s what  happened:

        • We created a database called “dbname2” in the tablespace “tbs1”. The default tablespace for all objects in the database also becomes tbs1.
        • The tables “t1” and “t2” are created in tbs1. You can explicitly specify the tablespace for the table, or use the database’s default.
        • The table “t3” is created in the tbs2 tablespace. It is possible to have only some objects in another tablespace.

        Physical Location with oid2name:

        postgres=# create table t4 ( a int );
        CREATE TABLE
        
        postgres=# select tablespace from pg_tables where tablename = 't4';
         tablespace 
        ------------
         NULL
        (1 row)

        NULL, in this case, means default tablespace.

        If you want to know where exactly the files that make up the tables are you can use oid2name:
        oid2name is a utility program that helps administrators to examine the file structure used by PostgreSQL.

        $ oid2name -t t4
        From database "postgres":
          Filenode  Table Name
        ----------------------
             24592          t4
        
        $ find $PGDATA -name 2459*
        /u02/pgdata/PG961/base/13322/24592

        In addition oid2name tells you more about the databases and the default tablespace associated to them:

        $ oid2name 
        All databases:
            Oid  Database Name  Tablespace
        ----------------------------------
          13322       postgres  pg_default
          13321      template0  pg_default
              1      template1  pg_default

        There are three main patterns paths in Tablespace:

        • 1.For files in the default tablespace: base/database_oid/table_and_index_files_oid
        • 2.For files in Non-default tablespace:The directory $PGDATA/pg_tblspc contains symbolic links that point to each of the non-built-in tablespaces defined in the cluster. pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid /table_and_index_files_oid
        • 3.For shared relations (see below): global/table_and_index_files_oid
        postgres=#\! ls -l /u02/tbs1/
        total 0
        drwx------. 2 postgres postgres 6 Nov 25 11:03 PG_9.6_201608131

        At least a directory which contains the version of PostgreSQL was created. What is inside this directory?

        postgres=#\! ls -l /u02/tbs1/PG_9.6_201608131/
        total 0

        Nothing, so lets create a table in this brand new tablespace:

        postgres=#create table t1 ( a int ) tablespace tbs1;
        CREATE TABLE
        
        postgres=# \d+ t1
                                  Table "public.t1"
         Column |  Type   | Modifiers | Storage | Stats target | Description 
        --------+---------+-----------+---------+--------------+-------------
         a      | integer |           | plain   |              | 
        Tablespace: "tbs1"

        How does the directory look like now?:

        postgres=#\! ls -l /u02/tbs1/PG_9.6_201608131/
        total 0
        drwx------. 2 postgres postgres 18 Nov 25 12:02 13322

        Ok, 13322 is the OID of the database which the table belongs to:

        postgres=# \! oid2name
        All databases:
            Oid  Database Name  Tablespace
        ----------------------------------
          13322       postgres  pg_default
          13321      template0  pg_default
              1      template1  pg_default

        And below that?

        postgres=#\! ls -l /u02/tbs1/PG_9.6_201608131/13322/
        total 0
        -rw-------. 1 postgres postgres 0 Nov 25 12:02 24596

        This is the OID of the table.

        For More Reference about Tablespace
        Layout :
        How to find out the database and table a file path refers to
        on PostgreSQL database ?
        Basic Commands of Tablespace:
        To determine the set of existing tablespaces:
        select oid,spcname from pg_tablespace;
        Following meta-command is also useful for listing the existing tablespaces:
         \db+

        Tablespace Rename:
        alter tablespace tbs1 rename to tbs3;

        Changing Tablespace ownership:
        alter tablespace tbs1 owner to scott;

        Tablespace reset:
        alter tablepace tbs1 reset default_tablespace;

        Tablespace Drop:
        drop tablespace tbs1;
        Note: A tablespace cannot be dropped until all objects in all databases using the tablespace have been removed.


        Assign default tablespace to particular user:
        ALTER ROLE someuser SET default_tablespace = tbs1;
        Disk space occupied by a tablespace:
        select pg_size_pretty(pg_tablespace_size(‘tbs1’));
        (or)
        /u02/tbs1/du -c -h

         

        Temporarily for current session while you are creating a batch of tables using
         SET default_tablespace = tbs2;
         
        Changing the default tablespace for the whole instance:
        all newly created objects go into a new tablespace.

        postgres=#  alter system set default_tablespace='tbs3';
        ALTER SYSTEM
         
        postgres=#  select pg_reload_conf();
         pg_reload_conf 
        ----------------
         t
        (1 row)
         
        postgres=#  show default_tablespace ;
         default_tablespace 
        --------------------
         tbs3
        (1 row)

        Tablespace creation with I/O cost:

        CREATE TABLESPACE tbs3 LOCATION '/some_disk_mount' 
        WITH (seq_page_cost=0.5, random_page_cost=0.75, effective_io_concurrency=10);

        How to find what tablespace a table/index is in on PostgreSQL?
        For table:

        SELECT tablespace FROM pg_tables WHERE tablename = 't1' AND schemaname = 'schema1';

        For index:

        SELECT tablespace FROM pg_indexes WHERE indexname = 't1_index' AND schemaname = 'schema1';
        Temporary Objects

        • Temporary tables and indexes are created by PostgreSQL either when explicitly asked to (“CREATE TEMP TABLE..”) or when it needs to hold large datasets temporarily for completing a query.
        • It is possible to tell PostgreSQL to place such objects in a separate tablespace. For example, if too many temporary tables are created in normal course, it might be possible to speed up your queries by placing such objects in a tablespace with faster hardware, faster/unjournaled/uncompressed filesystems, or even in-memory filesystems.
        • The default value is an empty string, which results in all temporary objects being created in the default tablespace of the current database.
        • It is a simple trick to speed up complex Postgres queries
        • Use the option temp_tablespaces to tell Postgres which tablespace to use for creating temporary tables.
        postgres=# alter system set temp_tablespaces='tbs2';
        ALTER SYSTEM
         
        postgres=# select pg_reload_conf();
         pg_reload_conf 
        ----------------
         t
        (1 row)
        
        postgres=# show temp_tablespaces ;
         temp_tablespaces 
        ------------------
         tbs2
        (1 row)

                                              ( or)

        CREATE TABLESPACE tbs4 LOCATION '/u02/tbs1;

        We then modify postgresql.conf to tell postgres to use this tablespace for temporary objects:

        temp_tablespaces = 'tbs4'

        Finally, restart postgres server

        Temporary tables in PostgreSQL have three properties that distinguish them from ordinary tables:

        1. They’re stored in a special schema, so that they are normally visible only to the creating backend.
        2. They are managed by the local buffer manager rather than the shared buffer manager.
        3. They are not WAL-logged.
        Backup Of Tablespace:
        Using pg_basebackup to back up a PostgreSQL cluster that has multiple tablespaces needs a couple of extra steps.
        If you’re using tarball-format backup, each tablespace comes out as it’s own tarball (with the tablespace OID as the filename). While restoring, this must be restored to the same path (like “/tmp/space2”) that used to be present while restoring. This is probably a bit of a pain because the backup script needs to store this additional information also somewhere alongside the backup.

        Here’s how the tarball backup happens:

        /tmp$ pg_basebackup --format=t --gzip --compress=9 -D tarbackup
        /tmp$ ls -l tarbackup
        total 3684
        -rw-r--r-- 1 alice alice  937355 May  8 13:22 16385.tar.gz
        -rw-r--r-- 1 alice alice 2812516 May  8 13:22 base.tar.gz
        -rw------- 1 alice alice   19259 May  8 13:22 pg_wal.tar.gz

        For plain format backups, it is possible to specify a new location for each tablespace. The data from each tablespace is written out into a new location. Every tablespace (other than pg_default and pg_global) must be mapped to a new location in the command-line, like this:

        /tmp$ pg_basebackup --format=p --tablespace-mapping=/tmp/space2=/tmp/space2backup -D plainbackup
        /tmp$ ls -l plainbackup/pg_tblspc/
        total 0
        lrwxrwxrwx 1 alice alice 17 May  8 13:35 16385 -> /tmp/space2backup
        Streaming Replication with Tablespace:

        • Setting up a new standby for a primary that already has tablespaces involves bringing over the main data directory and each tablespace directories over to the standby. If you’re using pg_basebackup to do this, then use the plain format backup to also specify appropriate new locations for the tablespaces on the standby.
        • Creating a tablespace on the primary of a replicated server is bit tricker, because the paths for the new tablespace go over unmodified to the standby. The standby server expects an existing directory at the same location as in the primary, and creates a tablespace at that location. Typically, you’d want to:
        • prepare and mount filesystems at both primary and standby, mount points have to be the same
        • create empty directories within mount points if needed
        • create tablespace at primary

        More About Tablespace
        Postgresql moving tablespaces

        Prev How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database
        Next PostgreSQL UnderStanding Of Objects

        ADVERTISEMENT

        Latest Courses

        PostgreSQL Database

        PostgreSQL Database

        $600.00 $500.00
        Greenplum Database

        Greenplum Database

        $2,000.00 $1,500.00

        Preview Course

        Free

        Latest Posts

        PostgreSQL Patching version 9, 10,11
        10Oct2019
        Tools for PostgreSQL
        16Sep2019
        Postgres user creation and restrict DDL & database access
        13Sep2019

        Recent Forum Topics

        • Are you going to take your first ste
        • How to start working on an application?
        • please let me know pre requirements to increase work_mem
        • how to copy some data in one table to another table in postgres
        • postgres script for finding queries more than 1 hours ?

        2ndquadrant.in

        (+91) 8838953252

        ITsupport@rayafeel.com

        Company

        • About Us
        • Contact
        • Our Team
        • Blog

        COURSES

        • List Of Course
        • Become An Instructor
        • Events
        • Postgres Support Blog

        Support

        • DBA Support
        • Consultancy Services
        • Postgres Migration Blogs
        • Forum

        Recommend

        • Groups
        • Login
        • FAQs
        • SignUp

        IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

        • Privacy
        • Terms

        Become An Instructor?

        Join thousand of instructors and earn money hassle free!

        Get Started Now

        Login with:

        Login with Google Login with Twitter Login with LinkedIn Login with Microsoft

        Login with your site account

        Lost your password?

        Not a member yet? Register now

        Register a new account

        Are you a member? Login now

        Modal title

        Message modal