Postgres DBA 入门学习第一篇

主要内容有：

dba入门学习：数据库创建、用户创建、用户授权、远程连接、创建表、表数据导出

用户创建        

postgres=# create user test1 password 'test1';
CREATE ROLE

创建数据库

postgres=# create database test1;
CREATE DATABASE

用户授权

postgres=# grant all on database test1 to test1;
GRANT

pg_hba.conf修改

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
host    testdb             test             192.168.81.130/24           md5
host    test1             test1             192.168.81.130/24           md5

客户端登录时如提示psql: FATAL:  no pg_hba.conf entry for host失败如图：

[hadoop@hadoop ~]$ psql -h 192.168.81.130 -U test1 test1
psql: FATAL:  no pg_hba.conf entry for host "192.168.81.130", user "test1", database "test1"

在服务端用管理员执行如下命令重新加载配置文件：

postgres=# select pg_reload_conf();
 pg_reload_conf 
----------------
 t
(1 row)

在此在客户端登录连接数据库

第一种交互式登录，登录过程中输入密码

[hadoop@hadoop ~]$ psql -h 192.168.81.130 -U test1 test1
Password for user test1: 
psql (11.12)
Type "help" for help.

test1=> 

或者先把密码输出环境变量，再执行连接，如下：

[postgres@hadoop ~]$ export PGPASSWORD=test1
[postgres@hadoop ~]$ psql -h 192.168.81.130 -U test1 test1
psql (11.12)
Type "help" for help.

test1=> 

登录之后就可以创建表并入数据：

test1=> create table users (id int,username varchar(30),addr varchar(100));
test1=> \d
       List of relations
 Schema | Name  | Type  | Owner 
--------+-------+-------+-------
 public | users | table | test1
(1 row)

test1=> insert into users select generate_series(1,1000),'testuser','testaddr';
INSERT 0 1000
test1=> 

数据导出txt文件

test1=> \timing
Timing is on.
test1=> \copy users to '/home/hadoop/users.txt' delimiter '|';
COPY 1000
Time: 5.102 ms
test1=> 

导数数据文件时，分割符号只能用单个字符或者16进制描述的分隔符，分隔符以16进制描述导出如下：

test1=> \copy users to '/home/hadoop/users.txt' delimiter e'\x03';
COPY 1000
Time: 12.015 ms