Postgresql

常用操作命令:

需要超级用户登录到数据库中

库的基本操作

\l         #显示库
\c dbname  #进入库
\d         #查看表

创建只读账号：

create user ro_user password 'readonly';
# 设置Postgres数据库为只读的transaction
alter user ro_user set default_transaction_read_only=on;

# 赋予用户权限，查看public模式下所有表：(新建表也会有只读权限)
grant usage on schema public to ro_user;
alter default privileges in schema public grant select on tables to ro_user;

授权只读账号slave:

# 赋予用户连接数据库权限 (首次需要配置)
grant connect on database zhong to ro_user;
# 切换到指定数据库
\c zhong

#给pg中salve只读用户添加权限 
grant USAGE on SCHEMA public to slave;   #public是Schema ,可以通过\c  dbname ; \d来查看表在的Schema
grant SELECT on ALL tables in schema public  to slave


#序列查看权限授权
grant SELECT on ALL sequences in schema public  to slave

PostgreSQL是一个比较高性能的数据库，结合PostGIS插件，使PostgreSQL成为了一个空间数据库，能够进行空间数据管理、数量测量与几何拓扑分析。PostgreSQL从9.3开始支持JSON数据类型， 9.4开始支持JSONB，具备NoSQL数据库功能，在性能上甚至超过MongoDB。

二进制包下载地址

https://www.postgresql.org/ftp/source/

此处下载版本13.1

https://ftp.postgresql.org/pub/source/v13.1/postgresql-13.1.tar.gz

1、安装-所有节点安装

腾讯云服务器，系统版本centos7.9 2c4g

1.1 部署规划

主节点： 172.21.48.6
从节点： 172.21.48.5

1.2 安装

在主从部署安装之前，先要在两台节点上分别都安装Postgresql，这里以安装Postgresql13.1版本为例。

yum install gcc-c++ -y
yum install perl-ExtUtils-Embed -y
yum install readline-devel -y
yum install zlib-devel -y
yum install python-devel -y

创建用户

groupadd postgres
useradd -g postgres postgres
echo "postgres"|passwd postgres --stdin

解压，编译安装

root@server01 ~]# tar xf postgresql-13.1.tar.gz  
[root@server01 ~]# cd postgresql-13.1/ 
./configure --prefix=/usr/local/pgsql13.1  --with-perl --with-python
make && make install
ln -sf /usr/local/pgsql13.1 /usr/local/pgsql

chown -R postgres:postgres /usr/local/pgsql13.1/
chown -R postgres:postgres /usr/local/pgsql/

安装contrib目录下的工具

cd postgresql-13.1/contrib
make && make install

切换用户postgres，创建环境变量

su   -   postgres
vim .bash_profile
export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib
export PGHOST=/tmp
export LANG=en_US.UTF-8
export PGDATA=/postgres/

vim /etc/profile
export PATH=/usr/local/pgsql/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/pgsql/lib:$LD_LIBRARY_PATH

1.3 数据库实例化

[root@server01 contrib]# mkdir -p  /data/appData/postgreSql
[root@server01 contrib]# chown -R postgres:postgres /data/appData/postgreSql

su  -  posrgres

export PGDATA=/data/appData/postgreSql
initdb -k   #对于数据可靠性要求很高的尝尽，建议打开数据块checksum校验功能
[postgres@server02 ~]$ /usr/local/pgsql13.1/bin/initdb  -D  /data/appData/postgreSql --encoding=UTF8 --lc-collate=en_US.UTF-8 --lc-ctype=en_US.UTF-8
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/appData/postgreSql ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: warning: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/pgsql13.1/bin/pg_ctl -D /data/appData/postgreSql -l logfile start

1.4 启动数据库

###1.启动服务: 添加-l logfile start 可以查看日志
#这种情况下服务是监听在127.0.0.1:5432下，所以需要修改配置文件，配置远程可以连接
[postgres@server01 ~]$ pg_ctl start -D /data/appData/postgreSql/
waiting for server to start....2023-06-24 22:00:22.629 CST [20378] LOG:  starting PostgreSQL 13.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-06-24 22:00:22.629 CST [20378] LOG:  listening on IPv6 address "::1", port 5432
2023-06-24 22:00:22.629 CST [20378] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2023-06-24 22:00:22.632 CST [20378] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-06-24 22:00:22.634 CST [20379] LOG:  database system was shut down at 2023-06-24 21:52:19 CST
2023-06-24 22:00:22.637 CST [20378] LOG:  database system is ready to accept connections
 done
server started

#####查看端口监听的情况
[postgres@server01 ~]$ netstat  -ntlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      20378/postgres      
tcp6       0      0 ::1:5432                :::*                    LISTEN      20378/postgres

###2.然后停止服务
[postgres@server01 ~]$ pg_ctl stop   -D /data/appData/postgreSql/
waiting for server to shut down....2023-06-24 22:03:24.367 CST [20378] LOG:  received fast shutdown request
2023-06-24 22:03:24.368 CST [20378] LOG:  aborting any active transactions
2023-06-24 22:03:24.369 CST [20378] LOG:  background worker "logical replication launcher" (PID 20385) exited with exit code 1
2023-06-24 22:03:24.369 CST [20380] LOG:  shutting down
2023-06-24 22:03:24.378 CST [20378] LOG:  database system is shut down
 done
