postgreSQL13 centos安装部署和备份

java_leaf

已于 2024-12-18 17:15:33 修改

阅读量472

点赞数 4

文章标签： centos linux 运维 postgresql

于 2024-12-18 17:07:32 首次发布

本文链接：https://blog.youkuaiyun.com/java_leaf/article/details/144564745

版权

对比MySQL

稳定性极强，比mysql稳定性更好。结合了许多安全存储和扩展最复杂数据工作负载的功能
可靠性，灾难恢复
1.预写日志（WAL）
2.复制：异步，同步，逻辑
3.时间点恢复（pitr），主动备用
PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景

PostgreSQL的各种丰富的功能和对事务的支持，可以满足实际金融业务的交易需求;

PostgreSQL的物理复制保证数据库零丢失，相对于MySQL基于binlog的逻辑复制。可以高效快速地将数据库可以跨机房,设置跨region同步，可以满足业务的在各region本地高效读的需求
PostgreSQL的json，jsonb，hstore等数据格式，特别适用于一些大数据格式的分析；而MySQL更加适合业务逻辑相对简单、数据可靠性要求较低的互联网场景（比如google、facebook、alibaba），当然现在MySQL的在innodb引擎的大力发展，功能表现良好。

在这里插入图片描述

centos7　安装postgresql13

CentOS 7
wget -O /etc/yum.repos.d/CentOS-Base.repo http://mirrors.aliyun.com/repo/Centos-7.repo

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql13-server

# 查看安装的服务
rpm -qa|grep postgre
#查看安装路径，默认安装路径在/usr/pgsql-13/bin
rpm -ql postgresql13-server
# 初始化postgresql
postgresql-13-setup initdb

systemctl enable postgresql-13
systemctl restart postgresql-13

yum install  postgresql13-contrib.x86_64

timescaledb 时序安装

网址：https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.5-x86_64/
下载timescaledb_13-2.4.1-1.rhel7.x86_64.rpm
https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-7.5-x86_64/timescaledb_13-2.8.0-1.rhel7.x86_64.rpm 不能升级其他版本不然有问题
2.安装
yum install –y timescaledb_13-2.4.1-1.rhel7.x86_64.rpm

3.改配置文件
vim /var/lib/pgsql/13/data/postgresql.conf
shared_preload_libraries = 'timescaledb' 
systemctl restart postgresql-13.service 

#查找
[root@localhost iot]# rpm -qa timescaledb_*
timescaledb_13-2.11.2-1PGDG.rhel7.x86_64
#卸载
rpm -e timescaledb_13-2.11.2-1PGDG.rhel7.x86_64
#安装  #升级是 rpm -Uvh xx
rpm -ivh timescaledb_13-2.4.1-1.rhel7.x86_64.rpm

-- 对数据库 iotdb加入时序库的插件支持。
create extension if not exists timescaledb cascade;

Centos9 安装postgresql

centos9 基本和原来的centos7相同
yum源安装方式yum和dnf同时可用；
ntp服务更新为chrony
#安装chrony
dnf -y install chrony
#配置
vi /etc/chrony.conf
#要文件底部增加以下一行
pool dlp.srv.world iburst
#设置开机启动
systemctl enable --now chronyd
#检查状态
chronyc sources
#启用时间同步
timedatectl set-ntp true
#禁用时间同步
timedatectl set-ntp false

安装postgresql

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 完成后执行
sudo tee /etc/yum.repos.d/timescale_timescaledb.repo <<EOL
# 然后复制以下内容并保存
[timescale_timescaledb]
name=timescale_timescaledb
baseurl=https://packagecloud.io/timescale/timescaledb/el/$(rpm -E %{rhel})/\$basearch
repo_gpgcheck=1
gpgcheck=0 
enabled=1 
gpgkey=https://packagecloud.io/timescale/timescaledb/gpgkey 
sslverify=1
sslcacert=/etc/pki/tls/certs/ca-bundle.crt
metadata_expire=300
EOL

yum update -y

yum install postgresql13-server

#开机启动，开启时enabled,否是 disabled
systemctl enable postgresql
#安装postgresql-server会附带安装上postgres客户端，因此不必重复安装。安装完成，postgresql操作相关的命令都会添加到/usr/bin目录下，可以在命令行下直接使用。
which psql
which postgresql-setup
whereis postgresql-setup
[postgres@localhost ~]$ psql --version
psql (PostgreSQL) 13.13
 # 查看生成的用户
