Working with Tablespace in PostgreSQL

最新推荐文章于 2025-03-24 17:31:11 发布

数据架构

最新推荐文章于 2025-03-24 17:31:11 发布

阅读量408

点赞数

分类专栏： Postgres 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/u011868279/article/details/130404163

版权

Postgres 专栏收录该内容

12 篇文章

订阅专栏

文章介绍了在PostgreSQL数据库中管理表空间的过程，包括创建新的表空间如hrd，将表如test1分配到特定表空间，以及在不同操作系统环境下（Windows和Linux）移动和删除表空间的步骤。操作涉及AlterTable和DropTablespace命令。

摘要由CSDN通过智能技术生成

1.Tablespace & its advantages

2.Default Tablespaces

3.Create tablespace

Windows:

C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=# select * from pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)


postgres=# create tablespace hrd location 'C:\user_tablespace';
CREATE TABLESPACE
postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 41022 | hrd        |       10 |        |
(3 rows)


postgres=# create table test1(sid int, serialname varchar(100)) tablespace hrd;
CREATE TABLE
postgres=# select * from pg_tables where tablename='test1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test1     | postgres   | hrd        | f          | f        | f           | f
(1 row)


postgres=#

Linux:

[root@postgres ~]# 
[root@postgres ~]# systemctl status postgresql-13
● postgresql-13.service - PostgreSQL 13 database server
   Loaded: loaded (/usr/lib/systemd/system/postgresql-13.service; enabled; vendor preset: disabled)
   Active: active (running) since Mon 2023-04-24 19:21:11 HKT; 2 days ago
     Docs: https://www.postgresql.org/docs/13/static/
  Process: 5240 ExecStartPre=/usr/pgsql-13/bin/postgresql-13-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
 Main PID: 5246 (postmaster)
    Tasks: 8 (limit: 24548)
   Memory: 25.2M
   CGroup: /system.slice/postgresql-13.service
           ├─5246 /usr/pgsql-13/bin/postmaster -D /var/lib/pgsql/13/data/
           ├─5247 postgres: logger 
           ├─5249 postgres: checkpointer 
           ├─5250 postgres: background writer 
           ├─5251 postgres: walwriter 
           ├─5252 postgres: autovacuum launcher 
           ├─5253 postgres: stats collector 
           └─5254 postgres: logical replication launcher 

Apr 24 19:21:11 postgres systemd[1]: Starting PostgreSQL 13 database server...
Apr 24 19:21:11 postgres postmaster[5246]: 2023-04-24 19:21:11.117 HKT [5246] LOG:  redirecting log output to logging collector process
Apr 24 19:21:11 postgres postmaster[5246]: 2023-04-24 19:21:11.117 HKT [5246] HINT:  Future log output will appear in directory "log".
Apr 24 19:21:11 postgres systemd[1]: Started PostgreSQL 13 database server.
[root@postgres ~]# 
[root@postgres ~]# su - postgres
[postgres@postgres ~]$ 
[postgres@postgres ~]$ psql
psql (13.9)
Type "help" for help.

postgres=# exit
[postgres@postgres ~]$ ls -ltr
total 0
drwx------ 4 postgres postgres 51 Nov 21 20:41 13
[postgres@postgres ~]$ cd 13
[postgres@postgres 13]$ cd data
[postgres@postgres data]$ ls - ltr
ls: cannot access '-': No such file or directory
ls: cannot access 'ltr': No such file or directory
[postgres@postgres data]$ ls -ltr
total 64
-rw------- 1 postgres postgres     3 Nov 21 20:41 PG_VERSION
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_twophase
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_tblspc
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_snapshots
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_serial
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_replslot
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_notify
drwx------ 4 postgres postgres    36 Nov 21 20:41 pg_multixact
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_dynshmem
drwx------ 2 postgres postgres     6 Nov 21 20:41 pg_commit_ts
-rw------- 1 postgres postgres  1636 Nov 21 20:41 pg_ident.conf
drwx------ 2 postgres postgres    18 Nov 21 20:41 pg_xact
drwx------ 3 postgres postgres    60 Nov 21 20:41 pg_wal
drwx------ 2 postgres postgres    18 Nov 21 20:41 pg_subtrans
drwx------ 5 postgres postgres    41 Nov 21 20:41 base
-rw------- 1 postgres postgres 28093 Nov 21 20:50 postgresql.conf
-rw------- 1 postgres postgres  4580 Nov 21 21:01 pg_hba.conf
drwx------ 2 postgres postgres    84 Apr 23 20:00 log
-rw------- 1 postgres postgres   106 Apr 23 20:06 postgresql.auto.conf
-rw------- 1 postgres postgres    58 Apr 24 19:21 postmaster.opts
-rw------- 1 postgres postgres    95 Apr 24 19:21 postmaster.pid
drwx------ 2 postgres postgres     6 Apr 24 19:21 pg_stat
drwx------ 2 postgres postgres  4096 Apr 24 19:21 global
drwx------ 4 postgres postgres    68 Apr 24 20:21 pg_logical
-rw------- 1 postgres postgres    30 Apr 25 12:02 current_logfiles
drwx------ 2 postgres postgres    63 Apr 27 10:02 pg_stat_tmp
[postgres@postgres data]$ df -h
Filesystem           Size  Used Avail Use% Mounted on
devtmpfs             1.9G     0  1.9G   0% /dev
tmpfs                2.0G   16K  2.0G   1% /dev/shm
tmpfs                2.0G  9.8M  1.9G   1% /run
tmpfs                2.0G     0  2.0G   0% /sys/fs/cgroup
/dev/mapper/cs-root   51G   11G   41G  21% /
/dev/mapper/cs-home   25G  294M   25G   2% /home
/dev/sda1           1014M  236M  779M  24% /boot
tmpfs                390M   36K  390M   1% /run/user/1000
[postgres@postgres data]$ cd /opt
[postgres@postgres opt]$ ls -ltr
total 0
drwxr-xr-x. 3 root root 28 Nov 21 19:57 rh
[postgres@postgres opt]$ mkdir user_tablespace
[postgres@postgres opt]$ 
[postgres@postgres opt]$ cd user_tablespace/
[postgres@postgres user_tablespace]$ pwd
/opt/user_tablespace
[postgres@postgres user_tablespace]$ psql
psql (13.9)
Type "help" for help.

