Lin Hong's TECH Blog! 刀不磨要生锈,人不学习要落后 - Thinking ahead

Postgresql 12 Database Study 005 Tips

2020-09-01

Postgresql 12 Database Study 005 Tips

Postgresql 数据库

数据库

数据库由一个或者多个模式(schema)组成

逻辑结构: PG Server -> Database -> Schema -> table/index/function

创建数据库需要超级用户superuser权限或者有创建数据库(create database)的角色(role)用户

创建数据库

语法:

postgres=# \h create database
Command:     CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
    [ [ WITH ] [ OWNER [=] user_name ]
           [ TEMPLATE [=] template ]
           [ ENCODING [=] encoding ]
           [ LC_COLLATE [=] lc_collate ]
           [ LC_CTYPE [=] lc_ctype ]
           [ TABLESPACE [=] tablespace_name ]
           [ ALLOW_CONNECTIONS [=] allowconn ]
           [ CONNECTION LIMIT [=] connlimit ]
           [ IS_TEMPLATE [=] istemplate ] ]

postgres=#
postgres=# \h create role
Command:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp'
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid

postgres=#

具体说明:

创建的参数 用途说明
name 创建数据库的名称
user_name 数据库所属用户的角色名称,缺省使用执行命令的用户
template 数据库使用模板的名称,缺省为template1(template0是不可修改的空白数据库)
encoding 数据库使用的字符集编码,缺省值是模板数据库使用的字符集编码。如指定一个字符串常量(如’UFT8’),或者代表字符集编码的整数等
lc_collate 数据库使用 collation 顺序。该参数会影响到字符串的排序,比如查询语句中使用order by,以及文本类型列的索引排序,缺省使用模板数据库的lc_collate
lc_ctype 数据库使用的字符分类,该参数会影响字符的分类,比如大小写字符、数字,缺省使用模板数据库的lc_ctype
tablespace_name 数据库相关的表空间名称,缺省使用模板数据库的表空间
connlimit 数据库可以接受的并发连接的数量,缺省为-1,表示无限制。

另外注意: create database 不能在事务块中执行

还有createdb的命令快速创建(linux平台下调用此命令)

root 用户yum安装which命令
# yum install which -y
postgres查看createdb

-bash-4.2$ which createdb
/usr/pgsql-10/bin/createdb
-bash-4.2$ createdb --help
createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
  -O, --owner=OWNER            database user to own the new database
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

By default, a database with the same name as the current user is created.

Report bugs to <pgsql-bugs@postgresql.org>.
-bash-4.2$

创建数据库示例

  • 创建表空间tab_01
  • 给用户lyn在表空间tab_01有创建的权限
  • 创建数据库db01,owner是lyn,关联表空间tab_01
  • lyn连接db_01测试
  • 行列显示 pg_database 的结果
  • 查看数据库各种属性
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 lyn       |                                                            | {}
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# create tablespace tab_01 location '/pgdata/10/tab_01';
CREATE TABLESPACE
postgres=# grant create on tablespace tab_01 to lyn;
GRANT
postgres=# create database db01 owner lyn tablespace tab_01;
CREATE DATABASE
postgres=# \c db01 lyn;
You are now connected to database "db01" as user "lyn".
db01=> \db
            List of tablespaces
    Name    |  Owner   |     Location
------------+----------+-------------------
 pg_default | postgres |
 pg_global  | postgres |
 tab_01     | postgres | /pgdata/10/tab_01
(3 rows)

db01=> \x
Expanded display is on.
db01=> select * from pg_database;
-[ RECORD 1 ]-+------------------------------------
datname       | postgres
datdba        | 10
encoding      | 6
datcollate    | en_US.utf8
datctype      | en_US.utf8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12952
datfrozenxid  | 548
datminmxid    | 1
dattablespace | 1663
datacl        |
-[ RECORD 2 ]-+------------------------------------
datname       | db01
datdba        | 16385
encoding      | 6
datcollate    | en_US.utf8
datctype      | en_US.utf8
datistemplate | f
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12952
datfrozenxid  | 548
datminmxid    | 1
dattablespace | 16387
datacl        |
-[ RECORD 3 ]-+------------------------------------
datname       | template1
datdba        | 10
encoding      | 6
datcollate    | en_US.utf8
datctype      | en_US.utf8
datistemplate | t
datallowconn  | t
datconnlimit  | -1
datlastsysoid | 12952
datfrozenxid  | 548
datminmxid    | 1
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}
-[ RECORD 4 ]-+------------------------------------
datname       | template0
datdba        | 10
encoding      | 6
datcollate    | en_US.utf8
datctype      | en_US.utf8
datistemplate | t
datallowconn  | f
datconnlimit  | -1
datlastsysoid | 12952
datfrozenxid  | 548
datminmxid    | 1
dattablespace | 1663
datacl        | {=c/postgres,postgres=CTc/postgres}

db01=>

修改数据库

语法:

postgres=# \h alter database
Command:     ALTER DATABASE
Description: change a database
Syntax:
ALTER DATABASE name [ [ WITH ] option [ ... ] ]

