RDBMS Information - PostgreSQL - How to move physical data files from one drive to another one to change the tablespace


The page describes the steps to move data files from one drive to another one.
Data files are the physical files in the tablespace, storing the data for the schemas. It also describes about how to produce a report on Postgres DB statistics using the internal Postgres DB tables and views.

Applicable RDBMS

How to move physical data files from one drive to another one to change the tablespace:

  1. Create a folder for tablespace in the drive where you want to keep data files  in pgAdmin.
  2. Create a new tablespace and set the location in the drive to above folder in pgAdmin. e.g. – abc_cast
  3. Create a temporary database. e.g. temp_db .abc_cast tablespace need to be affected to temp_db database
  4. pgAdmin needs to be closed after Step-3
  5. Alter current database – We need to change its tablespace.
    Command - psql -p 2280 -d temp_db -U operator –c "ALTER DATABASE  postgres SET TABLESPACE abc_cast "
  6. Verify tablespace folder. This should contain the data files. You can confirm by checking the size of the folder.
  7. Verify by accessing different schema .
  8. After validation delete the temp. database created.

PostgreSQL Views that can be used for Monitoring purpose:

Please refer to the official documentation of PostgreSQL which lists all the views and their description. They can be used to monitor the statistics of the database - PostgreSQL Statistics Monitoring.

Please check the pre-requisites of deploying CSS2 and AIP and at least have the minimum hardware requirement available as stated in this document of target deployment scenario as per the deployment scenario.