-------------------------------------------------------------------------------------------------------------【关闭其他用户连接】 SELECT * FROM pg_stat_activity; SELECT pg_terminate_backend(pid); psql -U [username] -d [dbname] #打开终端 psql -U system -d test_001 \du #查看所有用户 \l #列出所有数据库 \l+ 列出所有数据库的详细信息，如表空间、所有者等。 \c #切换数据库 \q #退出数据库 \d #列出所有表 \d tablename 描述表结构。\da 表和视图。 \dt 表，不包括视图。 \dv 视图。\ds 序列。\dn 模式。\di 索引。\du 用户 \q #退出终端 \encoding #显示当前数据库的字符编码 \i filename #从文件中读取并执行 SQL 命令 -------------------------------------------------------------------------------------------------------------【备份】 pg_dump -U system -F c -b -v -f construction_project_safety_bak_20241016_2_1.db construction_project_safety -T sys_log_error [例]pg_dump -U system -h localhost -p 5432 -F c -b -v -f backupfile.tar mydatabase -U system：使用 system 用户进行备份。 -h localhost：指定备份的数据库服务器地址为 localhost。 -p 5432：指定备份的数据库服务器端口号为 5432。 -F c：指定备份格式为自定义格式。 -b：生成一个可以由 pg_restore 使用的脚本来重建数据库。 -v：增加输出的详细程度。 -f backupfile.tar：指定备份文件的路径和名称。 mydatabase：指定要备份的数据库名称。 -F p 或 --format=plain #最简单的 SQL 输出格式，包含所有 SQL 命令来重建数据库。 -F c 或 --format=custom #二进制格式，支持增量备份，恢复速度较快。 -F t 或 --format=tar #使用 Tar 格式打包备份文件，支持压缩。 -F d 或 --format=directory #将备份数据保存在目录结构中，每个文件代表一部分数据。 -F s 或 --format=sql #与普通文本格式类似，但支持更多的 SQL 命令。 -------------------------------------------------------------------------------------------------------------【恢复】 pg_restore -U postgres -d construction_project_safety_dev_v2.0 -t sys_log_error -v -c -s bak_20241013.db [例]pg_restore -U system -d mydatabase -v backupfile.tar -U system：指定恢复时使用的数据库用户。 -d mydatabase：指定要恢复到的数据库名称。 -v：增加输出的详细程度，显示更多详细信息。 backupfile.tar：指定要恢复的备份文件路径 --clean #清除现有数据后再进行恢复。这会删除目标数据库中的所有对象。 --no-owner #不恢复所有者信息。 --no-acl #不恢复访问控制列表。 --data-only #只恢复数据，不恢复表结构。 --schema-only #只恢复表结构，不恢复数据。 -t table | --table=table #只恢复指定的表。 -T table | --exclude-table-data=table #排除指定的表。 -n schema | --schema=schema #只恢复指定的模式。 -N schema | --exclude-schema=schema #排除指定的模式。 -Z level | --compress=level #指定压缩级别，范围是 0 到 9，默认为 1。 -------------------------------------------------------------------------------------------------------------【pg_dump --help】 General options: -f, --file=FILENAME output file or directory name -F, --format=c|d|t|p output file format (custom, directory, tar, plain text (default)) -j, --jobs=NUM use this many parallel jobs to dump -v, --verbose verbose mode -V, --version output version information, then exit -Z, --compress=0-9 compression level for compressed formats --lock-wait-timeout=TIMEOUT fail after waiting TIMEOUT for a table lock --no-sync do not wait for changes to be written safely to disk -?, --help show this help, then exit Options controlling the output content: -a, --data-only dump only the data, not the schema -b, --blobs include large objects in dump -B, --no-blobs exclude large objects in dump -c, --clean clean (drop) database objects before recreating -C, --create include commands to create database in dump -E, --encoding=ENCODING dump the data in encoding ENCODING -n, --schema=PATTERN dump the specified schema(s) only -N, --exclude-schema=PATTERN do NOT dump the specified schema(s) -O, --no-owner skip restoration of object ownership in plain-text format -s, --schema-only dump only the schema, no data -S, --superuser=NAME superuser user name to use in plain-text format -t, --table=PATTERN dump the specified table(s) only -T, --exclude-table=PATTERN do NOT dump the specified table(s) -x, --no-privileges do not dump privileges (grant/revoke) --binary-upgrade for use by upgrade utilities only --column-inserts dump data as INSERT commands with column names --disable-dollar-quoting disable dollar quoting, use SQL standard quoting --disable-triggers disable triggers during data-only restore --enable-row-security enable row security (dump only content user has access to) --exclude-table-data=PATTERN do NOT dump data for the specified table(s) --extra-float-digits=NUM override default setting for extra_float_digits --if-exists use IF EXISTS when dropping objects --include-foreign-data=PATTERN include data of foreign tables on foreign servers matching PATTERN --inserts dump data as INSERT commands, rather than COPY --load-via-partition-root load partitions via the root table --no-comments do not dump comments --no-publications do not dump publications --no-security-labels do not dump security label assignments --no-subscriptions do not dump subscriptions --no-synchronized-snapshots do not use synchronized snapshots in parallel jobs --no-tablespaces do not dump tablespace assignments --no-unlogged-table-data do not dump unlogged table data --on-conflict-do-nothing add ON CONFLICT DO NOTHING to INSERT commands --quote-all-identifiers quote all identifiers, even if not key words --rows-per-insert=NROWS number of rows per INSERT; implies --inserts --section=SECTION dump named section (pre-data, data, or post-data) --serializable-deferrable wait until the dump can run without anomalies --snapshot=SNAPSHOT use given snapshot for the dump --strict-names require table and/or schema include patterns to match at least one entity each --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership Connection options: -d, --dbname=DBNAME database to dump -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) --role=ROLENAME do SET ROLE before dump If no database name is supplied, then the PGDATABASE environment variable value is used. Report bugs to . PostgreSQL home page: -------------------------------------------------------------------------------------------------------------【pg_restore --help】 General options: -d, --dbname=NAME connect to database name -f, --file=FILENAME output file name (- for stdout) -F, --format=c|d|t backup file format (should be automatic) -l, --list print summarized TOC of the archive -v, --verbose verbose mode -V, --version output version information, then exit -?, --help show this help, then exit Options controlling the restore: -a, --data-only restore only the data, no schema -c, --clean clean (drop) database objects before recreating -C, --create create the target database -e, --exit-on-error exit on error, default is to continue -I, --index=NAME restore named index -j, --jobs=NUM use this many parallel jobs to restore -L, --use-list=FILENAME use table of contents from this file for selecting/ordering output -n, --schema=NAME restore only objects in this schema -N, --exclude-schema=NAME do not restore objects in this schema -O, --no-owner skip restoration of object ownership -P, --function=NAME(args) restore named function -s, --schema-only restore only the schema, no data -S, --superuser=NAME superuser user name to use for disabling triggers -t, --table=NAME restore named relation (table, view, etc.) -T, --trigger=NAME restore named trigger -x, --no-privileges skip restoration of access privileges (grant/revoke) -1, --single-transaction restore as a single transaction --disable-triggers disable triggers during data-only restore --enable-row-security enable row security --if-exists use IF EXISTS when dropping objects --no-comments do not restore comments --no-data-for-failed-tables do not restore data of tables that could not be created --no-publications do not restore publications --no-security-labels do not restore security labels --no-subscriptions do not restore subscriptions --no-tablespaces do not restore tablespace assignments --section=SECTION restore named section (pre-data, data, or post-data) --strict-names require table and/or schema include patterns to match at least one entity each --use-set-session-authorization use SET SESSION AUTHORIZATION commands instead of ALTER OWNER commands to set ownership Connection options: -h, --host=HOSTNAME database server host or socket directory -p, --port=PORT database server port number -U, --username=NAME connect as specified database user -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) --role=ROLENAME do SET ROLE before restore The options -I, -n, -N, -P, -t, -T, and --section can be combined and specified multiple times to select multiple objects. If no input file name is supplied, then standard input is used. Report bugs to . PostgreSQL home page: ------------------------------------------------------------------------------------------------------------- CREATE ROLE admin WITH SUPERUSER CREATEDB CREATEROLE LOGIN PASSWORD 'adminpassword'; #创建超级管理员 CREATE DATABASE dbname; #创建数据库 DROP DATABASE dbname; #删除数据库