备案控制台
开发者社区 数据库 文章 正文

vacuum full搞瘫PostgreSQL主从复制

2016-06-18 1701
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云原生数据库 PolarDB MySQL 版，Serverless 5000PCU 100GB
推荐场景：
PolarDB for MySQL 多主集群体验
简介： 现象 1主2从同步复制环境下，在主库上执行了一个vacuum full，结果发现一直无法结束。 [root@db01 data]# ps -ef|grep postgres postgres 26268 1 0 Jun17 ? 00:00:12 /usr/pgsql-9.

现象

1主2从同步复制环境下，在主库上执行了一个vacuum full，结果发现一直无法结束。

  1. [root@db01 data]# ps -ef|grep postgres
  2. postgres 26268 1 0 Jun17 ? 00:00:12 /usr/pgsql-9.5/bin/postgres -D /data/pgsql/sysbenchdb
  3. postgres 26270 26268 0 Jun17 ? 00:00:00 postgres: logger process
  4. postgres 26272 26268 0 Jun17 ? 00:00:03 postgres: checkpointer process
  5. postgres 26273 26268 0 Jun17 ? 00:00:00 postgres: writer process
  6. postgres 26274 26268 0 Jun17 ? 00:00:33 postgres: wal writer process
  7. postgres 26275 26268 0 Jun17 ? 00:00:01 postgres: autovacuum launcher process
  8. postgres 26276 26268 0 Jun17 ? 00:00:02 postgres: stats collector process
  9. postgres 26316 26315 0 Jun17 pts/3 00:00:00 -bash
  10. postgres 26343 26316 0 Jun17 pts/3 00:00:00 psql
  11. postgres 26345 26268 0 Jun17 ? 00:07:10 postgres: postgres sysbench_1_10000w [local] VACUUM waiting for C4/B3CA33E0
  12. root 31697 29068 0 20:48 pts/0 00:00:00 grep postgres
  13. [root@db01 data]# psql -Upostgres
  14. psql (9.5.2)
  15. Type "help" for help.
  17. postgres=# select * from pg_stat_activity;
  18. datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | xact_start |
  19. query_start | state_change | waiting | state | backend_xid | backend_xmin | query
  20. -------+-------------------+-------+----------+----------+------------------+-------------+-----------------+-------------+-------------------------------+-------------------------------+----
  21. ---------------------------+-------------------------------+---------+--------+-------------+--------------+---------------------------------
  22. 13241 | postgres | 31762 | 10 | postgres | psql | | | -1 | 2016-06-18 20:50:15.583562+08 | 2016-06-18 20:50:17.407332+08 | 201
  23. 6-06-18 20:50:17.407332+08 | 2016-06-18 20:50:17.407337+08 | f | active | | 7676361 | select * from pg_stat_activity;
  24. 16386 | sysbench_1_10000w | 26345 | 10 | postgres | psql | | | -1 | 2016-06-17 23:50:47.009872+08 | 2016-06-17 23:51:07.835389+08 | 201
  25. 6-06-17 23:51:07.835389+08 | 2016-06-17 23:51:07.835392+08 | f | active | 7676361 | | vacuum full;
  26. (2 rows)

原因

查看复制状态，原来2个Slave的复制都已经断掉了，由于是同步复制，Master一直在等待Slave的应答。

  1. postgres=# select * from pg_stat_replication ;
  2. pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start | backend_xmin | state | sent_location | write_location | flush_location | replay_lo
  3. cation | sync_priority | sync_state
  4. -----+----------+---------+------------------+-------------+-----------------+-------------+---------------+--------------+-------+---------------+----------------+----------------+----------
  5. -------+---------------+------------
  6. (0 rows)

为什么Slave的复制会断掉？

分别查看Master和Slave的日志，可以发现vacuum full产生了大量的xlog和频繁的checkpoint。
最终导致还没有来的及传输到Slave上的wal日志被Master删掉了，于是Slave上的复制停止。
这是一个200多GB的库，vacuum full太有杀伤力了。

相关参数如下：
max_wal_size = 8GB
min_wal_size = 2GB
wal_keep_segments=32
full_page_writes = on

主库日志

  1. 2016-06-17 23:50:58.172 CST 26345 0STATEMENT: varcuum full;
  2. 2016-06-17 23:51:41.896 CST 26272 0LOG: checkpoint starting: xlog
  3. 2016-06-17 23:51:47.212 CST 26272 0LOG: checkpoint complete: wrote 34 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=3.426 s, sync=0.490 s, total=5.318 s; sync files=18, longest=0.415 s, average=0.027 s; distance=3342566 kB, estimate=3342566 kB
  4. 2016-06-17 23:52:21.904 CST 26272 0LOG: checkpoint starting: xlog
  5. 2016-06-17 23:52:23.206 CST 26272 0LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 205 recycled; write=0.009 s, sync=0.693 s, total=1.305 s; sync files=4, longest=0.446 s, average=0.173 s; distance=3358625 kB, estimate=3358625 kB
  6. 2016-06-17 23:52:57.883 CST 26272 0LOG: checkpoint starting: xlog
  7. 2016-06-17 23:52:59.802 CST 26272 0LOG: checkpoint complete: wrote 1 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 205 recycled; write=0.109 s, sync=0.268 s, total=1.918 s; sync files=4, longest=0.234 s, average=0.067 s; distance=3360452 kB, estimate=3360452 kB
  8. 2016-06-17 23:53:38.728 CST 26272 0LOG: checkpoint starting: xlog
  9. 2016-06-17 23:53:40.206 CST 26272 0LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 205 recycled; write=0.010 s, sync=0.663 s, total=1.477 s; sync files=4, longest=0.662 s, average=0.165 s; distance=3358649 kB, estimate=3360272 kB
  10. 2016-06-17 23:54:17.751 CST 26272 0LOG: checkpoint starting: xlog
  11. 2016-06-17 23:54:18.945 CST 26272 0LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 205 recycled; write=0.011 s, sync=0.669 s, total=1.193 s; sync files=4, longest=0.668 s, average=0.167 s; distance=3342837 kB, estimate=3358528 kB
  12. 2016-06-17 23:54:57.165 CST 26272 0LOG: checkpoint starting: xlog
  13. 2016-06-17 23:54:58.487 CST 26272 0LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 204 recycled; write=0.011 s, sync=0.380 s, total=1.392 s; sync files=5, longest=0.324 s, average=0.076 s; distance=3356734 kB, estimate=3358349 kB
  14. 2016-06-17 23:54:58.496 CST 26272 0LOG: checkpoints are occurring too frequently (1 second apart)
  15. 2016-06-17 23:54:58.496 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  16. 2016-06-17 23:54:58.496 CST 26272 0LOG: checkpoint starting: xlog
  17. 2016-06-17 23:54:58.874 CST 26272 0LOG: checkpoint complete: wrote 0 buffers (0.0%); 0 transaction log file(s) added, 0 removed, 176 recycled; write=0.010 s, sync=0.008 s, total=0.377 s; sync files=1, longest=0.008 s, average=0.008 s; distance=56337 kB, estimate=3028147 kB
  18. 2016-06-18 00:02:00.536 CST 26272 0LOG: checkpoint starting: xlog
  19. 2016-06-18 00:02:32.243 CST 26272 0LOG: checkpoint starting: xlog
  20. 2016-06-18 00:03:03.343 CST 26272 0LOG: checkpoint starting: xlog
  21. 2016-06-18 00:03:32.654 CST 26272 0LOG: checkpoints are occurring too frequently (29 seconds apart)
  22. 2016-06-18 00:03:32.654 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  23. 2016-06-18 00:03:32.654 CST 26272 0LOG: checkpoint starting: xlog
  24. 2016-06-18 00:04:00.859 CST 26272 0LOG: checkpoints are occurring too frequently (28 seconds apart)
  25. 2016-06-18 00:04:00.859 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  26. 2016-06-18 00:04:00.859 CST 26272 0LOG: checkpoint starting: xlog
  27. 2016-06-18 00:04:29.940 CST 26272 0LOG: checkpoints are occurring too frequently (29 seconds apart)
  28. 2016-06-18 00:04:29.941 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  29. 2016-06-18 00:04:29.943 CST 26272 0LOG: checkpoint starting: xlog
  30. 2016-06-18 00:02:00.536 CST 26272 0LOG: checkpoint starting: xlog
  31. 2016-06-18 00:02:32.243 CST 26272 0LOG: checkpoint starting: xlog
  32. 2016-06-18 00:03:03.343 CST 26272 0LOG: checkpoint starting: xlog
  33. 2016-06-18 00:03:32.654 CST 26272 0LOG: checkpoints are occurring too frequently (29 seconds apart)
  34. 2016-06-18 00:03:32.654 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  35. 2016-06-18 00:03:32.654 CST 26272 0LOG: checkpoint starting: xlog
  36. 2016-06-18 00:04:00.859 CST 26272 0LOG: checkpoints are occurring too frequently (28 seconds apart)
  37. 2016-06-18 00:04:00.859 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  38. 2016-06-18 00:04:00.859 CST 26272 0LOG: checkpoint starting: xlog
  39. 2016-06-18 00:04:29.940 CST 26272 0LOG: checkpoints are occurring too frequently (29 seconds apart)
  40. 2016-06-18 00:04:29.941 CST 26272 0HINT: Consider increasing the configuration parameter "max_wal_size".
  41. 2016-06-18 00:04:29.943 CST 26272 0LOG: checkpoint starting: xlog
  42. 2016-06-18 00:04:59.536 CST 26272 0LOG: checkpoint starting: xlog
  43. 2016-06-18 00:04:59.652 CST 26283 0ERROR: requested WAL segment 00000001000000C40000008C has already been removed
  44. 2016-06-18 00:04:59.666 CST 26281 0ERROR: requested WAL segment 00000001000000C4000000A7 has already been removed
  45. 2016-06-18 00:05:14.939 CST 26608 0ERROR: requested WAL segment 00000001000000C4000000A7 has already been removed
  46. 2016-06-18 00:05:15.003 CST 26609 0ERROR: requested WAL segment 00000001000000C40000008C has already been removed
  47. 2016-06-18 00:05:19.779 CST 26611 0ERROR: requested WAL segment 00000001000000C40000008C has already been removed

其中1个从库的日志

  1. 2016-06-17 23:48:48.283 CST 17272 0LOG: database system was shut down in recovery at 2016-06-17 14:20:29 CST
  2. 2016-06-17 23:48:48.298 CST 17272 0LOG: entering standby mode
  3. 2016-06-17 23:48:48.343 CST 17272 0LOG: consistent recovery state reached at BA/53FEACF8
  4. 2016-06-17 23:48:48.343 CST 17272 0LOG: invalid resource manager ID 52 at BA/53FEACF8
  5. 2016-06-17 23:48:48.344 CST 17270 0LOG: database system is ready to accept read only connections
  6. 2016-06-17 23:48:48.373 CST 17276 0LOG: started streaming WAL from primary at BA/53000000 on timeline 1
  7. 2016-06-17 23:48:48.530 CST 17272 0LOG: redo starts at BA/53FEACF8
  8. 2016-06-17 23:51:47.652 CST 17273 0LOG: restartpoint starting: xlog
  9. 2016-06-17 23:52:25.254 CST 17273 0LOG: restartpoint complete: wrote 399635 buffers (76.2%); 0 transaction log file(s) added, 0 removed, 32 recycled; write=36.981 s, sync=0.321 s, total=37.601 s; sync files=21, longest=0.168 s, average=0.014 s; distance=3342566 kB, estimate=3342566 kB
  10. 2016-06-17 23:52:25.287 CST 17273 0LOG: recovery restart point at BB/20024600
  11. 2016-06-17 23:52:25.287 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-17 23:49:38.964451+08
  12. 2016-06-17 23:52:25.288 CST 17273 0LOG: restartpoint starting: xlog
  13. 2016-06-17 23:53:02.126 CST 17273 0LOG: restartpoint complete: wrote 382293 buffers (72.9%); 0 transaction log file(s) added, 91 removed, 114 recycled; write=32.611 s, sync=0.126 s, total=36.837 s; sync files=6, longest=0.095 s, average=0.021 s; distance=3358625 kB, estimate=3358625 kB
  14. 2016-06-17 23:53:02.126 CST 17273 0LOG: recovery restart point at BB/ED00CC58
  15. 2016-06-17 23:53:02.126 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-17 23:49:38.964451+08
  16. 2016-06-17 23:53:02.128 CST 17273 0LOG: restartpoint starting: xlog
  17. 2016-06-17 23:53:43.815 CST 17273 0LOG: restartpoint complete: wrote 424800 buffers (81.0%); 0 transaction log file(s) added, 96 removed, 109 recycled; write=38.289 s, sync=0.455 s, total=41.686 s; sync files=8, longest=0.152 s, average=0.056 s; distance=3360452 kB, estimate=3360452 kB
  18. 2016-06-17 23:53:43.815 CST 17273 0LOG: recovery restart point at BC/BA1BDD58
  19. 2016-06-17 23:53:43.815 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-17 23:49:38.964451+08
  20. 2016-06-17 23:53:43.818 CST 17273 0LOG: restartpoint starting: xlog
  21. 2016-06-17 23:54:21.762 CST 17273 0LOG: restartpoint complete: wrote 418118 buffers (79.7%); 0 transaction log file(s) added, 96 removed, 109 recycled; write=34.681 s, sync=0.115 s, total=37.943 s; sync files=7, longest=0.043 s, average=0.016 s; distance=3358649 kB, estimate=3360272 kB
  22. 2016-06-17 23:54:21.762 CST 17273 0LOG: recovery restart point at BD/871AC458
  23. 2016-06-17 23:54:21.762 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-17 23:49:38.964451+08
  24. 2016-06-17 23:54:21.764 CST 17273 0LOG: restartpoint starting: xlog
  25. 2016-06-17 23:54:59.045 CST 17273 0LOG: restartpoint complete: wrote 420441 buffers (80.2%); 0 transaction log file(s) added, 98 removed, 107 recycled; write=34.745 s, sync=0.320 s, total=37.281 s; sync files=7, longest=0.142 s, average=0.045 s; distance=3342837 kB, estimate=3358528 kB
  26. 2016-06-17 23:54:59.046 CST 17273 0LOG: recovery restart point at BE/532299A0
  27. 2016-06-17 23:54:59.046 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-17 23:49:38.964451+08
  28. 2016-06-17 23:54:59.047 CST 17273 0LOG: restartpoint starting: xlog
  29. 2016-06-17 23:57:19.392 CST 17273 0LOG: restartpoint complete: wrote 356251 buffers (67.9%); 0 transaction log file(s) added, 68 removed, 136 recycled; write=138.817 s, sync=0.242 s, total=140.345 s; sync files=181, longest=0.111 s, average=0.001 s; distance=3413071 kB, estimate=3413071 kB
  30. 2016-06-17 23:57:19.393 CST 17273 0LOG: recovery restart point at BF/2373D968
  31. 2016-06-17 23:57:19.393 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-17 23:56:17.751189+08
  32. 2016-06-17 23:57:51.516 CST 17273 0LOG: restartpoint starting: time
  33. 2016-06-18 00:02:07.732 CST 17273 0LOG: restartpoint complete: wrote 484068 buffers (92.3%); 0 transaction log file(s) added, 98 removed, 110 recycled; write=252.381 s, sync=0.163 s, total=256.216 s; sync files=198, longest=0.111 s, average=0.000 s; distance=3337246 kB, estimate=3405489 kB
  34. 2016-06-18 00:02:07.732 CST 17273 0LOG: recovery restart point at BF/EF245338
  35. 2016-06-18 00:02:07.732 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-18 00:02:02.385133+08
  36. 2016-06-18 00:02:08.632 CST 17273 0LOG: restartpoint starting: xlog
  37. 2016-06-18 00:02:46.845 CST 17273 0LOG: restartpoint complete: wrote 389871 buffers (74.4%); 0 transaction log file(s) added, 103 removed, 101 recycled; write=33.994 s, sync=0.238 s, total=38.212 s; sync files=58, longest=0.123 s, average=0.004 s; distance=3352599 kB, estimate=3400200 kB
  38. 2016-06-18 00:02:46.845 CST 17273 0LOG: recovery restart point at C0/BBC4B058
  39. 2016-06-18 00:02:46.845 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-18 00:02:36.125659+08
  40. 2016-06-18 00:02:50.026 CST 17273 0LOG: restartpoint starting: xlog
  41. 2016-06-18 00:03:28.288 CST 17273 0LOG: restartpoint complete: wrote 468192 buffers (89.3%); 0 transaction log file(s) added, 130 removed, 74 recycled; write=33.569 s, sync=0.270 s, total=38.262 s; sync files=67, longest=0.061 s, average=0.004 s; distance=3331508 kB, estimate=3393331 kB
  42. 2016-06-18 00:03:28.288 CST 17273 0LOG: recovery restart point at C1/871B83C0
  43. 2016-06-18 00:03:28.288 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-18 00:03:01.080019+08
  44. 2016-06-18 00:03:28.291 CST 17273 0LOG: restartpoint starting: xlog
  45. 2016-06-18 00:04:07.549 CST 17273 0LOG: restartpoint complete: wrote 437802 buffers (83.5%); 0 transaction log file(s) added, 140 removed, 64 recycled; write=32.791 s, sync=0.181 s, total=39.257 s; sync files=35, longest=0.094 s, average=0.005 s; distance=3342050 kB, estimate=3388203 kB
  46. 2016-06-18 00:04:07.549 CST 17273 0LOG: recovery restart point at C2/53170EF0
  47. 2016-06-18 00:04:07.549 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-18 00:03:42.419304+08
  48. 2016-06-18 00:04:07.553 CST 17273 0LOG: restartpoint starting: xlog
  49. 2016-06-18 00:04:48.479 CST 17273 0LOG: restartpoint complete: wrote 447937 buffers (85.4%); 0 transaction log file(s) added, 157 removed, 47 recycled; write=33.236 s, sync=0.293 s, total=40.925 s; sync files=37, longest=0.160 s, average=0.007 s; distance=3357242 kB, estimate=3385107 kB
  50. 2016-06-18 00:04:48.479 CST 17273 0LOG: recovery restart point at C3/1FFFF8F0
  51. 2016-06-18 00:04:48.479 CST 17273 0DETAIL: last completed transaction was at log time 2016-06-18 00:04:02.282782+08
  52. 2016-06-18 00:04:48.484 CST 17273 0LOG: restartpoint starting: xlog
  53. 2016-06-18 00:04:59.698 CST 17276 0FATAL: could not receive data from WAL stream: ERROR: requested WAL segment 00000001000000C40000008C has already been removed
  54. 2016-06-18 00:04:59.723 CST 17272 0LOG: invalid magic number 0000 in log segment 00000001000000C40000008C, offset 15958016
  55. 2016-06-18 00:05:15.003 CST 17429 0LOG: started streaming WAL from primary at C4/8C000000 on timeline 1

如何回避?
1. 通过wal_keep_segments预留更多的wal。需要留多少一定要算好。
2. 通过slot槽进行复制。如果Slave挂了，Master上日志将一直保留，所以要做好监控，及时处理。
3. 设置full_page_writes=off减少wal的产生量。前提是有其它方式保障数据安全比如备份或底层文件系统的原子写。
4. 做好日志归档并在主从间共享，这样可以让Slave从归档取缺失的日志 。
5. 通过HA软件及时将Master切到异步复制，防止业务阻塞。
6. 千万不要对整个大库使用vacuum full(这一条才是重点，类似的除了 vacuum full还要小心大量更新的大事务)


文章标签：
云原生数据库 PolarDB
日志服务
关系型数据库
PostgreSQL
关键词：
云原生数据库 PolarDB主从复制
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系，提升产品性能，打磨产品功能，从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手，打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系， 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台，为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案，提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
小桥河西
目录
相关文章
蓝易云
|
8月前
|
负载均衡 监控 关系型数据库
百度搜索：蓝易云【PostgreSQL 主从复制方案】
请注意，上述仅为一种主从复制方案的概述，实际实施时可能需要根据特定环境和需求进行调整。建议参考PostgreSQL官方文档和其他可靠资源获取更详细的指南和说明。
蓝易云
92 1
晚风_END
|
1月前
|
缓存 负载均衡 关系型数据库
postgresql|数据库|centos7下基于postgresql-12的主从复制的pgpool-4.4的部署和使用
postgresql|数据库|centos7下基于postgresql-12的主从复制的pgpool-4.4的部署和使用
晚风_END
73 0
晚风_END
|
1月前
|
SQL 安全 关系型数据库
postgresql|数据库|【postgresql-12的基于pg_basebackup的主从复制部署】
postgresql|数据库|【postgresql-12的基于pg_basebackup的主从复制部署】
晚风_END
92 0
happyjared
|
关系型数据库 Java 数据库
Mycat（实践篇 - 基于PostgreSQL的水平切分、主从复制、读写分离）
写在前面   Mycat作为独立的数据库中间件，我们只需要进行相关的配置，就可以非常方便的帮我们实现水平切分、垂直切分、读写分离等功能，但PostgreSQL的主从复制需要我们通过其它方式实现。
happyjared
3292 0
科技小能手
|
关系型数据库 数据库 数据安全/隐私保护
PostgreSql基于Standby的异步流主从复制
科技小能手
2920 0
1442834948473151
|
13天前
|
关系型数据库 分布式数据库 数据库
数据库内核那些事｜PolarDB IMCI让你和复杂低效的子查询说拜拜
PolarDB IMCI（In-Memory Column Index）确实是数据库领域的一项重要技术，特别是当它面对复杂和低效的子查询时，表现尤为出色。以下是关于PolarDB IMCI如何助力解决
1442834948473151
40 2
yuanzhengme
|
6天前
|
关系型数据库 数据库 PostgreSQL
PostgreSQL数据库的字符串拼接语法使用说明
【6月更文挑战第11天】PostgreSQL数据库的字符串拼接语法使用说明
yuanzhengme
18 1
游客iyfakqyinfxv2
|
7天前
|
SQL 关系型数据库 数据库
Python查询PostgreSQL数据库
木头左教你如何用Python连接PostgreSQL数据库：安装`psycopg2`库，建立连接，执行SQL脚本如创建表、插入数据，同时掌握错误处理和事务管理。别忘了性能优化，利用索引、批量操作提升效率。下期更精彩！💡 csvfile
游客iyfakqyinfxv2
14 0
Python查询PostgreSQL数据库
游客iyfakqyinfxv2
|
8天前
|
SQL 关系型数据库 数据库
Python执行PostgreSQL数据库查询语句，并打印查询结果
本文介绍了如何使用Python连接和查询PostgreSQL数据库。首先，确保安装了`psycopg2`库，然后创建数据库连接函数。接着，展示如何编写SQL查询并执行，例如从`employees`表中选取所有记录。此外，还讨论了处理查询结果、格式化输出和异常处理的方法。最后，提到了参数化查询和事务处理以增强安全性及确保数据一致性。
游客iyfakqyinfxv2
34 4
Python执行PostgreSQL数据库查询语句，并打印查询结果
真的很搞笑
|
10天前
|
关系型数据库 数据库连接 分布式数据库
PolarDB操作报错合集之数据库访问量低时，可以正常连接数据库，访问量高了所有用户都连接不了数据库，为什么
PolarDB是阿里云推出的一种云原生数据库服务，专为云设计，提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案，可以有效地管理和优化PolarDB实例，确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
真的很搞笑
39 0

热门文章

最新文章

  • 1
    数据库内核那些事，PolarDB HTAP Serverless，打造经济易用的实时分析系统
  • 2
    流计算风云再起 - PostgreSQL携PipelineDB力挺IoT(物联网), 大幅提升性能和开发效率
  • 3
    PostgreSQL不同模式(SCHEMA)之间迁移数据
  • 4
    分布式1024节点！1天玩转PolarDB-X超大规模集群
  • 5
    2011年功力的德哥教你两天撸通PostgreSQL - 入门、开发、原理、管理、调优
  • 6
    Polar DB-O （兼容 Oracle 语法版本）和Polar DB PostgreSQL 版本概述（二）
  • 7
    PostgreSQL的pg_stats学习
  • 8
    PostgreSQL 10.1 手册_部分 II. SQL 语言_第 8 章 数据类型_8.16. 复合类型
  • 9
    PostgreSQL列存扩展hydra简单测试
  • 10
    PostgreSQL pg_rewind，时间线修复，脑裂修复，flashback - 从库开启读写后，回退为只读从库。异步主从发生角色切换后，主库rewind为新主库的从库
  • 1
    DataWorks产品使用合集之在使用 DataWorks 数据集成同步 PostgreSQL 数据库中的 Geometry 类型数据如何解决
    34
  • 2
    【MySQL】：探秘主流关系型数据库管理系统及SQL语言
    70
  • 3
    MaxCompute产品使用合集之怎么才可以将 PostgreSQL 中的 geometry 空间类型字段同步到 MaxCompute 或另一个 PostgreSQL 数据库
    59
  • 4
    PolarDB产品使用合集之PolarDB-X启动容器时出现32886连接失败的问题，如何解决
    35
  • 5
    PolarDB产品使用合集之PolarDB 2.3.0 版本的 CDC 功能支持 Polardb-X 到 Polardb-X 的数据同步吗
    179
  • 6
    PolarDB产品使用合集之PolarDB MySQL标准版中带有分区功能吗
    33
  • 7
    PolarDB产品使用合集之PolarDB-X sqlyog 无法执行show语句是什么原因
    33
  • 8
    PolarDB产品使用合集之关于在Docker环境中部署和维护PolarDB-X，有相关文章可以参考吗
    53
  • 9
    关系型数据库使用连接（JOIN）代替子查询
    45
  • 10
    PostgreSQL从入门到精通教程 - 第50讲：PG分区表管理
    52

    • 相关课程

    更多
  • 7天突破PolarDB for PostgreSQL 2022版
  • 关系型数据库ACP认证课程
  • 关系型数据库线上预习视频
  • PolarDB for PostgreSQL 入门
  • PostgreSQL云上开发实践
  • PostgreSQL实战进阶

    • 相关电子书

    更多
  • 云栖大会：开源 PolarDB 架构演进、关键技术与社区建设
  • 2023云栖大会：和客户一起玩转PolarDB新特性
  • 2023云栖大会：PolarDB for AI

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • 阿里云百炼xAnalyticDB PostgreSQL构建AIGC应用
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 使用CloudLens采集PolarDB日志并进行审计分析
  • PolarDB for PostgreSQL 闪回特性体验
  • 快速体验PolarDB开源数据库

    • 推荐镜像

    更多
  • postgresql
  • clickhouse
  • mongodb
    • 下一篇
    2024年阿里云免费云服务器及学生云服务器申请教程参考