TutorialDBA - Support | Training | Consultant

 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 w...

how to find out the database and table a file path refers to on PostgreSQL database ? There are three main patterns for paths: 1.For files in the default tablespace: base/database_oid/filenode id for the relation 2.For files in Non-default tablespace : pg_tblspc / tablespace_oid / tablespace_version_subdir / database_oid / filenode id for the relation 3.For shared relations (see below): global/filenode id for the relation 1.For files in the default tablespace: Tables’ file names aren’t necessarily the same as their oids in pg_class and  can change when VACUUM FULL, TRUNCATE, etc are run.  For example: billing_db=# \dt+ List of relations Schema | Name | Type | Owner | Size | Description --------+------+-------+----------+---------+------------- public | t1 | table | postgres | 0 bytes | (1 row) billing_db=# SELECT pg_relation_filepath('t1'); pg_relation_filepath ---------------------- base/18144/18146 (1 row) billing_...

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

Create a directory mkdir /home/Postdata/pg_data sudo chown postgres:postgres /home/Postdata/pg_data psql -d crsp -c "CREATE TABLESPACE tab1 OWNER u1 LOCATION '/home/Postdata/pg_data'"; If I wanted to move my database into this new tablespace: psql -d postgres -c "ALTER DATABASE db1 SET TABLESPACE tab1"; But if I just want to move the stuff I use and manage: SELECT ' ALTER TABLE '||schemaname||'.'||tablename||' SET TABLESPACE tab1;' FROM pg_tables WHERE schemaname NOT IN ('pg_catalog', 'information_schema'); and saved it as tab1.sql I then ran the following on the command line: psql -d db1 < tab1.sql | grep ALTER | psql -d db1 This uses SQL to create new SQL, then filters the relevant lines (these have the word “ALTER” in them), then pass these to psql. 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 wh...