Linux: PostgreSQL

sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm

#禁用内置的postgresql
sudo dnf -qy module disable postgresql
sudo dnf install -y postgresql17-server

#初始化数据库
sudo /usr/pgsql-17/bin/postgresql-17-setup initdb

#启动服务
systemctl enable --now postgresql-17

#验证成功
[root@rocky9 ~]#sudo -u postgres psql -c "SELECT version();"
PostgreSQL 17.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 11.4.1 20231218 (Red Hat 11.4.1-3), 64-bit

[root@rocky9 ~]#su - postgres
[postgres@rocky9~]$ psql
psql (17.1)
Type "help" for help.

postgres=# help
\copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

[root@rocky8 ~]#yum -y install postgresql-server

#默认无法启动
[root@rocky8 ~]#systemctl start postgresql.service

#查看日志提示做初始化
[root@rocky8 ~]#tail /var/log/messages Dec 23 12:01:41 rocky8

[root@rocky8 ~]#ls /var/lib/pgsql/data

#初始化数据库
[root@rocky8 ~]#/usr/bin/postgresql-setup --initdb
[root@rocky8 ~]#ls /var/lib/pgsql/data

[root@rocky8 ~]#systemctl enable --now postgresql.service

[root@rocky8 ~]#su - postgres
[postgres@rocky8 ~]$ psql psql (10.17)

postgres=#

#导入存储库签名密钥
sudo apt install curl ca-certificates
sudo install -d /usr/share/postgresql-common/pgdg
sudo curl -o /usr/share/postgresql-common/pgdg/apt.postgresql.org.asc --fail https://www.postgresql.org/media/keys/ACCC4CF8.asc
#创建存储库配置文件
sudo sh -c 'echo "deb [signed-by=/usr/share/postgresql-common/pgdg/apt.postgresql.org.asc] https://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
#更新存储并安装最新版本， 可以指定版本，如postgresql-16代替postgresql
sudo apt update
sudo apt -y install postgresql

#验证版本， 5432端口
root@debian:~/.jasper# sudo -u postgres psql -c "SELECT version();"
                                                       version                                                       
---------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 (Debian 17.0-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)
root@debian:~/.jasper# su - postgres
postgres@debian:~$ psql
psql (17.0 (Debian 17.0-1.pgdg120+1))
Type "help" for help.

postgres=# 


#安装12版本时，ubuntu需要初始化才能启动
#初始化并启动
#方法1
[root@ubuntu2004 ~]#pg_createcluster 12 main --start

#方法2
[root@ubuntu2004 ~]#su - postgres
#数据库初始化
postgres@ubuntu2004:~$/usr/lib/postgresql/12/bin/initdb -D /var/lib/postgresql/data
#启动服务
postgres@ubuntu2004:~$ /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start
#修改配置
postgres@ubuntu2004:~$ vim /var/lib/postgresql/data/postgresql.conf
listen_addresses = '*'

postgres@ubuntu2004:~$ vim /var/lib/postgresql/data/pg_hba.conf
# IPv4 local connections:

host all all 127.0.0.1/32 trust
host all all 0.0.0.0/0   md5

#重启服务
postgres@ubuntu2004:~$ /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/data -l logfile restart

postgres@ubuntu2004:~$ psql
#修改密码
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD '123456';
ALTER ROLE

#远程登录
[root@rocky8 ~]#psql -U postgres -h 10.0.0.200
Type "help" for help.

[root@ubuntu2004 ~]#apt update
[root@ubuntu2004 ~]#apt -y install postgresql
[root@ubuntu2004 ~]#su -postgres

postgres@ubuntu2004:~$ /usr/lib/postgresql/12/bin/pg_ctl init -D /var/lib/postgresql/12/main
Success. You can now start the database server using:

/usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/main -l logfile start

postgres@ubuntu2004:~$ /usr/lib/postgresql/12/bin/pg_ctl -D /var/lib/postgresql/12/main -l logfile start
postgres@ubuntu2004:~$ psql
postgres=# exit

[root@ubuntu1804 ~]#apt update
[root@ubuntu1804 ~]#apt -y install postgresql
[root@ubuntu1804 ~]#su - postgres

postgres@ubuntu1804:~$ psql
postgres=# help

./configure
make
make install

#关闭防火墙和SELinux等

#内核参数优化
# vi /etc/sysctl.conf
kernel.shmmax = 68719476736 （CentOS6以前版本默认值，CentOS7默认为18446744073692774399）
kernel.shmall = 4294967296 （CentOS6以前版本默认值，CentOS7默认为18446744073692774399）
kernel.shmmni = 4096
kernel.sem = 50100 64128000 50100 1280
fs.file-max = 7672460
net.ipv4.ip_local_port_range = 9000 65000
net.core.rmem_default = 1048576
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576

# sysctl -p

# vi /etc/security/limits.conf
​* - nofile 100000
​* - nproc 100000
​* - memlock 60000

#RHEL系统
yum install -y gcc make readline-devel zlib-devel

#Ubuntu
apt update
apt install -y gcc make libreadline-dev zlib1g-dev
apt install -y libicu-dev pkg-config bison flex
apt install -y build-essential zlib1g-dev libssl-dev libreadline-dev libpam0g-dev libdb-dev

#下载解压缩
wget https://ftp.postgresql.org/pub/source/v12.9/postgresql-12.9.tar.gz
tar xf postgresql-12.9.tar.gz
cd postgresql-12.9

wget https://ftp.postgresql.org/pub/source/v17.0/postgresql-17.0.tar.gz
tar xf postgresql-17.0.tar.gz
cd postgresql-17.0

#查看安装说明
cat postgresql-12.9/INSTALL

./configure
make
su
make install
adduser postgres
mkdir /usr/local/pgsql/data
chown postgres /usr/local/pgsql/data
su - postgres
/usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data
/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -l logfile start
/usr/local/pgsql/bin/createdb test
/usr/local/pgsql/bin/psql test

#查看编译选项(可选)
pg_config --configure

#开始编译三步曲,默认安装在/usr/local/pgsql
mkdir buid_dir
cd buid_dir
../configure --prefix=/apps/pgsql --with-pgport=5432
make -j 2 world #默认 make 不包括文档和其它模块,2表示当产主机的CPU核心数
make install-world #默认 make install 不包括安装文档

root@debian:~/postgresql-17.0/buid_dir# useradd test
root@debian:~/postgresql-17.0/buid_dir# getent passwd test
test:x:1001:1001::/home/test:/bin/sh

#创建数据库用户和组,注意此用户需要可以交互登录
useradd -s /bin/bash -m -d /home/postgres postgres

#修改postgres密码
echo -e '123456\n123456' | passwd postgres
echo postgres:123456|chpasswd

mkdir -pv /pgsql/data/

chown postgres.postgres /pgsql/data/

vim /etc/profile.d/pgsql.sh
export PGHOME=/apps/pgsql
export PATH=$PGHOME/bin/:$PATH
export PGDATA=/pgsql/data
export PGUSER=postgres
export MANPATH=/apps/pgsql/share/man:$MANPATH

su - postgres #验证
psql --version

su - postgres

#初始化
initdb
initdb -D $PGDATA
#如果没有指定选项 -D <datadir> ,按环境变量$PGDATA指定的路径进行初始化

#生产建议初始化方式
initdb -A md5 -D $PGDATA -E utf8 --locale=C -U postgres -W

#选项
-A #指定local connections默认的身份验证方法
-D #指定数据目录
-E #指定字符集
--locale=C #指定语言环境
-U #指定数据库superuser用户名
-W #指定数据库superuser的用户密码

#启动
pg_ctl -D /pgsql/data -l logfile start
postgres -D /pgsql/data &

#停止数据库的命令如下:
pg_ctl stop -D $PGDATA [-m SHUTDOWN-MODE]
其中-m是指定数据库的停止方法，有以下三种:
smart: 等所有的连接中止后，关闭数据库。如果客户端连接不终止，则无法关闭数据库。
fast: 快速关闭数据库，断开客户端的连接，让已有的事务回滚，然后正常关闭数据库。相当于Oracle数据库关闭时的immediate模式。此为默认值,建议使用
immediate: 立即关闭数据库，相当于数据库进程立即停止，直接退出，下次启动数据库需要进行恢复。相当于Oracle数据库关闭时的 abort模式

#smart关闭
pg_ctl stop -D /pgsql/data/ -ms
#fast关闭,推荐使用,也是默认模式
pg_ctl stop -D /pgsql/data/ -mf
#immediate 相当于kill -9
pg_ctl stop -D /pgsql/data/  -mi

#或者发送信号,直接向数据库主进程发送的signal 信号有以下三种。
SIGTERM: 发送此信号为Smart Shutdown关机模式。
SIGINT: 发送此信号为Fast Shutdown关机模式。
SIGQUIT: 发送此信号为Immediate Shutdown关机模式。

#重启
pg_ctl restart -mf

#源码目录中内置PostgreSQL的启动脚本
postgresql-12.9/contrib/start-scripts/linux

[root@ubuntu2004 ~]#cat /etc/rc.local
#!/bin/bash
su - postgres -c "/apps/pgsql/bin/pg_ctl -l logfile start"

​#/etc/init.d/postgresql start

[root@rocky8 ~]#cp postgresql-12.9/contrib/start-scripts/linux /etc/init.d/postgresql

[root@rocky8 ~]#chmod +x /etc/init.d/postgresql
[root@rocky8 ~]#chkconfig --add postgresql

[root@rocky8 ~]#vim /etc/init.d/postgresql
#修改下面两行
prefix=/apps/pgsql
PGDATA="/pgsql/data"

[root@rocky8 ~]#service postgresql start

#创建新的service文件
[root@ubuntu2004 ~]#cat > /lib/systemd/system/postgresql.service <<EOF
[Unit]
Description=PostgreSQL database server
After=network.target

[Service]
User=postgres
Group=postgres

ExecStart=/apps/pgsql/bin/postgres -D /pgsql/data
ExecReload=/bin/kill -HUP

