# pg_dump导出数据库报错处理

使用的备份语句如下：

```js
d:\Program Files\pgAdmin 4\runtime\pg_dump.exe --file "D:\\sunac_work\\sentry_data_backup_0626.sql" --host "172.17.36.76" --port "31263" --username "postgres" --no-password --format=c --large-objects --verbose "sentry"
```



报错如下：

![image.png](/static/img/3b8e1e8df20fd31becf4dfc18af0ac81.image.webp)

```js
pg_dump: dumping contents of table "public.hybridcloud_orgauthtokenreplica"
pg_dump: dumping contents of table "public.hybridcloud_regioncacheversion"
pg_dump: dumping contents of table "public.nodestore_node"
pg_dump: error: Dumping the contents of table "nodestore_node" failed: PQgetResult() failed.
pg_dump: detail: Error message from server: ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
pg_dump: detail: Command was: COPY public.nodestore_node (id, data, "timestamp") TO stdout;
```



# 问题解释
如果您具有副本/从属配置并且使用该副本/从属进行备份，那么如果查询花费太长时间才能读取，则返回的结果可能不再存在或在主节点上已更改。

 

# 建议的解决方案
在副本节点上编辑postgresql.conf并设置以下两个值：

```js
max_standby_archive_delay = 900s 
max_standby_streaming_delay = 900s
```
 

这样，持续时间少于 900 秒的从属/副本上的查询就不会被快速取消，从而为其提供更多时间来完成。


:::info{title="相关信息"}
max_standby_streaming_delay：设置热备服务器处理流式 WAL数据
max_standby_archive_delay：设置热备服务器处理归档 WAL 数据时取消查询之前的最大延迟
:::

pg_dump导出数据库报错处理

# shop小店添加配置ssl证书


![image.png](/static/img/d374da60bcc6c95ddad087d76101b42a.image.webp)




```js

server {
    listen 80;
    server_name shop.iftballs.com;
    if ($host ~ "shop.iftballs.com$") {
    rewrite ^/(.)$ https://shop.iftballs.com/ permanent;
    }
}

server
{
    listen 127.0.0.1:8452 ssl http2;
    server_name shop.iftballs.com;
    ssl_certificate '/www/server/panel/vhost/cert/shop.iftballs.com/fullchain.pem';
    ssl_certificate_key '/www/server/panel/vhost/cert/shop.iftballs.com/privkey.pem';
    ssl_protocols TLSv1.1 TLSv1.2 TLSv1.3;
    ssl_prefer_server_ciphers   on;
    ssl_session_timeout '64m';
    index index.php index.html index.htm default.php default.htm default.html;
    root /www/wwwroot/shop.iftballs.com;
    
    #SSL-START SSL相关配置，请勿删除或修改下一行带注释的404规则
    #error_page 404/404.html;
    #HTTP_TO_HTTPS_START
    if ($server_port !~ 8452){
        rewrite ^(/.*)$ https://$host$1 permanent;
    }
    #HTTP_TO_HTTPS_END
    
    
   # 强制清除缓存(新增配置项)
    proxy_cache_purge on;
    
    #PHP-INFO-START  PHP引用配置，可以注释或修改
    include enable-php-74.conf;
    #PHP-INFO-END
    
    location ^~ / {
        proxy_pass http://127.0.0.1:56789;
        proxy_set_header Host $host;
        proxy_set_header X-Real-IP $remote_addr;
        proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
        proxy_set_header REMOTE-HOST $remote_addr;
        proxy_set_header X-Forwarded-Proto  $scheme;

        add_header X-Cache $upstream_cache_status;

        proxy_set_header Accept-Encoding "";
        sub_filter "http://" "https://";
        sub_filter_once off;
    }
    

#禁止访问的文件或目录
location ~ ^/(.user.ini|.htaccess|.git|.svn|.project|LICENSE|README.md)
{
    return 404;
}
access_log off;

}
```

shop小店添加配置ssl证书

![image.png](/static/img/2f7f3610fe17b7dc5d672cb781e7b4ef.image.webp)

通过跳转堡垒机关闭数据库报错：[TERM] You are not allowed to use this command: shutdown immediate;

