postgresql自带指令命令系列一

目录

简介

bin目录

1.clusterdb

2.createdb????????

3.createuser

4.dropdb

5.dropuser?

6.ecpg

7.initdb

8.oid2name

9.pg_amcheck

10.pg_archivecleanup

11.pg_basebackup

12.pgbench

13.pg_checksums

---

简介

? ? ? ? 在安装postgresql数据库的时候会需要设置一个关于postgresql数据库的PATH变量
export PATH=/home/postgres/pg/bin:$PATH，该变量会指向postgresql安装路径下的bin目录。这个安装目录和我们在进行编译的时候./configure --prefix= [指定安装目录]? ? 中的prefix参数设置相关。--bindir=DIR? 参数可以指定bin目录的具体存放路径。postgresql对应的PATH变量没有设置正确，会导致执行文件对应的命令无法使用

? ? ? ? 本文使用postgresql-15.2版本进行讲解

bin目录

????????进入到对应的bin目录中去，可以看到有以下的执行文件，本博主将进行一一讲解

clusterdb
createdb
createuser
dropdb
dropuser
ecpg
initdb
oid2name
pg_amcheck
pg_archivecleanup
pg_basebackup
pgbench
pg_checksums
pg_config
pg_controldata
pg_ctl
pg_dump
pg_dumpall
pg_isready
pg_receivewal
pg_recvlogical
pg_resetwal
pg_restore
pg_rewind
pg_test_fsync
pg_test_timing
pg_upgrade
pg_verifybackup
pg_waldump
postgres
postmaster -> postgres
psql
reindexdb
vacuumdb
vacuumlo

1.clusterdb

clusterdb clusters all previously clustered tables in a database.

Usage:
  clusterdb [OPTION]... [DBNAME]

Options:
  -a, --all                 cluster all databases
  -d, --dbname=DBNAME       database to cluster
  -e, --echo                show the commands being sent to the server
  -q, --quiet               don't write any messages
  -t, --table=TABLE         cluster specific table(s) only
  -v, --verbose             write a lot of output
  -V, --version             output version information, then exit
  -?, --help                show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database

? ? ? ? 相当于数据库库中的cluster语法。重新组织表的物理存储顺序。它通过按照指定的索引对表进行排序来优化数据存储布局。这个排序的过程可以使得表中的数据更紧凑地存储在磁盘上，这有助于减少磁盘 I/O 操作，提高查询性能，尤其是对于范围查询和顺序访问的查询。支持远程执行

? ? ? ? 一下几种情况需要使用clusterdb对数据库中的表进行重新组织

????????数据库性能下降： 当数据库性能下降，特别是对于数据范围查询或顺序扫描性能不佳时，重新组织可以改善表的物理存储顺序，减少磁盘 I/O，从而提高性能。

????????大量数据更新和删除： 如果数据库中的某些表经常进行大量的更新和删除操作，可能导致表的碎片化，即数据不再紧凑存储。clusterdb 可以清理这些碎片，重新组织表以提高数据存储的效率。

????????索引性能问题： 索引是数据库性能的关键因素之一。如果索引的性能受到影响，可能需要重新组织索引。clusterdb 会考虑重新组织表的默认索引。

????????数据库定期维护： 作为数据库维护的一部分，管理员可能会定期执行 clusterdb 操作，以确保数据库表的物理存储顺序保持最佳状态。

2.createdb????????

createdb creates a PostgreSQL database.

Usage:
  createdb [OPTION]... [DBNAME] [DESCRIPTION]

Options:
  -D, --tablespace=TABLESPACE  default tablespace for the database
  -e, --echo                   show the commands being sent to the server
  -E, --encoding=ENCODING      encoding for the database
  -l, --locale=LOCALE          locale settings for the database
      --lc-collate=LOCALE      LC_COLLATE setting for the database
      --lc-ctype=LOCALE        LC_CTYPE setting for the database
      --icu-locale=LOCALE      ICU locale setting for the database
      --locale-provider={libc|icu}
                               locale provider for the database's default collation
  -O, --owner=OWNER            database user to own the new database
  -S, --strategy=STRATEGY      database creation strategy wal_log or file_copy
  -T, --template=TEMPLATE      template database to copy
  -V, --version                output version information, then exit
  -?, --help                   show this help, then exit

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
  -p, --port=PORT              database server port
  -U, --username=USERNAME      user name to connect as
  -w, --no-password            never prompt for password
  -W, --password               force password prompt
  --maintenance-db=DBNAME      alternate maintenance database

