• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • Scenario of Tablespace Moving In PostgreSQL Database

      Scenario of Tablespace Moving In PostgreSQL Database

      • Posted by Nijamutheen J
      • Categories Blog
      • Date January 16, 2019
      • Comments 0 comment

      Scenario:
      If Postgresql data partition is  running out of disk space How to solve this issue

      Ans:
      Option 1:Create a new tablespace on a separate drive and move existing tables to it.

      ALTER TABLE schema.table SET TABLESPACE tbs1;
      

      Or using following steps you can migrating data from old tablespace to new tablespace

      https://2ndquadrant.in/tablespace-postgres/

      You can also move all tables (or indexes) in one tablespace into another:

      postgres=# alter table all in tablespace tbs3 set tablespace pg_default; 
      ALTER TABLE
      

      Note: Downtime is required for alter table moving. For “online” moving one can though use tools like pg_repack or pg_squeeze,Affected tables are locked while being relocated

      Option 2:create a new tablespace and use for future tables:
      PostgreSQL provides many options for setting the default location of newly created tables

      You can default location based on the database using:

      ALTER DATABASE dbname SET default_tablespace = tbs2; 
      

      If you happened to have used mixed case when creating your database, you will need to quote the tablespace name e.g

      CREATE DATABASE dbname3 OWNER nijam TABLESPACE tbs2;
      

      based on the user creating the table

      ALTER ROLE someuser SET default_tablespace = tbs1;
      

      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:

      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)
      

      Option 3: using soft link you can move the tablespace location to other partition

      Moving PostgreSQL Tablespace Types

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      Moving Database objects or all tables to a different tablespace in PostgreSQL
      January 16, 2019

      Next post

      Moving PostgreSQL Tablespace Types
      January 16, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Don’t See What You’re Looking For? We’ll Customize a Package to Fit Your Business Office space!
      14May2022
      Need an Address, Phone Number, And Virtual Office In Chennai?
      14May2022
      Rayafeel Technologies can provide you with ideal meeting solutions
      14May2022
      Virtual Offices VS. Coworking Spaces: What’s The Difference?
      14May2022
      SQL Manager for Oracle Type 6 – 10
      13May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      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

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?