PostgreSQL深入浅出 | 数据库的日常维护

2022年9月11日09:41:25数据库教程评论90 views字数 18400阅读模式

1、概述

本篇是继上一篇笔记《课程笔记 | PostgreSQL深入浅出 | 数据库的启动与停止》的续集,本篇主要介绍PostgreSQL数据库的日常基本操作。后续也会持续更新内容。

2、查看数据库版本

 

[postgres@localhost ~]$ pg_ctl -V

pg_ctl (PostgreSQL) 12.12

3、登录数据库

[postgres@localhost ~]$ psql -d postgres

psql (12.12)

Type "help" for help.

postgres=#

4、列出所有数据库

postgres=# \l

PostgreSQL深入浅出 | 数据库的日常维护

5、显示所有用户

postgres=# \du

PostgreSQL深入浅出 | 数据库的日常维护

6、查看扩展

postgres=# \dx

PostgreSQL深入浅出 | 数据库的日常维护

7、列出表和视图的信息

postgres=# \dS

postgres=# \dS

List of relations

Schema   |              Name               | Type  |  Owner

------------+---------------------------------+-------+----------

pg_catalog | pg_aggregate                    | table | postgres

pg_catalog | pg_am                           | table | postgres

pg_catalog | pg_amop                         | table | postgres

pg_catalog | pg_amproc                       | table | postgres

pg_catalog | pg_attrdef                      | table | postgres

pg_catalog | pg_attribute                    | table | postgres

pg_catalog | pg_auth_members                 | table | postgres

pg_catalog | pg_authid                       | table | postgres

pg_catalog | pg_available_extension_versions | view  | postgres

pg_catalog | pg_available_extensions         | view  | postgres

pg_catalog | pg_cast                         | table | postgres

pg_catalog | pg_class                        | table | postgres

pg_catalog | pg_collation                    | table | postgres

pg_catalog | pg_config                       | view  | postgres

pg_catalog | pg_constraint                   | table | postgres

pg_catalog | pg_conversion                   | table | postgres

pg_catalog | pg_cursors                      | view  | postgres

pg_catalog | pg_database                     | table | postgres

pg_catalog | pg_db_role_setting              | table | postgres

pg_catalog | pg_default_acl                  | table | postgres

pg_catalog | pg_depend                       | table | postgres

pg_catalog | pg_description                  | table | postgres

pg_catalog | pg_enum                         | table | postgres

pg_catalog | pg_event_trigger                | table | postgres

pg_catalog | pg_extension                    | table | postgres

pg_catalog | pg_file_settings                | view  | postgres

pg_catalog | pg_foreign_data_wrapper         | table | postgres

pg_catalog | pg_foreign_server               | table | postgres

pg_catalog | pg_foreign_table                | table | postgres

pg_catalog | pg_group                        | view  | postgres

pg_catalog | pg_hba_file_rules               | view  | postgres

pg_catalog | pg_index                        | table | postgres

pg_catalog | pg_indexes                      | view  | postgres

pg_catalog | pg_inherits                     | table | postgres

pg_catalog | pg_init_privs                   | table | postgres

pg_catalog | pg_language                     | table | postgres

pg_catalog | pg_largeobject                  | table | postgres

pg_catalog | pg_largeobject_metadata         | table | postgres

pg_catalog | pg_locks                        | view  | postgres

pg_catalog | pg_matviews                     | view  | postgres

pg_catalog | pg_namespace                    | table | postgres

pg_catalog | pg_opclass                      | table | postgres

pg_catalog | pg_operator                     | table | postgres

pg_catalog | pg_opfamily                     | table | postgres

pg_catalog | pg_partitioned_table            | table | postgres

pg_catalog | pg_pltemplate                   | table | postgres

pg_catalog | pg_policies                     | view  | postgres

pg_catalog | pg_policy                       | table | postgres

pg_catalog | pg_prepared_statements          | view  | postgres

pg_catalog | pg_prepared_xacts               | view  | postgres

pg_catalog | pg_proc                         | table | postgres

pg_catalog | pg_publication                  | table | postgres

pg_catalog | pg_publication_rel              | table | postgres

pg_catalog | pg_publication_tables           | view  | postgres

pg_catalog | pg_range                        | table | postgres