[Install]
WantedBy=multi-user.target
EOF

[root@ubuntu2004 ~]#systemctl daemon-reload

#确认文件内容, 并开机启动
systemctl cat postgresql.service
systemctl enable --now postgresql

[postgres@postgresql ~]$pg_config
BINDIR = /apps/pgsql/bin
DOCDIR = /apps/pgsql/share/doc
HTMLDIR = /apps/pgsql/share/doc
INCLUDEDIR = /apps/pgsql/include
PKGINCLUDEDIR = /apps/pgsql/include
INCLUDEDIR-SERVER = /apps/pgsql/include/server
LIBDIR = /apps/pgsql/lib
PKGLIBDIR = /apps/pgsql/lib
LOCALEDIR = /apps/pgsql/share/locale
MANDIR = /apps/pgsql/share/man
SHAREDIR = /apps/pgsql/share
SYSCONFDIR = /apps/pgsql/etc
PGXS = /apps/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--prefix=/apps/pgsql'
CC = gcc
CPPFLAGS = -D_GNU_SOURCE
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-
statement -Werror=vla -Wendif-labels -Wmissing-format-attribute
-Wformat- security -fno-strict-aliasing -fwrapv
-fexcess-precision=standard -Wno-format- truncation
-Wno-stringop-truncation -O2

CFLAGS_SL = -fPIC
LDFLAGS = -Wl,--as-needed -Wl,-rpath,'/apps/pgsql/lib',--enable-new-dtags LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lpthread -lz -lreadline -lrt -lcrypt -ldl -lm
VERSION = PostgreSQL 12.9

#先切换用户
su - postgres

#初始化数据库
initdb [DATADIR]
pg_ctl init[db] [-s] [-D DATADIR] [-o options]

#pg_ctl命令调用initdb命令创建了一个新的PostgreSQL数据库实例,参数说明如下。

-s #只打印错误和警告信息，不打印提示性信息。
-D DATADIR  #指定数据库实例的数据目录。如果没有指定DATADIR,使用环境变量PGDATA指定的路径
-o options #为直接传递给initdb命令的参数

chown postgres: /pgsql/
su - postgres

postgres@debian:~$ pg_ctl  init -D /pgsql/data2
Success. You can now start the database server using:

    /apps/pgsql/bin/pg_ctl -D /pgsql/data2 -l logfile start

postgres@debian:~$ ls /pgsql/data2

#修改端口，启动实例
postgres@debian:~$ grep 5432 /pgsql/data2/postgresql.conf 
#port = 5432                            # (change requires restart)
postgres@debian:~$ echo port = 5433 >> /pgsql/data2/postgresql.conf
postgres@debian:~$ /apps/pgsql/bin/pg_ctl -D /pgsql/data2 -l logfile start

pg_ctl -D /pgsql/data2 stop

[root@ubuntu2004 postgresql-12.9]#./configure --help |grep segment
--with-segsize=SEGSIZE set table segment size in GB [1]

#创建模式
create schema schema_name;

#删除模式
drop schema schema_name;

#查看模式
\dn

psql -h <hostname or ip> -p<端口> [数据库名称] -U [用户名称]

-h #指定要连接的数据库主机名或IP地址,默认local socket登录(由配置项unix_socket_directories指定)
-p #指定连接的数据库端口
#最后两个参数是数据库名和用户名

#这些连接参数也可以用环境变量指定，比如:
export PGDATABASE=testdb
export PGHOST=10.0.0.200
export PGPORT=5432
export PGUSER=postgres

#然后运行psql即可，其效果与运行psql -h 10.0.0.200 -p 5432 testdb postgres相同。
#可通过下面命令查看详细帮助:man /apps/pgsql/share/man/man1/psql.1

#方法1
[root@rocky8 ~]#psql -d postgres -U postgres

#方法2
[root@rocky8 ~]#su - postgres
[postgres@rocky8 ~]$psql

#注意:默认PostgreSQL不支持远程登录,需要修改配置和授权才可以
#如果不指定hellodb数据库,默认连接和用户名同名的数据库

[root@rocky8 ~]#psql -h 10.0.0.200 -p 5432 hellodb postgres
Password for user postgres:
psql (12.9)
Type "help" for help.

hellodb=#

[root@rocky8 ~]#psql -U postgres -h 10.0.0.200 -p 5432 -d postgres -c "select current_time"
Password for user postgres: current_time
04:43:48.508999+00
(1 row)

[root@rocky8 ~]#cat test.sql
select current_time
\du

#方法1
[root@rocky8 ~]#psql -U postgres -h 10.0.0.200 -p 5432 -d postgres -f test.sql

#方法2
\i <文件名> #执行存储在外部文件中的sql语句或命令
[postgres@rocky8 ~]$psql
postgres=# \i hellodb.sql

postgres=# create database db1;
CREATE DATABASE
postgres=# \c db1
You are now connected to database "db1" as user "postgres".
db1=# create table t1(id int);
CREATE TABLE
db1=# \dt
 public | t1   | table | postgres


postgres@debian:~$ cat test.sql 
\c db1
create table t2(id int);
select * from t2;

postgres@debian:~$ psql -f test.sql 
You are now connected to database "db1" as user "postgres".
CREATE TABLE
 id 
----
(0 rows)

\c #显示当前连接的数据库
\c 库名 #连接数据库
\dn #显示schema
\l #显示数据库
\du #显示权限

#METHOD有如下值可选
md5：执行SCRAM-SHA-256或MD5身份验证加密密码来验证,是推荐使用的安全验证的方法
peer：从操作系统获取客户端的操作系统用户名，并检查它是否与请求的数据库用户名匹配。这仅适用于本地socket连接。
trust：允许无条件连接，允许任何PostgreSQL用户身份登录，而无需密码或任何其他身份验证。
reject：拒绝任何条件连接，这对于从组中“过滤掉”某些主机非常有用。
scram-sha-256：执行SCRAM-SHA-256身份验证以验证用户的密码。
password：要提供未加密的密码以进行身份验证。由于密码是通过网络以明文形式发送的，因此不建议使用
gss：使用GSSAPI对用户进行身份验证，这仅适用于TCP / IP连接。
sspi：使用SSPI对用户进行身份验证，这仅适用于Windows。

ident：允许客户端上的特定操作系统用户连接到数据库。这种认证方式的使用
场景是，客户端是主机上的某个操作系统用户，已经通过了操作系统的身份认证，
是数据库服务器可以信任的用户，不需要在数据库层面再次检测身份。比如，如
果配置了这种认证方式(配置中允许的用户名为dba)、这时在操作系统用户dba下，
就能以数据库用户dba的身份连接到数据库。服务器为了确定接收到的连接请求
确实是客户端机器上的dba用户发起的，而不是这台机器上其他用户发起的假冒
请求，会向客户端机器上的ident服务发起请求，让ident服务查看此TCP连接是
否是dba用户发起的，如果不是，说明是假冒，则认证失败。如果客户端通过本
地连接到服务器，因为客户端与服务器在一台机器上，数据库服务器可以直接检
查客户端用户的操作系统用户身份，就不需要向ident服务发送请求进行判断了。

ldap：使用LDAP服务器进行身份验证。
radius：使用RADIUS服务器进行身份验证。
cert：使用SSL客户端证书进行身份验证。
pam：使用操作系统提供的可插入身份验证模块（PAM）服务进行身份验证。
bsd：使用操作系统提供的BSD身份验证服务进行身份验证。

#如果一台机器只给数据库使用，而没有其他用途，则可以在pg_hba.conf中加上下面一行配置:
local all all trust
#该配置表示在这台机器上，任何操作系统的用户都可以使用任何数据库用户（包括数据库超级用户）
#连接到数据库而不需要任何密码。因为这台主机只供数据库使用，可以把不用的操作系统用户都禁止掉,以保证安全性。

#如果数据库中有一个用户“dba”，操作系统中也有一个用户“dba”
#在操作系统"dba”用户下连接数据库不需要密码验证的设置方法:
local all dba ident

#如果想在数据库主机上使用密码验证，可以使用下面的配置项:
local all all md5

#如果想让其他主机的连接都使用md5密码验证，则使用如下配置:
host all all 0.0.0.0/0 md5

#允许用户通过10.0.0.0/24的远程主机进行md5验证登录
#TYPE   DATABASE  USER   ADDRESS      METHOD
host    all       all    10.0.0.0/24  md5

#允许用户wang通过任意远程主机进行md5验证登录test数据库
#TYPE   DATABASE  USER   ADDRESS      METHOD
host    test       wang    0.0.0.0/0  md5

#修改用户postgres密码
[root@rocky8 ~]#psql
postgres=# ALTER USER postgres with password '123456';
ALTER ROLE

#查看监听地址和端口,默认为127.0.0.1:5432
[root@rocky8 ~]#ss -ntl

[root@rocky8 ~]#vim /pgsql/data/postgresql.conf
listen_addresses = '*' #修改此行中的localhost为 *
#listen_addresses = '0.0.0.0' #或者修改为0.0.0.0

[root@rocky8 ~]#vim /pgsql/data/pg_hba.conf
host all all ::1/128 trust
#上面行的后面加一行
host all all 0.0.0.0/0 md5

#重启服务生效
[postgres@rocky8 ~]$pg_ctl restart -mf

#查看监听地址和端口
[root@rocky8 ~]#ss -ntl

#psql
Usage:
  psql [OPTION]... [DBNAME [USERNAME]]

#测试远程登录
[root@rocky8 ~]#psql -d postgres -h PostgreSQL主机IP -p 5432 -U postgres

$ psql  -h 10.0.0.131 db1 postgres
Password for user postgres: 
psql (17.0)
Type "help" for help.

db1=# 

[root@rocky8 ~]#vim .pgpass
#hostname:port:database:username:password
10.0.0.200:5432:testdb:postgres:123456

[root@rocky8 ~]#chmod 600 .pgpass

