PostgreSQL管理命令

0. 安装：

# wget -q https://www.postgresql.org/media/keys/ACCC4CF8.asc -O - | sudo apt-key add -
# sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ stretch-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'
# apt install postgresql-11
# apt install postgis

1. 系统程序：

createdb       创建一个新的数据库（同SQL：CREATE DATABASE） 
createuser     创建一个新的用户（同SQL：CREATE USER ）
dropdb         删除数据库 
dropuser       删除用户 
pg_dump        将指定数据库导出到一个脚本文件 
pg_dumpall     将所有数据库导出到一个脚本文件 
pg_restore     从一个由pg_dump或pg_dumpall程序导出的脚本文件中恢复数据库 
psql           命令行交互式客户端，如psql [database]
vacuumdb       清理和分析一个PostgreSQL数据库，它是客户端程序psql环境下SQL语句VACUUM的shell脚本封装，二者功能完全相同

pg_ctl         启动、停止、重启（如：pg_ctl start 和 service postgresql start相同） 
pg_controldata 显示PostgreSQL服务的内部控制信息

2. psql 命令

db=# \l  查看系统中现存的数据库 
db=# \q  退出客户端程序psql 
db=# \c  从一个数据库中转到另一个数据库中，如db=# \c sales 从db转到sales 
db=# \dt 查看表列表
db=# \d  查看Object [表,序列等]
db=# \d [表名]  查看表结构 
db=# \di 查看索引

3. 基本数据库操作及DML

安装扩展:  CREATE EXTENSION postgis
创建数据库：create database [数据库名]; 
删除数据库：drop database [数据库名]; 
创建表：   create table 
重命名表： alter table [表名A] rename to [表名B]; 
删除表：   drop table [表名]; 
在已有的表里添加字段： alter table [表名] add column [字段名] [类型]; 
删除字段： alter table [表名] drop column [字段名]; 
修改数据类型：alter table [表名] alter column [字段名] type [类型];
重命名字段： alter table [表名] rename column [字段名A] to [字段名B]; 
给字段设置缺省值： alter table [表名] alter column [字段名] set default [默认值]; 
去除缺省值： alter table [表名] alter column [字段名] drop default;

4. PostgreSQL用户管理

PostgreSQL数据目录中的pg_hba.conf的作用就是用户认证。
有以下几个例子可以看看：

允许在本机上的任何身份连接任何数据库

# TYPE DATABASE USER IP-ADDRESS METHOD 
 local  all      all            trust

允许IP地址为192.168.1.x的任何主机与数据库sales连接

# TYPE DATABASE USER IP-ADDRESS  METHOD 
  host  sales   all  192.168.1.0/32  md5

创建PostgreSQL数据库用户示例

启用客户端程序，并进入template1数据库
psql template1
创建用户
CREATE USER hellen WITH ENCRYPED PASSWORD 'zhenzhen';
修改密码
ALTER USER hellen WITH PASSWORD 'postgres';
因为设置了密码，所以要编辑pg_hba.conf，使用户和配置文件同步。
在原有记录上面添加md5
local all hellen md5
使用新用户登录数据库
template1=# \q
psql -U hellen -d template1
切换用户：
template1=# \!psql -U tk -d template1

设定用户的权限

创建一个用户组：
CREATE GROUP sale;
添加几个用户进入该组
ALTER GROUP sale ADD USER sale1,sale2,sale3;
授予用户级sale针对表employee和products的SELECT权限
GRANT SELECT ON employee,products TO GROUP sale;
在sale中将用户user2删除
ALTER GROUP sale DROP USER sale2;

5. 备份数据库

可以使用pg_dump和pg_dumpall来完成。比如备份sales数据库：
pg_dump sales>/home/tk/pgsql/backup/1.bak

6. 主从配置

MASTER：

psql

CREATE ROLE replica login replication encrypted password 'replica'

vi /etc/postgresql/11/postgresql.conf

wal_level = replica
max_wal_senders=10
wal_keep_segments=256
archive_mode = on
archive_command = 'rsync -a %p postgres@slave:/var/lib/pgsql/11/walarchive%f' 
listen_addresses = '*'

vi /etc/postgresql/11/pg_hda.conf

host    replication     replica         172.59.59.0/24          md5

SLAVE：

mkdir -m 700 /var/lib/pgsql/11/walarchive

service stop postgresql
rm -rf $PGDATA/*
pg_basebackup -h 172.59.59.112  -D /var/lib/postgresql/10/main/ -P -U replicate --wal-method=stream

vi /etc/postgresql/11/postgres.conf

hot_standby = on
hot_standby_feedback=on

vi  $PGDATA/recovery.conf

standby_mode = 'on'
primary_conninfo = 'user=replica password=replica host=172.59.59.112 port=5432'
restore_command = 'cp /var/lib/pgsql/11/walarchive/%f %p'
archive_cleanup_command='/usr/bin/pg_archivecleanup /var/lib/postgresql/11/walarchive %r'
trigger_file = '/var/lib/postgresql/11/main/finish.replication'
recovery_target_timeline = 'latest'