cut -d : -f 1 /etc/passwd
# postgres 最后一行看到是这个用户
# 初始化数据库
[root@localhost ~]# postgresql-setup initdb
# 都会存放在路径/var/lib/pgsql/data下 可以到这个目录查看
systemctl enable postgresql-13
systemctl restart postgresql-13
# netstat -nat # 服务端口是 5432端口
[root@localhost data]# netstat -nat
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State      
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN     
tcp        0    236 192.168.56.102:22       192.168.56.1:63747      ESTABLISHED
tcp        0      0 192.168.56.102:22       192.168.56.1:61484      ESTABLISHED
tcp6       0      0 :::22                   :::*                    LISTEN     
tcp6       0      0 ::1:5432                :::*                    LISTEN     

# 设置下 postgrep 用户的密码
passwd postgres
su - postgres
psql
\l 查看所有
\q 退出
# 在windows上使用 navicat premium
# 出现拒接远程连接 ，修改配置
systemctl stop postgresql
vim /var/lib/pgsql/data/postgresql.conf
    listen_addresses='*'
    port = 5432
    
# 修改文件pg_hba.conf
在文件末尾添上：
# TYPE DATABASE USER CIDR-ADDRESS METHOD
host    all             all              0.0.0.0/0              md5

# 但是我们的客户端一般没有加密所以 md5 要改成 trust
service postgresql restart
# 连接 postgre centos 会有问题 psql: error: FATAL:  Peer authentication failed for user "postgres"
psql -U postgres  
# 安装完 PostgresQL 后， PostgresQL 连接时的默认认证方式为 peer
vim /var/lib/pgsql/data/pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# Peer 改为 md5

trust：凡是能连接到服务器的，都是可信任的。只需要提供数据库用户名，可以没有对应的操作系统同名用户；
password 和 md5：对于外部访问，需要提供 psql 用户名和密码。对于本地连接，提供 psql 用户名密码之外，还需要有操作系统访问权（用操作系统同名用户验证）。password 和 md5 的区别就是外部访问时传输的密码是否用 md5 加密；
ident：对于外部访问，从 ident 服务器获得客户端操作系统用户名，然后把操作系统作为数据库用户名进行登录；对于本地连接，实际上使用了peer；
peer：通过客户端操作系统内核来获取当前系统登录的用户名，并作为psql用户名进行登录。

安装TimescaleDB数据库：


 yum search timescaledb

yum install timescaledb-2-postgresql-13  

3.改配置文件
vim /var/lib/pgsql/data/postgresql.conf
shared_preload_libraries = 'timescaledb' 
service postgresql restart

-- 对数据库 iotdb加入时序库的插件支持。
create extension if not exists timescaledb cascade;

表需要加入时序

CREATE TABLE IF NOT EXISTS ts_kv (
    entity_id uuid NOT NULL,
    key int NOT NULL,
    ts bigint NOT NULL,
    bool_v boolean,
    str_v varchar(10000000),
    long_v bigint,
    dbl_v double precision,
    json_v json,
    CONSTRAINT ts_kv_pkey PRIMARY KEY (entity_id, key, ts)
);
-- long型
SELECT create_hypertable('ts_kv', 'ts', chunk_time_interval => 86400000, if_not_exists => true);
--date
--Date类型下面会报错，用最下面那个
SELECT create_hypertable('ts_kv', 'times', chunk_time_interval => 86400000, if_not_exists => true);

SELECT create_hypertable('bk_en_device', 'times', chunk_time_interval => interval '1 day');

修改密碼

首先，登录PostgreSQL
su - postgres psql postgres -p 5432
ALTER USER postgres WITH PASSWORD 'xxxx';
exit;
# PostgreSQL都会创建一个默认的linux用户postgres

在${pgsql}/data/pg_hba.conf中将“trust”替换为“md5”；
否则，任何密码都能够登录。

# vim /var/lib/pgsql/data/pg_hba.conf
# service postgresql restart
Redirecting to /bin/systemctl restart postgresql.service

备份和还原

备份

/usr/pgsql-13/bin/pg_dump --file "/root/boke_iot.backup" --host "192.168.6.43" --port "5432" --username "postgres" --dbname "xx_iot" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"

还原

//还原/导入 要先创建数据库 boke_iot 然后创建时序
/usr/pgsql-13/bin/pg_restore --username "postgres" --host "192.168.6.48" --port "15432"   --role "postgres" --dbname "xx_xx"    --verbose /root/boke_iot.backup

备份脚本：
backup.sh