[root@rocky8 ~]#ll .pgpass
-rw------- 1 root root 81 Dec 30 10:04 .pgpass

#psql默认连接本机，需要指定和.pgpass文件内容相匹配信息才可以使用.pgpass文件连接
[root@rocky8 ~]#psql -U postgres -h 10.0.0.200 -d testdb -w
testdb=# \c
You are now connected to database "testdb" as user "postgres".
testdb=#

#列出psql帮助用法
help

#列出以\开头的命令,即psql的命令
\?

#列出所有SQL命令的帮助，注意：SQL语句必须以 ; 结束
\h

#查看指定SQL的帮助
\h create database
\help create user

#后续查询将坚着显示,类似于MySQL中的\G
\x

#开启命令执行时长提示
\timing on

#显示详细的信息,可以打印出报出问题的源代码位置
\set VERBOSITY verbose

postgres=# \x  #开启竖着显示
Expanded display is on

postgres=# \l  #列出数据库信息。可以看到是竖着显示的
db1=# \l
List of databases
-[ RECORD 1 ]-----+----------------------
Name              | db1
Owner             | postgres
Encoding          | UTF8
Locale Provider   | libc
Collate           | en_US.UTF-8
Ctype             | en_US.UTF-8
Locale            | 
ICU Rules         | 
Access privileges | 


postgres=# select pg_sleep(3);
pg_sleep
(1 row)

testdb=# \timing on
Timing is on.

testdb=# select pg_sleep(3);
Time: 3003.928 ms (00:03.004)

testdb=# \timing off
Timing is off.

testdb=# select pg_sleep(3);
testdb=#

[root@ubuntu2004 ~]#su - postgres
postgres@ubuntu2004:~$ psql
postgres=# \set VERBOSITY verbose
postgres=# select wang;
ERROR: 42703: column "wang" does not exist LINE 1: select wang;
LOCATION: errorMissingColumn, parse_relation.c:3349
#说明:错误对应的是parse_relation.c文件中的3349行中errorMissingColumn函数

[root@ubuntu2004 ~]#find -name parse_relation.c
./postgresql-12.9/src/backend/parser/parse_relation.c

[root@ubuntu2004 ~]#vim +3349 `find -name parse_relation.c`

errorMissingColumn(ParseState *pstate,
                   const char *relname, const char *colname, int location)