# oracle数据库添加只读账号并授权

![image.png](/static/img/abe5336f800c5444c677f50c914edc05.image.webp)
## 第一步：创建只读账号

```sql
create user app_zijin_reader 
identified by 123456 --初始口令
default tablespace ZIJIN; --初始口令过期，会提示用户重置口令
```



## 第二步：赋予账号连接数据库等基本权限

```sql
--赋予app_zijin_reader连接等常规权限
grant connect to app_zijin_reader;
grant create view to app_zijin_reader;
grant create session to app_zijin_reader;
grant create synonym to app_zijin_reader;
```
## 第三步：获取原账号的查询权限

```sql
-- 获取原账号app_zijin用户的所有查询表权限
select 'grant select on '||owner||'.'||object_name||' to app_zijin_reader;'
from dba_objects
where owner in ('APP_ZIJIN')
and object_type='TABLE';
```
## 第四步：将原账号权限赋值为新账号


```js

$ sqlplus / as sysdba > ./app_zijin_reader.sql <<sqleof
select 'grant select on '||owner||'.'||object_name||' to app_zijin_reader;' from dba_objects where owner in ('APP_ZIJIN')  and object_type='TABLE';
sqleof

grant select on app_zijin.ENTRY_CERT to app_zijin_reader;
grant select on app_zijin.SUB_MESSAGE_INFO to app_zijin_reader;
grant select on app_zijin.ENTRY_CERT_RELATION to app_zijin_reader;
grant select on app_zijin.ENTRY_CERT_RELATION to app_zijin_reader;
grant select on app_zijin.ENTRY_DECL_TAX to app_zijin_reader;
grant select on app_zijin.ENTRY_DOCU to app_zijin_reader;
grant select on app_zijin.ENTRY_FEES to app_zijin_reader;
grant select on app_zijin.ENTRY_GOODS_TAX to app_zijin_reader;
grant select on app_zijin.ENTRY_HEAD to app_zijin_reader;
grant select on app_zijin.ENTRY_LIST to app_zijin_reader;
grant select on app_zijin.ENTRY_WORKFLOW to app_zijin_reader;
grant select on app_zijin.IQ_APPEND to app_zijin_reader;
grant select on app_zijin.IQ_CERT to app_zijin_reader;
grant select on app_zijin.SUB_SWAP to app_zijin_reader;
grant select on app_zijin.VIN_LIST to app_zijin_reader;

```

## 第五步：在新账号端创建同位显示表

```js
$ sqlplus / as sysdba > ./app_zijin_reader_synonym.sql <<sqleof
select 'create or replace SYNONYM app_zijin_reader.'||object_name||' for ' ||owner||'.'||object_name||';' from dba_objects where owner in ('APP_ZIJIN')  and object_type='TABLE';
sqleof
```
## 第六步：查询结果在新账号端执行

```js
在只读账号app_zijin_reader端执行：添加显示各个表信息；在SYSNONYM目录下，tables目录下无显示
create or replace SYNONYM app_zijin_reader.VIN_LIST for app_zijin.VIN_LIST;
create or replace SYNONYM app_zijin_reader.SUB_SWAP for app_zijin.SUB_SWAP;
create or replace SYNONYM app_zijin_reader.SUB_MESSAGE_INFO for app_zijin.SUB_MESSAGE_INFO;
create or replace SYNONYM app_zijin_reader.IQ_CERT for app_zijin.IQ_CERT;
create or replace SYNONYM app_zijin_reader.IQ_APPEND for app_zijin.IQ_APPEND;
create or replace SYNONYM app_zijin_reader.ENTRY_WORKFLOW for app_zijin.ENTRY_WORKFLOW;
create or replace SYNONYM app_zijin_reader.ENTRY_LIST for app_zijin.ENTRY_LIST;
create or replace SYNONYM app_zijin_reader.ENTRY_HEAD for app_zijin.ENTRY_HEAD;
create or replace SYNONYM app_zijin_reader.ENTRY_GOODS_TAX for app_zijin.ENTRY_GOODS_TAX;
create or replace SYNONYM app_zijin_reader.ENTRY_FEES for app_zijin.ENTRY_FEES;
create or replace SYNONYM app_zijin_reader.ENTRY_DOCU for app_zijin.ENTRY_DOCU;
create or replace SYNONYM app_zijin_reader.ENTRY_DECL_TAX for app_zijin.ENTRY_DECL_TAX;
create or replace SYNONYM app_zijin_reader.ENTRY_CONTAINER for app_zijin.ENTRY_CONTAINER;
create or replace SYNONYM app_zijin_reader.ENTRY_CERT_RELATION for app_zijin.ENTRY_CERT_RELATION;
create or replace SYNONYM app_zijin_reader.ENTRY_CERT for app_zijin.ENTRY_CERT;
```
## 第七步：执行完成之后 登录新账号，查看结果
新账号可以查询原账号的所有表结构，但是无法执行 增删改相关操作

