Postgresql-数据库导入导出

Postgresql-导入导出

版本

postgresql 13

备份脚本

#!/bin/sh
DATETIME=$(date +"%Y%m%d%H%M")

cd /pgdata_backup

#pg_dumpall -p 5083 > all.sql
pg_dumpall --exclude-database=common -p 5083 -c --if-exists |gzip > part1-all-exclude-common.sql.gz
pg_dump --dbname=common --exclude-schema=offline_map -p 5083 -c --if-exists|gzip > part2-common-excluede-offline_map.sql.gz

tar -cvf pgbackup_all_${DATETIME}.tar part*.sql.gz --remove-files

/usr/local/bin/aws s3 cp pgbackup_all_${DATETIME}.gz s3://aaa/backup/

find /pgdata_backup -mtime +30 -type f -name "*.gz" | xargs rm -f

数据库导出

pg_dumpall -p 5083 -c --if-exists |gzip > all.sql.gz
pg_dump --dbname=testdb -p 5083 -c --if-exists|gzip > testdb.sql.gz

## 只导结构
pg_dump --dbname=testdb -p 5083 -c --if-exists -s | > testdb.sql.gz

数据库导入

## 整库
psql -f alldb.sql

## 单个数据库
create database testdb;
psql -f testdb.sql -d testdb 

导出到文本

PG_HOST=`grep PG_HOST= ./cfg/system.cfg |awk -F\= '{print $2}'`
PG_PATH=`grep PG_PATH= ./cfg/system.cfg |awk -F\= '{print $2}'`
PG_LD_LIBRARY_PATH=`grep PG_LD_LIBRARY_PATH= ./cfg/system.cfg |awk -F\= '{print $2}'`
export PATH=$PATH:$PG_PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PG_LD_LIBRARY_PATH
export PGPASSWORD=$PASSWORD

psql -h $PG_HOST -U $USERNAME -d $DBNAME <<! 2>> $LOG 1>&2
create temp table tmp_101 (pkeyvalue varchar(200));
insert into tmp_101
select policyno from prpcmain
where startdate>='$BDATE' and startdate<='$EDATE'
and policyno is not null and policyno<>'' and policyno<>' ';
\copy tmp_101 to '$OUT';
!

文本导入

PG_HOST=`grep PG_HOST= ./cfg/system.cfg |awk -F\= '{print $2}'`
PG_PATH=`grep PG_PATH= ./cfg/system.cfg |awk -F\= '{print $2}'`
PG_LD_LIBRARY_PATH=`grep PG_LD_LIBRARY_PATH= ./cfg/system.cfg |awk -F\= '{print $2}'`
export PATH=$PATH:$PG_PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$PG_LD_LIBRARY_PATH
export export PGPASSWORD=$PASSWORD

psql -h $PG_HOST -U $USERNAME -d $DBNAME <<! 2>> $LOG 1>&2
\copy tmp_pkey from '$TMP_OUT1';
!