# pg_dump -h localhost -U postgres -b postgres > C:\Users\Administrator\Desktop\databaseBackup\public.gz
# psql -h localhost -U postgres -d sjdata <C:\Users\Administrator\Desktop\databaseBackup\public.gz
以上方式,如果需要设置不输入密码:
设置环境变量:PGPASSWORD
其他环境变量(export为linux,windows直接设置环境变量):
-
export PGHOST='localhost'
-
export PGUSER=postgres
-
export PGDATABASE=postgres
-
export PGPASSWORD=null
-
export PGPORT=5432
其他备份输入方式:
1、备份
pg_dump -b -c -T "sj_basedata*|temp*" "host=127.0.0.1 port=5432 user=postgres password=postgres dbname=postgres" >"C:\Users\Administrator\Desktop\databaseBackup\public.gz"(-T:例外的表(不导出),通过正则表达式实现;-t:需要导入的表,设置于-T一致;-c:数据库存在的情况下先删除在导入,会覆盖)
2、恢复
psql "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=postgres password=postgres dbname=sjdata" <C:\Users\Administrator\Desktop\databaseBackup\public.gz
3、linux下:
可以设置大数据库分块
pg_dump -h 127.0.0.1 -U postgres -j 50 -F d postgres -f /opt/databasebackup
全库备份:
pg_dump --file "D:\\temp\\gp0808.BAC" --dbname "host=192.168.1.220 port=2345 user=gpadmin password=gpadmin dbname=gpdw sslmode='disable'" --clean --verbose --encoding "UTF8" --format c --create --exclude-schema "gp_toolkit"
还原:
pg_restore --dbname "host=192.168.1.220 port=2345 user=gpadmin password=gpadmin dbname=gpdw1 sslmode='disable'" < "D:\\temp\\gp0808.BAC"
查看表占用页面数:
--清除(Vacuum Full和Vacuum最大的不同就是,Vacuum Full是物理删除dead tuples,并把释放的空间重新交给操作系统,所以在vacuum full后,表的大小会减小为实际的空间大小)
vacuum full test_a;
--查看
select relname,relfilenode,relpages from pg_class where relname='test_a' or relname='test_a_idx';
删除数据库被占用时,可以调用如下,清除占用
select pg_terminate_backend(pid) from pg_stat_activity where datname='gpdw2';