postgresql数据库 timescaledb数据库 还原数据库

postgresql数据库 timescaledb数据库备份 还原数据库 数据迁移

最近碰到一个问题 timescaledb数据库 所在的服务器内存满了 新加一个盘 考虑如何迁移过去

因为 timescaledb数据库 是不断上传数据的 考虑到尽可能的减少数据丢失 考虑用新建表空间的方式来迁移数据，方法如下

一 .建立表空间

首先建立表空间 tbs_cs1 指定路径在新盘上

1. 创建目录

mkdir -p  /u01/cs/pgdata

2.分配权限

[root@localhost ~]# chown postgres:postgres /u01/cs/pgdata

3.创建表空间

create tablespace tbs_cs1 owner postgres location '/u01/cs/pgdata';

二.建立一个新的数据库 cs 指定表空间 tbs_cs1

2.1 建立新库 指定表空间 安装插件timescaledb

CREATE DATABASE cs SET TABLESPACE tbs_cs1 ;
cs=# CREATE SCHEMA hrmw;
cs=# set search_path to hrmw; 
cs=# CREATE EXTENSION timescaledb;

现在建好新库就是考虑数据迁移的问题
我采用的是备份-还原的方式

三 备份原数据库

3.1 备份整个库

我先把timescaledb库全都备份了

/usr/pgsql-11/bin/pg_dump --file "/u01/pgsql.backup" --host "0.0.0.0" --port "5432" --username "postgres" --dbname "原数据库名" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"

3.2 备份超级表

 psql --host "0.0.0.0"  --port "5432" --dbname "原数据库名"  --username "postgres" \
-c "\COPY (SELECT * FROM 超级表名 ｛后面可以跟where 加限制条件｝) TO /u01/data.csv DELIMITER ',' CSV"

有些 超表的数据量过大 有些数据不想要可以用 where加条件 进行过滤

四.还原数据库

4.1 还原全库

数据备份完了 就考虑还原了 我先跟postgres 数据库一样采用pg_restore 进行全库 还原

cs=#  SELECT timescaledb_pre_restore();
pg_restore --username "postgres" --host "1.1.1.1" --port "5432"  --password  --role "postgres" --dbname "cs"    --verbose /u01/pgsql.backup 
cs=# SELECT timescaledb_post_restore();

还原后 我测试发现发现超表出现问题 插不进数据 但是分区能 我上网找了半天没找到办法（哪位大佬知道办法 请留言 或私聊我 谢谢） 但是我发现重新建立超表就没有这个问题了 ！！！
报错如下：

ERROR: invalid INSERT on the root table of hypertable “超表名”
HINT: Make sure the TimescaleDB extension has been preloaded.

4.2 超表单独还原

我采用先还原模式 ——删除超表——还原其余数据 ——新建超表——导入超表数据 的方式

1.先还原模式

pg_restore -s --username "postgres" --host "1.1.1.1" --port "5432"  --password  --role "postgres" --dbname "cs"    --verbose /u01/pgsql.backup 

2.删除原有的超表

drop table "超表名" CASCADE；

3.还原其他表数据

pg_restore -a --username "postgres" --host "1.1.1.1" --port "5432"  --password  --role "postgres" --dbname "cs"    --verbose /u01/pgsql.backup 

4 创建普通表 并把他改为超表

CREATE TABLE b1（"ID" varchar(36) NOT NULL ， "alarm_time" timestamp(6) NOT NULL,）;
SELECT create_hypertable('b1', 'alarm_time', chunk_time_interval => INTERVAL '7 day');
CREATE INDEX "b1_alarm_time_idx" ON "b1" USING btree (
  "create_time"  DESC NULLS LAST
);

5.还原超表数据

psql --host "1.1.1.1"  --port "5432" --dbname "cs"  --username "postgres"   -c "\COPY   表名 FROM  /u01/data.csv CSV"

到这里就完成数据库的还原了 剩下就是切换数据库

五 切换数据库

因为在实际中有好多服务连接着数据库 需求把这些服务都给停掉才行

5.1 停止服务

因为实际中服务不一样 按照实际情况停

5.2 切换数据库

我把原数据库与新建的数据库cs的连接都杀掉了

 postgres=# 
 SELECT
    pg_terminate_backend(pid)
FROM
    pg_stat_activity
WHERE
    -- don't kill my own connection
    pid <> pg_backend_pid()
    -- don't kill the connections to other databases
    AND datname = '原库名'
    ;

有时候会杀不掉 多执行几遍 只到显示结果为0时就可以了

5.3数据库改名

把原库另起他名 ，把新库改名原库名

 postgres=# ALTER DATABASE 原库名 RENAME TO aa;
 postgres=# ALTER DATABASE cs RENAME TO 原库名;

5.4 开启服务

把之前停掉的服务开启

5.5 查看数据库

看看数据库上数了吗？ 上数就ok了