pg_catalog | pg_replication_origin           | table | postgres

pg_catalog | pg_replication_origin_status    | view  | postgres

pg_catalog | pg_replication_slots            | view  | postgres

pg_catalog | pg_rewrite                      | table | postgres

pg_catalog | pg_roles                        | view  | postgres

pg_catalog | pg_rules                        | view  | postgres

pg_catalog | pg_seclabel                     | table | postgres

pg_catalog | pg_seclabels                    | view  | postgres

pg_catalog | pg_sequence                     | table | postgres

pg_catalog | pg_sequences                    | view  | postgres

pg_catalog | pg_settings                     | view  | postgres

pg_catalog | pg_shadow                       | view  | postgres

pg_catalog | pg_shdepend                     | table | postgres

pg_catalog | pg_shdescription                | table | postgres

pg_catalog | pg_shseclabel                   | table | postgres

pg_catalog | pg_stat_activity                | view  | postgres

pg_catalog | pg_stat_all_indexes             | view  | postgres

pg_catalog | pg_stat_all_tables              | view  | postgres

pg_catalog | pg_stat_archiver                | view  | postgres

pg_catalog | pg_stat_bgwriter                | view  | postgres

pg_catalog | pg_stat_database                | view  | postgres

pg_catalog | pg_stat_database_conflicts      | view  | postgres

pg_catalog | pg_stat_gssapi                  | view  | postgres

pg_catalog | pg_stat_progress_cluster        | view  | postgres

pg_catalog | pg_stat_progress_create_index   | view  | postgres

pg_catalog | pg_stat_progress_vacuum         | view  | postgres

pg_catalog | pg_stat_replication             | view  | postgres

pg_catalog | pg_stat_ssl                     | view  | postgres

pg_catalog | pg_stat_subscription            | view  | postgres

pg_catalog | pg_stat_sys_indexes             | view  | postgres

pg_catalog | pg_stat_sys_tables              | view  | postgres

pg_catalog | pg_stat_user_functions          | view  | postgres

pg_catalog | pg_stat_user_indexes            | view  | postgres

pg_catalog | pg_stat_user_tables             | view  | postgres

pg_catalog | pg_stat_wal_receiver            | view  | postgres

pg_catalog | pg_stat_xact_all_tables         | view  | postgres

pg_catalog | pg_stat_xact_sys_tables         | view  | postgres

pg_catalog | pg_stat_xact_user_functions     | view  | postgres

pg_catalog | pg_stat_xact_user_tables        | view  | postgres

pg_catalog | pg_statio_all_indexes           | view  | postgres

pg_catalog | pg_statio_all_sequences         | view  | postgres

pg_catalog | pg_statio_all_tables            | view  | postgres

pg_catalog | pg_statio_sys_indexes           | view  | postgres

pg_catalog | pg_statio_sys_sequences         | view  | postgres

pg_catalog | pg_statio_sys_tables            | view  | postgres

pg_catalog | pg_statio_user_indexes          | view  | postgres

pg_catalog | pg_statio_user_sequences        | view  | postgres

pg_catalog | pg_statio_user_tables           | view  | postgres

pg_catalog | pg_statistic                    | table | postgres

pg_catalog | pg_statistic_ext                | table | postgres

pg_catalog | pg_statistic_ext_data           | table | postgres

pg_catalog | pg_stats                        | view  | postgres

pg_catalog | pg_stats_ext                    | view  | postgres

pg_catalog | pg_subscription                 | table | postgres

pg_catalog | pg_subscription_rel             | table | postgres

pg_catalog | pg_tables                       | view  | postgres

pg_catalog | pg_tablespace                   | table | postgres

pg_catalog | pg_timezone_abbrevs             | view  | postgres

pg_catalog | pg_timezone_names               | view  | postgres

pg_catalog | pg_transform                    | table | postgres

pg_catalog | pg_trigger                      | table | postgres

pg_catalog | pg_ts_config                    | table | postgres

pg_catalog | pg_ts_config_map                | table | postgres

pg_catalog | pg_ts_dict                      | table | postgres

pg_catalog | pg_ts_parser                    | table | postgres

pg_catalog | pg_ts_template                  | table | postgres

