PostgreSQL

PostgreSQL是一个功能强大的开源对象关系数据库管理系统(ORDBMS)。 用于安全地存储数据; 支持最佳做法，并允许在处理请求时检索它们。
特点：

• PostgreSQL可在所有主要操作系统(即Linux，UNIX和Windows等)上运行；
• PostgreSQL支持文本，图像，声音和视频，并包括用于C/C++，Java，Perl，Python，Ruby，Tcl和开放数据库连接(ODBC)的编程接口；
• PostgreSQL支持SQL的许多功能；
• 在PostgreSQL中，表可以设置为从“父”表继承其特征。
  可以安装多个扩展以向PostgreSQL添加附加功能。

安装与设置

安装：

sudo apt-get install postgresql-10

这创建了一个名为postgres的数据库和名为postgres的数据库系统用户。同时默认创建一个linux系统用户postgres，并且没有设置密码

[email protected]:~$ sudo cat /etc/shadow
root:!:17822：
daemon:*:17737:
ulysses:$6$or/1T15bg4hDlm
postgres:$6$QkGHle
epmd:*:1783

为这个新的linux系统用户设置密码：

[email protected]:/home/ulysses# passwd postgres
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully

将postgres添加到sudoer列表中，否则无法从postgres切换到其他用户。在root用户，下添加sudoers文件的写权限：

[email protected]:/home/ulysses# chmod u+w /etc/sudoers
[email protected]:/home/ulysses# vim /etc/sudoers

添加postgres用户：

# User privilege specification
root    ALL=(ALL:ALL) ALL
postgres  ALL=(ALL) ALL

切换到postgres用户，并使用psql命令进入数据库：

[email protected]:/home/ulysses# su postgres
[email protected]:/home/ulysses$ psql
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
Type "help" for help.

postgres=# 

这相当于使用系统用户postgres以同名数据库用户的身份，登录数据库，这是不用输入数据库密码的。
可以修改数据库用户postgres的密码：

postgres=# ALTER USER postgres WITH PASSWORD '<***password***>';
ALTER ROLE
postgres=# \q
[email protected]:/home/ulysses$ 

数据库管理

创建一个linux系统用户dbuser，用这个用户来管理ubuntu上安装的数据库:

[email protected]:/home/ulysses# adduser dbuser
Adding user `dbuser' ...
Adding new group `dbuser' (1003) ...
Adding new user `dbuser' (1001) with group `dbuser' ...
Creating home directory `/home/dbuser' ...
Copying files from `/etc/skel' ...
Enter new UNIX password: 
Retype new UNIX password: 
passwd: password updated successfully
Changing the user information for dbuser
Enter the new value, or press ENTER for the default
	Full Name []: 
	Room Number []: 
	Work Phone []: 
	Home Phone []: 
	Other []: 
Is the information correct? [Y/n] y
[email protected]:/home/ulysses# su dbuser
[email protected]:/home/ulysses$ su ulysses
Password: 
[email protected]:~$ 

进入postgres数据库，创建一个数据库用户dbuser，并创建用户数据库exampledb，并指定所有者为dbuser：

postgres=# create user dbuser with password '****';
CREATE ROLE
postgres=# create database exampledb owner dbuser;
CREATE DATABASE

删除数据库DROP DATABASE <dbname>， 删除数据库用户DROP USER <username>。
登录方法：psql -U <dbeuser> -d <database> -h <host> -p <port>
如果当前Linux系统用户也是PostgreSQL用户，可以使用简写的方式登录数据库，省略用户名，之后也不用输入密码：

[email protected]:/home/ulysses$ psql exampledb
psql (10.5 (Ubuntu 10.5-0ubuntu0.18.04))
Type "help" for help.

exampledb=> 

可视化管理工具pgAdmin

PostgreSQL提供可视化的管理工具pgAdmin，可以在不同系统环境中使用，也可在容器中使用。Ubuntu下使用Python wheel安装，

