首页 > 数据库 >repmgr搭建一主+一从+一witness的PostgreSQL高可用集群

repmgr搭建一主+一从+一witness的PostgreSQL高可用集群

时间:2024-11-07 19:59:32浏览次数:1  
标签:pgsql16 PostgreSQL -- 一从 192.168 usr 一主 repmgr local

测试环境


环境如下:
OS:ubuntu 20
PostgreSQL:PostgreSQL 16.4
repmgr:repmgr 5.3.3

三台测试环境,均已安装好PostgreSQL 16.4,数据库实例信息如下

IP          postgresql实例名   身份
192.168.152.100:postgresql9000:  主节点
192.168.152.101:postgresql9000:  从节点
192.168.152.102:postgresql9000:  witness节点

 

主节点上的操作

###1 repmgr源码编译安装

drwxr-xr-x  4 root root     4096 Nov  5 12:45 ./
drwxr-xr-x 14 root root     4096 Sep 29 16:10 ../
-rw-r--r--  1 root root     2784 Nov  6 11:28 pg_install.sh
drwxrwxrwx  6 1107 1107     4096 Nov  6 11:31 postgresql-16.4/
-rw-r--r--  1 root root 32660355 Aug  6 04:19 postgresql-16.4.tar.gz
drwxrwxr-x  6 root root     4096 Nov  7 09:20 repmgr-5.3.3/
-rw-r--r--  1 root root   456931 Oct 21 17:00 repmgr-5.3.3.tar.gz
root@ubuntu01:/usr/local/pg16#
root@ubuntu01:/usr/local/pg16# tar -xzvf repmgr-5.3.3.tar.gz
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3# cd repmgr-5.3.3/
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3# whereis pg_config
pg_config: /usr/local/pgsql16/server/bin/pg_config
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3#
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3#
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3#  ./configure --prefix=/usr/local/pgsql16/server
checking for a sed that does not truncate output... /usr/bin/sed
checking for pg_config... /usr/local/pgsql16/server/bin/pg_config
configure: building against PostgreSQL 16.4
checking for gnused... no
checking for gsed... no
checking for sed... yes
configure: creating ./config.status
config.status: creating Makefile
config.status: creating Makefile.global
config.status: creating config.h
config.status: config.h is unchanged
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3#
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3# make && make install
Building against PostgreSQL 16
make: Nothing to be done for 'all'.
Building against PostgreSQL 16
/usr/bin/mkdir -p '/usr/local/pgsql16/server/lib'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/share/extension'
/usr/bin/mkdir -p '/usr/local/pgsql16/server/bin'
/usr/bin/install -c -m 755  repmgr.so '/usr/local/pgsql16/server/lib/repmgr.so'
/usr/bin/install -c -m 644 .//repmgr.control '/usr/local/pgsql16/server/share/extension/'
/usr/bin/install -c -m 644 .//repmgr--unpackaged--4.0.sql .//repmgr--unpackaged--5.1.sql .//repmgr--unpackaged--5.2.sql .//repmgr--unpackaged--5.3.sql .//repmgr--4.0.sql .//repmgr--4.0--4.1.sql .//repmgr--4.1.sql .//repmgr--4.1--4.2.sql .//repmgr--4.2.sql .//repmgr--4.2--4.3.sql .//repmgr--4.3.sql .//repmgr--4.3--4.4.sql .//repmgr--4.4.sql .//repmgr--4.4--5.0.sql .//repmgr--5.0.sql .//repmgr--5.0--5.1.sql .//repmgr--5.1.sql .//repmgr--5.1--5.2.sql .//repmgr--5.2.sql .//repmgr--5.2--5.3.sql .//repmgr--5.3.sql  '/usr/local/pgsql16/server/share/extension/'
/usr/bin/install -c -m 755 repmgr repmgrd '/usr/local/pgsql16/server/bin/'
root@ubuntu01:/usr/local/pg16/repmgr-5.3.3#

注意:
1,编译后会在/usr/local/pgsql16/server/bin路径下生成repmgrd和repmgr两个文件
2,尝试偷懒,将本地编译的repmgrd和repmgr两个文件复制到其他机器上,绕过编译步骤,但是在启动repmgr的时候会一直提示找不到可执行文件,所以还是老老实实逐台机器编译。
3,提示找不到libpq.so.5,尝试建一个软链接后解决