pg_catalog | pg_type                         | table | postgres

pg_catalog | pg_user                         | view  | postgres

pg_catalog | pg_user_mapping                 | table | postgres

pg_catalog | pg_user_mappings                | view  | postgres

pg_catalog | pg_views                        | view  | postgres

(126 rows)

postgres=#

补充:postgres=# \dS+ 显示了更多信息,包括表的大小信息。

 

8、查看数据库端口号

postgres=# show port;

 

PostgreSQL深入浅出 | 数据库的日常维护

9、查看表空间

postgres=# \db+

PostgreSQL深入浅出 | 数据库的日常维护

 

10、列出所有模式

postgres=# \dn

 

PostgreSQL深入浅出 | 数据库的日常维护

 

11、查看数据库的表

postgres=# \dt

12、查看表字段

postgres=# \d {{tablename}}

13、创建一个数据库

CREATE DATABASE DBTEST1;

 

PostgreSQL深入浅出 | 数据库的日常维护

 

14、切换数据库

postgres=# \c dbtest1

 

PostgreSQL深入浅出 | 数据库的日常维护

 

15、显示所有帮助信息

dbtest1=# \h

 

dbtest1=# \h

Available help:

ABORT                            CHECKPOINT                       CREATE USER                      DROP TRIGGER

ALTER AGGREGATE                  CLOSE                            CREATE USER MAPPING              DROP TYPE

ALTER COLLATION                  CLUSTER                          CREATE VIEW                      DROP USER

ALTER CONVERSION                 COMMENT                          DEALLOCATE                       DROP USER MAPPING

ALTER DATABASE                   COMMIT                           DECLARE                          DROP VIEW

ALTER DEFAULT PRIVILEGES         COMMIT PREPARED                  DELETE                           END

ALTER DOMAIN                     COPY                             DISCARD                          EXECUTE

ALTER EVENT TRIGGER              CREATE ACCESS METHOD             DO                               EXPLAIN

ALTER EXTENSION                  CREATE AGGREGATE                 DROP ACCESS METHOD               FETCH

ALTER FOREIGN DATA WRAPPER       CREATE CAST                      DROP AGGREGATE                   GRANT

ALTER FOREIGN TABLE              CREATE COLLATION                 DROP CAST                        IMPORT FOREIGN SCHEMA

ALTER FUNCTION                   CREATE CONVERSION                DROP COLLATION                   INSERT

ALTER GROUP                      CREATE DATABASE                  DROP CONVERSION                  LISTEN

ALTER INDEX                      CREATE DOMAIN                    DROP DATABASE                    LOAD

ALTER LANGUAGE                   CREATE EVENT TRIGGER             DROP DOMAIN                      LOCK

ALTER LARGE OBJECT               CREATE EXTENSION                 DROP EVENT TRIGGER               MOVE

ALTER MATERIALIZED VIEW          CREATE FOREIGN DATA WRAPPER      DROP EXTENSION                   NOTIFY

ALTER OPERATOR                   CREATE FOREIGN TABLE             DROP FOREIGN DATA WRAPPER        PREPARE

ALTER OPERATOR CLASS             CREATE FUNCTION                  DROP FOREIGN TABLE               PREPARE TRANSACTION

ALTER OPERATOR FAMILY            CREATE GROUP                     DROP FUNCTION                    REASSIGN OWNED

ALTER POLICY                     CREATE INDEX                     DROP GROUP                       REFRESH MATERIALIZED VIEW

ALTER PROCEDURE                  CREATE LANGUAGE                  DROP INDEX                       REINDEX

ALTER PUBLICATION                CREATE MATERIALIZED VIEW         DROP LANGUAGE                    RELEASE SAVEPOINT

ALTER ROLE                       CREATE OPERATOR                  DROP MATERIALIZED VIEW           RESET

ALTER ROUTINE                    CREATE OPERATOR CLASS            DROP OPERATOR                    REVOKE

ALTER RULE                       CREATE OPERATOR FAMILY           DROP OPERATOR CLASS              ROLLBACK

ALTER SCHEMA                     CREATE POLICY                    DROP OPERATOR FAMILY             ROLLBACK PREPARED

ALTER SEQUENCE                   CREATE PROCEDURE                 DROP OWNED                       ROLLBACK TO SAVEPOINT