## 第八步：执行删除、修改sql语句测试
新账号进行删除操作时，会提示报错，无权删除

## 附录：Oracle查询账号及权限详细语句


```js
附录：Oracle查询账号及权限详细语句
1.查看所有用户：
select * from dba_users;
select * from all_users;
select * from user_users;
 
2.查看用户或角色系统权限(直接赋值给用户或角色的系统权限)：
select * from dba_sys_privs;
select * from user_sys_privs;
 
3.查看角色(只能查看登陆用户拥有的角色)所包含的权限
sql>select * from role_sys_privs;
 
4.查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;
 
5.查看所有角色：
select * from dba_roles;
 
6.查看用户或角色所拥有的角色：
select * from dba_role_privs;
select * from user_role_privs;
```

oracle数据库添加只读账号并授权

>背景：

| 环境及系统 | 集群及版本 | 配置 |
| --- | --- | --- |
| 数据库环境 | 3节点MGR集群 |primary节点
| 操作系统 | CentOS Linux release 7.9.2009 (Core) |8核16G



>目前主库数据库使用内存达到88%左右，其他的2从库内存使用率40%左右，通过观察主库内存使用率每天都有小幅度上升趋势。但是，innodb_buffer_pool_size才设置8G, 怀疑mysql客户端连接的线程断开后，内存不能正常释放.



# 最初处理思路：

把线程相关的内存参数调整小一些


```mysql
sort_buffer_size
read_buffer_size
read_rnd_buffer_size
join_buffer_size
binlog_cache_size
tmp_table_size
```

重启mysql实例后，释放内存，运行一段时间，问题依然存在，没有得到解决。

# 通过脚本查看内存

编写查看内存脚本如下：

```bash
[root@wtj1vpdbmgnt01 shell]# cat check_mem_alloc.sh
#!/bin/bash

# ----------------------------------------------------------------
#       Copyright © 2024 Openserv Ltd. All rights reserved.
#       FileName   : check_mem_alloc.sh
#       Author     : dba
#       Mail       : dba@openserv.com.cn
#       Version    : v1.0.0
#       CreateTime : 2024-06-06 10:55:19
#       COPYRIGHT  : Copyright ©2024 - 开源服务
#       Description: Welcome Use The Script.
# ----------------------------------------------------------------

# 修改为自已数据库实例的账号和密码等信息 
/acdata/data/mysqlsoft3306/bin/mysql -h 10.3.8.112 -u root -p'xxxxxx' -P 3306 -e "show variables; show status" | awk '  
{
VAR[$1]=$2  
}
END {  
MAX_CONN = VAR["max_connections"]  
MAX_USED_CONN = VAR["Max_used_connections"]  
BASE_MEM=VAR["key_buffer_size"] + VAR["query_cache_size"] + VAR["innodb_buffer_pool_size"] + VAR["innodb_additional_mem_pool_size"] + VAR["innodb_log_buffer_size"]  
MEM_PER_CONN=VAR["read_buffer_size"] + VAR["read_rnd_buffer_size"] + VAR["sort_buffer_size"] + VAR["join_buffer_size"] + VAR["binlog_cache_size"] + VAR["thread_stack"] + VAR["tmp_table_size"] + VAR["net_buffer_length"] 
MEM_TOTAL_MIN=BASE_MEM + MEM_PER_CONN*MAX_USED_CONN  
MEM_TOTAL_MAX=BASE_MEM + MEM_PER_CONN*MAX_CONN
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "key_buffer_size", VAR["key_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "query_cache_size", VAR["query_cache_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "innodb_buffer_pool_size", VAR["innodb_buffer_pool_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "innodb_additional_mem_pool_size", VAR["innodb_additional_mem_pool_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "innodb_log_buffer_size", VAR["innodb_log_buffer_size"]/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "BASE MEMORY", BASE_MEM/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "sort_buffer_size", VAR["sort_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "read_buffer_size", VAR["read_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "read_rnd_buffer_size", VAR["read_rnd_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "join_buffer_size", VAR["join_buffer_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "thread_stack", VAR["thread_stack"]/1048576  
printf "| %40s | %15.3f MB |\n", "binlog_cache_size", VAR["binlog_cache_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "tmp_table_size", VAR["tmp_table_size"]/1048576  
printf "| %40s | %15.3f MB |\n", "net_buffer_length", VAR["net_buffer_length"]/1048576
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "MEMORY PER CONNECTION", MEM_PER_CONN/1048576  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %18d |\n", "Max_used_connections", MAX_USED_CONN  
printf "| %40s | %18d |\n", "max_connections", MAX_CONN  
printf "+------------------------------------------+--------------------+\n"  
printf "| %40s | %15.3f MB |\n", "TOTAL (MIN)", MEM_TOTAL_MIN/1048576  
printf "| %40s | %15.3f MB |\n", "TOTAL (MAX)", MEM_TOTAL_MAX/1048576  
printf "+------------------------------------------+--------------------+\n"  
}'

```


# 运行脚本，查看分配的内存大小 

![](https://vlogs.iftballs.com/static/img/58af2c6f4e83cfef08510640ceb7bd9b.image.webp)

按照脚本计算实例当前会话的最大的连接数为80，最大内存使用应该是8G，而现在mysqld占用的内存达到10G.



# 通过Performance Schema and sys schema to monitor MySQL memory usage进行计算


```mysql

SELECT SUBSTRING_INDEX(event_name,'/',2) AS
          code_area, FORMAT_BYTES(SUM(current_alloc))
          AS current_alloc
          FROM sys.x$memory_global_by_current_bytes
          GROUP BY SUBSTRING_INDEX(event_name,'/',2)
          ORDER BY SUM(current_alloc) DESC;

mysql> SELECT SUBSTRING_INDEX(event_name,'/',2) AS
    ->           code_area, FORMAT_BYTES(SUM(current_alloc))
    ->           AS current_alloc
    ->           FROM sys.x$memory_global_by_current_bytes
    ->           GROUP BY SUBSTRING_INDEX(event_name,'/',2)
    ->           ORDER BY SUM(current_alloc) DESC;
+---------------------------+---------------+
| code_area                 | current_alloc |
+---------------------------+---------------+
| memory/innodb             | 6.45 GiB      |
| memory/group_rpl          | 1024.00 MiB   |
| memory/performance_schema | 278.82 MiB    |
| memory/temptable          | 71.00 MiB     |
| memory/sql                | 63.23 MiB     |
| memory/mysys              | 12.23 MiB     |
| memory/mysqld_openssl     | 2.87 MiB      |
| memory/mysqlx             | 3.44 KiB      |
| memory/myisam             |  696 bytes    |
| memory/csv                |   88 bytes    |
| memory/blackhole          |   88 bytes    |
| memory/vio                |   16 bytes    |
+---------------------------+---------------+
12 rows in set (0.01 sec)
```

:::warning{title="注意"}
有些怀疑是不是有内存泄漏的问题，去官方也没有找到此版本内存泄漏的相关问题
:::


最后，使用大绝招，逼mysql就范。



# glibc的内存管理器自身缺陷导致

  简言之，就是调用glibc申请的内存使用完毕后，归还给OS时没有被正常回收，而变成了碎片，随着碎片的不断增长，就能看到mysqld进程占用的内存不断上升。这时，可以调用函数主动回收释放这些碎片。



```bash

# gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'
 PID USER      PR  NI    VIRT    RES    SHR  S  %CPU %MEM     TIME+ COMMAND
45305 mysql     20   0   28.4g    5.2g   8288 S  2.7  17.0  64:56.82 mysqld
```


这就像是在InnoDB表中产生太多碎片后，主动执行OPTIMIZE TABLE重建表的做法。


```bash
[root@wyvpjhsql01 ~]# gdb --batch --pid `pidof mysqld` --ex 'call malloc_trim(0)'
[New LWP 12846]
[New LWP 32443]
[New LWP 15459]
[New LWP 32563]
[New LWP 14120]
[New LWP 24868]
[New LWP 2599]
[New LWP 1819]
[New LWP 32141]
[New LWP 20832]
[New LWP 20730]
[New LWP 20581]
[New LWP 30140]
[New LWP 29796]
[New LWP 29630]
[New LWP 17314]
[New LWP 16350]
[New LWP 14404]
[New LWP 14402]
[New LWP 2256]
[New LWP 2254]
[New LWP 1712]
[New LWP 1710]
[New LWP 1631]
[New LWP 1599]
[New LWP 1597]
[New LWP 1596]
[New LWP 1595]
[New LWP 1110]
[New LWP 12170]
[New LWP 31006]
[New LWP 12831]
[New LWP 12766]
[New LWP 12196]
[New LWP 429]
[New LWP 31775]
[New LWP 30259]
[New LWP 28717]
[New LWP 28519]
[New LWP 28353]
[New LWP 28352]
[New LWP 28257]
[New LWP 27899]
[New LWP 27662]
[New LWP 27575]
[New LWP 27545]
[New LWP 27514]
[New LWP 27498]
[New LWP 27492]
[New LWP 27491]
[New LWP 27490]
[New LWP 27461]
[New LWP 27452]
[New LWP 27451]
[New LWP 27449]
[New LWP 27438]
[New LWP 27437]
[New LWP 27435]
[New LWP 27434]
[New LWP 27433]
[New LWP 27430]
[New LWP 27429]
[New LWP 27428]
[New LWP 27424]
[New LWP 27422]
[New LWP 27421]
[New LWP 27420]
[New LWP 27419]
[New LWP 27417]
[New LWP 27409]
[New LWP 27408]
[New LWP 18138]
[New LWP 18136]
[New LWP 18135]
[New LWP 18134]
[New LWP 18133]
[New LWP 18132]
[New LWP 18131]
[New LWP 11498]
[New LWP 11444]
[New LWP 11441]
[New LWP 11440]
[New LWP 11439]
[New LWP 11435]
[New LWP 11434]
[New LWP 11433]
[New LWP 11432]
[New LWP 11429]
[New LWP 11428]
[New LWP 11427]
[New LWP 11426]
[New LWP 11425]
[New LWP 11424]
[New LWP 11423]
[New LWP 11422]
[New LWP 11421]
[New LWP 11420]
[New LWP 11419]
[New LWP 11418]
[New LWP 11417]
[New LWP 11416]
[New LWP 11415]
[New LWP 11414]
[New LWP 11413]
[New LWP 11412]
[New LWP 11411]
[New LWP 11410]
[New LWP 11409]
[New LWP 11408]
[New LWP 11407]
[New LWP 11406]
[New LWP 11405]
[New LWP 11404]
[New LWP 11403]
[New LWP 11402]
[New LWP 11401]
[New LWP 11400]
[New LWP 11399]
[New LWP 11398]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib64/libthread_db.so.1".
0x00007fc1a23bdddd in poll () from /lib64/libc.so.6
$1 = 1
[Inferior 1 (process 11385) detached]
```

执行后发现内存使用率明显降下来了

```js
[root@wyvpjhsql01 ~]# free -m
              total        used        free      shared  buff/cache   available
Mem:          15884       11831         719          63        3334        3659
Swap:          8187         585        7602
```
![](https://vlogs.iftballs.com/static/img/18c6eb74ddde1d11c2edd6d5098470ae.image.webp)

# 小结：
 至此问题告一段落，后续继续进行跟踪观察。