首页 > 数据库技术 > 详细

psql 数据表导入导出

时间:2021-07-27 09:42:52      阅读:25      评论:0      收藏:0      [点我收藏+]

 

 

 

$ createdb old_cms
$ psql old_cms

$ pg_restore --verbose --clean --no-acl --no-owner -h localhost -U aaa -d old_cms ~/Downloads/old_cms.dump

# select * from pg_tables;
# select * from "BlogPost";
# select count(*) from "BlogPost";
# \d "BlogPost";
# select * from "BlogPost" where ‘FeaturedImageCaption‘ != ‘‘;

get the password by:
#sudo docker inspect cms | grep \"SS_DATABASE

#pg_dump -h preproduction-aaa.amazonaws.com -U ss -Fc ss > PG_DUMP_21_JULY_2021.dump -T *_versions

#scp ubuntu@preproduction-aaat.com:/home/ubuntu/PG_DUMP_21_JULY_2021.dump ~/Downloads/

scp ubuntu@preproduction-aaa.com:/home/ubuntu/PG_DUMP_21_JULY_2021.dump ~/Downloads/


导出整个表结构
pg_dump --host localhost -U aaa --file BlogCategory.dump --table "public.\"BlogCategory\"" "old_cms"

导入
psql -U aaa -d preprod-cms < BlogCategory.dump


导出表中数据数据 (不带header的方法)
COPY (select * from "BlogCategory") TO ‘/Users/aaa/Downloads/BlogCategory.csv‘ WITH csv;
导入 (不带header的csv数据)
COPY "BlogCategory" FROM ‘/Users/aaa/Downloads/BlogCategory.csv‘ WITH csv;


# DELETE FROM "BlogCategory" where "ID" > 1;

$ scp ~/Downloads/BlogCategory.csv ubuntu@preproduction-aaa.com:/home/ubuntu/

$ psql -h preproduction-aaa.amazonaws.com -U ss -Fc ss

 

真正导入的时候发现前面要加一个反斜线 \

 

# \copy "BlogCategory" FROM ‘/home/ubuntu/BlogCategory.csv‘ with csv;
由于csv文件改变了日期的格式,需要先run一下这行命令
# SET datestyle = dmy;

 

 

导出带header的csv文件

COPY (select * from "Blog_Live") TO ‘/Users/aaa/Downloads/Blog_Live.csv‘ WITH csv header;

拷贝到远程服务器
scp ~/Downloads/Blog_Live.csv ubuntu@preproduction-aaa.com:/home/ubuntu/

链接到远程服务器:

ssh ubuntu@preproduction-aaa.com
sudo docker inspect cms | grep \"SS_DATABASE

登录到服务器的数据库

psql -h preproduction-aaa.amazonaws.com -U ss -Fc ss

导入带header的csv文件

\copy "Blog_Live" FROM ‘/home/ubuntu/Blog_Live.csv‘ with csv header;

psql 数据表导入导出

原文:https://www.cnblogs.com/iwangzheng/p/15064034.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!