ALTER SERVER                     CREATE PUBLICATION               DROP POLICY                      SAVEPOINT

ALTER STATISTICS                 CREATE ROLE                      DROP PROCEDURE                   SECURITY LABEL

ALTER SUBSCRIPTION               CREATE RULE                      DROP PUBLICATION                 SELECT

ALTER SYSTEM                     CREATE SCHEMA                    DROP ROLE                        SELECT INTO

ALTER TABLE                      CREATE SEQUENCE                  DROP ROUTINE                     SET

ALTER TABLESPACE                 CREATE SERVER                    DROP RULE                        SET CONSTRAINTS

ALTER TEXT SEARCH CONFIGURATION  CREATE STATISTICS                DROP SCHEMA                      SET ROLE

ALTER TEXT SEARCH DICTIONARY     CREATE SUBSCRIPTION              DROP SEQUENCE                    SET SESSION AUTHORIZATION

ALTER TEXT SEARCH PARSER         CREATE TABLE                     DROP SERVER                      SET TRANSACTION

ALTER TEXT SEARCH TEMPLATE       CREATE TABLE AS                  DROP STATISTICS                  SHOW

ALTER TRIGGER                    CREATE TABLESPACE                DROP SUBSCRIPTION                START TRANSACTION

ALTER TYPE                       CREATE TEXT SEARCH CONFIGURATION DROP TABLE                       TABLE

ALTER USER                       CREATE TEXT SEARCH DICTIONARY    DROP TABLESPACE                  TRUNCATE

ALTER USER MAPPING               CREATE TEXT SEARCH PARSER        DROP TEXT SEARCH CONFIGURATION   UNLISTEN

ALTER VIEW                       CREATE TEXT SEARCH TEMPLATE      DROP TEXT SEARCH DICTIONARY      UPDATE

ANALYZE                          CREATE TRANSFORM                 DROP TEXT SEARCH PARSER          VACUUM

BEGIN                            CREATE TRIGGER                   DROP TEXT SEARCH TEMPLATE        VALUES

CALL                             CREATE TYPE                      DROP TRANSFORM                   WITH

dbtest1=#

 

16、显示创建表的帮助信息

dbtest1=# \h create table

 

dbtest1=# \h create table

Command:     CREATE TABLE

Description: define a new table

Syntax:

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name ( [

{ column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

| table_constraint

| LIKE source_table [ like_option ... ] }

[, ... ]

] )

[ INHERITS ( parent_table [, ... ] ) ]

[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]

[ USING method ]

[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name

OF type_name [ (

{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]

| table_constraint }

[, ... ]

) ]

[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]

[ USING method ]

[ WITH ( storage_parameter [= value] [, ... ] ) | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXISTS ] table_name

PARTITION OF parent_table [ (

{ column_name [ WITH OPTIONS ] [ column_constraint [ ... ] ]

| table_constraint }

[, ... ]

) ] { FOR VALUES partition_bound_spec | DEFAULT }

[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) } [ COLLATE collation ] [ opclass ] [, ... ] ) ]

dbtest1=#

17、显示ALTER TABLE的帮助信息

dbtest1=# \h ALTER TABLE

dbtest1=# \h ALTER TABLE

Command:     ALTER TABLE

Description: change the definition of a table

Syntax:

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]

action [, ... ]

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]

RENAME [ COLUMN ] column_name TO new_column_name

ALTER TABLE [ IF EXISTS ] [ ONLY ] name [ * ]

RENAME CONSTRAINT constraint_name TO new_constraint_name

ALTER TABLE [ IF EXISTS ] name

RENAME TO new_name

ALTER TABLE [ IF EXISTS ] name

SET SCHEMA new_schema

ALTER TABLE ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]

SET TABLESPACE new_tablespace [ NOWAIT ]

ALTER TABLE [ IF EXISTS ] name

ATTACH PARTITION partition_name { FOR VALUES partition_bound_spec | DEFAULT }

ALTER TABLE [ IF EXISTS ] name

DETACH PARTITION partition_name

where action is one of:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]

DROP [ COLUMN ] [ IF EXISTS ] column_name [ RESTRICT | CASCADE ]