root@ubuntu01:~# repmgr -V
repmgr: error while loading shared libraries: libpq.so.5: cannot open shared object file: No such file or directory
root@ubuntu01:~# psql --version
psql (PostgreSQL) 16.4
root@ubuntu01:~# sudo find / -name libpq.so.5
/usr/local/pgsql16/server/lib/libpq.so.5
/usr/local/pg16/postgresql-16.4/src/interfaces/libpq/libpq.so.5
root@ubuntu01:~#
root@ubuntu01:~# ln -s /usr/local/pgsql16/server/lib/libpq.so.5 /usr/lib/x86_64-linux-gnu/libpq.so.5
root@ubuntu01:~# repmgr -V
repmgr 5.3.3
root@ubuntu01:~#

 

###2 主节点配置文件修改

wal_level = 'replica'或者'logic'
hot_standby = on
archive_mode = on
archive_command = 'test ! -f /postgres/product/archivedir/%f && cp %p /postgres/product/archivedir/%f'
shared_preload_libraries = 'repmgr'

 

###3 修改属主

由于是root用户编译的repmgr,所以重新授权

chown -R postgres:postgres /usr/local/pgsql16/
chmod 700 -R /usr/local/pgsql16/

 

###4 重启数据库

systemdctl restart postgresql9000

 

###5 创建repmgr数据库

--创建repmgr用户
create user repmgr replication password 'password123456';
--alter  user repmgr replication password 'password123456';
alter user repmgr superuser;
create database repmgr owner repmgr;
ALTER USER repmgr SET search_path TO repmgr, "$user", public;
alter user repmgr superuser ;
--进入repmgr数据库
create schema repmgr ;
ALTER USER repmgr SET search_path TO repmgr, "$user", public;
--创建repmgr扩展
create extension repmgr;
--检查扩展
SELECT * FROM pg_extension;

 

###6 pg_hba.conf新增配置

#TYPE DATABASE USER ADDRESS METHOD [OPTIONS]
host repmgr repmgr 192.168.152.0/24         md5
host repmgr repmgr 192.168.152.0/24         md5
host replication repmgr 192.168.152.0/24    md5
host replication repmgr 192.168.152.0/24    md5

 

###7 编辑repmgr配置文件

###/usr/local/pgsql16/repmgr/repmgr.conf
node_id=1
node_name='ubuntu01'
conninfo='host=192.168.150.100 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100'
data_directory='/usr/local/pgsql16/pg9000/data'
pg_bindir='/usr/local/pgsql16/server/bin'
failover=automatic
promote_command='/usr/local/pgsql16/server/bin/repmgr standby promote -f /usr/local/pgsql16/repmgr/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql16/server/bin/repmgr standby follow -f /usr/local/pgsql16/repmgr/repmgr.conf --log-to-file --upstream-node-id=%n'
log_file='/usr/local/pgsql16/repmgr/repmgr.log'

 

###8 编辑密码文件

root@ubuntu01:~# su postgres
postgres@ubuntu01:/root$
postgres@ubuntu01:/root$
postgres@ubuntu01:/root$ cd /home/postgres/
postgres@ubuntu01:~$
postgres@ubuntu01:~$ vi ~/.pgpass
192.168.152.100:9000:repmgr:repmgr:password123456
192.168.152.100:9000:repmgr:repmgr:password123456
192.168.152.100:9000:replication:repmgr:password123456
192.168.152.100:9000:replication:repmgr:password123456
192.168.152.101:9000:repmgr:repmgr:password123456
192.168.152.101:9000:repmgr:repmgr:password123456
192.168.152.101:9000:replication:repmgr:password123456
192.168.152.101:9000:replication:repmgr:password123456
192.168.152.102:9000:repmgr:repmgr:password123456
192.168.152.102:9000:repmgr:repmgr:password123456
192.168.152.102:9000:replication:repmgr:password123456
192.168.152.102:9000:replication:repmgr:password12345

对密码文件重新授权,一定一定一定要授权,否则后续节点注册的时候会提示connection to server at "192.168.152.100", port 9000 failed: fe_sendauth: no password supplied
chmod 0600 ~/.pgpass

 

###9 注册主节点

