PostgreSQL 简单使用

0x00 安装

➜  brew install postgresql
➜  echo 'export PATH="/usr/local/Cellar/postgresql/11.4/bin:$PATH"' >> ~/.zshrc
➜  source ~/.zshrc
-- 初始化数据库
➜  initdb /usr/local/var/postgres
➜  pg_ctl -D /usr/local/var/postgres -l /usr/local/var/log/postgres.log start

0x01 简单操作

  • 常用操作命令
reber=# \password #设置当前登录用户的密码
reber=# \password [user_name] #设置其他用户的密码
reber=# \l #列出所有数据库
reber=# \du #列出所有用户
reber=# \c [database_name] #连接数据库
reber=# \d #列出当前数据库的所有表
reber=# \d [table_name] #列出表结构
reber=# \conninfo #列出当前数据库和连接的信息
  • 连接数据库
➜  psql postgres #初始化数据库后会生成默认的数据库 postgres
psql (11.4)
Type "help" for help.

postgres=# \l #查看当前数据库情况
                              List of databases
   Name    | Owner | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-------+----------+-------------+-------------+-------------------
 postgres  | reber | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | reber | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber         +
           |       |          |             |             | reber=CTc/reber
 template1 | reber | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber         +
           |       |          |             |             | reber=CTc/reber
(4 rows)

postgres=# \du #查看当前用户
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 reber     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \c
You are now connected to database "postgres" as user "reber".
postgres=# \password
Enter new password:
Enter it again:
postgres-# \q
  • 修改配置文件改端口

vim /usr/local/var/postgres/postgresql.conf

port = 55432
  • 修改配置文件使密码生效(METHOD 改为 md5)

vim /usr/local/var/postgres/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     md5
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

然后重启 pg_ctl -D /usr/local/var/postgres -l /usr/local/var/log/postgres.log restart

再次登录就会要求输入密码

➜  psql -d postgres -p 55432
Password for user reber:
psql (12.3)
Type "help" for help.

postgres=#
  • 创建新用户
postgres=# create user reber0ask with password '3eRa1kBg95D7';
CREATE ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 reber     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 reber0ask |                                                            | {}

postgres=#
  • 为新用户创建新数据库
postgres=# create database rscan owner reber0ask;
CREATE DATABASE
postgres=# \l
                                List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-----------+----------+-------------+-------------+-------------------
 postgres  | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 rscan     | reber0ask | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber         +
           |           |          |             |             | reber=CTc/reber
 template1 | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber         +
           |           |          |             |             | reber=CTc/reber
(4 rows)

postgres=#
  • 将新数据库的所有权限都给用户
postgres=# grant all privileges on database rscan to reber0ask;
GRANT
postgres=# \l
 postgres  | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 rscan     | reber0ask | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =Tc/reber0ask          +
           |           |          |             |             | reber0ask=CTc/reber0ask
 template0 | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber               +
           |           |          |             |             | reber=CTc/reber
 template1 | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber               +
           |           |          |             |             | reber=CTc/reber

postgres=#
  • 给用户创建数据库的属性
postgres=# alter role reber0ask createdb;
ALTER ROLE
postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 reber     | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 reber0ask | Create DB                                                  | {}

postgres=#

0x02 以指定用户登陆并创建表

➜  psql -d rscan -U reber0ask -h 127.0.0.1 -p 55432
Password for user reber0ask:
psql (11.4)
Type "help" for help.

rscan=> \l
                                List of databases
   Name    |   Owner   | Encoding |   Collate   |    Ctype    | Access privileges
-----------+-----------+----------+-------------+-------------+-------------------
 postgres  | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 rscan     | reber0ask | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 |
 template0 | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber         +
           |           |          |             |             | reber=CTc/reber
 template1 | reber     | UTF8     | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/reber         +
           |           |          |             |             | reber=CTc/reber
(4 rows)

