PostgreSql备份和恢复

介绍

PostgreSQL自带一个客户端pgAdmin，里面有个备份，恢复选项，也能对数据库进行备份 恢复（还原），但最近发现数据库慢慢庞大的时候，经常出错，备份的文件过程中出错的几率那是相当大，手动调节灰常有限。所以一直寻找完美的备份恢复方案。
PostgreSQL内置不少的工具，寻找的备份恢复方案就在其中：pg_dump，psql。这两个指令 在数据库的安装目录下
用法：

备份数据库，指令如下：

pg_dump -h 164.82.233.54 -U postgres databasename > C:\databasename.bak

指令解释：如上命令，pg_dump 是备份数据库指令，164.82.233.54是数据库的ip地址（必须保证数据库允许外部访问的权限哦~），当然本地的数据库ip写 localhost；postgres 是数据库的用户名；databasename 是数据库名。> 意思是导出到C:\databasename.bak文件里，如果没有写路径，单单写databasename.bak文件名，那么备份文件会保存在C: \Program Files\PostgreSQL\9.0\bin 文件夹里。
**恢复数据库，指令如下： **

psql -h localhost -U postgres -d databasename <  C:\databasename.bak(测试没有成功)

pg_restore.exe --host localhost --port 5432 --username "postgres" --dbname "symbolmcnew" --no-password  --verbose "databasename.backup"(测试成功)

指令解释：如上命令，psql是恢复数据库命令，localhost是要恢复到哪个数据库的地址，当然你可以写上ip地址，也就是说能远程恢复（必须保证 数据库允许外部访问的权限哦~）；postgres 就是要恢复到哪个数据库的用户；databasename 是要恢复到哪个数据库。< 的意思是把C:\databasename.bak文件导入到指定的数据库里。

备份：

/opt/PostgreSQL/9.5/bin/pg_dump -h 164.82.233.54 -U postgres databasename > databasename.bak

恢复：

/opt/PostgreSQL/9.5/bin/psql -h localhost -U postgres -d databasename < databasename.bak

有三种不同的备份方法：

• SQL dump
• 文件系统级备份（File system level backup）
• 连续归档（Continuous archiving）

备份
先切换到postgres用户下：

su - postgres

备份：

pg_dumpall > /usr/local/pgsql/backup/pg_all.dmp 

恢复数据库

psql –f /usr/local/pgsql/backup/pg_all.dmp postgres 

查看数据库pg1是否恢复

psql pg1 
pg=# select * from tb1; 

pg_dump [database_name] > /home/postgres/db.dump – 或者 db.sql
导入：

psql [database_name] -U [username] < /home/postgres/db.dump
此时，数据库jck_bjgck下会多一个名称为jck的schema，该schema的owner为jck_bjgck。

使用用户jck_bjgck登录数据库jck_bjgck，重命名schema jck，与其owner的名称一致：

alter schema jck rename to jck_bjgck;

Postgresql的数据迁移

postgresql数据库改变data目录 文档

例:把目录改变至/data中

0.准备工作 停掉 pg server

  service postgresql stop

1.修改配置文件/etc/postgresql/9.3/main/postgresql.conf (记得备份)

data_directory = '/data/postgresql/9.3/main'

2.把默认数据目录的东西拷贝过来

cd /data
mkdir postgresql
cp -r /var/lib/postgresql/* /data/postgresql/

3.修改data_directory的所有者

(记得 -R )
chown -R postgres:postgres /data

4.根据提示修改数据目录权限

 chmod 700 /data 

文件系统级别备份（冷备份）
文件系统级别的备份是冷备份，需要停止数据库。
1，停止数据库

pg_ctl –D /usr/local/pgsql/data stop 

2，备份数据库

tar –jcv –f /usr/local/pgsql/backup/filesystem.tar.bz2 /usr/local/pgsql/data/ 

3，删除/usr/local/pgsql/data/目录

rm –r /usr/local/pgsql/data/ 

4，解压备份文件到原目录

tar –jxv –f /usr/local/pgsql/backup/filesystem.tar.bz2 –C / 

5，启动数据库

pg_ctl –D /usr/local/pgsql/data start 

6，查看数据库pg1是否恢复

psql pg1 
pg=# select * from tb1; 
a
1 
(1 rows) 

7，查看数据库pg2是否恢复

psql pg2 
pg=# select * from tb2; 

postgresql 导入和导出数据

备份某个数据库:

pg_dump -h 主机名 -p 端口 -U 用户名 -W 密码 -f 导出的sql脚本文件名 --column-inserts 数据库名

备份全部数据库:

pg_dumpall -h 主机名 -p 端口 -U 用户名 -W 密码 --column-inserts > 导出的sql脚本文件名

恢复某个数据库:

psql -h 主机名 -p 端口 -U 用户名 -W 密码 -d 数据库名 < pg_dump导出的sql脚本文件名

恢复全部数据库:

psql -h 主机名 -p 端口 -U 用户名 -W 密码 < pg_dumpall导出的sql脚本文件名

postgres pgdump 备份还原
备份

pg_dump -h 127.0.0.1 -U postgres -d testdb  > "C:\Users\honey\Desktop\testdb.bak"

pg_dump -h 127.0.0.1 -U postgres -d testdb -f "C:\Users\honey\Desktop\testdb.bak"

还原

psql -h 127.0.0.1 -U postgres -d testdb < "C:\Users\honey\Desktop\testdb.bak"

psql -h 127.0.0.1 -U postgres -d testdb -f  "C:\Users\honey\Desktop\testdb.bak"

移动article_others中的数据到新的分区表

--清空临时表
delete from tmp_article;
 
--复制需要移动的数据到临时表
insert into tmp_article(aid,style,oaid,fid,bid,cid,tid,url,tm_post,tm_last_rply,author,title,ab_content,rply_cnt,read_cnt,url_hash,hash_plain,guid,
neg_pos,match_code,tm_spider,tm_update,stage,rply_cut,read_cut,src,rfid,labels,kwds,like_cnt,content) select aid,style,oaid,fid,bid,cid,tid,url,tm_post,tm_last_rply,author,title,ab_content,rply_cnt,read_cnt,url_hash,hash_plain,a.guid,
neg_pos,match_code,tm_spider,tm_update,stage,rply_cut,read_cut,src,rfid,labels,kwds,like_cnt,a.content from article_others a where cid=[cid];
--插入数据到新的分区表
insert into article_[cid] select * from tmp_article;
--删除article_others里的数据
delete from article_others where cid=[cid];

导入和导出postgresql数据库脚本

导出

/usr/bin/pg_dump -U postgres testdb > /home/app/testdb`date +%Y%m%d`.sql

cd /home/app

gzip testdb`date +%Y%m%d`.sql

导入

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

drop database testdb;//删除原来的库

CREATE DATABASE testdb;

psql -U postgres -d testdb -f /home/app/testdb${day}.sql

参考链接 ：
PostgreSql备份和恢复 ： https://blog.csdn.net/kaopu/article/details/84616235
原文链接：https://blog.csdn.net/zhangzeyuaaa/article/details/77946976
https://blog.csdn.net/flowerspring/article/details/89917000