postgres@ubuntu01:~$ /usr/local/pgsql16/server/bin/repmgr -f /usr/local/pgsql16/repmgr/repmgr.conf primary register
INFO: connecting to primary database...
INFO: "repmgr" extension is already installed
NOTICE: primary node record (ID: 100) registered
postgres@ubuntu01:~$

此时可以从repmgr数据库的表中查询到已注册的节点

select * from repmgr.repmgr.nodes;
node_id|upstream_node_id|active|node_name|type   |location|priority|conninfo                                                                                            |repluser|slot_name|config_file                          |
-------+----------------+------+---------+-------+--------+--------+----------------------------------------------------------------------------------------------------+--------+---------+-------------------------------------+
    100|                |true  |ubuntu01 |primary|default |     100|host=192.168.152.100 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100|repmgr  |         |/usr/local/pgsql16/repmgr/repmgr.conf|

 

 

从节点上操作

###1 编译安装repmgr

与主节点一直,略

 

###2 编辑配置文件

node_id=101
node_name='192.168.152.101'
conninfo='host=192.168.152.101 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100'
data_directory='/usr/local/pgsql16/pg9000/data'
pg_bindir='/usr/local/pgsql16/server/bin'
failover=automatic
promote_command='/usr/local/pgsql16/server/bin/repmgr standby promote -f /usr/local/pgsql16/repmgr/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql16/server/bin/repmgr standby follow -f /usr/local/pgsql16/repmgr/repmgr.conf --log-to-file --upstream-node-id=%n'
log_file='/usr/local/pgsql16/repmgr/repmgr.log'

 

###3 首先停止从节点上的服务

systemctl stop postgresql9000

备份好当前节点的数据文件,清空数据文件目录内容

 

###4 编辑密码文件

root@ubuntu01:~# su postgres
postgres@ubuntu01:/root$
postgres@ubuntu01:/root$
postgres@ubuntu01:/root$ cd /home/postgres/
postgres@ubuntu01:~$
postgres@ubuntu01:~$ vi ~/.pgpass
192.168.152.100:9000:repmgr:repmgr:password123456
192.168.152.100:9000:repmgr:repmgr:password123456
192.168.152.100:9000:replication:repmgr:password123456
192.168.152.100:9000:replication:repmgr:password123456
192.168.152.101:9000:repmgr:repmgr:password123456
192.168.152.101:9000:repmgr:repmgr:password123456
192.168.152.101:9000:replication:repmgr:password123456
192.168.152.101:9000:replication:repmgr:password123456
192.168.152.102:9000:repmgr:repmgr:password123456
192.168.152.102:9000:repmgr:repmgr:password123456
192.168.152.102:9000:replication:repmgr:password123456
192.168.152.102:9000:replication:repmgr:password12345

对密码文件重新授权,一定一定一定要授权,否则会提示connection to server at "192.168.152.100", port 9000 failed: fe_sendauth: no password supplied
chmod 0600 ~/.pgpass

 

###5 从主节点clone测试

/usr/local/pgsql16/server/bin/repmgr -h 192.168.152.100 -p 9000 -U repmgr -d repmgr -f /usr/local/pgsql16/repmgr/repmgr.conf standby clone --dry-run

postgres@ubuntu02:/usr/local/pgsql16$ /usr/local/pgsql16/server/bin/repmgr -h 192.168.152.100 -p 9000 -U repmgr -d repmgr -f /usr/local/pgsql16/repmgr/repmgr.conf standby clone --dry-run
NOTICE: destination directory "/usr/local/pgsql16/pg9000/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.152.100 port=9000 user=repmgr dbname=repmgr
DETAIL: current installation size is 28 MB
INFO: "repmgr" extension is installed in database "repmgr"
INFO: replication slot usage not requested;  no replication slot will be set up for this standby
INFO: parameter "max_wal_senders" set to 10
NOTICE: checking for available walsenders on the source node (2 required)
INFO: sufficient walsenders available on the source node
DETAIL: 2 required, 10 available
NOTICE: checking replication connections can be made to the source server (2 required)
INFO: required number of replication connections could be made to the source server
DETAIL: 2 replication connections required
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
NOTICE: standby will attach to upstream node 100
HINT: consider using the -c/--fast-checkpoint option
INFO: would execute:
  /usr/local/pgsql16/server/bin/pg_basebackup -l "repmgr base backup"  -D /usr/local/pgsql16/pg9000/data -h 192.168.152.100 -p 9000 -U repmgr -X stream
