课程笔记 | PostgreSQL深入浅出 | 数据库的日常维护

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、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源,请您支持作者,购买正版内容。

6、资源失效,请下方留言,欢迎分享资源链接

文章评论

0条评论