[root@iotweb backup]# cat pg_backup.sh 
#!/bin/bash
echo "开始执行 PostgreSql 数据库  的备份！"
nowtime=$(date +%Y-%m-%d-%H:%M:%S)   # 时间
export PGPASSWORD=xxxx       #这里写密码
echo "时间：" $nowtime
set timeout 500
/usr/pgsql-13/bin/pg_dump --file "/home/backup/pgsqldata/iotbackup-$nowtime.backup" --host "192.168.6.43" --port "5432" --username "postgres" --dbname "xxx_iot" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"
echo "数据库   备份完成！"
exit;

创建自增序列

CREATE SEQUENCE public.ts_xxxx_seq
    AS integer
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;
create sequence product_soft_seq
increment  by 1  --步长
minvalue 1     --最小值
maxvalue 9999999 --最大值
start 1  --起始值
cache 1  --每次生成几个值
cycle;  --到达最大值或最小值循环(不加默认不循环)

# postgresql的int4类型中的长度为4个字节，32位 是定长4字节(=32位bit)。其对应的十进制取值范围是 -21474836478 ~ 2147483647
# 查看当前序列的值  建议后面跟 _quence 后缀 或者使用 _seq
select * from product_soft_quence ;

# 删除序列
drop sequence product_soft_quence ;
# 使用序列
insert into product_info values('测试产品','产品'||nextval('product_soft_seq')||'号','20220710') returning *;

Java 中怎么使用？下面是jpa的使用序列

    @Id
    @SequenceGenerator(name = "somethingGen", sequenceName = "product_soft_seq", allocationSize = 30)
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "somethingGen")
    @Column(name = "id", unique = true, nullable = false)
    private long id;

mybatis-plus 使用序列也很简单


public class MybatisPlusConfig {

/**
 * Sequence主键自增
 * @return 返回PostgreKeyGenerator自增类
 */
@Bean
public PostgreKeyGenerator postgreKeyGenerator(){
    return new PostgreKeyGenerator();
}


// 使用
@TableName("soft_upgrade")
@KeySequence(value = "soft_upgrade_seq")
public class SoftUpgrade implements Serializable {
    /**
     * 序列ID
     */
    @TableId(value = "id", type = IdType.INPUT)
   private Integer id;

时序统计查询

select sum(val),times from bk_en_device group by times;

SELECT  sum(val) as energy ,time_bucket('1 day', times) AS bucket, COUNT(*) FROM bk_en_device GROUP BY bucket ORDER BY bucket; --用这个方式 time_bucket 不用上面的

SELECT  sum(val) as energy ,date_trunc('month', times) AS bucket, COUNT(*),pro_id FROM bk_en_device GROUP BY bucket,pro_id ORDER BY bucket; -- 按月份

JSONB字段查询记录

        ---- 案例：
  --# 使用->返回的结果是带引号的
select '{"nickname": "goodspeed", "avatar": "avatar_url"}'::json->'nickname' as nickname;
                
                --# {tags,0}代表查询key=‘tags’，且value为数组中数组的第一个元素
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"]}'::json#>'{tags,0}' as tag;

                --# {aa,b,0}代表查询key=‘aa’，对应的value中，key=‘b’的value中，数组的第一个元素
select '{"nickname": "gs", "avatar": "avatar_url", "tags": ["python", "golang", "db"], "aa":{"b": [{"c": 1}]}}'::jsonb#>>'{aa,b,0}';

select '[{"type": "type", "cycle": "1", "seqId": "1666700296305786882", "times": "12:00", "behaviour": "json", "entrySeqId": "1666691666571476993"}]'::jsonb->>'cycle' as name2



WITH sample_data_array(arr) AS (
    VALUES ('[{"name":"Mickey Mouse","age":10},{"name":"Donald Duck","age":5}]'::jsonb)
)
, sample_data_elements(elem) AS (
    SELECT jsonb_array_elements(arr) FROM sample_data_array
)
SELECT elem->'name' AS extracted_name FROM sample_data_elements;


(SELECT  json->'cycle' cycle,json->'times' as times  FROM bk_auto_trigger s ,jsonb_array_elements(s.do_condition) json
WHERE s.organization_id='57f3910d-3785-4481-bab6-7cef31d3925e' and s.tenant_id='37810f70-b8c0-11ed-8979-bf7c624ed181' and s.device_id='d3dfe9d0-c7ad-11ed-8fc7-6da484c8bc67')

 SELECT device_id FROM bk_auto_trigger s, jsonb_array_elements(s.do_condition) json
WHERE s.organization_id='57f3910d-3785-4481-bab6-7cef31d3925e' and s.tenant_id='37810f70-b8c0-11ed-8979-bf7c624ed181'  and json->>'seqId' ='1676104074658947074' or json->>'entrySeqId'= '1676104074658947074' ;