{

    FuzzyAttrMatchState *state; 
    char *closestfirst = NULL;

    state = searchRangeTableForCol(pstate, relname, colname, location);

    if (state->rfirst && AttributeNumberIsValid(state->first)) 
        closestfirst = strVal(list_nth(state->rfirst->eref->colnames,
                                      state->first - 1));
    if (!state->rsecond)

createdb [option...] [dbname [description]]

#参数说明：
options：参数可选项，可以是以下值：
-D tablespace指定数据库默认表空间。
-e 将createdb 生成的命令发送到服务端。
-E encoding指定数据库的编码。
-l locale指定数据库的语言环境。
-T template指定创建此数据库的模板。
--help显示 createdb 命令的帮助信息。
-h host指定服务器的主机名。
-p port指定服务器监听的端口，或者 socket 文件。
-U username连接数据库的用户名。
-w忽略输入密码。
-W连接时强制要求输入密码

dbname：要创建的数据库名。
description：关于新创建的数据库相关的说明。

#方法1
[root@ubuntu2004 ~]#createdb -h 10.0.0.200 -p 5432 -U postgres testdb
Password:

#方法2
postgres@ubuntu2004:~$ psql
postgres=# create database testdb;

postgres@ubuntu2004:~$ psql
postgres=# drop database testdb; #连着这个库时无法删除，先切到别的库再删除

postgres=# select oid,datname from pg_database;
  oid  |  datname  
-------+-----------
     5 | postgres
 24580 | db1
     1 | template1
     4 | template0
 24587 | db2
(5 rows)

postgres@debian:~$ ls /pgsql/data/base/
1  24580  24587  4  5

#创建模式
create schema schema_name;

#删除模式
drop schema schema_name;

#列出所有schema
postgres=# \dn

postgres=# \c db1
You are now connected to database "db1" as user "postgres".

db1=# create schema m48_sch;
CREATE SCHEMA
db1=# create schema m47_sch;
CREATE SCHEMA
db1=# \dn
 m47_sch | postgres
 m48_sch | postgres
 public  | pg_database_owner

db1=# create table m48_sch.t1(id int);
CREATE TABLE
db1=# create table m47_sch.t1(id int);
CREATE TABLE
db1=# \dt
 public | t1   | table | postgres
 public | t2   | table | postgres

#查看
db1=# \dt m48_sch.t1
 m48_sch | t1   | table | postgres

db1=# \dt m48_sch.*
 m48_sch | t1   | table | postgres

db1=# \dt m47_sch.*
 m47_sch | t1   | table | postgres

#列出所有数据库名,相当于MySQL中的show databases;
postgres=# \l

#显示数据库详细信息,比如大小
testdb-# \l+


#查看当前连接信息
postgres=# \c
You are now connected to database "postgres" as user "postgres".

#查看当前连接详细信息
postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".

#连接数据库,相当于use
postgres=# \c hellodb
You are now connected to database "hellodb" as user "postgres".
hellodb=#

postgres=# select typname from pg_type;

typname
--------------
bool
bytea
char
name
int8
int2
int2vector
int4
regproc
text
oid
tid
xid
cid
oidvector
pg_type
pg_attribute
pg_proc
pg_class
json
xml

postgres=# \c testdb
testdb=# create table tb1 (id serial primary key,name text);
CREATE TABLE

#生成9次随机数插入到name中
testdb=# insert into tb1 (name) select (md5(random()::text)) from generate_series (2,10);
INSERT 0 9
Time: 0.816 ms

testdb=# select * from tb1;
 id |               name               
----+----------------------------------
  1 | b57de43d05a691dac86ae85b9191583e
  2 | 2543f7146cfa18cfb3d5842dcd6a4304
  3 | da0ea56dc318ce616f00b8750210cdec
  4 | 7185258c81c16cca69591c7aaae11df4
  5 | e49acadbce73bf718da4f037c45aee3b
  6 | 3ffeac879d5e22202806b3c3354fb47c
  7 | d8f1526f8f7bb636e2339729764af096
  8 | cd2a34be663b47b0751d467df0f3128d
  9 | b85b403e135688aca61b5f770e3c0562


#PostgreSQL中插入100万条记录只需要花2s
testdb=# \timing on
testdb=# insert into tb1 (name) select (md5(random()::text)) from generate_series (1,1000000);
INSERT 0 1000000
Time: 2111.662 ms (00:02.112)
testdb=# select count(*) from tb1;
  count  
---------
 1000000
(1 row)


#复制表结构，不复制数据
testdb=# create table tb2 ( like tb1 );
CREATE TABLE
testdb=# \d tb2
testdb=# select * from tb2;
testdb=# drop table tb2;

#列出所有表,视图,序列
\d

#列出public的schema中所有的表名,相当于show tables;
\dt

#查看t1的表信息
\dt t1

#支持通配符*和？，以下显示所有t开头的表
\dt t*

#列出myschema模式的表结构
\dt myschema.*

#查看t1的表结构,相当于desc
\d t1

#列出所有表信息,包括大小
hellodb-# \dt+

#列出表信息
hellodb=# \dt students

#列出表信息的大小信息
hellodb=# \dt+ students


#查看所有表
postgres=# select * from pg_tables;

#列出系统表
db1=# \dt  pg_catalog.*


#查看表大小
testdb=# select pg_total_relation_size('tb1');

testdb=# select pg_total_relation_size('tb1')/1024/1024||'MB';

db1=# select oid,datname from pg_database where datname = 'db1';
16385

db1=# select relid from pg_stat_all_tables where relname='tb2';
16413

[root@ubuntu2004 ~]#ll /pgsql/data/base/16385/16413
-rw------- 1 postgres postgres 0 Feb 14 07:21 /pgsql/data/base/16385/16413

postgres=# select * from pg_stat_all_tables;
postgres=# \c testdb

#查看指定表t1的信息
testdb=# select * from pg_stat_all_tables where relname='t1';

#查看系统表
postgres=# \dS
postgres=# \dS+

#列出所有pg开头的系统表
postgres=# \dt pg_*

#列出所有pg开头的系统视图
postgres=# \dv pg_*

#查看系统表pg_database的结构
postgres=# \d pg_database
               Table "pg_catalog.pg_database"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 oid            | oid       |           | not null | 
 datname        | name      |           | not null | 
 datdba         | oid       |           | not null | 
 encoding       | integer   |           | not null | 
 datlocprovider | "char"    |           | not null | 
 datistemplate  | boolean   |           | not null | 
 datallowconn   | boolean   |           | not null | 
 dathasloginevt | boolean   |           | not null | 
 datconnlimit   | integer   |           | not null | 
 datfrozenxid   | xid       |           | not null | 
 datminmxid     | xid       |           | not null | 
 dattablespace  | oid       |           | not null | 
 datcollate     | text      | C         | not null | 
 datctype       | text      | C         | not null | 
 datlocale      | text      | C         |          | 
 daticurules    | text      | C         |          | 
 datcollversion | text      | C         |          | 
 datacl         | aclitem[] |           |          | 

postgres=# \d pg_tables;
              View "pg_catalog.pg_tables"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 schemaname  | name    |           |          | 
 tablename   | name    |           |          | 
 tableowner  | name    |           |          | 
 tablespace  | name    |           |          | 
 hasindexes  | boolean |           |          | 
 hasrules    | boolean |           |          | 
 hastriggers | boolean |           |          | 
 rowsecurity | boolean |           |          | 

testdb=# select * from pg_relation_filepath('t1');  #pg_relation_filepath系统函数
pg_relation_filepath
base/16408/16409

[root@ubuntu2004 ~]#ll /pgsql/data/base/16408/16409
-rw------- 1 postgres postgres 76562432 Jan 16 03:44 /pgsql/data/base/16408/16409

db1=# create database testdb;
db1=# \c testdb

testdb=# create table tb1 (id serial,name varchar(10));
CREATE TABLE

testdb=# \d tb1;
                                   Table "public.tb1"
 Column |         Type          | Collation | Nullable |             Default             
--------+-----------------------+-----------+----------+---------------------------------
 id     | integer               |           | not null | nextval('tb1_id_seq'::regclass)
 name   | character varying(10) |           |          | 

testdb=# insert into tb1 (name)values('wang');
INSERT 0 1
testdb=# insert into tb1 (name)values('li');
INSERT 0 1
testdb=# select * from tb1;

testdb=# update tb1 set name='zhao' where id=2;
UPDATE 1
testdb=# select * from tb1;

testdb=# delete from tb1 where id=2;
DELETE 1
testdb=# select * from tb1; 

#清空表
testdb=# truncate tb1;
testdb=# truncate table tb1;
TRUNCATE TABLE
testdb=# select * from tb1;

hellodb=# \d students;
hellodb=# select pg_column_size(name),name from students;

testdb=# select generate_series(3,6);
3
4
5
6

testdb=#

testdb=# create table tb1(id int,info text,crt_time timestamp);
CREATE TABLE

testdb=# insert into tb1 select generate_series(1,100000),md5(random()::text),clock_timestamp();
INSERT 0 100000

testdb=# select * from tb1 limit 3;
1 | 4d801e211aca0b2787ecbd489eb91460 | 2020-03-18 03:06:55.279125
2 | c798c226fcf884c0d892de5f6bed0355 | 2020-03-18 03:06:55.279367
3 | 268445645ff62f6c7cbbad98a74704b8 | 2020-03-18 03:06:55.279369

#创建索引
testdb=# create index idx_tb1_id on tb1(id); #索引名， idx_表名_列名
CREATE INDEX
testdb=# \d tb1
                           Table "public.tb1"
  Column  |            Type             | Collation | Nullable | Default 
----------+-----------------------------+-----------+----------+---------
 id       | integer                     |           |          | 
 info     | text                        |           |          | 
 crt_time | timestamp without time zone |           |          | 
Indexes:
    "idx_tb1_id" btree (id)


#删除索引
testdb=# drop index idx_tb1_id ;
DROP INDEX
testdb=# \d tb1
testdb=#

#打开时间
testdb=#\timing on

#查询条件是索引列
testdb=# explain analyze select * from tb1 where id = 99999;
                                                   QUERY PLAN                                                    
-----------------------------------------------------------------------------------------------------------------
 Index Scan using idx_tb1_id on tb1  (cost=0.29..8.31 rows=1 width=45) (actual time=0.112..0.114 rows=1 loops=1) #Index Scan 索引查询
   Index Cond: (id = 99999)
 Planning Time: 0.473 ms
 Execution Time: 0.136 ms


#查询条件不是索引列
testdb=# explain analyze select * from tb1 where info ='f522a254fa2314150c574160bf1e7139';
                                            QUERY PLAN                                            
--------------------------------------------------------------------------------------------------
 Seq Scan on tb1  (cost=0.00..2185.00 rows=1 width=45) (actual time=0.014..12.195 rows=1 loops=1) #Seq Scan 不是索引
   Filter: (info = 'f522a254fa2314150c574160bf1e7139'::text)
   Rows Removed by Filter: 99999
 Planning Time: 0.052 ms
 Execution Time: 12.211 ms
(5 rows)


#关闭索引
testdb=# set enable_indexscan=off;
testdb=# set enable_bitmapscan=off;

#再次查询全表扫描
testdb=# explain analyze select * from tb1 where id = 99999;
testdb=# explain (analyze,verbose,costs,buffers,timing) select * from tb1 where id = 99999;

#列出所有表空间,实际上PostgresQL中的表空间就是对应一个目录，
#放在这个表空间的表，就是把表的数据文件放到这个表空间下。
postgres=# \db
       List of tablespaces
    Name    |  Owner   | Location 
------------+----------+----------
 pg_default | postgres | 
 pg_global  | postgres | 
(2 rows)
testdb=# \db+  #显示表空间的大小
                                  List of tablespaces
    Name    |  Owner   | Location | Access privileges | Options |  Size  | Description 
------------+----------+----------+-------------------+---------+--------+-------------
 pg_default | postgres |          |                   |         | 138 MB | 
 pg_global  | postgres |          |                   |         | 565 kB | 
(2 rows)


#复制表到文件中
testdb=# select * from t1;
testdb=# copy t1 to '/tmp/t1.txt';
COPY 2

[root@ubuntu2004 ~]#cat /tmp/t1.txt
1
2

[root@ubuntu2004 ~]#su - postgres
postgres@ubuntu2004:~$ mkdir ts1

postgres@ubuntu2004:~$ psql testdb

#创建表空间
postgres=# create tablespace ts1 location '/home/postgres/ts1/';
CREATE TABLESPACE

postgres=# \db
            List of tablespaces
    Name    |  Owner   |      Location      
------------+----------+--------------------
 pg_default | postgres | 
 pg_global  | postgres | 
 ts1        | postgres | /home/postgres/ts1
(3 rows)

postgres@ubuntu2004:~$ readlink /pgsql/data/pg_tblspc/16442
/home/postgres/ts1

#删除表空间，提前清空表空间中的表
testdb=# drop tablespace ts1;
DROP TABLESPACE

[root@ubuntu2004 ~]#ls /pgsql/data/pg_tblspc
[root@ubuntu2004 ~]#su -postgres
postgres@ubuntu2004:~$ mkdir /tmp/tbs1

postgres@ubuntu2004:~$ psql
testdb=# create tablespace tbs1 location '/tmp/tbs1' ;
testdb=# create table tb1(id int) tablespace tbs1;
testdb=# select * from pg_relation_filepath('tb1');


postgres@ubuntu2004:~$ tree /pgsql/data/pg_tblspc/
postgres@ubuntu2004:~$ tree /tmp/tbs1/

#查看版本信息
postgres=# select version();

#查看数据库启动时间
postgres=# select pg_postmaster_start_time();

#查看加载配置文件时间
postgres@ubuntu2004:~$ pg_ctl reload
postgres=# select pg_conf_load_time();

#查看时区和时间
postgres=# show timezone;
Etc/UTC (1 row)

#临时修改
postgres=# set timezone='Asia/Shanghai'

#永久修改时区
postgres@ubuntu2004:~$ vim /pgsql/data/postgresql.conf
timezone = 'Asia/Shanghai'

postgres@ubuntu2004:~$ pg_ctl reload
postgres=# select now();

#查看当前用户
postgres=# select user;
postgres=# select current_user;
postgres=# select session_user;

#查看当前数据库
postgres=# \c
#连接数据库
postgres=# \c testdb

#查看当前session所在的客户端IP和端口
[root@rocky8 ~]#psql -h 10.0.0.200 -U postgres
postgres=# select inet_client_addr(),inet_client_port();

#查看当前session所连接的数据库服务器的IP和端口
postgres=# select inet_server_addr(),inet_server_port();

#查询当前session对应的后台服务时程pid
postgres=# select pg_backend_pid();

ps auxf |grep postgres

#查看当前内置变量
postgres=> \set

#查看当前指定配置
postgres=# show max_connections;
postgres=# select current_setting('max_connections');
postgres=# select current_setting('listen_addresses');

#显示系统函数
postgres=# \dfS+

#查看连接数
postgres=# select count(*) from pg_stat_activity;

#查看当前最大的连接数
postgres=# select setting from pg_settings where name = 'max_connections';

#查看所有设置名称
postgres=# select name from pg_settings;

#查看当前设置名和值
postgres=# select name,setting from pg_settings;

#查看指定的当前的参数设置
postgres=# show port;
postgres=# show archive_mode;
 archive_mode 
--------------
 off

# 查看参数
SHOW name;
SHOW ALL;

postgres=# show all;

#修改配置
SET [ SESSION | LOCAL ] configuration_parameter { TO | = } { value |'value' | DEFAULT }
SET [ SESSION | LOCAL ] TIME ZONE { timezone | LOCAL | DEFAULT }

postgres=# show maintenance_work_mem ;
postgres=# set maintenance_work_mem to '128MB';
testdb=# set maintenance_work_mem='128MB'; #写法2
SET
postgres=# show maintenance_work_mem ;

#注意:不是所有配置都可以直接修改的
postgres=# set max_connections to '200';
ERROR: parameter "max_connections" cannot be changed without restarting the server

#查看数据库的大小,pg_size_pretty函数会把数字以MB,GB等易读格式显示
postgres=# select pg_database_size('hellodb'),pg_size_pretty(pg_database_size('hellodb'));
pg_database_size | pg_size_pretty
 8029039 | 7841 kB

#explain可以查看SQL的执行计划
hellodb=# explain select * from students;
hellodb=# explain analyze select * from students;
hellodb=# explain analyze verbose select * from students;

#查看所有用户\du或\dg
postgres=# \du
                             List of roles
 Role name |                         Attributes                         
-----------+------------------------------------------------------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS


#查看当前用户
postgres=# select user ;
postgres=# select current_user;

#显示表,视图,序列的权限分配情况
postgres=# \z
postgres=# \z t1

#和\z功能相同
postgres=# \dp

#开始事务
BEGIN [ISOLATION LEVEL { SERIALIZABLE | REPEATABLE READ | READ COMMITTED ​| READ UNCOMMITTED }]

#提交和取消事务
COMMIT|END
ROLLBACK

#关闭自动提交,可以用rollback取消DML语句
\set AUTOCOMMIT off  #使用commit; 来提交
\set AUTOCOMMIT on

#查看AUTOCOMMIT状态
\echo :AUTOCOMMIT

#查看事务ID
select txid_current();

postgres@ubuntu2004:~$ psql
postgres=# \c testdb

testdb=# begin;
BEGIN
testdb=# create table tb1 (id int);
testdb=# insert into tb1 values (1);
testdb=# select * from tb1;
1

testdb=# rollback;
ROLLBACK
testdb=# \d
Did not find any relations.

#查看事务ID
postgres=# select txid_current();
543

#事务块中不支持create database
testdb=# begin;
BEGIN
testdb=# create database db1;
2022-01-18 01:54:36.823 UTC [14674] ERROR: CREATE DATABASE cannot run inside a transaction block
2022-01-18 01:54:36.823 UTC [14674] STATEMENT: create database db1;
ERROR: CREATE DATABASE cannot run inside a transaction block

#查看ctid(数据所在的数据块的编号及位移),xmin(插入事务XID),xmax(删除记录的事务XID)
testdb=# select ctid,xmin,xmax,* from tb1;

#查看锁信息
testdb=# select relation::regclass,* from pg_locks;

#查看当前日志文件lsn位置;
select pg_current_wal_lsn();
select pg_current_xlog_location();

#当前xlog buffer中的insert位置，注意和上面pg_current_xlog_location()的区别:
SELECT pg_current_wal_insert_lsn();
select pg_current_xlog_insert_location();

#查看某个1sn对应的日志名:
select pg_walfile_name(lsn) ;
select pg_xlogfile_name(1sn);

#查看某个1sn在日志中的偏移量:
select pg_walfile_name_offset('lsn号');
select pg_xlogfile_name_offset('lsn号');

#查看两个lsn位置的差距;
select pg_wa1_1sn_diff('lsn号','lsn号');
select pg_xlog_1ocation_diff('lsn号','lsn号');

#查看备库接收到的1sn位置:
select pg_last_wal_receive_lsn();
select pg_last_xlog_receive_location();

#查看备库回放的lsn位置:
select pg_last_xact_replay_timestamp();
select pg_last_xlog_relay_location();

#创建还原点:
select pg_create_restore_point(now()::text);

#查看表的数据文件路径，filenode:
select pg_relation_filenode( '表名');

#查看表students的oid:
select 'students'::regclass::oid;

#查看当前会话pid:
select pg_backend_pid();

#生成序列:
select generate_series (1,8,2);

#生成uuid (pg13新特性):
se1ect gen_random_uuid();

#重载配置文件信息:
select pg_reload_conf();

#查看数据库启动时间:
select pg_postmaster_start_time();

CREATE USER name [[WITH] option [ ...]] CREATE ROLE name [[WITH] option [ ...]]

#上面两个命令都可以创建用户,不同的是CREATE USER创建的用户默认可以登录,而CREATE ROLE不可以登录
#除了CREATE USER 默认创建出来的用户有LOGIN 的权限，而CREATE ROLE
#创建出来的用户没有“LOGIN"的权限之外，CREATE RULE 与 CREATE USER没有其他任何的区别。

#上面语法中的“option”可以是如下内容。

SUPERUSER | NOSUPERUSER:表示创建出来的用户是否为超级用户。只有超级用户才能创建超级用户。
CREATEDB /NOCREATEDB:指定创建出来的用户是否有执行'CREATE DATABASE'的权限。
CREATEROLE NOCREATEROLE:指定创建出来的用户是否有创建其他角色的权限。
CREATEUSER NOCREATEUSER:指定创建出来的用户是否有创建其他用户的权限。
INHERIT ​|NOINHERIT:如果创建的一个用户拥有某一个或某几个角色，这时若指定INHERIT，
则表示用户自动拥有相应角色的权限，否则这个用户没有该角色的权限。

LOGIN | NOLOGIN:指定创建出来的用户是否有“LOGIN”的权限，可以临时地禁
止一个用户的“LOGIN”权限，这时此用户就不能连接到数据库

CONNECTION LIMIT connlimit:指定该用户可以使用的并发连接数量。默认值是-1，表示没有限制。
[ENCRYPTED | UNENCRYPTED ] PASSWORD'password' : 用于控制存储在系统表里面的口令是否加密。

VALID UNTIL 'timestamp':密码失效时间，如果不指定这个子句，那么口令将永远有效。

INROLE role name [,...]:指定用户成为哪些角色的成员，请注意没有任何选项可以把新角色添
加为管理员，必须使用独立的GRANT命令来做这件事情。

IN GROUP role_name [,...]:与IN ROLE相同，是已过时的语法。
ROLE role_name [,...]: role_name将成为这个新建的角色的成员。
ADMIN role_name [,...]: role_name将有这个新建角色的WITH ADMIN OPTION权限。
USER role_name[,....]:与ROLE子句相同，但已过时。
SYSID uid:此子句主要是为了SQL向下兼容，实际没有什么用处。

#查看帮助
\h create user
\h alter user
\h drop user
\h create role
\h alter role
\h drop role

#以下两个命令用法相似
create user #创建的用户默认可以连接 create role #创建的用户默认无法连接

#修改用户
alter user

#删除用户
drop user

#显出所有用户和角色
#\du和\dg命令等价。原因是在PostgreSQL数据库中用户和角色不分的。
\du
\dg

#创建可以登录的用户和密码
CREATE USER wang WITH PASSWORD '123456';

#创建不可登录用户
create role zhao WITH PASSWORD '123456';

#创建可以连接的用户
CREATE ROLE li WITH LOGIN PASSWORD '123456' VALID UNTIL '2020-07-01'

#创建管理员
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD '123456' ;

#创建复制用户
CREATE USER repl REPLICATION LOGIN ENCRYPTED PASSWORD '123456';

#修改密码
ALTER USER admin with password '654321';

#修改权限和密码
alter user wang with nologin password '123';
alter user zhao with login;

#删除用户
DROP USER song;

#查看用户信息
\du

#查看指定用户信息
\du wang

#使用postgres用户登录（PostgresSQL安装后会自动创建postgres用户）
[root@rocky8 ~]#su - postgres

#登录postgresql数据库
[root@rocky8 ~]# psql -U postgres
#安全起见,修改数据库管理员postgres用户的密码
postgres=# ALTER USER postgres WITH ENCRYPTED PASSWORD '123456';
ALTER ROLE

#GRANT命令有两个作用
#1.让某个用户成为某个角色的成员，从而使其拥有角色的权限:
GRANT role_name [, ...] T0 role_name [, ...] [ WITH ADMIN OPTION ];

#2.把某些数据库逻辑结构对象的操作权限赋予某个用户(或角色)，命令的格式如下:
GRANT some privileqes ON database_object_type object_name TO role_name;

其中，“some privileges”表示在这个数据库对象中的权限,“database_object_type”是数据库对象的类型,
如“TABLE”、“SEQUENCE”、“SCHEMA”，等。

#授权创建新数据库
postgres=# alter user wang with CREATEDB;

#database权限设置
GRANT create ON DATABASE testdb TO wang;

#schema权限
ALTER SCHEMA wang OWNER to wang;
GRANT select,insert,update,delete ON ALL TABLES IN SCHEMA wang TO wang;

#创建test的schema指定所有者为joe
CREATE SCHEMA IF NOT EXISTS test AUTHORIZATION joe;

#object权限
GRANT select,insert,update,delete ON testdb.t1 TO wang;

#创建数据库并指定所有者的用户
create user wang with password '123456';
CREATE DATABASE zabbix OWNER wang;

postgres=# create database pinxixi;
postgres=#\c pinxixi
pinxixi=#create user wanrentuan with password '123456';

#方法1
pinxixi=#create schema wanrentuan;
pinxixi=#ALTER SCHEMA wanrentuan OWNER to wanrentuan;
#方法2
pinxixi=#CREATE SCHEMA AUTHORIZATION wanrentuan;
#方法3
pinxixi=#GRANT select, insert,update,delete oN ALL TABLES IN SCHEMA wanrentuan to wanrentuan;

#将创建一个名为“readonly”的用户
CREATE USER readonly with password '123456';

#把在public的schema下现有的所有表的SELECT 权限赋给用户readonly
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

[root@ubuntu2004 ~]#apt install pgadmin4 -y
[root@ubuntu2004 ~]#pgadmin4

[root@rocky8 ~]#docker run -e PGADMIN_DEFAULT_EMAIL[email protected] \
               -e PGADMIN_DEFAULT_PASSWORD=123456 -d --name pgadmin -p 80:80 \
               dpage/pgadmin4

[root@ubuntu2004 ~]#ps auxf|grep ^postgres

[root@ubuntu2004 ~]#ps auxf|grep ^postgres

postgres=# show shared_buffers;
128MB
postgres=# show maintenance_work_mem;
64MB

postgres=# show work_mem;
4MB

PGDATA
- File segment, Tablespace, Control file, Online WAL log, ARCH WAL log, Log file

postgresql.conf #数据库实例的主配置文件，基本上所有的配置参数都在此文件中。
pg_hba.conf   #认证配置文件，配置了允许哪些IP的主机访问数据库，认证的方法是什么等信息。
pg_ident.conf #认证方式ident的用户映射文件。

base            #默认表空间的目录,每个数据库都对应一个base目录下的子目录,每个表和索引对应一个独立文件
global          #这个目录对应pg_global表空间,存放实例中的共享对象
pg_clog         #存储事务提交状态数据
pg_bba.conf     #数据库访问控制文件
pg_log          #数据库系统日志目录，在查询一些系统错误时就可查看此目录下日志文件。(根据配置定义,可能没有这个目录)
pg_xact         #提交日志commit log的目录,pg 9之前叫pg_clog
pg_multixact​    #共享行锁的事务状态数据
pg_notify       #异步消息相关的状态数据
pg_serial       #串行隔离级别的事务状态数据
pg_snapshots    #存储执行了事务snapshot导出的状态数据
pg_stat_tmp     #统计信息的临时文件
pg_subtrans     #子事务状态数据
pg_stat         #统计信息的存储目录。关闭服务时,将pg_stat_tmp目录中的内容移动至此目录实现保存
pg_stat_tmp     #统计信息的临时存储目录。开启数据库时存放统计信息
pg_tblsp        #存储了指向各个用户自建表空间实际目录的链接文件
pg_twophase     #使用两阶段提交功能时分布式事务的存储目录
pg_wal          #WAL日志的目录,早期版一本目录为pg_xlog
PG_VERSION      #数据库版本
postmaster.opts #记录数据库启动时的命令行选项
postmaster.pid  #数据库启动的主进程信息文件，包括PID，SPGDATA目录,
                数据库启动时间，监听端口，socket文件路径，临听地址,共享内存的地址信
                息(ipsc可查看),主进程状态

[root@ubuntu2004 ~]#ll $PGDATA

[root@ubuntu2004 ~]#ls /pgsql/data
[root@ubuntu2004 ~]#cat /pgsql/data/PG_VERSION 14
[root@ubuntu2004 ~]#cat /pgsql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.

[root@ubuntu2004 ~]#cat /pgsql/data/postmaster.opts
/apps/pgsql/bin/postgres "-D" "/pgsql/data"

postgres@ubuntu2004:~$ cat /pgsql/data/postmaster.pid
892
/pgsql/data
1579156864
5432
/tmp/
*
   5432001 0
ready

[root@ubuntu2004 ~]#find /tmp -type s -ls

listen_addresses='*'    #监听客户端的地址，默认是本地的，需要修改为*或者0.0.0.0
port = 5432             #pg端口，默认是5432
max_connections = 2000  #最大连接数，默认100
unix_socket_directories #socket文件的位置，默认在/tmp下面
shared_buffers          #数据缓存区，建议值1/4--1/2主机内存，和Oracle的buffer cache类似
maintenance_work_mem    #维护工作内存，用于vacuum,create index,reindex等。建议值(1/4主机内存)/autovacuum_max_workers
max_worker_processes    #总worker数
max_parallel_workers_per_gather #单条QUERY中，每个node最多允许开启的并行计算WORKER数
wal_level               #wal级别，版本11+默认是replica
wal_buffers             #类似Oracle的log buffer
checkpoint_timeout      #checkpoint时间间隔
max_wal_size            #控制wal的最大数量
min_wal_size            #控制wal的最小数量
archive_command         #开启归档命令，示例：'test ! -f /arch/%f && cp %p /arch/%f'
autovacuum              #开启自动vacuum

postgres=# \d pg_settings;
               View "pg_catalog.pg_settings"
     Column      |  Type   | Collation | Nullable | Default 
-----------------+---------+-----------+----------+---------
 name            | text    |           |          | 
 setting         | text    |           |          | 
 unit            | text    |           |          | 
 category        | text    |           |          | 
 short_desc      | text    |           |          | 
 extra_desc      | text    |           |          | 
 context         | text    |           |          | 
 vartype         | text    |           |          | 
 source          | text    |           |          | 
 min_val         | text    |           |          | 
 max_val         | text    |           |          | 
 enumvals        | text[]  |           |          | 
 boot_val        | text    |           |          | 
 reset_val       | text    |           |          | 
 sourcefile      | text    |           |          | 
 sourceline      | integer |           |          | 
 pending_restart | boolean |           |          

postgres=# select name,short_desc,setting from pg_settings where name like 'listen_addresses';
       name       |                     short_desc                     | setting 
------------------+----------------------------------------------------+---------
 listen_addresses | Sets the host name or IP address(es) to listen to. | 0.0.0.0

#查看运行时参数
postgres=# show listen_addresses;
0.0.0.0

postgres=# show timezone;
Etc/UTC (1 row)

#动态修改配置
postgres=# set timezone="Asia/Shanghai";
postgres=# show timezone;
Asia/Shanghai (1 row)

#有些参数不支持动态修改
postgres=# set port=1234;
ERROR: parameter "port" cannot be changed without restarting the server

[root@ubuntu2004 ~]#vim /pgsql/data/postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
port = 1234

[root@ubuntu2004 ~]#vim /pgsql/data/postgresql.conf
port = 4321

[root@ubuntu2004 ~]#systemctl restart postgresql.service

[root@ubuntu2004 ~]#ss -ntlp|grep postmaster


postgres@ubuntu2004:~$ psql -p1234
postgres=# show port;
1234

#pg_ident.conf如下实现操作系统test用户映射为数据库用户dba
#MAPNAME SYSTEM-USERNAME PG-USERNAME
map1     test     dba

#pg_hba.conf如下:
#TYPE DATABASE USER CIDR-ADDRESS METHOD
local all all ident map=map1

[root@ubuntu2004 ~]#useradd -s /bin/bash -m dba
[root@ubuntu2004 ~]#su - postgres

postgres@ubuntu2004:~$ psql
postgres=# create user dba WITH PASSWORD '123456';

[root@ubuntu2004 ~]#vim /pgsql/data/pg_hba.conf
local all all ident #如果本地操作系统账号和数据库账号同名，就可以自动关联登录

[root@ubuntu2004 ~]#pg_ctl -D /pgsql/data restart

#测试连接
[root@ubuntu2004 ~]#su - dba
dba@ubuntu2004:~$ psql postgres
postgres=>

[root@ubuntu2004 ~]#su - postgres
postgres@ubuntu2004:~$ psql
postgres=# create user dba WITH PASSWORD '123456';

[root@ubuntu2004 ~]#useradd -s /bin/bash -m test

[root@ubuntu2004 ~]#vim /pgsql/data/pg_ident.conf
# MAPNAME SYSTEM-USERNAME PG-USERNAME
map1 test dba

[root@ubuntu2004 ~]#vim /pgsql/data/pg_hba.conf
local all   all   ident map=map1
#​在此行上面加上面行
local all   all  trust

[root@ubuntu2004 ~]#pg_ctl -D /pgsql/data restart

#测试连接
[root@ubuntu2004 ~]#su - test

#直接登录失败
test@ubuntu2004:~$ psql
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed:
FATAL: Peer authentication failed for user "test"

#需要指定映射的数据库的用户和数据库
test@ubuntu2004:~$ psql -Udba postgres
postgres=>

[root@ubuntu2004 postgresql-14.2]#./configure --help|grep size
--with-blocksize=BLOCKSIZE
   set table block size in kB [8]
--with-segsize=SEGSIZE
   set table segment size in GB [1]
--with-wal-blocksize=BLOCKSIZE
   set WAL block size in kB [8]

#查看数据目录路径
testdb=# show data_directory;
/pgsql/data

#查看数据库的OID
testdb=# select oid,datname from pg_database;

#查看表的node
testdb=# select relfilenode from pg_class where relname='tb1';
relfilenode
16397

#查看指定表的目录路径
testdb=# select pg_relation_filepath('tb1');
base/16384/16397

postgres@ubuntu2004:~$ ls -l $PGDATA/base/16384/16397
-rw------- 1 postgres postgres 8192 May 16 14:26 /pgsql/data/base/16384/16397

#PG14版的控制文件
postgres@ubuntu2004:~$ file /pgsql/data/global/pg_control
/pgsql/data/global/pg_control: PGP Secret Sub-key -

#PG12版的控制文件
postgres@ubuntu2004:~$ file /pgsql/data/global/pg_control
/pgsql/data/global/pg_control: data


#查看控制文件内容
postgres@ubuntu2004:~$ pg_controldata $PGDATA
pg_control version number:            1700
Catalog version number:               202406281
Database system identifier:           7434814718768345198
Database cluster state:               in production #数据库当前状态
pg_control last modified:             Mon 11 Nov 2024 09:07:11 PM EST
Latest checkpoint location:           0/D173AF8
Latest checkpoint's REDO location:    0/D173AF8
Latest checkpoint's REDO WAL file:    00000001000000000000000D  #预写日志
Latest checkpoint's TimeLineID:       1
Latest checkpoint's PrevTimeLineID:   1
Latest checkpoint's full_page_writes: on
Latest checkpoint's NextXID:          0:802
Latest checkpoint's NextOID:          32827
Latest checkpoint's NextMultiXactId:  1
Latest checkpoint's NextMultiOffset:  0
Latest checkpoint's oldestXID:        730
Latest checkpoint's oldestXID's DB:   1
Latest checkpoint's oldestActiveXID:  0
Latest checkpoint's oldestMultiXid:   1
Latest checkpoint's oldestMulti's DB: 1
Latest checkpoint's oldestCommitTsXid:0
Latest checkpoint's newestCommitTsXid:0
Time of latest checkpoint:            Mon 11 Nov 2024 09:07:11 PM EST
Fake LSN counter for unlogged rels:   0/3E8
Minimum recovery ending location:     0/0
Min recovery ending loc's timeline:   0
Backup start location:                0/0
Backup end location:                  0/0
End-of-backup record required:        no
wal_level setting:                    replica
wal_log_hints setting:                off
max_connections setting:              1000
max_worker_processes setting:         8
max_wal_senders setting:              10
max_prepared_xacts setting:           0
max_locks_per_xact setting:           64
track_commit_timestamp setting:       off
Maximum data alignment:               8
Database block size:                  8192
Blocks per segment of large relation: 131072
WAL block size:                       8192
Bytes per WAL segment:                16777216
Maximum length of identifiers:        64
Maximum columns in an index:          32
Maximum size of a TOAST chunk:        1996
Size of a large-object chunk:         2048
Date/time type storage:               64-bit integers
Float8 argument passing:              by value
Data page checksum version:           0
Mock authentication nonce:            c8438a61af4513aba125b7c2e39f2cf9cea0545b5eaf729c7b0f6d8afb7cf31b

pg_dump [connection-option...] [option...] [dbname]

#连接选项和psql基本相同,pg_dump连接选项的参数如下
-h host或--host=host #指定运行服务器的主机名。如果以斜杠开头，则被用作到UNIX域套接字的路径。默认情况下，如果设置了SPGHOST
环境变量，则从此环境变量中获取，否则尝试一个UNIX域套接字连接。

-p port或--port=port  #指定服务器正在侦听的TCP端口或本地UNIX域套接字文件的扩展。
默认情况下，如果设置了$PGPORT环境变量，则从此环境变量中获取，否则取值为默认端口5432(编译时可以改变这个默认端口)。

-U username或--username=username指定要连接的用户名。

-w或--no-password #从不提示密码。密码可以通过其他方式如~/.pgpass文件获取

dbname #指定连接的数据库名，实际上也是要备份的数据库名。如果没有使用这个参数，则使用环境变量
SPGDATABASE。如果SPGDATABASE 也没声明，那么可使用发起连接的用户名。

#pg_dump专用选项
-a或--data-only #这个选项只是对纯文本格式有意义。只输出数据，不输出数据定义的SQL语句。
-b或--blobs #在转储中是否包含大对象。除非指定了选择性转储的选项--schema、--table、--schema-only开关，
否则默认会转储大对象。此选项仅用于选择性转储时控制是否转储大对象。

-c或一clean #这个选项只对纯文本格式有意义。指定输出的脚本中是否生成清理该数据库对象语句(如drop table命令)。

-C或--create #这个选项只对纯文本格式有意义。指定脚本中是否输出一条create
database语句和连接到该数据库的语句。一般在备份的源数据库与恢复的目标数据库的名称一致时，才指定这个参数。

-E encoding或--encoding=encoding #以指定的字符集编码创建转储。默认转储是依据数据库编码创建的。
如果不指定此参数，可以通过设置环境变量SPGCLIENTENCODING达到相同的目的

-f file --file=file #输出到指定的文件中。如果没有指定此参数,则输出到标准输出中。

-F format或--format=format #选择输出的格式。format可以是p、c或t。
p是plain 的意思，为纯文本SQL 脚本文件的格式，这是默认值。大库不荐
c是custom的意思，以一个适合pg_restore使用的自定义二进制格式输出并归档。这是最灵活的输出格式，在该格式中允许手动查询并且可以在pg
restore恢复时重排归档项的顺序。该格式默认是压缩的。
t是tar的意思，以一个适合输人pg_restore的
tar格式输出并归档。该输出格式允许手动选择并且在恢复时重排归档项的顺序，但是这个重排序是有限制的，比如，表数据项的相关顺序在恢复时不能更改。同时，
tar格式不支持压缩，并且对独立表的大小限制为8GB。

-n schema或--schema=schema #只转储匹配schema的模式内容，包括模式本身以及其中包含的对象。
如果没有声明这个选项，所有目标数据库中的非系统模式都会被转储出来。可以使用多个-n选项指定多个模式。

-t table或--table=table #只转储出匹配table的表、视图、序列。可以使用多个-t选项匹配多个表。同样table参数将按照psql
的\d命令的规则被解释为匹配模式，因此可以使用通配符匹配多个模式。在使用通配符时，最好用引号进行界定，以防止shell将通配符进行扩展。

-T table或--exclude-table=table #不转储任何匹配table模式的表。模式匹配规则与t完全相同。
可以指定多个-T以排除多种匹配的表。如果同时指定了-t和-T，那么将只转储匹配-t但不匹配-T的表。如果出现了-T而未出现-t，那么匹配-T的表不会被转储。

pg_restore [connection-option...] [option...] [filename]

pg_restore 的连接参数与pg_dump基本相同，如下
-h host或--host=host
-p port或--port=port
-U username或--username=username-w或--no-password
-W或--password
-d dbname或--dbname=dbname #不同之处在于，pg_restore使用-d的参数来连接指定的数据库并恢复数据至此数据库
filename #要恢复的备份文件的位置。如果没有声明，则使用标准输入。
-a或--data-only #只恢复数据，而不恢复表模式（数据定义)。
-c或--clean #创建数据库对象前先清理（删除）它们。
-C或--create #在恢复数据库之前先创建它。如果出现了这个选项，和-d在一起的数据库名只是用于发出最初的CREATE
DATABASE命令，所有数据都恢复到名字出现在归档中的数据库中。

-F format 或--format=format #指定备份文件的格式。pg_restore可自动判断格式，如果一定要指定,它可以是t或c之一。
t表示 tar，指备份文件是一个tar文件。
c表示custom，备份的格式是来自pg_dump的自定义格式,这是最灵活的格式，因为它允许对数据重新排序，也允许重载表模式元素，默认这个格式是压缩的。

-n namespace或--schema=schema #只恢复指定名字的模式里的定义和/或数据。这个选项可以和-t选项一起使用，只恢复一个表的数据。

-t table或--table=table #只恢复指定的表的定义和/或数据。可以与-n参数（指定schema)联合使用。

#备份单个数据库test中的所有表到指定目录,  注意，这样写没有创建库的SQL
pg_dump -U postgres -f /backup/test_backup test

#备份test数据库中的t1表和t2表∶
pg_dump -U postgres -t t1 -t t2 -f /backup/test_backup_t1_t2 test

#备份指定数据库，注意，没有创建库的SQL
pg_dump -d testdb > /backup/testdb.sql

#恢复过程
#注意:事先需要存在数据库,且删除所有表后才能恢复
psql -d testdb < /backup/testdb.sql

#备份全部数据库，每个数据库都需要输入密码，有N个数据库，就需要输入N次密码
pg_dumpall -U postgres -f full_backup.sql
gp_dumpall > full_backup.sql

#恢复
psql < full_backup.sql

#当连接的是一个本地数据库，并不需要密码时，要对数据库hellodb进行备份，备份文件的格式是脚本文件格式
#有创建库的SQL
pg_dump -C hellodb > hellodb.sql

#使用pg_dump也可以备份一个远程的数据库，如下面的命令备份10.0.0.200机器上的hellodb数据库
pg_dump -h 10.0.0.200 -U postgres -C hellodb > hellodb.sql

#如果想生成的备份文件格式为自定义格式，可以使用下面的命令:
pg_dump -Fc -h 10.0.0.200 -Upostgres hellodb > hellodb.dump

file hellodb.dump
hellodb.dump: PostgreSQL custom database dump - v1.14-0 #定制的格式

#查看备份的项目
pg_restore -l hellodb.dump
[root@rocky8 ~]#pg_restore -l /backup/hellodb.dump
;
; Archive created at 2022-02-16 15:21:03 CST
; dbname: hellodb
; TOC Entries: 34
; Compression: -1
; Dump Version: 1.14-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 14.2
; Dumped by pg_dump version: 14.2

#把上述备份文件恢复到另一个数据库hellodb2中
#先创建数据库才能恢复
psql -U postgres -h 10.0.0.200 -c "create database hellodb2"

#还原
pg_restore -h 10.0.0.200 -U postgres -d hellodb2 hellodb.dump

#只备份数据库hellodb中的表students
pg_dump -h 10.0.0.200 -Upostgres -t students hellodb > students.sql

#备份schema1模式中所有以job开头的表，但是不包括job_log表
pg_dump -t 'schema1.job*' -T schema1.job_log hellodb > schema1.emp.sql

#备份所有数据库对象，但是不包括名字以_log结尾的表
pg_dump -T '*log' hellodb > log.sql

#先从10.0.0.200备份数据库hellodb，然后恢复到10.0.0.100机器上
pg_dump -h 10.0.0.200 -U postgres hellodb -Fc > hellodb.dump
pg_restore -h 10.0.0.100 -U postgres -C -d postgres hellodb.dump
#在pg_restore命令中，-d中指定的数据库可以是10.0.0.200机器上实例中的任意数据库,pg_restore仅用该数据库名称进行连接，
-C 表示先执行CREATE DATABASE命令创建hellodb数据库，然后再重新连接到
hellodb数据库，最后把备份的表和其他对象建到hellodb数据库中

#将备份出来的数据重新加载到一个新建的不同名称的数据库hellodb2中
createdb -T template0 hellodb2
pg_restore -d hellodb2 hellodb.dump
#注意，上面的命令从template0而不是template1创建新数据库，确保干净。这里没有使用-C选项,而是直接连接到将要恢复的数据库上。

#导出
COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
       TO { 'filename' | PROGRAM 'command' | STDOUT } [ [ WITH ] ( option [, ...] ) ]

#导入：
COPY table_name [ ( column_name [, ...] ) ]
        FROM { 'filename' | PROGRAM 'command' | STDIN } [ [ WITH ] ( option [,...] ) ]

#常用参数说明：
table_name 现存表的名字(可以有模式修饰)
column_name 可选的待拷贝字段列表。如果没有声明字段列表，那么将使用所有字段
query 一个必须用圆括弧包围的SELECT或VALUES命令，其结果将被拷贝
filename 输入或输出文件的路径名。输入文件名可以是绝对或是相对的路径，但输出文件名必须是绝对路径。
Windows用户可能需要使用E”字符串和双反斜线作为路径名称
PROGRAM 需执行的程序名。在COPY
FROM命令中，输入是从程序的标准输出中读取，而在COPY
TO中，命令的输出会作为程序的标准输入。

注意，程序一般是在命令行界面下执行，当用户需要传递一些变量给程序时，如果这些变量的来源不是可靠
的，用户必须小心过滤处理那些对命令行界面来说是有特殊意义的字符。
基于安全的原因，最好是使用固定的命令字符串，或者至少是应避免直接使用用户输入（应先过滤特殊字符）
STDOUT 声明输入将写入客户端应用
FORMAT 选择被读或者写的数据格式：text、csv（逗号分隔值），或者binary。默认是text

#注意:
copy命令必须在psql命令行执行，执行用户必须为superuser，否则会提示错误如果
用普通用户进行执行，需要在copy前面加入“\”，即 \copy即可

#示例：
COPY students TO '/tmp/students.csv' WITH csv;

#可以导出指定的属性：
COPY students(stuid,name) TO '/tmp/students.csv' WITH csv;

#可以使用select 语句：
COPY (select * from students) TO '/tmp/students.csv' WITH csv;

#可以指定要导出哪些字段并带有列名：
COPY (select stuid,name,age from students) TO '/tmp/students.csv' WITH csv header;

#导入命令基本与导出一样，只是将TO 改为 FROM
create table students2 (likestudents);
COPY students2 FROM '/tmp/students.csv' WITH csv;

#如果导出的时候，指定了header属性，那么在导入的时候，也需要指定：
COPY students(stuid,name,age,gender) TO '/tmp/students.csv' WITH csv header;
COPY students2(stuid, name,age,gender) FROM '/tmp/students.csv' WITH csv header;

pg_basebackup [option.. .]
此命令后可以跟多个选项，选项的具体说明如下。

-D directory或--pgdata=directory
:指定把备份写到哪个目录。如果这个目录或这个目录路径中的各级父目录不存在，则pg_basebackup就会自动创建这个目录。

-F format或--format=format:
指定输出的格式。“format”指一种格式，它目前又支持两种格式，一种是原样输出，即把主数据库中的各个数据文件、
配置文件、目录结构都完全一样地写到备份目录，这种情况
下“format”指定为“p”或“plain”;另一种是tar格式，相当于把输出的备份文件打包到一个tar文件中，这种情况下“format”应为“t”或“tar”。

-x或--xlog:
备份时会把备份中产生的xlog文件也自动备份出来，这样才能在恢复数据库时，应用这些xlog文件把数据库推到
一个一致点，然后真正打开这个备份的数据库。这个选项与选项“-X
fetch”是完全一样的。使用这个选项，需要设置“wal_keep_segments”参数，以保证在备份过程中，需要的WAL日志文件不会被覆盖。

-X method或--xlog-method=method :
" method”可以取的值为“f”、“ fetch”、“s”.“stream"，其中“f”与“fetch相同，其意义与“-x”参数是一样的。“s”与“stream”表示的意思也相同，
表示备份开始后，启动另一个流复制连接从主库接收WAL日志。这种方式避免了使用“-X f”时，
主库上的WAL日志有可能被覆盖而导致失败的问题。但这种方式需要与主库建两个连接，
因此使用这种方式时，主库的“max_wal_senders”参数至少要设置为2或大于2的值。新版取消此选项

-z或--gzip:
仅能与tar输出模式配合使用，表明输出的tar备份包是经过gzip压缩的，相当于生成了一个​*.tar.gz的备份包。

-Z level或--compress=level:
指定gzip的压缩级别,可以选1~9的数字，与gzip命令中的压缩级别是一样的,9表示最高压缩率,但也最耗CPU。

-R, --write-recovery-conf #将复制的配置信息保存,生成standby.signal

-c fast/spread或--checkpoint=fast|spread:设置checkpoint的模式是fast还是spread。

-l label或--label=label:
指定备份的一个标识，备份的标识是一个任意字符串，便于今后维护人员识别这个备份，该标识就是手工做基础备份时运行“
select pg_start_backup(‘label')”传递给pg_start_backup函数的参数。在备份集中有一个文件叫“backup
label”，这里面除了记录开始备份时 WAL日志的起始位置、Checkpoint
的WAL日志位置、备份的开始时间外，也记录了这个标识串的信息。

-P或--progress
:允许在备份过程中实时地打印备份的进度。当然这个打印的进度不是百分之百精确的，因为在备份过程中，
数据库的数据还会发生变化,还会不断产生一些WAL日志。

-v或--verbose:详细模式，使用了-P参数后，还会打印出正在备份的具体文件的信息。

-V或--version:打印pg_basebackup的版本后退出。

-?或--help:显示帮助信息后退出。控制连接数据库的参数。

-h host或--host=host:指定连接的数据库的主机名或IP地址。

-p port或--port=port:指定连接的端口。

-s interval或--status-interval=interval
:指定向服务器端周期反馈状态的秒数,如果服务器上配置了流复制的超时，在使用--xlog=stream选项时，
则需要设置这个参数，默认值为10秒。如果设置为0，表示不向服务器反馈状态。

-U username或--username=username:指定连接的用户名。

-w或--no-password:指定从来不提示输入密码。

-W或--password:强制让pg _basebackup出现输入密码的提示。

-R write configuration for replication

#在postgreSQL服务器先授权
[postgres@gpserver ~]$vi /pgsql/data/pg_hba.conf
host replication all 10.0.0.0/24 md5

[postgres@gpserver ~]$pg_ctl restart -D $PGDATA

#在备份服务器执行下面操作
#注意:备份目录/pgsql/backup/必须为空
[postgres@gpserver ~]mkdir -p /pgsql/backup/

#备份, -Ft备份成tar包， -R 必须要加
[postgres@gpserver ~]$pg_basebackup -D /pgsql/backup/ -Ft -Pv -Upostgres -h 10.0.0.200 -p 5432 -R

#自动在$PGDATA目录下生成备份记录文件
[postgres@gpserver ~]$cat /pgsql/data/backup_label.old
START WAL LOCATION: 0/45000028 (file 000000010000000000000045)
CHECKPOINT LOCATION: 0/45000060
BACKUP METHOD: streamed BACKUP FROM: master
START TIME: 2020-05-11 10:38:49 UTC
LABEL: pg_basebackup base backup START TIMELINE: 1

#查看备份生成的文件
[postgres@gpserver ~]$ 1s /pgsql/backup/
base.tar pg_wal.tar backup_manifest

#在备份服务器上执行下面操作进行还原
[root@@gpserver ~]#mkdir /archive/
[root@@gpserver ~]#chown postgres.postgres /archive/

#确认备份文件存在
[postgres@gpserver ~]$ 1s /pgsql/backup/
base.tar pg_wal.tar

#模拟故障
[postgres@gpserver ~]$echo $PGDATA
/pgsql/data
[postgres@gpserver ~]$ pg_ctl -D $PGDATA stop
[postgres@gpserver ~]$ rm -rf $PGDATA/*
[postgres@gpserver ~]$ rm -rf /archive/*

#进行还原
[postgres@gpserver ~]$ tar xf /pgsql/backup/base.tar -C $PGDATA/
[postgres@gpserver ~]$ tar xf /pgsql/backup/pg_wal.tar -C /archive

#修改配置
[postgres@gpserver ~]$ vim $PGDATA/postgresql.conf
#添加下面行
restore_command = 'cp /archive/%f %p'
recovery_target = 'immediate'

#如果pg_basebackup执行时添加-R选项,可以不执行下面,否则会无法启动服务,执行下面指令创建文件后才能启动
[postgres@gpserver ~]$touch /pgsql/data/recovery.signal
[postgres@gpserver ~]$pg_ctl start -D $PGDATA

#停止恢复过程,否则为只读
postgres=# select pg_wal_replay_resume();

#创建复制的用户并授权
[postgres@master ~]$ psql
postgres=#create role repluser with replication login password '123456';

#修改pg_hba.conf进行授权
[postgres@master ~]$vi /pgsql/data/pg_hba.conf
host replication repluser 0.0.0.0/0 md5

#修改配置(可选):
[postgres@master ~]$vi /pgsql/data/postgresql.conf
synchronous_standby_names = '*' #开启此项,表示同步方式,需要同时打开synchronous_commit = on,此为默认值,默认是异步方式
synchronous_commit = on #开启同步模式
archive_mode = on #建议打开归档模式,防止长时间无法同步,WAL被覆盖造成数据丢失
archive_command = '[ ! -f /archive/%f ] && cp %p /archive/%f'
wa1_level = replica #设置wal的级别
max_wal_senders = 5 #这个设置可以最多有几个流复制连接，一般有几个从节点就设置几个
wal_keep_segments = 128 #设置流复制保留的最多的WAL文件数目
wal_sender_timeout = 60s #设置流复制主机发送数据的超时时间
max_connections = 200 # 一般查多于写的应用从库的最大连接数要比较大
hot_standby = on #对主库无影响,用于将来可能会成为从库,这台机器不仅仅是用于数据归档，也用于数据查询,在从库上配置此项后为只读
max_standby_streaming_delay = 30s #数据流备份的最大延迟时间
wal_receiver_status_interval = 10s #多久向主报告一次从的状态，当然从每次数据复制都会向主报告状态，只是设置最长的间隔时间
hot_standby_feedback = on #如果有错误的数据复制，是否向主进行反馈
wal_log_hints = on #对非关键更新进行整页写入

[postgres@master ~]$pg_ctl restart -D /pgsql/data

#清空数据和归档
[postgres@standby ~]$ pg_ctl stop -D $PGDATA
[postgres@standby ~]$ rm -rf /pgsql/data/*
[postgres@standby ~]$ rm -rf /archive/*
[postgres@standby ~]$ rm -rf /pgsql/backup/*

#备份主库数据到备库
[postgres@standby ~]$ pg_basebackup -D /pgsql/backup/ -Ft -Pv -Urepluser -h 10.0.0.101 -p 5432 -R

#还原备份的数据,实现初始的主从数据同步
[postgres@standby ~]$ tar xf /pgsql/backup/base.tar -C /pgsql/data
[postgres@standby ~]$ tar xf /pgsql/backup/pg_wal.tar -C /archive/

#方法1
#修改postgresql.conf文件
[postgres@standby ~]$ vi /pgsql/data/postgresql.conf
#添加下面两行
primary_conninfo = 'host=10.0.0.101 port=5432 user=repluser password=123456'
restore_command = 'cp /archive/%f %p' #此项可不配置

#修改配置(可选):
hot_standby = on #开启此项,此是默认项
recovery_target_timeline = latest # 默认
max_connections = 120 # 大于等于主节点，正式环境应当重新考虑此值的大小
max_standby_streaming_delay = 30s
wal_receiver_status_interval = 10
shot_standby_feedback = on
max_wal_senders = 15
logging_co1lector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'

#方法2
#修改postgresql.auto.conf
[postgres@standby ~]$ vi /pgsql/data/postgresql.auto.conf
primary_conninfo = 'host=10.0.0.101 port=54321 user=repluser password=123456 sslmode=disable sslcompression=0 gssencmode=disable krbsrvname=post_session_attrs=any' #此行自动生成,只修改用户名即可
restore_command = 'cp /archive/%f %p'

[postgres@standby ~]$ pg_ctl -D /pgsql/data start