INFO: all prerequisites for "standby clone" are met
postgres@ubuntu02:/usr/local/pgsql16$

 

###6 从主节点clone

postgres@ubuntu02:/usr/local/pgsql16$ /usr/local/pgsql16/server/bin/repmgr -h 192.168.152.100 -p 9000 -U repmgr -d repmgr -f /usr/local/pgsql16/repmgr/repmgr.conf standby clone
NOTICE: destination directory "/usr/local/pgsql16/pg9000/data" provided
INFO: connecting to source node
DETAIL: connection string is: host=192.168.152.100 port=9000 user=repmgr dbname=repmgr
DETAIL: current installation size is 28 MB
INFO: replication slot usage not requested;  no replication slot will be set up for this standby
NOTICE: checking for available walsenders on the source node (2 required)
NOTICE: checking replication connections can be made to the source server (2 required)
WARNING: data checksums are not enabled and "wal_log_hints" is "off"
DETAIL: pg_rewind requires "wal_log_hints" to be enabled
INFO: checking and correcting permissions on existing directory "/usr/local/pgsql16/pg9000/data"
NOTICE: starting backup (using pg_basebackup)...
HINT: this may take some time; consider using the -c/--fast-checkpoint option
INFO: executing:
  /usr/local/pgsql16/server/bin/pg_basebackup -l "repmgr base backup"  -D /usr/local/pgsql16/pg9000/data -h 192.168.152.100 -p 9000 -U repmgr -X stream
NOTICE: standby clone (using pg_basebackup) complete
NOTICE: you can now start your PostgreSQL server
HINT: for example: pg_ctl -D /usr/local/pgsql16/pg9000/data start
HINT: after starting the server, you need to register this standby with "repmgr standby register"
postgres@ubuntu02:/usr/local/pgsql16$

 

###7 从节点注册

postgres@ubuntu02:/usr/local/pgsql16$ /usr/local/pgsql16/server/bin/repmgr -f /usr/local/pgsql16/repmgr/repmgr.conf standby register
INFO: connecting to local node "192.168.152.101" (ID: 101)
INFO: connecting to primary database
WARNING: --upstream-node-id not supplied, assuming upstream node is primary (node ID: 100)
INFO: standby registration complete
NOTICE: standby node "192.168.152.101" (ID: 101) successfully registered
postgres@ubuntu02:/usr/local/pgsql16$

 

###8 主节点上:检查节点以及信息

###主节点上:检查主从复制状态
select * from pg_stat_replication ;
pid |usesysid|usename|application_name|client_addr    |client_hostname|client_port|backend_start                |backend_xmin|state    |sent_lsn |write_lsn|flush_lsn|replay_lsn|write_lag|flush_lag|replay_lag|sync_priority|sync_state
----+--------+-------+----------------+---------------+---------------+-----------+-----------------------------+------------+---------+---------+---------+---------+----------+---------+---------+----------+-------------+----------
2898|   16388|repmgr |192.168.152.101 |192.168.152.101|               |      53602|2024-11-07 10:09:48.526447+08|            |streaming|0/50007F0|0/50007F0|0/50007F0|0/50007F0 |         |         |          |            0|async     
###任何一个节点上:检查集群节点信息
select * from repmgr.nodes;
node_id|upstream_node_id|active|node_name      |type   |location|priority|conninfo                                                                                            |repluser|slot_name|config_file                          |
-------+----------------+------+---------------+-------+--------+--------+----------------------------------------------------------------------------------------------------+--------+---------+-------------------------------------+
    100|                |true  |ubuntu01       |primary|default |     100|host=192.168.152.100 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100|repmgr  |         |/usr/local/pgsql16/repmgr/repmgr.conf|
    101|             100|true  |192.168.152.101|standby|default |     100|host=192.168.152.101 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100|repmgr  |         |/usr/local/pgsql16/repmgr/repmgr.conf|

 

 

witness 节点安装操作

 

###1 编译安装repmgr

与主节点一直,略

 

###2 编辑repmgr配置文件

