• 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

      Blog

      • Home
      • Blog
      • Blog
      • Moving PostgreSQL Tablespace Types

      Moving PostgreSQL Tablespace Types

      • Posted by Nijamutheen J
      • Categories Blog
      • Date January 16, 2019
      • Comments 0 comment
      postgresql tablespace moving is two types
      they are:
      1. online moving – with database down
      2. offline moving – without database down
      why moving tablespace ?
      once default tablespace or non-default tablespace filesystem is full means you will get issue/error  at the time of new transaction in postgresql database
      Error:- Cannot insert in that file id
      For this purpose we are moving our tabelspace to some other filesystem location1.Online moving – with database down:
      online method is too slow method to moving tablespace to other filesystem and no need to stop/shutdown the cluster but objects will lock at the time of moving tables/indexes

      Steps for online moving tablespace:

      1. create a new tablespace in the desired new location
      2. go through all of the tables/indexes stored in the old tablespace, and do ALTER <object> TABLESPACE <new_tablespace>on each of them.
      3. drop the old tablespace
      4. More Reference : tablespace objects moving

      2.Offline moving – without database down
      Offline method is too fast for moving postgresql tablespace to other filesystem/partition but database mode should be shutdown/stop and data will be more consistency than online moving

      Steps for offline moving tablespace:
        
      step 1. Check the tablespace oid and name 

      nijam=# SELECT oid,spcname FROM pg_tablespace WHERE spcname = 'tblspc';
        oid  | spcname 
      -------+---------
       24580 | tblspc
      (1 row)
      (2)

      step 2.Stop postgres Server

      $ pg_ctl -D $PGDATA stop

      step 3.Move or copy tablespace data directory from old one to new one

      $ mv /old/dir /new/dir
      or
      $ cp -r /old/dir /new/dir
      $ rm -rf /old/dir

      step 4. Change tablespace’s link to new directoryIf you look in your $PGDATA directory, you will find a subdirectory called pg_tblspc.  This directory contains entries like this: 

      $ cd $PGDATA/pg_tblspc
      $ rm 24580
      $ ln -s /new/dir 24580

      step 5. Start postgres postgres server

      $ pg_ctl -D $PGDATA start
      

      Then, you can see the tablespace’s directory has changed.

      nijam=# SELECT pg_tablespace_location(24580);
      • Share:
      Admin bar avatar
      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

      Scenario of Tablespace Moving In PostgreSQL Database
      January 16, 2019

      Next post

      tablespace moving In Postgresql Database
      January 16, 2019

      Leave A Reply Cancel reply

      You must be logged in to post a comment.

      Login with:

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


      Search

      ADVERTISEMENT

      Latest Posts

      PostgreSQL Patching version 9, 10,11
      10Oct2019
      Tools for PostgreSQL
      16Sep2019
      Postgres user creation and restrict DDL & database access
      13Sep2019
      PostgreSQL SSL Setup
      07Sep2019
      How to DELETE current XLOG / WAL LOG in postgresql database ?
      19Aug2019

      Latest Courses

      PostgreSQL Database

      PostgreSQL Database

      $600.00 $500.00
      Greenplum Database

      Greenplum Database

      $2,000.00 $1,500.00

      Preview Course

      Free

      Recent Forum Topics

      • thought behind whiteboard activity
      • 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

      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