postgres=# select * from pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions 
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        | 
 1664 | pg_global  |       10 |        | 
(2 rows)

postgres=# create tablespace hrd location 'opt/user_tablespace';
ERROR:  tablespace location must be an absolute path
postgres=# create tablespace hrd location '/opt/user_tablespace';
CREATE TABLESPACE
postgres=# select * from pg_tablespace;                          
  oid  |  spcname   | spcowner | spcacl | spcoptions 
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        | 
  1664 | pg_global  |       10 |        | 
 16386 | hrd        |       10 |        | 
(3 rows)

postgres=# \q
[postgres@postgres user_tablespace]$ ls -ltr
total 0
drwx------ 2 postgres postgres 6 Apr 27 10:05 PG_13_202007201
[postgres@postgres user_tablespace]$ cd PG_13_202007201/
[postgres@postgres PG_13_202007201]$ ls -ltr
total 0
[postgres@postgres PG_13_202007201]$ psql
psql (13.9)
Type "help" for help.

postgres=# CREATE TABLE TEST1(SIDNO INT,SERIALNAME VARCHAR(100)) TABLESPACE HRD;
CREATE TABLE
postgres=# select * from pg_tables where tablename='test1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity 
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test1     | postgres   | hrd        | f          | f        | f           | f
(1 row)

postgres=# exit
[postgres@postgres PG_13_202007201]$ ls -ltr
total 0
drwx------ 2 postgres postgres 19 Apr 27 10:06 14386
[postgres@postgres PG_13_202007201]$ cd 14386/
[postgres@postgres 14386]$ ls -ltr
total 0
-rw------- 1 postgres postgres 0 Apr 27 10:06 16387
[postgres@postgres 14386]$

4.Move Table between tablespaces

5.Drop Tablespace

C:\Users\Maxwell Pan>
C:\Users\Maxwell Pan>psql -U postgres
Password for user postgres:
psql (15.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

postgres=#
postgres=# select * from pg_tables where tablename='test1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test1     | postgres   | hrd        | f          | f        | f           | f
(1 row)


postgres=# drop tablespace hrd;
ERROR:  tablespace "hrd" is not empty
postgres=# select * from pg_tablespace;
  oid  |  spcname   | spcowner | spcacl | spcoptions
-------+------------+----------+--------+------------
  1663 | pg_default |       10 |        |
  1664 | pg_global  |       10 |        |
 41022 | hrd        |       10 |        |
(3 rows)


postgres=# alter table test1 set tablespace pg_default;
ALTER TABLE
postgres=# select * from pg_tables where tablename='test1';
 schemaname | tablename | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
------------+-----------+------------+------------+------------+----------+-------------+-------------
 public     | test1     | postgres   |            | f          | f        | f           | f
(1 row)


postgres=# select pg_relation_filepath('test1');
 pg_relation_filepath
----------------------
 base/5/41026
(1 row)


postgres=# drop tablespace hrd;
DROP TABLESPACE
postgres=# select * from pg_tablespace;
 oid  |  spcname   | spcowner | spcacl | spcoptions
------+------------+----------+--------+------------
 1663 | pg_default |       10 |        |
 1664 | pg_global  |       10 |        |
(2 rows)


postgres=#