记一次 Centos7 Postgresql v11 数据库备份、还原

太过爱你忘了你带给我的痛 2023-02-15 05:27 13阅读 0赞

一、数据库安装

根据自身环境需要选择安装

1、yum 指定目录安装

[https://blog.csdn.net/llwy1428/article/details/105143053][https_blog.csdn.net_llwy1428_article_details_105143053]

2、yum 直接安装

[https://blog.csdn.net/llwy1428/article/details/102486414][https_blog.csdn.net_llwy1428_article_details_102486414]

3、编译安装

[https://blog.csdn.net/llwy1428/article/details/95444151][https_blog.csdn.net_llwy1428_article_details_95444151]

4、PostgreSql 基本操作

[https://blog.csdn.net/llwy1428/article/details/102598732][https_blog.csdn.net_llwy1428_article_details_102598732]

5、Centos7 yum 安装、配置 PgAdmin4

[https://blog.csdn.net/llwy1428/article/details/102486511][https_blog.csdn.net_llwy1428_article_details_102486511]

6、Centos7 PostgreSql 数据库安装扩展

[https://blog.csdn.net/llwy1428/article/details/105167524][https_blog.csdn.net_llwy1428_article_details_105167524]

7、Centos7 PostgreSql 数据库使用FDW扩展

[https://blog.csdn.net/llwy1428/article/details/106291669][https_blog.csdn.net_llwy1428_article_details_106291669]

8、Centos7 postgresql v11 安装时序数据库 TimescaleDB

[https://blog.csdn.net/llwy1428/article/details/106357900][https_blog.csdn.net_llwy1428_article_details_106357900]

二、数据库备份

1、备份本机数据库

把数据库 dbfrom 备份到 /tmp 路径下 命名为 sysdb.backup 文件

/usr/pgsql-11/bin/pg_dump --file "/tmp/sysdb.backup" --host "localhost" --port "5432" --username "postgres" --dbname "dbfrom" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"

2、备份远程数据库

vim ~/.pgpass
    写入内容：
    目标数据库IP:5432:目标数据库:postgres:目标数据库密码

执行备份命令

/usr/pgsql-11/bin/pg_dump --file "/tmp/sysdb.backup" --host "目标数据库IP" --port "5432" --username "postgres" --dbname "目标数据库" --verbose --role "postgres" --format=c --blobs --encoding "UTF8"

三、数据库还原

chmod 755 /tmp/sysdb.backup

切换用户

su - postgres
    psql

创建数据库

CREATE DATABASE dbto;

切换至 postgres 用户下，执行还原命令

su - postgres
    pg_restore --username "postgres" --no-password --role "postgres" --dbname "dbto" --verbose /tmp/sysdb.backup

Postgresql v11 数据库备份、还原操作完毕！

[https_blog.csdn.net_llwy1428_article_details_105143053]: https://blog.csdn.net/llwy1428/article/details/105143053
[https_blog.csdn.net_llwy1428_article_details_102486414]: https://blog.csdn.net/llwy1428/article/details/102486414
[https_blog.csdn.net_llwy1428_article_details_95444151]: https://blog.csdn.net/llwy1428/article/details/95444151
[https_blog.csdn.net_llwy1428_article_details_102598732]: https://blog.csdn.net/llwy1428/article/details/102598732
[https_blog.csdn.net_llwy1428_article_details_102486511]: https://blog.csdn.net/llwy1428/article/details/102486511
[https_blog.csdn.net_llwy1428_article_details_105167524]: https://blog.csdn.net/llwy1428/article/details/105167524
[https_blog.csdn.net_llwy1428_article_details_106291669]: https://blog.csdn.net/llwy1428/article/details/106291669
[https_blog.csdn.net_llwy1428_article_details_106357900]: https://blog.csdn.net/llwy1428/article/details/106357900