PG数据库安装​timescale数据库以及备份配置

1.PG数据库安装

系统版本

数据库版本PG14.4

1.1 依赖包安装

yum -y install libicu libicu-devel libxslt perl-libs python-psycopg2 expect

rpm -ivh dwz-0.11-3.el7.x86_64.rpm

rpm -ivh perl-srpm-macros-1-8.el7.noarch.rpm

rpm -ivh python-srpm-macros-3-34.el7.noarch.rpm

rpm -ivh python-rpm-macros-3-34.el7.noarch.rpm

rpm -ivh redhat-rpm-config-9.1.0-88.el7.centos.noarch.rpm

rpm -ivh tk-8.5.13-6.el7.x86_64.rpm

yum -y localinstall python3*

rpm -ivh ncurses-devel-5.9-14.20130511.el7_4.x86_64.rpm

rpm -ivh libedit-devel-3.0-12.20121213cvs.el7.x86_64.rpm

yum -y localinstall llvm5.0-*

---> 软件包 llvm5.0.x86_64.0.5.0.1-7.el7 将被 安装

---> 软件包 llvm5.0-devel.x86_64.0.5.0.1-7.el7 将被 安装

---> 软件包 llvm5.0-libs.x86_64.0.5.0.1-7.el7 将被 安装

rpm -ivh kernel-headers-3.10.0-1160.71.1.el7.x86_64.rpm

rpm -ivh kernel-headers-3.10.0-1160.71.1.el7.x86_64.rpm

yum -y localinstall glibc*

--> 正在检查事务

---> 软件包 glibc.x86_64.0.2.17-317.el7 将被 升级

---> 软件包 glibc.x86_64.0.2.17-326.el7_9 将被 更新

---> 软件包 glibc-common.x86_64.0.2.17-317.el7 将被 升级

---> 软件包 glibc-common.x86_64.0.2.17-326.el7_9 将被 更新

---> 软件包 glibc-devel.x86_64.0.2.17-326.el7_9 将被 安装

---> 软件包 glibc-headers.x86_64.0.2.17-326.el7_9 将被 安装

yum -y localinstall devtoolset-7*

正在解决依赖关系

--> 正在检查事务

---> 软件包 devtoolset-7-binutils.x86_64.0.2.28-11.el7 将被 安装

---> 软件包 devtoolset-7-gcc.x86_64.0.7.3.1-5.16.el7 将被 安装

---> 软件包 devtoolset-7-gcc-c++.x86_64.0.7.3.1-5.16.el7 将被 安装

---> 软件包 devtoolset-7-libstdc++-devel.x86_64.0.7.3.1-5.16.el7 将被 安装

---> 软件包 devtoolset-7-runtime.x86_64.0.7.1-4.el7 将被 安装

rpm -ivh llvm-toolset-7-runtime-5.0.1-4.el7.x86_64.rpm

rpm -ivh llvm5.0-5.0.1-7.el7.x86_64.rpm

rpm -ivh llvm5.0-libs-5.0.1-7.el7.x86_64.rpm

rpm -ivh llvm5.0-devel-5.0.1-7.el7.x86_64.rpm

rpm -ivh llvm-toolset-7-compiler-rt-5.0.1-2.el7.x86_64.rpm

rpm -ivh llvm-toolset-7-libomp-5.0.1-2.el7.x86_64.rpm

rpm -ivh centos-release-scl-rh-2-3.el7.centos.noarch.rpm

rpm -ivh llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm

rpm -ivh llvm-toolset-7-llvm-libs-5.0.1-8.el7.x86_64.rpm

rpm -ivh llvm-toolset-7-clang-libs-5.0.1-4.el7.x86_64.rpm

rpm -ivh llvm-toolset-7-clang-5.0.1-4.el7.x86_64.rpm

1.2数据库安装

rpm -ivh postgresql14-libs-14.4-1PGDG.rhel7.x86_64

rpm -ivh postgresql14-libs-14.4-1PGDG.rhel7.x86_64.rpm

rpm -ivh postgresql14-server-14.4-1PGDG.rhel7.x86_64.rpm

rpm -ivh postgresql14-contrib-14.4-1PGDG.rhel7.x86_64.rpm

rpm -ivh postgresql14-devel-14.4-1PGDG.rhel7.x86_64.rpm

rpm -ivh timescaledb_14-2.7.0-1.rhel7.x86_64.rpm

1.3 设置环境变量

Su - postgres

[postgres@timescale ~]-> cat .bash_profile

[ -f /etc/profile ] && source /etc/profile

