You are reading the article How To Create Tablespaces In Postgresql? updated in October 2023 on the website Nhunghuounewzealand.com. We hope that the information we have shared is helpful to you. If you find the content interesting and meaningful, please share it with your friends and continue to follow and support us for the latest updates. Suggested November 2023 How To Create Tablespaces In Postgresql?Introduction to PostgreSQL Tablespace
The PostgreSQL Tablespaces is a physical location on a drive or disk where the PostgreSQL stores data files containing database objects like indexes and tables etc. PostgreSQL uses it to map logical names to a physical location on a disk or drive.
Start Your Free Data Science Course
Hadoop, Data Science, Statistics & others
It has the following two default tablespaces:
pg_default: Stores all users data.
pg_global: Stores all global data.How does Tablespaces work in PostgreSQL?
The user who executes the CREATE TABLESPACE is the owner of the PostgreSQL tablespace.
The SQL statement also allows us to assign the ownership of it to a different user by specifying the name in the OWNER clause.
The path defined under the LOCATION clause is used to create the PostgreSQL tablespace; this path is an absolute path to an empty. In order to read and write data from/to the directory, the system user must own this directory.How to create Tablespaces in PostgreSQL?
Let’s illustrate the following syntax for creating a new tablespace:CREATE TABLESPACE tablespace_name OWNER user_name LOCATION directory_path;
The name of the PostgreSQL tablespace to create a new PostgreSQL tablespace. We can not define the name which begins with pg_; the names starting with pg_ are reserved for PostgreSQL system tablespaces.
The name defined is the user who will own the newly created PostgreSQL tablespace. If the user_name is not specified when creating a new PostgreSQL tablespace, then the user who executes the statement will become the owner of the new Tablespace. Only superusers can create this Tablespace, but they can assign ownership of the Tablespace to non-superusers.
It will use the directory defined under the LOCATION clause. The directory path should be defined as an absolute path. Also, the defined directory should be owned by the user and must be empty.
Let’s have a look at the following snapshot showing default tablespaces:
The following example will create a tablespace named ‘edutablespace’ at a specified location.CREATE TABLESPACE edutablespace OWNER postgres LOCATION 'C:MyFolderArticlesTablespaces';
Illustrate the result of the above statement with the help of the following snapshot.How to Alter Tablespaces in PostgreSQL?
To perform some alter operations on Tablespace using the ALTER TABLESPACE statement, you should be a superuser or the owner of it. We can not change the location used by Tablespace as it’s not supported by PostgreSQL yet.
Consider we have a tablespace that is created, and we want to change the tablespace definition with the help of the ALTER TABLESPACE as follows:ALTER TABLESPACE action;
It allows us to perform some actions like renaming the tablespace name, changing the tablespacehe own, and settinget some tablespace parameters.
1. To modify the tablespace name, we use the following statement:ALTER TABLESPACE tablespace_name RENAME TO new_name;
The following statement renames edutablespace Tablespace to edutablespace_new:ALTER TABLESPACE edutablespace RENAME TO edutablespace_new;
Illustrate the result of the above statement with the help of the following snapshot.
2. To modify the tablespace owner, we use the following statement:ALTER TABLESPACE tablespace_name OWNER TO new_owner;
Currently, the owner of the edutablespace_new is Postgres have a look at the following snapshot
The following statement changes the owner of the edutablespace_new Tablespace to ‘educba.’ALTER TABLESPACE edutablespace_new OWNER to educba;
3. Modify the parameters of the Tablespace.ALTER TABLESPACE tablespace_name SET parameter = value; How to delete Tablespaces in PostgreSQL?
In order to remove it, you should be the owner or the superuser.
Consider the following syntax:DROP TABLESPACE IF EXISTS tablespace_name;
Define the name of it after the DROP TABLESPACE clause.
The IF EXISTS helps us to prevent the error of deleting a non-existent tablespace.
We need to ensure the Tablespace is empty before removing it, which means verifying no database objects are inside the Tablespace.DROP TABLESPACE edutablespace_new;
The above statement will drop the edutablespace_new Tablespace.Conclusion
We hope from the above article, you have learned how to create a tablespace using CREATE TABLESPACE statement and change the Tablespace’s definition with the PostgreSQL ALTER help TABLESPACE statement.
We hope that this EDUCBA information on “PostgreSQL Tablespaces” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
You're reading How To Create Tablespaces In Postgresql?
Update the detailed information about How To Create Tablespaces In Postgresql? on the Nhunghuounewzealand.com website. We hope the article's content will meet your needs, and we will regularly update the information to provide you with the fastest and most accurate information. Have a great day!