where option can be:

    ALLOW_CONNECTIONS allowconn
    CONNECTION LIMIT connlimit
    IS_TEMPLATE istemplate

ALTER DATABASE name RENAME TO new_name

ALTER DATABASE name OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

ALTER DATABASE name SET TABLESPACE new_tablespace

ALTER DATABASE name SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER DATABASE name SET configuration_parameter FROM CURRENT
ALTER DATABASE name RESET configuration_parameter
ALTER DATABASE name RESET ALL

postgres=#
  • allowconn 如果设置为false,则这个db不能连接

  • connlimit 允许最大连接数(但不包括超级用户连接和后台进程的连接)

  • istemplate 如果设置为true的话,可以指作为template 数据库,用于clone使用

  • configuration_parameter value 为当前数据库会话指定的配置参数设置指定值,如果value的值为DEFAULT,或者使用RESET,数据库指定的设置将被删除。在新的会话中会继承系统范围内的所有默认配置。

  • set xxx from current 把当前值保存为数据库指定值, 用reset all 清除所有数据库指定配置

示例:

  • 数据库db01表空间的更改
postgres=# \x
Expanded display is off.
postgres=# \db
            List of tablespaces
    Name    |  Owner   |     Location
------------+----------+-------------------
 pg_default | postgres |
 pg_global  | postgres |
 tab_01     | postgres | /pgdata/10/tab_01
(3 rows)

postgres=# select datname,spcname from pg_database, pg_tablespace where pg_database.dattablespace = pg_tablespace.oid;
  datname  |  spcname
-----------+------------
 postgres  | pg_default
 db01      | tab_01
 template1 | pg_default
 template0 | pg_default
(4 rows)

postgres=# alter database db01 set tablespace pg_default;
ALTER DATABASE
postgres=# select datname,spcname from pg_database, pg_tablespace where pg_database.dattablespace = pg_tablespace.oid;
  datname  |  spcname
-----------+------------
 postgres  | pg_default
 template1 | pg_default
 template0 | pg_default
 db01      | pg_default
(4 rows)

postgres=# alter database db01 set tablespace tab_01;
ALTER DATABASE
postgres=# select datname,spcname from pg_database, pg_tablespace where pg_database.dattablespace = pg_tablespace.oid;
  datname  |  spcname
-----------+------------
 postgres  | pg_default
 template1 | pg_default
 template0 | pg_default
 db01      | tab_01
(4 rows)

postgres=#

修改数据库参数

postgres=# \c db01 lyn
You are now connected to database "db01" as user "lyn".
db01=> \h show
Command:     SHOW
Description: show the value of a run-time parameter
Syntax:
SHOW name
SHOW ALL

db01=> show enable_indexscan ;
 enable_indexscan
------------------
 on
(1 row)

db01=> alter database db01 set enable_indexscan off;
ERROR:  syntax error at or near "off"
LINE 1: alter database db01 set enable_indexscan off;
                                                 ^
db01=> alter database db01 set enable_indexscan to off;
ALTER DATABASE
db01=> show enable_indexscan;
 enable_indexscan
------------------
 on
(1 row)

db01=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# show enable_indexscan;
 enable_indexscan
------------------
 on
(1 row)

postgres=# \c db01 lyn;
You are now connected to database "db01" as user "lyn".
db01=> show enable_indexscan;
 enable_indexscan
------------------
 off
(1 row)

db01=>

删除数据库

语法:

db01=> \h drop database
Command:     DROP DATABASE
Description: remove a database
Syntax:
DROP DATABASE [ IF EXISTS ] name

db01=>

示例:

  • 删除时候需要断开全部连接

  • 当前连接中的数据库不能删除当前数据库

db01=> drop database db01;
ERROR:  cannot drop the currently open database
db01=> \c
You are now connected to database "db01" as user "lyn".
db01=> \c postgres postgres
You are now connected to database "postgres" as user "postgres".
postgres=# drop database db01;
ERROR:  database "db01" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=# drop database db01;
DROP DATABASE
postgres=#

docker 提交现有状态

[root@centos7_pg ~]# docker commit pgtest01 postgresql_100days:day05
sha256:4ef2f2733421c2a94d7a8ac7a19d21681336fa0642035369660b2f712c76c088
[root@centos7_pg ~]# docker ps
CONTAINER ID        IMAGE                        COMMAND                  CREATED             STATUS              PORTS                    NAMES
0b35109c39c9        postgres_10_centos7.7.1908   "docker-entrypoint.s…"   2 hours ago         Up 2 hours          0.0.0.0:5431->5432/tcp   pgtest01
[root@centos7_pg ~]# docker image ls
REPOSITORY                   TAG                 IMAGE ID            CREATED             SIZE
postgresql_100days           day05               4ef2f2733421        9 seconds ago       637MB
postgres_10_centos7.7.1908   latest              b2afe3990005        26 hours ago        477MB
centos                       7.6.1810            f1cb7c7d58b7        13 months ago       202MB
[root@centos7_pg ~]#

Have a good work&life! 2020/09 via LinHong


Similar Posts

Comments