SRE文档

深浅模式

1.PostgreSQL在线扩容 ​

[!WARNING]

第一种：懒汉模式 直接使用系统默认表空间pg_default,不需要知道表空间的概念

[!WARNING]

第二种：悲观模式 用户预先创建好文件系统目录，创建自定义表空间，再创建数据库，建库使用自定义表空间，以后使用过程中直接创建表无需关注表空间，无感知。这也是推荐比较良好的使用习惯

[!WARNING]

第三种：乐观模式 不够用或者出问题再扩容 不管是使用默认表空间或者自定义表空间也有可能会出现空间满的情况，这时可以在现有库对新表指定新的表空间来满足使用

演示： ​

sql
postgres@postgres=>select oid,spcname from pg_tablespace ;
 oid  |  spcname   
------+------------
 1663 | pg_default
 1664 | pg_global
(2 rows)

-- 初始化后默认有三个数据库，template0和template1，一般不能直接使用
-- 另一个postgres数据库oid为13593

postgres@postgres=>select oid,datname from pg_database ;
  oid  |  datname  
-------+-----------
 13447 | postgres
     1 | template1
 13446 | template0
(4 rows)

-- 第一种：postgres数据库创建数据表t
postgres@postgres=>create table t(id int,info text) ;
CREATE TABLE

postgres@postgres=>select to_regclass('t')::oid;
 to_regclass 
-------------
       17726
(1 row)

--查看文件存储位置
postgres@postgres=>select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13447/17726
(1 row)
-- base为$PGDATA下的目录，默认表空间pg_default放在此目录下
-- 13447代表数据库postgres
-- 17726是上面t表的oid


-- 创建表空间名字
postgres@postgres=>create tablespace my_tablespace owner postgres location '/home/ptgres/kongjian';
CREATE TABLESPACE

-- 新建数据库test,并指定表空间
postgres@postgres=>create database test owner=test tablespace=my_tablespace;
CREATE DATABASE


-- 查看数据库，可以看到test数据库的oid为 17734
postgres@postgres=>select oid,datname from pg_database ;
  oid  |  datname  
-------+-----------
 13447 | postgres
     1 | template1
 13446 | template0
 17734 | test
(5 rows)


-- 第二种：test数据库使用test用户创建数据表t
test=>  create table t(id int,info text) ;
test=>  select to_regclass('t')::oid;
 to_regclass 
-------------
       24694
(1 row)

--查看文件存储位置
test=> select pg_relation_filepath('t');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/24692/PG_12_201909212/24693/24694
(1 row)
-- pg_tblspc下为符号链接
-- 24692是自定义表空间my_tablespace的oid
-- PG_12_201909212是一个特定命名，12是主版本号，201909212是目录版本号
-- 24693是test数据库的oid
-- 24694是t表的oid

$ ls -l pg_tblspc/
lrwxrwxrwx. 1 postgres dba 17 Jan  7 14:07 24692 -> /opt/mytablespace

-- 第三种：postgres数据库创建数据表t2，同时指定表空间
postgres=# create table t2 (id int,info text) tablespace my_tablespace ;
postgres=# select pg_relation_filepath('t2');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/24692/PG_12_201909212/13593/24700
(1 row)
-- 24692是自定义表空间my_tablespace的oid
-- 13593是postgres数据库的oid
-- 24700是t2表的oid
postgres@postgres=>select oid,spcname from pg_tablespace ;
 oid  |  spcname   
------+------------
 1663 | pg_default
 1664 | pg_global
(2 rows)

-- 初始化后默认有三个数据库，template0和template1，一般不能直接使用
-- 另一个postgres数据库oid为13593

postgres@postgres=>select oid,datname from pg_database ;
  oid  |  datname  
-------+-----------
 13447 | postgres
     1 | template1
 13446 | template0
(4 rows)

-- 第一种：postgres数据库创建数据表t
postgres@postgres=>create table t(id int,info text) ;
CREATE TABLE

postgres@postgres=>select to_regclass('t')::oid;
 to_regclass 
-------------
       17726
(1 row)

--查看文件存储位置
postgres@postgres=>select pg_relation_filepath('t');
 pg_relation_filepath 
----------------------
 base/13447/17726
(1 row)
-- base为$PGDATA下的目录，默认表空间pg_default放在此目录下
-- 13447代表数据库postgres
-- 17726是上面t表的oid


-- 创建表空间名字
postgres@postgres=>create tablespace my_tablespace owner postgres location '/home/ptgres/kongjian';
CREATE TABLESPACE

-- 新建数据库test,并指定表空间
postgres@postgres=>create database test owner=test tablespace=my_tablespace;
CREATE DATABASE


-- 查看数据库，可以看到test数据库的oid为 17734
postgres@postgres=>select oid,datname from pg_database ;
  oid  |  datname  
-------+-----------
 13447 | postgres
     1 | template1
 13446 | template0
 17734 | test
(5 rows)


-- 第二种：test数据库使用test用户创建数据表t
test=>  create table t(id int,info text) ;
test=>  select to_regclass('t')::oid;
 to_regclass 