[email protected]:~/Desktop$ pip3 install ./pgadmin4-3.5-py2.py3-none-any.whl

它会被安装在home/< username >/.local/python< version >/site-packages/下 ，运行pgAdmin4需要flask，安装：pip3 install flask：

[email protected]:~/.local/lib/python3.6/site-packages/pgadmin4$ sudo python3 pgAdmin4.py

第一次登录会要求输入邮箱和密码：

NOTE: Configuring authentication for SERVER mode.

Enter the email address and password to use for the initial pgAdmin user account:

Email address:

可以看到它是运行了一个flask应用：

pgAdmin 4 - Application Initialisation
======================================

Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
 * Serving Flask app "pgadmin" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: off

按提示在浏览器输入http://127.0.0.1:5050，还可以选择使用中文界面：

打开bashrc 设置指令别名

# some more ls aliases
alias ll='ls -alF'
alias la='ls -A'
alias l='ls -CF'
alias pgadmin4='python3 ~/.local/lib/python3.6/site-packages/pgadmin4/pgAdmin4.py'

之后运行重新启动bashrc：source ~/.bashrc

[email protected]:~$ source ~/.bashrc
[email protected]:~$ pgadmin4
/home/ulysses/.local/lib/python3.6/site-packages/psycopg2/__init__.py:144: UserWarning: The psycopg2 wheel package will be renamed from release 2.8; in order to keep installing from binary please use "pip install psycopg2-binary" instead. For details see: <http://initd.org/psycopg/docs/install.html#binary-install-from-pypi>.
  """)
Starting pgAdmin 4. Please navigate to http://127.0.0.1:5050 in your browser.
 * Serving Flask app "pgadmin" (lazy loading)
 * Environment: production
   WARNING: Do not use the development server in a production environment.
   Use a production WSGI server instead.
 * Debug mode: off

数据库操作

基本的PostgreSQL数据库操作符合SQL语言，简单的增、删、改查：

• 创建表

exampledb=> create table student (id int primary key, name varchar(20));
CREATE TABLE

• 插入数据

exampledb=> insert into student values(1, 'ulysses');
INSERT 0 1

• 选择数据

exampledb=> select * from student;
 id |  name   
----+---------
  1 | ulysses
(1 row)

• 修改数据

exampledb=> update student set name ='raider' where id =1;
UPDATE 1
exampledb=> select * from student;
 id |  name  
----+--------
  1 | raider
(1 row)

• 删除数据

exampledb=> delete from student where name = 'raider';
DELETE 1

• 删除数据表

exampledb=> drop table if exists student;
DROP TABLE

数据库连接

Pycharm连接数据库

在Pycharm的database设置中选择PostgreSQL数据库，填写host，port（默认5432） ，数据库名称与密码就可以连接

可以查看到数据库中的数据表和管理用户

python连接PostgreSQL数据库

下载 psycopg2模块，它是最常用的使用Python语言连接PostgreSQL数据库的模块。

import psycopg2

conn = psycopg2.connect(database="exampledb", user="dbuser", password="****", host="127.0.0.1", port="5432")


cur = conn.cursor()
cur.execute('select * from student')
rows = cur.fetchall()
for row in rows:
    print(f'name:{row[0]}\nid:{row[1]}')
cur.close()
conn.close()

运行后，结果

远程连接数据库

打开postgresql配置文件postgresql.conf， 修改 listen_addresses = ‘localhost’，将其改为='*'（可能默认为’*’），并取消前面的注释；确认port=5432。
打开pg_hba.conf，在末尾添加host all all 0.0.0.0/0 md5

local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5
host    all             all             0.0.0.0/0               md5

之后重启服务：service postgresql restart
这样设置后就能从远程访问本机上的PostgreSQL数据库了。
外部访问虚拟机上的PostgreSQL数据库-端口映射(虚拟机5432端口->本机5432端口)-外部访问（host：主机ip:映射的port，数据库名：exampledb，用户dbuser）