PostgreSQL的一些使用技巧
一:导出schemal
导出heros_game库 表 player 的schemal
pg_dump -h 10.3.1.10 -p 5432 -U aoagm -d heros_game -t player -s -f player.sql
二:导出导入EXCEL CSV
导出EXCEL CSV格式文件
导出
psql -h 10.3.1.10 -p 5432 -U aoagm -d heros_game -o ./test2.csv -c "COPY (select * from inventory where player_id = 2000006285) to stdout csv header"
导入
heros_game=# COPY player from '/root/ld.csv' with csv header;
带密码的方式导出表数据
psql -o ./player.csv -c "COPY (select * from player where player_id = 2000006285) to stdout csv header;" "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=lduser password=ld2018 dbname=heros_game"
带密码的导入csv数据
psql -c "COPY player from '/tmp/player.csv' with csv header;" "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=lduser password=ld2018 dbname=heros_game"
导出csv数据
psql -o ./player.csv -c "COPY (select * from player where player_id = 2000006285) to stdout csv header;" "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=lduser password=ld2018 dbname=heros_game"
导入csv数据
psql -c "COPY player from '/tmp/player.csv' with csv header;" "host=127.0.0.1 hostaddr=127.0.0.1 port=5432 user=lduser password=ld2018 dbname=heros_game"
三:创建指定账户
sudo -u postgres createuser --superuser lduser
sudo -u postgres createdb -O lduser ldpsql
创建heros_game数据库,属于lduser
sudo -u postgres createdb -O lduser heros_game
sudo -u postgres psql
\password lduser
#lddb2018
psql -U lduser -d ldpsql -h 127.0.0.1
psql -U lduser -d heros_game -h 127.0.0.1
#ld2018
psql -U aoagm -h 127.0.0.1 -d gm_data -f game_data.sql
登录
psql -U postgres -h cp-kards.csd3yjynselh.rds.cn-north-1.amazonaws.com.cn
四:PostgreSQL导入导出数据
命令行导出
pg_dump:将一个PostgreSQL数据库转储到一个脚本文件或者其它归档文件中
pg_dump mydb > db.sql:将mydb数据库转储到一个 SQL 脚本文件
pg_dump mydb -s > db.sql:将mydb数据库转储到一个 SQL 脚本文件(只导出数据库表结构,不带数据)
pg_dump -D -a -t zones_seq - t interway -t table_3 ... > /tmp/zones_seq.sql:导出部分表,支持通配符,见参考文章1
pg_dumpall:将一个PostgreSQL数据库集群转储到一个脚本文件中
命令行导入
psql:psql -U gpadmin -d your-db -f your-table.sql
pg_restore: pg_restore -d your-db your-table.tar
pg_dump "host=10.3.1.11 hostaddr=10.3.1.11 port=5432 user=aoagm password=aoa#psql dbname=gm_data" > gm_data.sql
本机导出数据库:
pg_dump "host=127.0.0.1 hostaddr=127.0.0.1 port=50009 user=aoagm password=aoa#psql dbname=heros_game" > heros_game.sql
导入数据库:
psql "host=127.0.0.1 hostaddr=127.0.0.1 port=50009 user=aoagm password=aoa#psql dbname=heros_game" < heros_game.sql
五:PostgreSQL备份与恢复示例
(一)pg_dump
1,创建数据库
create database pg;
2,连入数据库pg
psql pg
3,创建测试表,插入数据
pg=# create table pg_test(a int);
pg=# insert into pg_test(a) values(1);
pg=# insert into pg_test(a) values(2);
备份文件:
pg_dump pg > ./pg.dmp
恢复文件:
psql pg2
需要先创建 数据库
1.显示所有的数据库
\l
2.切换数据库
\c pg
3.显示所有的表
\dt
4.查看表结构
heros_user=> \d logic_server
5.插入数据
6.查询数据 (单引号)
select * from player where wechat_id = 'okx-Es20KB0wgY4DF03Fz_SZAOpI';