rscan=> create table msg(
rscan(> id serial primary key,
rscan(> title character varying(100),
rscan(> content character varying(2014)
rscan(> );
CREATE TABLE
rscan=> \d
             List of relations
 Schema |    Name    |   Type   |   Owner
--------+------------+----------+-----------
 public | msg        | table    | reber0ask
 public | msg_id_seq | sequence | reber0ask
(2 rows)

rscan=> \d msg;
                                     Table "public.msg"
 Column  |          Type           | Collation | Nullable |             Default
---------+-------------------------+-----------+----------+---------------------------------
 id      | integer                 |           | not null | nextval('msg_id_seq'::regclass)
 title   | character varying(100)  |           |          |
 content | character varying(2014) |           |          |
Indexes:
    "msg_pkey" PRIMARY KEY, btree (id)

rscan=> insert into public.msg(title,content) values('hi','hello world!');
INSERT 0 1
rscan=> select * from msg;
 id | title |   content
----+-------+--------------
  1 | hi    | hello world!
(1 row)

0x03 导入与导出

  • 导出
➜  pg_dump -d rscan -U reber0ask -h 127.0.0.1 -p 55432  > rscan.sql
Password:
➜  ls -al rscan.sql
-rw-r--r--@ 1 reber  staff  15045  5 16 20:54 rscan.sql
  • 导入
➜  psql -d rscan -U reber0ask -h 127.0.0.1 -p 55432
Password for user reber0ask:
psql (12.3)
Type "help" for help.

rscan=> \c postgres
You are now connected to database "postgres" as user "reber0ask".
postgres=> drop database rscan;
ERROR:  database "rscan" is being accessed by other users
DETAIL:  There is 1 other session using the database.
postgres=> revoke connect on database rscan from public;
REVOKE
postgres=> select pg_terminate_backend(pg_stat_activity.pid)
postgres-> from pg_stat_activity
postgres-> where pg_stat_activity.datname = 'rscan';
 pg_terminate_backend
----------------------
 t
(1 row)

postgres=> drop database rscan;
DROP DATABASE

postgres=> create database rscan;
CREATE DATABASE

postgres=> \q
➜  psql -d rscan -U reber0ask -h 127.0.0.1 -p 55432 -f rscan.sql

0x04 允许远程连接数据库

➜  vim  /usr/local/var/postgres/postgresql.conf
listen_addresses = '*'
➜  vim  /usr/local/var/postgres/pg_hba.conf
# TYPE  DATABASE  USER  CIDR-ADDRESS  METHOD
host  all  all 0.0.0.0/0 md5

0x05 python 简单连接

#!/usr/bin/env python
# -*- coding: utf-8 -*-
'''
@Author: reber
@Mail: reber0ask@qq.com
@Date: 2019-08-23 15:45:21
@LastEditTime: 2019-08-23 16:22:02
'''

import psycopg2
import contextlib
from urllib.parse import urlparse

class PgSQLX(object):
    def __init__(self, pgsql_uri):
        super(PgSQLX, self).__init__()
        self.pgsql_uri = pgsql_uri
        self.pgsql_info = self.pgsql_parse_uri()

    def pgsql_parse_uri(self):
        o = urlparse(self.pgsql_uri)
        host = o.hostname
        port = o.port
        user = o.username
        password = o.password
        database = o.path[1:]

        return {
            'host': host,
            'port': port,
            'user': user,
            'password': password,
            'database': database,
        }

    @contextlib.contextmanager
    def init(self):
        dbconn = psycopg2.connect(**self.pgsql_info)
        cursor = dbconn.cursor()
        # dbconn = psycopg2.connect(
        #     host = self.pgsql_info.get('host'),
        #     port = self.pgsql_info.get('port'),
        #     user = self.pgsql_info.get('user'),
        #     password = self.pgsql_info.get('password'),
        #     database = self.pgsql_info.get('database'),
        #     )
        # cursor = dbconn.cursor()

        try:
            yield cursor #这里就是with返回的
        finally:
            dbconn.commit()
            cursor.close()
            dbconn.close()

    def query(self, sql, arg=''):
        try:
            with self.init() as cursor:
                if arg:
                    cursor.execute(sql,arg) #返回受影响行数
                else:
                    cursor.execute(sql)
                result = cursor.fetchall() #返回数据格式是[(),()]
                # result = cursor.fetchone() #返回数据格式是()
                return result
        except Exception as e:
            print(sql,str(e))


if __name__ == '__main__':
    pgsql_uri = "postgresql://reber0ask:3eRa1kBg95D7@192.168.3.19:55432/rscan"
    sqlconn = PgSQLX(pgsql_uri)
    result = sqlconn.query("select * from msg where id=%s",(id,))
    print(result)
若未作声明则文章版权归本人(@reber)所有,转载请注明原文链接:

可以返回顶部