-------------
       24694
(1 row)

--查看文件存储位置
test=> select pg_relation_filepath('t');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/24692/PG_12_201909212/24693/24694
(1 row)
-- pg_tblspc下为符号链接
-- 24692是自定义表空间my_tablespace的oid
-- PG_12_201909212是一个特定命名，12是主版本号，201909212是目录版本号
-- 24693是test数据库的oid
-- 24694是t表的oid

$ ls -l pg_tblspc/
lrwxrwxrwx. 1 postgres dba 17 Jan  7 14:07 24692 -> /opt/mytablespace

-- 第三种：postgres数据库创建数据表t2，同时指定表空间
postgres=# create table t2 (id int,info text) tablespace my_tablespace ;
postgres=# select pg_relation_filepath('t2');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/24692/PG_12_201909212/13593/24700
(1 row)
-- 24692是自定义表空间my_tablespace的oid
-- 13593是postgres数据库的oid
-- 24700是t2表的oid

扩容： ​

1.查看表空间 ​

sql
postgres=# \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size   | Description 
------------+----------+----------+-------------------+---------+---------+-------------
 pg_default | postgres |          |                   |         | 1088 MB | 
 pg_global  | postgres |          |                   |         | 2167 kB | 
(2 rows)
postgres=# \db+
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size   | Description 
------------+----------+----------+-------------------+---------+---------+-------------
 pg_default | postgres |          |                   |         | 1088 MB | 
 pg_global  | postgres |          |                   |         | 2167 kB | 
(2 rows)

可以看到这里没有自定义表空间，默认使用pg_default 表空间

2.创建演示数据库和用户 ​

使用新的数据库app,并且owner为app用户来进行演示

sql
postgres@postgres=>create user app password'XXX123456';
CREATE ROLE

postgres@postgres=>create database app owner app;
CREATE DATABASE

postgres@postgres=>\c app app
You are now connected to database "app" as user "app".

app@app=> create schema app authorization app;
CREATE SCHEMA

app@app=>create table public.t1(id int);
CREATE TABLE

app@app=>create table app.t2(id int);
CREATE TABLE
postgres@postgres=>create user app password'XXX123456';
CREATE ROLE

postgres@postgres=>create database app owner app;
CREATE DATABASE

postgres@postgres=>\c app app
You are now connected to database "app" as user "app".

app@app=> create schema app authorization app;
CREATE SCHEMA

app@app=>create table public.t1(id int);
CREATE TABLE

app@app=>create table app.t2(id int);
CREATE TABLE

3.新建表空间 ​

创建表空间需要超级用户权限

sql
postgres@postgres=>create tablespace tbl_app owner app location '/home/ptgres/kongjian';
CREATE TABLESPACE
postgres@postgres=>create tablespace tbl_app owner app location '/home/ptgres/kongjian';
CREATE TABLESPACE

4.数据库app使用新表空间作为默认表空间 ​

sql
postgres@postgres=>\c app app
You are now connected to database "app" as user "app".

app@app=>alter database app set default_tablespace to tbl_app;
ALTER DATABASE
postgres@postgres=>\c app app
You are now connected to database "app" as user "app".

app@app=>alter database app set default_tablespace to tbl_app;
ALTER DATABASE

5.修改用户app默认表空间为tbl_app ​

先恢复下上面的配置

sql
app@app=>drop table public.t3;
DROP TABLE
app@app=>drop table app.t4;
DROP TABLE
app@app=> alter user app set default_tablespace to tbl_app;
ALTER ROLE
app@app=>\q

-- 退出psql再重新连接
postgres@postgres=>\c app app
You are now connected to database "app" as user "app".
app@app=>create table public.t5(id int);
CREATE TABLE
app@app=>create table app.t6(id int);
CREATE TABLE
app@app=>select pg_relation_filepath('t5');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/17744/PG_12_201909212/17736/17751
(1 row)

app@app=>select pg_relation_filepath('app.t6');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/17744/PG_12_201909212/17736/17754
(1 row)
app@app=>drop table public.t3;
DROP TABLE
app@app=>drop table app.t4;
DROP TABLE
app@app=> alter user app set default_tablespace to tbl_app;
ALTER ROLE
app@app=>\q

-- 退出psql再重新连接
postgres@postgres=>\c app app
You are now connected to database "app" as user "app".
app@app=>create table public.t5(id int);
CREATE TABLE
app@app=>create table app.t6(id int);
CREATE TABLE
app@app=>select pg_relation_filepath('t5');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/17744/PG_12_201909212/17736/17751
(1 row)

app@app=>select pg_relation_filepath('app.t6');
            pg_relation_filepath             
---------------------------------------------
 pg_tblspc/17744/PG_12_201909212/17736/17754
(1 row)

如果要迁移数据表的表空间，会锁表，要注意对业务的影响。

新建表修改database级别或者user级别(一般还是database级别)的默认表空间，只需配置一次，原有的数据表存储不改变，这种方式是推荐的