psql 資料表導入導出

$ 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 [email protected]:/home/ubuntu/PG_DUMP_21_JULY_2021.dump ~/Downloads/

scp [email protected]:/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 [email protected]:/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 [email protected]:/home/ubuntu/

連結到遠端伺服器：

ssh [email protected]

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;