Login
欢迎访问木星教程网_技术成就梦想 - 为知识分享服务
我要投稿 请登录 免费注册 安全退出

您现在的位置是: 首页 > 运维 > 数据库

数据库

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

2022-12-07 数据库 加入收藏 生成海报
1、概述

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




5、显示所有用户

postgres=# du




6、查看扩展

postgres=# dx




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;






9、查看表空间

postgres=# db+






10、列出所有模式

postgres=# dn








11、查看数据库的表

postgres=# dt


12、查看表字段

postgres=# d {{tablename}}


13、创建一个数据库

CREATE DATABASE DBTEST1;








14、切换数据库

postgres=# c dbtest1








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

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




5、显示所有用户

postgres=# du




6、查看扩展

postgres=# dx




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;






9、查看表空间

postgres=# db+






10、列出所有模式

postgres=# dn








11、查看数据库的表

postgres=# dt


12、查看表字段

postgres=# d {{tablename}}


13、创建一个数据库

CREATE DATABASE DBTEST1;








14、切换数据库

postgres=# c dbtest1








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


原文来自: