The Go Blog

PostgreSQL 入门

bantana
16 March 2015

Install

Mac

brew install postgresql

Raspberry Pi

sudo apt-get install postgresql

centos 7

sudo yum install https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
sudo yum -y install postgresql96
sudo yum -y install postgresql96-server

Mac 开发环境

初始化一个postgresql instance:

mkdir -p $HOME/dbs
cd $HOME/dbs
initdb aozsky

启动这个instance:

postgres -D aozsky

or

pg_ctl -D aozsky -l aozsky.log start

查看启动的instance情况:

14:48 $ psql -l
                              List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
-----------+---------+----------+-------------+-------------+---------------------
 postgres  | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/bantana         +
           |         |          |             |             | bantana=CTc/bantana
 template1 | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/bantana         +
           |         |          |             |             | bantana=CTc/bantana
(3 rows)

建立一个database demodb:

14:48 $ createdb demodb

14:50 $ psql -l
                                List of databases
   Name    |  Owner  | Encoding |   Collate   |    Ctype    |  Access privileges
-----------+---------+----------+-------------+-------------+---------------------
 demodb    | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/bantana         +
           |         |          |             |             | bantana=CTc/bantana
 template1 | bantana | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/bantana         +
           |         |          |             |             | bantana=CTc/bantana
(4 rows)

连接demodb:

14:51 $ psql demodb
psql (9.4.4)
Type "help" for help.

demodb=#

然后可以跳过User授权部分直接到后面的数据库操作部分继续。

User

默认安装会建立一个postgres的pam用户,

pi@raspberrypi ~ $ id postgres
uid=107(postgres) gid=111(postgres) groups=111(postgres),110(ssl-cert)

同时还会初始化一个postgres的database和一个postgres的db role。

后面会使用这个用户来建立其他用户和数据库,包括授权等操作;

start postgreSQL

pi@raspberrypi:/etc/init.d$ sudo /etc/init.d/postgresql status
Running clusters: 9.1/main

config file

pi@raspberrypi ~ $ tree /etc/postgresql/9.1/main/
/etc/postgresql/9.1/main/
├── environment
├── pg_ctl.conf
├── pg_hba.conf
├── pg_ident.conf
├── postgresql.conf
└── start.conf

0 directories, 6 files

默认postgresql.conf配置监听'localhost';

59 #listen_addresses = 'localhost'         # what IP address(es) to listen on;
60 listen_addresses = '*'          # what IP address(es) to listen on;
61                                         # comma-separated list of addresses;
62                                         # defaults to 'localhost', '*' = all
63                                         # (change requires restart)
64 port = 5432                             # (change requires restart)
65 max_connections = 100                   # (change requires restart)

除了上面的监听地址外,还需要设置系统网络访问授权pg_hba.conf:

# host       DATABASE  USER  ADDRESS  METHOD  [OPTIONS]
host    all             all     0.0.0.0/0               md5

上述意思表示授权all User可访问all DATABASE,通过0.0.0.0/0的ip,通过host方式访问,要求role的md5密码。

除了上述网络系统授权还有postgreSQL的role限制:

role 授权

下面建立一个pi的role来操作postgresql数据库pi;

使用postgres用户操作

pi@raspberrypi ~ $ sudo su - postgres
postgres@raspberrypi:~$ psql
psql (9.1.15)
Type "help" for help.

postgres=#

postgres=# CREATE USER pi WITH PASSWORD 'rolePiPassword';

postgres=# ALTER USER pi CREATEDB;

建立数据库

postgres=# CREATE DATABASE pi;

数据库操作

psql连接数据库

$ psql -h 10.8.1.35 -U pi -d pi

创建新表

pi=# CREATE TABLE user_tbl(name VARCHAR(20), signup_date DATE);
CREATE TABLE
pi=# \d
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | user_tbl | table | pi
(1 row)

Insert table

pi=# INSERT INTO user_tbl(name, signup_date) VALUES('罗海虹', '2015-03-16');
INSERT 0 1
pi=# \d
         List of relations
 Schema |   Name   | Type  | Owner
--------+----------+-------+-------
 public | user_tbl | table | pi
(1 row)

Select Operator

pi=# SELECT * FROM user_tbl;
  name  | signup_date
--------+-------------
 罗海虹 | 2015-03-16
(1 row)

Update Operator

pi=# UPDATE user_tbl set name = '罗海虹' WHERE name = '罗海红';
UPDATE 0

Delete Operator

pi=# INSERT INTO user_tbl(name, signup_date) VALUES('柴春洋', '2015-03-16');
pi=# INSERT INTO user_tbl(name, signup_date) VALUES('黄紫萱', '2015-03-16');

pi=# DELETE FROM user_tbl WHERE name = '黄紫萱' ;
DELETE 1

添加字段

pi=# ALTER TABLE user_tbl ADD email VARCHAR(40);
ALTER TABLE

更新结构

pi=# ALTER TABLE user_tbl ALTER COLUMN signup_date SET NOT NULL;
ALTER TABLE

更名栏位

pi=# ALTER TABLE user_tbl RENAME COLUMN signup_date TO signup;
ALTER TABLE

删除栏位

pi=# ALTER TABLE user_tbl DROP COLUMN email;
ALTER TABLE

表格更名

pi=# ALTER TABLE user_tbl RENAME TO backup_tbl;
ALTER TABLE

删除表格

pi=# DROP TABLE IF EXISTS backup_tbl;
DROP TABLE

默认字符集和用户环境有关,也可以单独指定

/usr/pgsql-10/bin/initdb --locale en_US.UTF-8 -E UTF8
createdb -E UTF8 -l en_US.UTF-8

Related articles