? ? ? ? 等同于create database 语法，创建一个数据库，支持远程执行

3.createuser

????????

createuser creates a new PostgreSQL role.

Usage:
  createuser [OPTION]... [ROLENAME]

Options:
  -c, --connection-limit=N  connection limit for role (default: no limit)
  -d, --createdb            role can create new databases
  -D, --no-createdb         role cannot create databases (default)
  -e, --echo                show the commands being sent to the server
  -g, --role=ROLE           new role will be a member of this role
  -i, --inherit             role inherits privileges of roles it is a
                            member of (default)
  -I, --no-inherit          role does not inherit privileges
  -l, --login               role can login (default)
  -L, --no-login            role cannot login
  -P, --pwprompt            assign a password to new role
  -r, --createrole          role can create new roles
  -R, --no-createrole       role cannot create roles (default)
  -s, --superuser           role will be superuser
  -S, --no-superuser        role will not be superuser (default)
  -V, --version             output version information, then exit
  --interactive             prompt for missing role name and attributes rather
                            than using defaults
  --replication             role can initiate replication
  --no-replication          role cannot initiate replication
  -?, --help                show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to create)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

? ? ? ? 相当于数据库中的create user 语法。支持远程执行

4.dropdb

????????

dropdb removes a PostgreSQL database.

Usage:
  dropdb [OPTION]... DBNAME

Options:
  -e, --echo                show the commands being sent to the server
  -f, --force               try to terminate other connections before dropping
  -i, --interactive         prompt before deleting anything
  -V, --version             output version information, then exit
  --if-exists               don't report error if database doesn't exist
  -?, --help                show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as
  -w, --no-password         never prompt for password
  -W, --password            force password prompt
  --maintenance-db=DBNAME   alternate maintenance database

????????相当于数据库中drop table database语法，支持远程执行

5.dropuser?

dropuser removes a PostgreSQL role.

Usage:
  dropuser [OPTION]... [ROLENAME]

Options:
  -e, --echo                show the commands being sent to the server
  -i, --interactive         prompt before deleting anything, and prompt for
                            role name if not specified
  -V, --version             output version information, then exit
  --if-exists               don't report error if user doesn't exist
  -?, --help                show this help, then exit

Connection options:
  -h, --host=HOSTNAME       database server host or socket directory
  -p, --port=PORT           database server port
  -U, --username=USERNAME   user name to connect as (not the one to drop)
  -w, --no-password         never prompt for password
  -W, --password            force password prompt

????????相当于数据库中的drop? user 语法，支持远程执行?

6.ecpg

ecpg is the PostgreSQL embedded SQL preprocessor for C programs.

Usage:
  ecpg [OPTION]... FILE...

Options:
  -c             automatically generate C code from embedded SQL code;
                 this affects EXEC SQL TYPE
  -C MODE        set compatibility mode; MODE can be one of
                 "INFORMIX", "INFORMIX_SE", "ORACLE"
  -D SYMBOL      define SYMBOL
  -h             parse a header file, this option includes option "-c"
  -i             parse system include files as well
  -I DIRECTORY   search DIRECTORY for include files
  -o OUTFILE     write result to OUTFILE
  -r OPTION      specify run-time behavior; OPTION can be:
                 "no_indicator", "prepare", "questionmarks"
  --regression   run in regression testing mode
  -t             turn on autocommit of transactions
  -V, --version  output version information, then exit
  -?, --help     show this help, then exit

?????????ecpg是一个嵌入的用于 C 语言的 SQL 预编译器。 它把嵌有 SQL 语句的 C 程序通过将 SQL 调用替换成特殊的函数调用的方法转换成普通的 C 代码。 然后输出的文件就可以用任何 C 编译工具进行处理。这个指令在进行postgresql内核开发的时候比较重要。

? ? ? ? 做个范例：

? ? ? ? 创建测试表，插入测试数据。

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    salary INTEGER
);
INSERT INTO employees (name, salary) VALUES
    ('Alice', 50000),
    ('Bob', 60000),
    ('Charlie', 70000);

?????????创建 example.ec 文件（C 代码）：

#include <stdio.h>
#include <stdlib.h>
#include <libpq-fe.h>

EXEC SQL INCLUDE sqlca;