PGDATA=/var/lib/pgsql/14/data

export PGDATA

# If you want to customize your settings,

# Use the file below. This is not overridden

# by the RPMS.

[ -f /var/lib/pgsql/.pgsql_profile ] && source /var/lib/pgsql/.pgsql_profile

export PS1="[$USER@`/bin/hostname -s` W]-> "

export PGPORT=5432

export PGDATA=/zdata/pg/pgmain

export LANG=en_US.utf8

export PGHOME=/usr/pgsql-14

export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH

export DATE=`date +"%Y%m%d%H%M"`

export PATH=$PGHOME/bin:$PATH

export MANPATH=$PGHOME/share/man:$MANPATH

export PGHOST=$PGDATA

export PGUSER=postgres

export PGDATABASE=postgres

alias rm='rm -i'

alias ll='ls -lh'

alias vi='vim'

[postgres@timescale ~]->

1.4 初始化数据库

postgres用户

mkdir -p /zdata/dbbak/archivewal

chown -R postgres:postgres /zdata/dbbak/archivewal

initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.UTF8 -X /zdata/dbbak/archivewal -k

注意-X /zdata/dbbak/archivewal 这个归档不配置,我们后续再配置,初始化用下面的配置。

initdb -D $PGDATA -U postgres -E UTF8 --lc-collate=C --lc-ctype=en_US.UTF8 -k

Root用户修改权限

[root@timescale usr]# chown -R postgres.postgres /usr/pgsql-14/

1.5 创建systemctl服务启动

[root@timescale xxx]# systemctl enable postgresql-14.service

Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-14.service to /usr/lib/systemd/system/postgresql-14.service.

[root@timescale xxx]# vim /usr/lib/systemd/system/postgresql-14.service

[root@timescale xxx]# cat /usr/lib/systemd/system/postgresql-14.service

注意我们要修改

Environment=PGDATA=/var/lib/pgsql/14/data/

修改为

#Environment=PGDATA=/var/lib/pgsql/14/data/

Environment=PGDATA=/zdata/pg/pgmain/

然后

[root@timescale xxx]# systemctl daemon-reload

[root@timescale xxx]# systemctl start postgresql-14.service

1.6 pg_hba.conf

[postgres@timescale pgmain]-> egrep -v '^[[:space:]]|^$|^#' pg_hba.conf

local all all trust

host all all 127.0.0.1/32 trust

host all all 0.0.0.0/0 md5

host all all ::1/128 trust

local replication all trust

host replication all 127.0.0.1/32 trust

host replication all ::1/128 trust

[postgres@timescale pgmain]->

1.7 postgresql.conf配置

shared_preload_libraries = 'timescaledb,pg_stat_statements'这个必须有

其他参数按照数据库标准参数配置

2.用户安装

CREATE USER timescaler WITH PASSWORD 'timescaler' login;

Create database timescaledb;

GRANT ALL PRIVILEGES ON DATABASE timescaledb TO timescaler;

GRANT ALL PRIVILEGES ON all tables in schema public TO timescaler;

timeseries=​​​# create extension timescaledb;                            # 方式一,添加 TimescaleDB 插件            ​​

timeseries=​​# CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;      # 方式二,使用 TimescaleDB 扩展数据库

PG数据库安装​timescale数据库以及备份配置

3.安装go环境

[root@timescale ~]# tar -C /usr/local -xzf go1.18.4.linux-amd64.tar.gz

[root@timescale ~]# vim /etc/profile

[root@timescale ~]# tail -1 /etc/profile

export PATH=$PATH:/usr/local/go/bin

[root@timescale ~]# source /etc/profile

4.安装timescaledb-parallel-copy

su - postgres

# 临时启用Go Modules 功能

$ export GO111MODULE=on

# 临时设置 环境变量

$ export GOPROXY=https://goproxy.io,direct

# 还可以设置不走 proxy 的私有仓库或组,多个用逗号相隔

$ export GOPRIVATE=git.mycompany.com,github.com/my/private

go install github.com/timescale/timescaledb-parallel-copy/cmd/timescaledb-parallel-copy@latest

[postgres@timescale ~]-> cd go/bin/

[postgres@timescale bin]-> ls

timescaledb-parallel-copy

[postgres@timescale bin]->

设置环境变量:

export PATH=$PATH:/var/lib/pgsql/go/bin

注意:如果是离线安装的话,需要安装工具包,然后再进行安装。

GitHub - timescale/timescaledb-toolkit: Extension for more hyperfunctions, fully compatible with TimescaleDB and PostgreSQL ?