ALTER [ COLUMN ] column_name [ SET DATA ] TYPE data_type [ COLLATE collation ] [ USING expression ]

ALTER [ COLUMN ] column_name SET DEFAULT expression

ALTER [ COLUMN ] column_name DROP DEFAULT

ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL

ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ]

ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...]

ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ]

ALTER [ COLUMN ] column_name SET STATISTICS integer

ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )

ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )

ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }

ADD table_constraint [ NOT VALID ]

ADD table_constraint_using_index

ALTER CONSTRAINT constraint_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

VALIDATE CONSTRAINT constraint_name

DROP CONSTRAINT [ IF EXISTS ]  constraint_name [ RESTRICT | CASCADE ]

DISABLE TRIGGER [ trigger_name | ALL | USER ]

ENABLE TRIGGER [ trigger_name | ALL | USER ]

ENABLE REPLICA TRIGGER trigger_name

ENABLE ALWAYS TRIGGER trigger_name

DISABLE RULE rewrite_rule_name

ENABLE RULE rewrite_rule_name

ENABLE REPLICA RULE rewrite_rule_name

ENABLE ALWAYS RULE rewrite_rule_name

DISABLE ROW LEVEL SECURITY

ENABLE ROW LEVEL SECURITY

FORCE ROW LEVEL SECURITY

NO FORCE ROW LEVEL SECURITY

CLUSTER ON index_name

SET WITHOUT CLUSTER

SET WITHOUT OIDS

SET TABLESPACE new_tablespace

SET { LOGGED | UNLOGGED }

SET ( storage_parameter [= value] [, ... ] )

RESET ( storage_parameter [, ... ] )

INHERIT parent_table

NO INHERIT parent_table

OF type_name

NOT OF

OWNER TO { new_owner | CURRENT_USER | SESSION_USER }

REPLICA IDENTITY { DEFAULT | USING INDEX index_name | FULL | NOTHING }

and partition_bound_spec is:

IN ( partition_bound_expr [, ...] ) |

FROM ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] )

TO ( { partition_bound_expr | MINVALUE | MAXVALUE } [, ...] ) |

WITH ( MODULUS numeric_literal, REMAINDER numeric_literal )

and column_constraint is:

[ CONSTRAINT constraint_name ]

{ NOT NULL |

NULL |

CHECK ( expression ) [ NO INHERIT ] |

DEFAULT default_expr |

GENERATED ALWAYS AS ( generation_expr ) STORED |

GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] |

UNIQUE index_parameters |

PRIMARY KEY index_parameters |

REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]

[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint is:

[ CONSTRAINT constraint_name ]

{ CHECK ( expression ) [ NO INHERIT ] |

UNIQUE ( column_name [, ... ] ) index_parameters |

PRIMARY KEY ( column_name [, ... ] ) index_parameters |

EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] |

FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ]

[ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] }

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

and table_constraint_using_index is:

[ CONSTRAINT constraint_name ]

{ UNIQUE | PRIMARY KEY } USING INDEX index_name

[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]

index_parameters in UNIQUE, PRIMARY KEY, and EXCLUDE constraints are:

[ INCLUDE ( column_name [, ... ] ) ]

[ WITH ( storage_parameter [= value] [, ... ] ) ]

[ USING INDEX TABLESPACE tablespace_name ]

exclude_element in an EXCLUDE constraint is:

{ column_name | ( expression ) } [ opclass ] [ ASC | DESC ] [ NULLS { FIRST | LAST } ]

URL: https://www.postgresql.org/docs/12/sql-altertable.html

dbtest1=#

18、查询数据库的链接信息

postgres=# select * from pg_stat_activity;

19、查询数据库中已经存在的用户和角色

postgres=# select * from pg_stat_activity;
postgres=# SELECT rolname FROM pg_roles;

20、退出数据库

postgres=# \q

PostgreSQL深入浅出 | 数据库的日常维护

  • 本站内容整理自互联网,仅提供信息存储空间服务,以方便学习之用。如对文章、图片、字体等版权有疑问,请在下方留言,管理员看到后,将第一时间进行处理。
  • 转载请务必保留本文链接:https://www.cainiaoxueyuan.com/sjk/27655.html
匿名

发表评论

匿名网友 填写信息

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定