int main() {
    EXEC SQL BEGIN DECLARE SECTION;
    char conninfo[100] = "dbname=postgres user=postgres password=postgres";
    int salary;
    char name[100];
    EXEC SQL END DECLARE SECTION;

    EXEC SQL CONNECT TO :conninfo;

    if (sqlca.sqlcode < 0) {
        fprintf(stderr, "Database connection error\n");
        exit(1);
    }

    EXEC SQL DECLARE cur CURSOR FOR SELECT name, salary FROM employees;
    EXEC SQL OPEN cur;

    if (sqlca.sqlcode < 0) {
        fprintf(stderr, "Error opening cursor\n");
        exit(1);
    }

    while (1) {
        EXEC SQL FETCH cur INTO :name, :salary;
        if (SQLCODE == 100) break; // No more rows

        printf("Name: %s, Salary: %d\n", name, salary);
    }

    EXEC SQL CLOSE cur;
    EXEC SQL COMMIT;

    return 0;
}

##使用 ecpg 编译生成 C 代码：

ecpg example.ec
##这将生成一个名为 example.c 的 C 代码文件。


##编译生成的 C 代码：
cc -o example example.c -I/usr/pgsql-14/include -lpq -lecpg

##运行生成的可执行文件：
./example

? ? ? ? 会输出显示所有员工的姓名和薪水。

7.initdb

? ? ? ? ?初始化一个新的 PostgreSQL 数据库簇（cluster）。也就是我们常说的一个数据库实例，仅支持本地初始化。

8.oid2name

?????????在 PostgreSQL 的较新版本，特别是在 PostgreSQL 12 之后的版本，OID（对象标识符）的使用方式发生了一些变化。OID 列默认不再是用户表的一部分，但仍然可能在某些系统表和特殊情况下使用。以下是 PostgreSQL 15.2 版本中 OID 可能用于标识的一些对象：

pg_class 表： pg_class 表存储了数据库中的所有表、索引、序列等的信息。oid唯一标识

pg_index 表： pg_index 表存储了索引的信息，包括索引的数据类型等。oid唯一标识

pg_constraint 表： pg_constraint 表存储了约束的信息，包括主键、外键等。oid唯一标识

pg_namespace 表： pg_namespace 表存储了数据库中的命名空间信息。oid唯一标识

pg_attrdef 表： pg_attrdef 表存储了表列的默认值信息。oid唯一标识

? ? ? ? 以上的数据都会存储到磁盘的数据目录中去，并以oid作为文件名。

postgres=# select  pg_relation_filepath('form2_customer');
 pg_relation_filepath 
----------------------
 base/5/24781
(1 row)

postgres=# \q
[postgres@vm05 base]$ oid2name -f 24781
From database "postgres":
  Filenode      Table Name
--------------------------
     24781  form2_customer

在数据库目录中可以通过oid2name去查看该文件的具体对应对象?，执行远程查询

9.pg_amcheck

? ? ? ? 该指令在PG14之后才有，pg_amcheck?仅仅 PostgreSQL?14.0 及更高版本配合使用。

? ? ? ? 这个指令非常有用，常常用于检查数据库中索引损坏的排查

pg_amcheck checks objects in a PostgreSQL database for corruption.

Usage:
  pg_amcheck [OPTION]... [DBNAME]

Target options:
  -a, --all                       check all databases
  -d, --database=PATTERN          check matching database(s)
  -D, --exclude-database=PATTERN  do NOT check matching database(s)
  -i, --index=PATTERN             check matching index(es)
  -I, --exclude-index=PATTERN     do NOT check matching index(es)
  -r, --relation=PATTERN          check matching relation(s)
  -R, --exclude-relation=PATTERN  do NOT check matching relation(s)
  -s, --schema=PATTERN            check matching schema(s)
  -S, --exclude-schema=PATTERN    do NOT check matching schema(s)
  -t, --table=PATTERN             check matching table(s)
  -T, --exclude-table=PATTERN     do NOT check matching table(s)
      --no-dependent-indexes      do NOT expand list of relations to include indexes
      --no-dependent-toast        do NOT expand list of relations to include TOAST tables
      --no-strict-names           do NOT require patterns to match objects

Table checking options:
      --exclude-toast-pointers    do NOT follow relation TOAST pointers
      --on-error-stop             stop checking at end of first corrupt page
      --skip=OPTION               do NOT check "all-frozen" or "all-visible" blocks
      --startblock=BLOCK          begin checking table(s) at the given block number
      --endblock=BLOCK            check table(s) only up to the given block number

B-tree index checking options:
      --heapallindexed            check that all heap tuples are found within indexes
      --parent-check              check index parent/child relationships
      --rootdescend               search from root page to refind tuples

Connection options:
  -h, --host=HOSTNAME             database server host or socket directory
  -p, --port=PORT                 database server port
  -U, --username=USERNAME         user name to connect as
  -w, --no-password               never prompt for password
  -W, --password                  force password prompt
      --maintenance-db=DBNAME     alternate maintenance database