创建一个超表,并导入

数据集来源--

来源:https://docs.timescale.com/v1.1/tutorials/other-sample-datasets

下载地址:https://timescaledata.blob.core.windows.net/datasets/devices_big.tar.gz

DROP TABLE IF EXISTS "device_info";

CREATE TABLE "device_info"(

device_id TEXT,

api_version TEXT,

manufacturer TEXT,

model TEXT,

os_name TEXT

);

DROP TABLE IF EXISTS "readings";

CREATE TABLE "readings"(

time TIMESTAMP WITH TIME ZONE NOT NULL,

device_id TEXT,

battery_level DOUBLE PRECISION,

battery_status TEXT,

battery_temperature DOUBLE PRECISION,

bssid TEXT,

cpu_avg_1min DOUBLE PRECISION,

cpu_avg_5min DOUBLE PRECISION,

cpu_avg_15min DOUBLE PRECISION,

mem_free DOUBLE PRECISION,

mem_used DOUBLE PRECISION,

rssi DOUBLE PRECISION,

ssid TEXT

);

CREATE INDEX ON "readings"(time DESC);

CREATE INDEX ON "readings"(device_id, time DESC);

-- 86400000000 is in usecs and is equal to 1 day

SELECT create_hypertable('readings', 'time', chunk_time_interval => 86400000000);

通过timescaledb-parallel-copy导入数据csv

## 导入大数据集并计时

