postgresql 基本操作和主从复制集群

psql 基本操作

登录

psql -Upostgres -W

1、列举数据库：\l
2、选择数据库：\c 数据库名
3、查看该某个库中的所有表：\dt
4、切换数据库：\c interface
5、查看某个库中的某个表结构：\d 表名
6、查看某个库中某个表的记录：select * from apps limit 1;
7、显示字符集：\encoding
8、退出psgl：\q
9、 创建大写字母的对象： 把含有大写字母的对象名称用双引号包裹起来
create database "CGB";
或者
createdb -h 1.1.1.1 -p 5432 -e -U postgres "CGB"

导出数据

导出一个库

pg_dump -h localhost -U postgres  postgres -F c -b --encoding "UTF8" >  allpgsql.sql

-U 用户名
-b 在转储中包含大型对象
-F c 导入的文档格式， c 为 custom 容量会压缩

当有大容量数据需要导出备份时，可以使用多进程

docker-compose exec pgsql pg_dump -U postgres  -d db_name  -Fd  -b --encoding "UTF8" -j 2 -f dump_dir

-j 2 使用 2 核心进行
-Fd 使用gzip 压缩的方式对每个表导出一个 gzip 压缩后端文件，并放到一个目录中，这个目录使用 -f 指定，目前需要不存在

导入使用如下方式

docker-compose exec pgsql pg_restore -U postgres -d CGB -Fd -j4 CGB

导出某个库的某个表

pg_dump -h localhost -U postgres 库名 -t 表名 > allpgsql.sql

导入数据

psql -U postgres(用户名) 数据库名(缺省时同用户名) < /data/dum.sql

设置主从

主节点操作

1 主节点配置

pg_hba.conf
添加如下配置内容

host    replication     postgres       0.0.0.0/0                trust

postgresql.conf
添加或者修改如下内容

listen_addresses = '*'
max_connections = 200                   # (change requires restart)
password_encryption = scram-sha-256     # md5 or scram-sha-256
shared_buffers = 1GB                    # min 128kB
temp_buffers = 64MB                     # min 800kB
work_mem = 64MB                         # min 64kB
max_stack_depth = 4MB                   # min 100kB
dynamic_shared_memory_type = posix      # the default is the first option
wal_level = replica                     # minimal, replica, or logical
fsync = on                              # flush data to disk for crash safety
synchronous_commit = on         # synchronization level;
wal_log_hints = on                      # also do full page writes of non-critical updates
checkpoint_timeout = 10min              # range 30s-1d
max_wal_size = 1GB
min_wal_size = 80MB
archive_mode = on               # enables archiving; off, on, or always
archive_command = 'cp %p /var/lib/postgresql/data/pgdata/archive/%f'            # command to use to archive a logfile segment
max_wal_senders = 10            # max number of walsender processes
wal_keep_size = 1024            # in megabytes; 0 disables
max_slot_wal_keep_size = 10     # in megabytes; -1 disables
wal_sender_timeout = 120s       # in milliseconds; 0 disables
hot_standby = on                        # "off" disallows queries during recovery
log_timezone = 'Asia/Shanghai'
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'en_US.utf8'                      # locale for system error message
lc_monetary = 'en_US.utf8'                      # locale for monetary formatting
lc_numeric = 'en_US.utf8'                       # locale for number formatting
lc_time = 'en_US.utf8'                          # locale for time formatting
default_text_search_config = 'pg_catalog.english'
shared_preload_libraries = ''   # (change requires restart)

2 重启主服务器的 pgsql 服务

从节点操作

1 停止 pgsql 服务

2 删除pgsql 数据目录的所有文件

3 复制主节点数据到从节点
注意使用 postgres 用户

sudo -u postgres  /usr/pgsql-13/bin/pg_basebackup -h 192.168.1.76 -U postgres -p 5432 -F p   -X s -v -P -R -D /var/lib/pgsql/13/data

其中：-Fp表示以plain格式数据，-Xs表示以stream方式包含所需的WAL文件，-P表示显示进度，-R表示为replication写配置信息。

备份完成，在数据库实例目录下自动生成standby.signal“信号”文件，并在postgresql.auto.conf文件写入了主库的连接信息

4 查看验证服务角色是否为 从，返回 t 表示是从节点

psql -c "select pg_is_in_recovery()"

在主节点可以查询集群状态

sudo -u postgres /usr/pgsql-13/bin/psql -x -c "select * from pg_stat_replication" -d postgres

主从切换

从节点上操作
1 注释 data/postgres.auto.conf 文件中关于主节点的信息

2 使用PostgreSQL的命令pg_ctl promote，在某个从节点执行，将该节点升格为主节点

sudo -u postgres /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

此时已经没有主从集群了，只是这个从节点可以读写了，并不会将数据同步到另一个从节点

3 按照之前主从设置步骤，将坏掉的主节点和剩余的从节点服务停止，将data目录清空，使用pg_basebackup命令将主节点的data同步过来，之后启动服务

idle连接过多

参考文章　http://www.yu7s.com/article/20230506121505652.html