server stopped
##

####修改远程可以连接
修改listen监听为0.0.0.0 
[postgres@server01 ~]$ cat  /data/appData/postgreSql/postgresql.conf  | grep listen
listen_addresses = '0.0.0.0'		# what IP address(es) to listen on;

####修改pg_hba.conf，增加host    all             all             0.0.0.0/0               trust
[postgres@server01 ~]$ cat   /data/appData/postgreSql/pg_hba.conf  | grep host | grep -v "^#"
host    all             all            0.0.0.0/0                trust
host    all             all             ::1/128                 trust
host    all             all             0.0.0.0/0               trust
[postgres@server01 ~]$ 

创建测试账号-此部可以忽略

# 创建用户
createuser -U commonuser -h127.0.0.1 -p5432 -s postgres
psql -d postgres -U commonuser -h127.0.0.1 -p5432 -c "create user synthetic_user with superuser password '123456';"

2、主从同步-主库操作

主库操作

一定要保存好这个文件 all_DB.sql

2.2 创建主从同步用户

create role repl login replication encrypted password '123456'; ###非常关键

如果已安装postgresql建议先进行备份操作，备份操作参考如下：

# 创建备份SQL文件存放目录
mkdir -p /data/appData/backup /data/app/backup /data/tmp

## 导出系统库-非常关键
cd /data/appData/postgreSql/
pg_dumpall -U postgres -h 127.0.0.1 -p 5432 -f /data/appData/postgreSql/all_DB.sql

# 停止服务
pg_ctl stop -D /data/appData/postgreSql/

# 备份文件
cd /data/app
cp -arp /data/app/postgreSql  /data/app/backup/
cp -arp /data/appData/postgreSql  /data/appData/backup/

# 创建主从同步用户
create role repl login replication encrypted password '123456';

2.3 pg_hba.conf 配置

# 添加从库网段
echo "host    replication     repl            172.21.48.5/24        md5" >> /data/appData/postgreSql/pg_hba.conf

# 查看
[postgres@server01 ~]$ cat /data/appData/postgreSql/pg_hba.conf    | grep md5
# METHOD can be "trust", "reject", "md5", "password", "scram-sha-256",
# Note that "password" sends passwords in clear text; "md5" or
host    replication     repl            172.21.48.5/24        md5

2.4 postgresql.conf配置

参考配置

mkdir -p  /data/appData/postgreSql/pg_archive/xlog_files

vim /data/appData/postgreSql/postgresql.conf
listen_addresses = '*'      # 监听所有ip
wal_level = hot_standby     # 热备模式
archive_mode = on           #允许归档
archive_command = 'cp %p /data/appData/postgreSql/pg_archive/xlog_files/%f' # 此目录需要创建
max_wal_senders = 32        #同步最大的进程数量
wal_sender_timeout = 60s    #流复制主机发送数据的超时时间
max_connections = 200       #最大连接数，从库的max_connections必须要大于主库的

2.5 重启主库

pg_ctl restart -D /data/appData/postgreSql/

3. 从数据库操作

注意从库无需上文主库安装流程中的初始化步骤，使用pg_basebackup命令从主库同步数据

3.1 从主库同步数据

###备份从库数据
pg_ctl stop     -D /data/appData/postgreSql/
cd /data/appData/postgreSql 
tar all.tar.gz ./*   && mv all.tar.gz  ~/  ##备份

###从主库同步数据
pg_basebackup -h  172.21.48.6  -p 5432 -U repl -F p -P -D /data/appData/postgreSql 
# 输入repl用户密码：123456


###参数说明：

-h 指定连接的数据库的主机名或IP地址
-U 指定连接的用户名
-F 指定了输出的格式，支持p（原样输出）或者t（tar格式输出）。
-P 表示允许在备份过程中实时的打印备份的进度。
-D 指定把备份写到哪个目录，必须为空目录

3.2 postgresql.conf配置

从 PostgreSQL 12 开始已经移除了 recovery.conf 文件,相关配置合并到了 postgresql.conf 中,因为配置postgresql.conf 是从主库同步过来的，这里需要修改一些配置，改为从库的配置

vim /data/appData/postgreSql/postgresql.conf

## 移除或注释 wal_level，从库不需要这个配置
# wal_level = hot_standby

## 修改或添加以下
max_standby_streaming_delay=30s #可选，流复制最大延迟
wal_receiver_status_interval=10s #可选，向主库报告状态的最大间隔时间
hot_standby_feedback=on #可选，查询冲突时向主库反馈
max_connections=1000 #最大连接数一般大于主库就行
primary_conninfo = 'host=host=172.21.48.6 port=5432 user=repl password=123456'  # 表示主库连接信息
recovery_target_timeline = 'latest'     # 表示恢复最新的数据

3.3 创建 standby.signal

创建 standby.signal 文件,声明从库

[postgres@server02 ~]$ echo "standby_mode = on" >> /data/appData/postgreSql/standby.signal

3.4 启动从库

pg_ctl start -D /data/appData/postgreSql/

4.主从验证

4.1 主库验证方式

4.1.1 查看进程

[postgres@server01 ~]$ ps aux |grep "sender"|grep -v "grep"
postgres 24020  0.0  0.0 274820  2588 ?        Ss   23:03   0:00 postgres: walsender repl 172.21.48.5(60640) streaming 0/3000148
# 返回 postgres: walsender repl 172.16.98.201(53768) streaming

4.1.2 查表验证

登录主库

[postgres@server01 ~]$ psql -h 127.0.0.1 -p 5432 -U postgres
psql (13.1)
Type "help" for help.

postgres=# select pid,application_name,state,client_addr,sync_priority,sync_state from pg_stat_replication;
  pid  | application_name |   state   | client_addr | sync_priority | sync_state 
-------+------------------+-----------+-------------+---------------+------------
 24020 | walreceiver      | streaming | 172.21.48.5 |             0 | async
(1 row)

postgres=#


## 返回 async
## 如果该字段的值为 "sync"，则表示使用物理复制。如果值为 "async"，则表示使用逻辑复制。

主从复制的模式，在从服务上是无法实现数据的写入，如果想停止从服务的复制，可以使用下面的语句

#停止复制进程：在连接到从服务器的客户端会话中，执行以下 SQL 命令来停止复制进程
#该命令将暂停复制进程，并停止应用主服务器上的事务日志。
SELECT pg_wal_replay_pause();

#如果该查询返回结果为 "true"，则表示复制进程已成功停止
SELECT pg_is_wal_replay_paused();

4.2 从库验证

[postgres@server02 postgreSql]$ ps aux |grep "receiver" |grep -v "grep"
postgres 24574  0.0  0.0 323388  1888 ?        Ss   23:03   0:00 postgres: walreceiver streaming 0/3000148
[postgres@server02 postgreSql]$

# 返回 postgres: walreceiver streaming

5.数据库备份还原

https://www.cnblogs.com/uniqid/p/4154598.html

数据库导出

#备份导出数据
pg_dump  -U postgres -c  gyjtcq -f /home/postgres/gangyan0623.sql

#-U, --username=USERNAME  database user name (default: "postgres")

数据库还原

psql  -d  gyjtcq  -f   /home/postgres/gangyan0623.sql  postgres
#-d, --dbname=DBNAME      database name to connect to (default: "postgres")

5.1 关于备份的一些说明

Pg_restore 恢复。

To dump a database into a custom-format archive file:

一定要dump成custom格式才能用pg_restore恢复。

pg_dump -Fc -U postgres -t test -t test1 -t test2 postgres  > c:\test012-1.sql

Password:

pg_restore -d postgres -U postgres -t test1  c:\test012-1.sql

pg_dumpall导出

pg_dump 一次只能导出一个数据库。而且不能导出关于角色和表空间信息。（因为表空间和角色跨越单个数据库，属于cluster数据 库的。）pg_dumpall  能够帮你导出cluster数据库的完整内容。也可以单独导出单一数据库，角色和表空间的内容。(由于cluster不 好翻译。可以理解为多数据库。集群，簇数据库，不知道哪个更贴切，就不翻译了)

pg_dumpall > outfile

The resulting dump can be restored with psql: 导出结果能用psql恢复。

psql -f infile postgres

从节点：

如果让从节点提升为主节点执行下面语句

SELECT pg_promote();

select pg_is_in_recovery();   结果是f则为主库，t为备库

create database ilync  #测试是否成功

6、主从切换

先停止从库

从操作

备份原目录文件

###备份从库数据
pg_ctl stop     -D /data/appData/postgreSql/
cd /data/appData/postgreSql 
tar all.tar.gz ./*   && mv all.tar.gz  ~/  ##备份

###从主库同步数据
pg_basebackup -h  172.21.48.6  -p 5432 -U repl -F p -P -D /data/appData/postgreSql 

删除配置文件

rm -f /data/appData/postgreSql/standby.signal

注释掉主节点信息

vim /data/appData/postgreSql/postgresql.conf

## 移除或注释 wal_level，从库不需要这个配置
wal_level = hot_standby
max_connections=1000 #最大连接数一般大于主库就行
#primary_conninfo = 'host=host=172.21.48.6 port=5432 user=repl password=123456'  # 表示主库连接信息

然后重新启动从库，停止主库

常用操作命令:​

1、安装-所有节点安装​

1.1 部署规划​

1.2 安装​

1.3 数据库实例化​

1.4 启动数据库​

2、主从同步-主库操作​

2.2 创建主从同步用户​

2.3 pg_hba.conf 配置​

2.4 postgresql.conf配置​

2.5 重启主库​

3. 从数据库操作​

3.1 从主库同步数据​

3.2 postgresql.conf配置​

3.3 创建 standby.signal​

3.4 启动从库​

4.主从验证​

4.1 主库验证方式​

4.2 从库验证​

5.数据库备份还原​

6、主从切换​