for f in /data/TAQ/csv/*.csv ; do

tail -n +2 $f | timescaledb-parallel-copy

--workers 12

--reporting-period 1s

--copy-options "CSV"

--connection "host=localhost user=postgres password=postgres dbname=test sslmode=disable"

--db-name test

--table taq

--batch-size 200000

文件 $f 导入完成"

done

# 导出数据并计时

chown postgres:postgres /data/devices

su postgres

cd ~

time psql -d test -c "COPY (SELECT * FROM readings) TO /data/devices/devices_dump.csv DELIMITER ',' CSV"

5.PG数据库备份

https://postgrespro.github.io/pg_probackup/

PG-pg_probackup备份恢复工具 - KuBee - 博客园 (cnblogs.com)

请务必查看

5.1 创建用户

BEGIN;

CREATE ROLE backup WITH LOGIN;

GRANT USAGE ON SCHEMA pg_catalog TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.current_setting(text) TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.set_config(text, text, boolean) TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_is_in_recovery() TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_start_backup(text, boolean, boolean) TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_stop_backup(boolean, boolean) TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_create_restore_point(text) TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_switch_wal() TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_last_wal_replay_lsn() TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.txid_current() TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.txid_current_snapshot() TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.txid_snapshot_xmax(txid_snapshot) TO backup;

GRANT EXECUTE ON FUNCTION pg_catalog.pg_control_checkpoint() TO backup;

COMMIT;

ALTER ROLE backup WITH REPLICATION;

5.2 如何下载pg_probackup的rpm包

rpm -ivh http://repo.postgrespro.ru/pg_probackup/keys/pg_probackup-repo-centos.noarch.rpm

yum install --downloadonly --downloaddir=/root pg_probackup-14

[root@timescale ~]# rpm -ivh pg_probackup-14-2.5.5-1.0834e54fc37bd841f11717e07291d59ba92e3333.x86_64.rpm

警告:pg_probackup-14-2.5.5-1.0834e54fc37bd841f11717e07291d59ba92e3333.x86_64.rpm: 头V4 RSA/SHA512 Signature, 密钥 ID 636d717e: NOKEY

准备中... ################################# [100%]

正在升级/安装...

1:pg_probackup-14-2.5.5-1.0834e54fc################################# [100%]

[root@timescale ~]#

5.3 初始化catalog

[postgres@timescale ~]-> pg_probackup-14 init -B /zdata/dbbak/probackup

INFO: Backup catalog '/zdata/dbbak/probackup' successfully inited

[postgres@timescale ~]->

5.4 向备份目录添加新的备份实例

pg_probackup-14 add-instance -B backup_dir -D data_dir --instance instance_name [remote_options]

#-- 本地添加备份instance 示例

pg_probackup-14 add-instance -B /zdata/dbbak/probackup -D $PGDATA --instance timescaledb

5.5服务端参数配置

一般情况下,我们安装为集群。

主库上配置归档

说明

搭建PG数据库的时候,归档一般一份放在pg_wal目录,另外一份作为备份放在备份空间。

因为容灾的时候,灾备传输一般时应用pg_wal的目录,pg_wal目录受到设置的影响,有可能导致归档无法及时的应用,导致断掉。

这时候在备份的归档(我一般保留10天)就起到了作用。

搭建PG集群时,配置归档备份目录

首先我们脚本都放在/zdata/dbbak/scripts目录

cd /zdata/dbbak/scripts

vim archive.sh

#!/bin/bash

source /var/lib/pgsql/.bash_profile

DATE=`date +%Y%m%d`

DIR="/zdata/dbbak/archivedir/$DATE"

BACK="/zdata/dbbak/archivedir/"`date -d '-10 day' +%Y%m%d`

if [ -d "$BACK" ]; then

rm -rf $BACK

echo "success rm $BACK" >/zdata/dbbak/logs/pg_archive_logs

else

echo "the old backup file not exists!" >/zdata/dbbak/logs/pg_archive_logs

fi

(test -d $DIR || mkdir -p $DIR) && cp $1 $DIR/$2

archive_command = 'bin/bash /zdata/dbbak/scripts/archive.sh %p %f'

然后配置postgresql.conf

archive_mode = on # enables archiving; off, on, or always

archive_command = '/bin/bash /zdata/dbbak/scripts/archive.sh %p %f'

对数据库重启

sudo systemctl restart postgresql-11.service

归档目录每天会生成一个天数目录。

-bash-4.2$ ll

total 0

drwxr-xr-x 6 postgres postgres 72 Sep 13 17:53 dbbak

drwxrwxr-x 4 postgres postgres 34 Aug 5 15:57 pg

-bash-4.2$ cd dbbak/

-bash-4.2$ ll

total 0

drwxr-xr-x 3 postgres postgres 22 Sep 13 18:14 archivedir

drwxr-xr-x 2 postgres postgres 6 Aug 5 15:51 archivelog

drwxr-xr-x 2 postgres postgres 6 Aug 5 15:51 backups

drwxr-xr-x 2 postgres postgres 24 Sep 13 18:14 scripts

-bash-4.2$ cd archivedir/

-bash-4.2$ ll

total 4

drwxr-xr-x 2 postgres postgres 4096 Sep 13 18:33 20210913

-bash-4.2$ pwd

/zdata/dbbak/archivedir

-bash-4.2$

Pg_probackup放置于备库上,配置如下:

确保wal_level参数高于minimal,我们选择always,将其放置于备库上。这样不影响主库。

如果要在primary上配置存档,则存档模式必须设置为“on”或“always”。要在standby下执行存档,请将此参数设置为“always"

archive_command 参数配置

archive_command = '"install_dir/pg_probackup" archive-push -B "backup_dir" --instance instance_name --wal-file-name=%f [remote_options]'

# -- 示例

pg_probackup-14 archive-push -B /zdata/dbbak/probackup --instance timescaledb --wal-file-path=%p --wal-file-name=%f

修改postgresql.conf

# - Archiving -

archive_mode = always # enables archiving; off, on, or always

# (change requires restart)

archive_command = 'bin/bash /zdata/dbbak/scripts/archive.sh %p %f&& pg_probackup-14 archive-push -B /zdata/dbbak/probackup --instance timescaledb --wal-file-path=%p --wal-file-name=%f' # command to use to archive a logfile segment

通过以上配置,我们就有2份完整的归档了。放置于不通的目录。一份是/zdata/dbbak/probackup/wal

一份位于/zdata/dbbak/archivedir/

5.6 创建一个备份

pg_probackup-14 backup -B backup_dir --instance instance_name -b backup_mode

  • backup_mode 取值:
  • FULL:完整备份
  • DELTA :自上次备份以来发生更改的页面创建增量备份
  • PAGE :基于自上次完整或增量备份后生成的 WAL 文件创建增量备份。仅从数据文件中读取更改的块。
  • PTRACK :动态创建增量备份跟踪页面更改

# -- 示例

全备,一般设置为1周2次,周日一次,周3一次,

Probakcup_full.sh &>>/<path>/Probakcup_full.log

pg_probackup-14 backup -B /zdata/dbbak/probackup --instance timescaledb -b full --delete-wal --delete-expired

增量备份,增量备份,设置为1小时备份一次。从看增量来看。1小时备份后还是比较快的。

Probakcup_page.sh&>>/<path>/Probakcup_page.log

pg_probackup-14 backup -B /zdata/dbbak/probackup --instance timescaledb -b page

5.7查看备份

pg_probackup show -B /zdata/dbbak/probackup

发表评论

相关文章