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