Other options:
  -e, --echo                      show the commands being sent to the server
  -j, --jobs=NUM                  use this many concurrent connections to the server
  -P, --progress                  show progress information
  -v, --verbose                   write a lot of output
  -V, --version                   output version information, then exit
      --install-missing           install missing extensions
  -?, --help                      show this help, then exit

pg_amcheck -d <database_name> [options] table_name index_name

10.pg_archivecleanup

?????????pg_archivecleanup 是 PostgreSQL 提供的一个用于清理归档日志文件的工具。归档日志（archive logs）是 PostgreSQL 中的事务日志，用于支持基于日志的流复制和点时间恢复。pg_archivecleanup 工具的主要作用是删除旧的归档日志文件，此工具与 PostgreSQL 的流复制设置一起使用，以确保备用服务器能够获取到所有需要的归档。同时释放主节点的磁盘空间。

pg_archivecleanup removes older WAL files from PostgreSQL archives.

Usage:
  pg_archivecleanup [OPTION]... ARCHIVELOCATION OLDESTKEPTWALFILE

Options:
  -d             generate debug output (verbose mode)
  -n             dry run, show the names of the files that would be removed
  -V, --version  output version information, then exit
  -x EXT         clean up files if they have this extension
  -?, --help     show this help, then exit

For use as archive_cleanup_command in postgresql.conf:
  archive_cleanup_command = 'pg_archivecleanup [OPTION]... ARCHIVELOCATION %r'
e.g.
  archive_cleanup_command = 'pg_archivecleanup /mnt/server/archiverdir %r'

Or for use as a standalone archive cleaner:
e.g.
  pg_archivecleanup /mnt/server/archiverdir 000000010000000000000010.00000020.backup

? ? ? ? 可以植入到postgresql.conf中的archive_cleanup_command参数中去

?关键字解析：

%r：%r 会被替换为一个 32 位的十六进制数字，代表当前 PostgreSQL 服务器的最新复制位置。这确保了在清理归档日志时，只有早于当前复制位置的归档日志文件会被删除，而不会删除仍然需要进行复制的文件。

????????
11.pg_basebackup

? ? ? ? ?常用的备份工具

12.pgbench

? ? ? ? ?数据库的压测工具，可以模拟多任务、多并发、多用户链接等测试。

13.pg_checksums

????????用于管理 PostgreSQL 数据目录中的页校验和。页校验和是一种用于检测数据文件损坏的机制，它通过在文件的每个页面上存储一个校验和值，来确保读取和写入的数据的完整性。

? ? ? ? 在使用时需要停库

[postgres@vm05 bin]$ pg_checksums --enable $PGDATA
Checksum operation completed
Files scanned:   1752  --表示扫描的文件总数，这里是 PostgreSQL 数据目录下文件的数量
Blocks scanned:  6247  --表示扫描的块总数，即扫描的文件中的页（block）的总数。
Files written:  1316  --表示写入校验和的文件总数。这里是指在启用页校验和后，对文件进行了校验和写入的文件数量。
Blocks written: 6247 --表示写入校验和的块总数。每个文件中的所有页都会被计算并写入校验和。
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums enabled in cluster
[postgres@vm05 bin]$ pg_checksums $PGDATA
Checksum operation completed
Files scanned:   1752  --表示扫描的文件总数，这里是 PostgreSQL 数据目录下文件的数量
Blocks scanned:  6247  --表示扫描的块总数，即扫描的文件中的页（block）的总数。
Bad checksums:  0      --表示发现的校验和错误数量。在这里是0，说明在扫描过程中没有发现校验和不匹配的块，即所有块的校验和都正确。
Data checksum version: 1  --表示数据文件的校验和版本。在这里是1，表示使用的是版本1的校验和。校验和的版本可能因 PostgreSQL 的版本而有所不同。
[postgres@vm05 bin]$ pg_checksums --disable 
pg_checksums: syncing data directory
pg_checksums: updating control file
Checksums disabled in cluster



上述返回值表示在扫描和启用页校验和的过程中，总共有1752个文件，6247个块被扫描，其中1316个文件的6247个块被写入了校验和。在这个过程中没有发现校验和错误，校验和的版本为1。

? ? ? ? 当第一次校验的时候需要加--enable参数，后面校验需要使用--disable 将其关掉，保持打开状态会在每次写入数据页时，都需要计算并存储校验和值。这会增加写入操作的开销。