node_id=102
node_name='192.168.152.102'
conninfo='host=192.168.152.102 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100'
data_directory='/usr/local/pgsql16/pg9000/data'
pg_bindir='/usr/local/pgsql16/server/bin'
failover=automatic
promote_command='/usr/local/pgsql16/server/bin/repmgr standby promote -f /usr/local/pgsql16/repmgr/repmgr.conf --log-to-file'
follow_command='/usr/local/pgsql16/server/bin/repmgr standby follow -f /usr/local/pgsql16/repmgr/repmgr.conf --log-to-file --upstream-node-id=%n'
log_file='/usr/local/pgsql16/repmgr/repmgr.log'

 

###3 编辑密码文件

###编辑密码文件
postgres@ubuntu03:/usr/local/pg_install_package$ vi ~/.pgpass
192.168.152.100:9000:repmgr:repmgr:password123456
192.168.152.100:9000:repmgr:repmgr:password123456
192.168.152.100:9000:replication:repmgr:password123456
192.168.152.100:9000:replication:repmgr:password123456
192.168.152.101:9000:repmgr:repmgr:password123456
192.168.152.101:9000:repmgr:repmgr:password123456
192.168.152.101:9000:replication:repmgr:password123456
192.168.152.101:9000:replication:repmgr:password123456
192.168.152.102:9000:repmgr:repmgr:password123456
192.168.152.102:9000:repmgr:repmgr:password123456
192.168.152.102:9000:replication:repmgr:password123456
192.168.152.102:9000:replication:repmgr:password123456

 

###4 编辑配置文件

pg_hba.conf postgresql.conf 参考主节点即可

 

###5 重启数据库

systemdctl restart postgresql9000



###6 创建repmgr用户

create user repmgr replication password 'password123456';
--alter  user repmgr replication password 'password123456';
alter user repmgr superuser;
create database repmgr owner repmgr;
ALTER USER repmgr SET search_path TO repmgr, "$user", public;
alter user repmgr superuser ;

 

###7 注册witness

postgres@ubuntu03:/usr/local/pg_install_package$ /usr/local/pgsql16/server/bin/repmgr -h 192.168.152.100 -U repmgr -d repmgr -p9000 -f /usr/local/pgsql16/repmgr/repmgr.conf witness register
INFO: connecting to witness node "192.168.152.102" (ID: 102)
INFO: connecting to primary node
NOTICE: attempting to install extension "repmgr"
NOTICE: "repmgr" extension successfully installed
INFO: witness registration complete
NOTICE: witness node "192.168.152.102" (ID: 102) successfully registered
postgres@ubuntu03:/usr/local/pg_install_package$

 

###8 检查节点信息以及复制状态

###主节点上:检查主从复制状态
pid |usesysid|usename|application_name|client_addr    |client_hostname|client_port|backend_start                |backend_xmin|state    |sent_lsn |write_lsn|flush_lsn|replay_lsn|write_lag|flush_lag|replay_lag|sync_priority|sync_state
----+--------+-------+----------------+---------------+---------------+-----------+-----------------------------+------------+---------+---------+---------+---------+----------+---------+---------+----------+-------------+----------
2898|   16388|repmgr |192.168.152.101 |192.168.152.101|               |      53602|2024-11-07 10:09:48.526447+08|            |streaming|0/5000FA8|0/5000FA8|0/5000FA8|0/5000FA8 |         |         |          |            0|async     
###任何一个节点上:检查集群节点信息
select * from repmgr.nodes;
node_id|upstream_node_id|active|node_name      |type   |location|priority|conninfo                                                                                            |repluser|slot_name|config_file                          |
-------+----------------+------+---------------+-------+--------+--------+----------------------------------------------------------------------------------------------------+--------+---------+-------------------------------------+
    100|                |true  |ubuntu01       |primary|default |     100|host=192.168.152.100 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100|repmgr  |         |/usr/local/pgsql16/repmgr/repmgr.conf|
    101|             100|true  |192.168.152.101|standby|default |     100|host=192.168.152.101 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100|repmgr  |         |/usr/local/pgsql16/repmgr/repmgr.conf|
    102|             100|true  |192.168.152.102|witness|default |       0|host=192.168.152.102 user=repmgr dbname=repmgr password=password123456 port=9000 connect_timeout=100|repmgr  |         |/usr/local/pgsql16/repmgr/repmgr.conf|

 

 

至此,一个最简单的基于repmgr的PostgreSQL流复制高可用环境搭建完成,整个过程虽然不复杂,但是比较繁琐。

标签:pgsql16,PostgreSQL,--,一从,192.168,usr,一主,repmgr,local
From: https://www.cnblogs.com/wy123/p/18531710

