Postgresql 基本操作

初始化：

/usr/pgsql-9.6/bin/postgresql96-setup initdb

默认配置：

/var/lib/pgsql/9.6/data

控制台：psql

\\password postgres   #修改密码
CREATE USER dbuser WITH PASSWORD 'password';   #创建用户
CREATE DATABASE exampledb OWNER dbuser;    #创建库
GRANT ALL PRIVILEGES ON DATABASE exampledb to dbuser;    #授权
\\q
CREATE USER fg WITH PASSWORD 'ue7wgakzo3'; 
CREATE DATABASE fg OWNER fg;
GRANT ALL PRIVILEGES ON DATABASE fg to fg;

授权：

GRANT ALL ON SCHEMA pt_1226 to pt;
GRANT ALL ON ALL SEQUENCES IN SCHEMA pt_1226 to pt;
GRANT ALL ON ALL TABLES IN SCHEMA pt_statistics to pt;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA pt_statistics to pt;

修改所有者：

alter schema pt_18 owner to pt;
alter TABLE pt_18.game_log_10 owner TO pt;
alter SEQUENCE public.white_ip_id_seq  owner TO pt;
alter FUNCTION public.validate_user()  owner TO pt;

登录：

psql -U dbuser -d exampledb -h 127.0.0.1 -p 5432

* \\h：查看SQL命令的解释，比如\\h select。
* \\?：查看psql命令列表。
* \\l：列出所有数据库。
* \\c [database_name]：连接其他数据库。
* \\d：列出当前数据库的所有表格。
* \\d [table_name]：列出某一张表格的结构。
* \\du：列出所有用户。
* \\e：打开文本编辑器。
* \\conninfo：列出当前数据库和连接的信息。
*\\dnS 或者 SELECT nspname FROM pg_namespace;  查所有的schema

# 创建新表
CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
# 插入数据
INSERT INTO user_tbl(name, signup_date) VALUES('张三', '2013-12-22');
# 选择记录
SELECT * FROM user_tbl;
# 更新数据
UPDATE user_tbl set name = '李四' WHERE name = '张三';
# 删除记录
DELETE FROM user_tbl WHERE name = '李四' ;
# 添加栏位
ALTER TABLE user_tbl ADD email VARCHAR(40);
# 更新结构
ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
# 更名栏位
ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
# 删除栏位
ALTER TABLE user_tbl DROP COLUMN email;
# 表格更名      
ALTER TABLE user_tbl RENAME TO backup_tbl;
# 删除表格
DROP TABLE IF EXISTS backup_tbl;
# 删除schema
DROP SCHEMA mystuff CASCADE;
# 删库
DROP DATABASE fg_prod;

postgresql查看数据库占用的物理存储空间大小

SELECT datname,oid from pg_database;  #查询每个库的oid，然后读取文件夹大小
select pg_database.datname, pg_size_pretty(pg_database_size(pg_database.datname)) AS size from pg_database;  #查所有库大小
select schemaname,relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables order by pg_relation_size(relid) desc;
所有schema的所有表大小
select pg_size_pretty(pg_database_size('postgres'));  #查单库大小

导出：

pg_dump -U postgres -h 192.168.200.2  ca > ~/test.bak

导入：

psql testdb <  test.bak
psql -Ufg -h10.200.77.25 -d fg </tmp/test-fg.sql

其它：

#带压缩(-s 表结构，-a 数据，不带 表结构和数据)
pg_dump -Fc -a -t public.game_data_total -t public.game_details -t public.gametype -t public.gamelist -f 123.sql fg
#不带压缩（表结构和数据，-c 带删除）
pg_dump -c -t public.game_data_total -t public.game_details -t public.gametype -t public.gamelist -f 1234.sql fg
#还原（带压缩的文件）
pg_restore -d fg 1234.sql
#还原（文本）
psql -d fg<1234.sql

导出表结构与模式的部分数据
pg_dump -Fc -s -f 123.sql fg
pg_dump -Fc -a -T public.t_cash_flow -T public.player_chips -T public.player -T public.userinfo -f DATA.sql -d fg -n public
md5-de4989a30f6b85a79f1d2e70a333f391
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = true
log_rotation_age = 1d
log_rotation_size = 500MB
md5-0c329470493f1f3f3a2d9c04c001ec77
#日循环
log_filename = 'postgresql-%H.log'
log_rotation_age = 60