pgsql 备份表结构数据

-- 备份表结构
pg_dump -h ${host} -p ${port} -d ${dbname} -U ${username} -s > /data/${dbname}.sql

-- 只备份数据 -a ，只备份结构 -s，表结构和数据 什么都不加

-- 还原库
psql -h ${host} -p ${port} -U ${username} -d ${dbname} < /data/${dbname}.sql

-- 备份指定表(包含数据)
pg_dump -h ${host} -p ${port} -d ${dbname} -t ${tablename1} -t ${tablename2}  -U ${username} > /data/${filename}.sql

-- 导出导入数据到txt

copy ${tablename} to '/data/pgdata/csvbak/xxx.txt'

copy ${tablename} from '/data/pgdata/csvbak/xxx.txt'

csv:

\copy (select * from ${tablename}) to '/data/pgdata/csvbak/xxx.csv' with (FORMAT csv,DELIMITER ';' ,header true,quote '''',encoding 'gbk')

\copy ${tablename}(${field}, ${field}, ${field}) from '/data/pgdata/csvbak/xxx.csv' with (FORMAT csv,DELIMITER ';',header true,quote '''',encoding 'gbk');

-- 现在很多数据库等都是放在docker，docker中备份到宿主机

docker ps 查看容器

--导出

docker exec -it ${CONTAINER ID} pg_dump -h ${host} -p ${port} -d ${dbname} -U ${username} -s > /data/${dbname}.sql

此处端口为容器中数据库端口，不是宿主机端口

-- 导入

docker exec -it ${CONTAINER ID} psql -h ${host} -p ${port} -U ${username}

create database ${dbname};

exit 退不出来用 \q

docker exec -i ${CONTAINER ID} psql -h ${host} -p ${port} -U ${username} -d ${dbname} < /data/${dbname}.sql

另一种写法

docker exec -i ${CONTAINER ID} psql "host=${host} port=${port} user=${username} dbname=${dbname} password=${password}" < /data/init/${xx}.sql

#替换备份文件schema
sed -i "s/dtc_multi/${platschema}/g" /data/init/platform.sql

查看docker映射文件

docker inspect container_name | grep Mounts -A 20

进入docker命令模式执行sql

docker exec -it ${CONTAINER ID} /bin/bash

psql -h ${host} -p ${port} -U ${username} -d ${dbname} < /映射文件/init_busi_data.sql （文件要先放在source里）