相关文章

  • 用处多多!信创PostgreSQL认证证书含金量
    PostgreSQL是目前讨论比较多的数据库技术,国内很多大的企业都在开发基于PostgreSQL的数据库产品,比如腾讯云TDSQL-PG版、阿里云PolarDB-PG版、人大金仓等等,考取PostgreSQL数据库证书对个人在数据库领域的职业发展具有多方面的积极作用。以下是对其用处的详细分析:​一、提升专业能......
  • 零基础学习Spring AI Java AI使用向量数据库postgresql 检索增强生成 RAG
    零基础学习SpringAIJavaAI使用向量数据库postgresql检索增强生成RAG向量数据库是一种特殊类型的数据库,在人工智能应用中发挥着至关重要的作用。在向量数据库中,查询与传统的关系数据库不同。它们不是进行精确匹配,而是执行相似性搜索。当给定一个向量作为查询时,向量数......
  • 数据库 PostgreSQL 和 MySQL 开源协议的区别
    说到开源数据库,很多人想到了MySQL和PostgreSQL数据库,这是两种比较有名和使用量较多的数据库技术,两者虽然都是开源的,但开源协议是不一样的。PostgreSQL遵循灵活的开源协议BSD,MySQL使用的是GPL(GNUGeneralPublicLicense)协议,二者有以下区别:1.商业使用限制方面-PostgreSQLBS......
  • postgreSQL install pgvector
    组件地址:https://github.com/pgvector/pgvector我的机器是MacOS,下载的postgreSQL是15,按理下载下来是自动安装了pgvector,但是测试的时候发现并没有这个extension。按文档执行:make--报错,clang:error:unsupportedargument'native'tooption'-march='。经查询命令改为:mak......
  • postgresql和mysql中的limit使用方法
    @目录区别举例说明MySQL中PostgreSQL中区别在msyql中,limit使用如下select*frommytablelimita,b其中:a为起始索引,从0开始,b为获取数据长度在postgresql中,limit使用如下select*frommytablelimitaoffsetb其中:b为起始索引,a为获取数据长度其中:mysql使用逗号",",而pg......
  • PostgreSQL11从入门到精通
    书:pan.baidu.com/s/1IVTI-jQveSb4Cdrio6XkBw?pwd=ijykPostgreSQL11是一个功能强大的开源关系型数据库管理系统,以下是对PostgreSQL11从入门到精通的技术分享:一、PostgreSQL11简介PostgreSQL起源于加利福尼亚的伯克利大学,经历了无数次开发升级,现已成为世界上最先进的开源关......
  • PostgreSQL技术大讲堂 - 第70讲:PG数据库数据加载调优案例
     PostgreSQL技术大讲堂-第70讲,主题:postgresq数据库数据加载调优案例讲课内容:  1、数据库参数调整  2、后台进程cpu绑定调整  3、数据库并行操作调整  数据加载是每个DBA经常需要完成的工作,如何让数据加载变得更快,本期视频跟大家一起分享调优带来的乐趣......
  • 基于LORA的一主多从监测系统_4G模块上巴法云
    临时添加一个更新,更换云平台为巴法云,事情的起因是因为阿里云这个老六,早上睡了一觉起来发短信告诉我云平台给我停了,得交钱,好嘛,不过也没办法现在这基本都收费,当然还有onenet可以用,别问我为什么不用,主要是因为onenet现在整的太复杂了,以前用的多现在也不想用了。所以这里使用我大......
  • PostgreSQL中checkpoint的作用和工作原理
    ###checkpoint的作用将脏页写入磁盘,避免数据库实例重启之后需要从WAL中恢复大量的数据而增加数据库恢复时间 ###checkpoints的触发时机1,手动CHECKPOINT命令;2,pg_basebackup,CREATEDATABASE,或pg_ctlstop|restart;3,定期执行的checkpoint,也即每隔checkpoint_timeout定时执行的4,自......
  • postgresql 下载安装
    一、postgresql下载pg官网:postgres.org一般推荐用源码安装,下载.tar.gz包二、安装本文以12.6版本安装为例:2.1、安装前要求和环境配置#1、要求GNUmake版本3.80或以上(GNUmake有时以名字gmake安装),要测试make版本可以使用以下命令(如果是安装其他版本的pg具体要求make......