postgresql学习

快捷键：
\l：查看系统中现存的数据库
\c：切换库，如\c mydb切换到mydb库
\d <数据表名/视图名/序列名>
\dt：列出所有表和sequence
\du:查看所有用户名
\db:查看所有表空间
\dv 查看当前数据库的全部视图（View）
\dn 查看当前数据库的全部模式（Schema）
\ds 查看当前数据库的全部序列（Sequence）
\df 查看当前数据库的全部函数（Function），包括触发器函数
\dD 查看当前数据库的全部域（Domain）
\q：退出客户端程序psql

以root用户安装并初始化数据库
yum install -y postgresql15-server
/usr/pgsql-15/bin/postgresql-15-setup initdb
systemctl enable postgresql-15
systemctl start postgresql-15

su - postgres抬头显示bash-4.2$
解决办法：
cd /etc/skel/
cp .bash* /var/lib/pgsql/
reboot

表空间:
1.pg_default和pg_global表空间是在initdb()时候创建的
2.如果没有显式指定表空间，默认存储在pg_default表空间中
3.数据库集群级别管理的表被存放在pg_global表空间中
4.pg_default表空间物理位置是$PGDATA/base(例如：/var/lib/pgsql/15/data/base)
5.pg_global表空间物理位置是$PGDATA/global(例如：/var/lib/pgsql/15/data/global)
6.一个表空间可以被多个数据库公用
7.用户创建的表空间在$PGDATA/tblspc目录中会创建对应的符号链接 

create tablespace pgts_myts location '/var/lib/pgsql/15/myts';
drop tablespace pgts_myts;
alter tablespace pgts_myts owner to admin;
迁移user_db数据到pgts_myts
alter database user_db set tablespace pgts_myts;

查询数据库，表的存储位置；
在对一张表执行truncate,vacuum full等操作后,会重写这个表的数据,会引发这个表relfilenode值的变更
SELECT oid,datname FROM pg_database WHERE datname='user_db';
SELECT oid,relfilenode FROM pg_class WHERE relname='infor';
SELECT oid,relfilenode FROM pg_class WHERE relname='name';

1、创建用户，并赋权createdb
CREATE USER admin WITH PASSWORD '123';
ALTER USER admin WITH CREATEDB;
ALTER USER postgres WITH PASSWORD '123456';
CREATE USER repl WITH PASSWORD '123';
ALTER USER repl WITH REPLICATION;

ALTER USER role_specification [ WITH ] option [ ... ]
where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | BYPASSRLS | NOBYPASSRLS
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
    | VALID UNTIL 'timestamp'


2、删除用户
drop user admin;
\c user_db
\d 
ALTER database user_db owner to postgres;
ALTER table infor owner to postgres;

drop user admin;
revoke all on user_db from admin;

ALTER database user_db owner to admin;
ALTER table infor owner to admin;

3、备份
pg_dump
  -f, --file=FILENAME          输出文件或目录名 
  -F, --format=c|d|t|p         输出文件格式 (custom, dir, tar)  tezt(默认值)) 
  -j, --jobs=NUM               执行多个并行任务进行备份转储工作
  -v, --verbose                详细模式
  -V, --version                输出版本信息，然后退出
  -Z, --compress=0-9           被压缩格式的压缩级别
pg_dump -U postgres -h localhost -d user_db -F c -Z 9 > /var/lib/pgsql/backup/backup.custom
pg_restore -d user_db /var/lib/pgsql/backup/backup.custom

pg_dumpall
优点：
    它转储全局 的东西——角色和表空间，这些不能被pg_dump转储。
    单个命令，你可以获得整个集群的结果
    常用来备份全局对象而非全库数据

    -a, --data-only             只转储数据，不转储模式
    -c, --clean                 在重新创建之前清理（删除）数据库
    -E, --encoding=ENCODING     转储编码 ENCODING 中的数据
    -g, --globals-only          只转储全局对象，不转储数据库
    -O, --no-owner              跳过恢复对象所有权
    -r, --roles-only            只转储角色，不转储数据库或表空间
    -s, --schema-only           只转储模式，不转储数据
    -S, --superuser=NAME        在转储中使用的超级用户用户名
    -t, --tablespaces-only      只转储表空间，不转储数据库或角色
    -x, --no-privileges         不转储权限（授予/撤销）
pg_dumpall -g > /var/lib/pgsql/backup/pg_all.sql
psql -f /var/lib/pgsql/backup/pg_all.sql

pg_basebackup
 控制输出的选项：
  -D, --pgdata=DIRECTORY  接收基本备份到目录
  -F, --format=p|t        输出格式（plain（默认），tar）
  -r, --max-rate=RATE     传输数据目录的最大传输速率（以 kB/s为单位，或使用后缀“k”或“M”）
  -R, --write-recovery-conf       用于复制的写入配置
  -T, --tablespace-mapping=OLDDIR=NEWDIR 将 OLDDIR 中的表空间重定位到 NEWDIR
      --waldir=WALDIR             预写日志目录的位置
  -X, --wal-method=none|fetch|stream 包含指定方法所需的 WAL 文件
  -z, --gzip                      压缩 tar 输出
  -Z, --compress=0-9      使用给定的压缩级别压缩 tar 输出
 常规选项：
  -c, --checkpoint=fast|spread 设置快速或扩展检查点
  -C, --create-slot 创建复制槽
  -l, --label=LABEL 设置备份标签
  -n, --no-clean 出错后不清理
  -N, --no-sync 不等待更改安全写入磁盘
  -P, --progress 显示进度信息
  -S, --slot=SLOTNAME 要使用的复制槽
  -v, --verbose 输出详细信息
  -V, --version 输出版本信息，然后退出
      --no-slot 防止创建临时复制槽
      --no-verify-checksums 不验证校验和
  -?, --help 显示此帮助，然后退出
 连接选项：
  -d, --dbname=CONNSTR 连接字符串
  -h, --host=HOSTNAME 数据库服务器主机或套接字目录
  -p, --port=PORT 数据库服务器端口号
  -s, --status-interval=状态包发送到服务器的间隔时间（以秒为单位）
  -U, --username=NAME 以指定的数据库用户连接
  -w, --no-password 从不提示输入密码
  -W, --password 强制密码提示（应该自动）
pg_basebackup -U repl -Ft -z -Xs -c fast -P -v -D /var/lib/pgsql/pg_back
postgresql.auto.conf
    恢复到备份时间
    restore_command = 'cp /var/lib/pgsql/15/arch/%f %p'
    recovery_target = 'immediate'
    恢复到某个时间点
    restore_command = 'cp /var/lib/pgsql/15/arch/%f %p'
    recovery_target_time = '2023-12-15 11:12:00'
touch /var/lib/pgsql/15/data/recovery.signal
/usr/pgsql-15/bin/pg_ctl start -D /var/lib/pgsql/15/data

默认恢复后的库为只读库，通过修改recovery_target_action参数或手动更改数据库恢复后的状态
recovery_target_action  #指定在达到恢复目标时服务器采取的动作。 
pause  #默认值，表示恢复将被暂停
promote  #表示恢复结束且服务器将开始接受连接
shutdown #表示在达到恢复目标之后停止服务器。
显示一个PostgreSQL数据库集簇的控制信息
pg_controldata -D /var/lib/pgsql/15/data
手动将数据库状态改为promote状态
pg_ctl promote -D /var/lib/pgsql/15/data