1. Apt-get 安装postgresql postgresql-contrib
1.1 apt-get install postgresql postgresql-contrib, 安装出奇的顺利，并没有出现不能安装的情况。看了执行过程，发现并没有安装postsql,又单独安装了一次postgresql，发现人家只是装了postgresql-doc,果然没那么简单。
apt-get install postgresql postgresql-contrib，执行结果如下：
root@iZwz984fp76m132ex9esc8Z:/# sudo apt-get install postgresql postgresql-contrib Reading package lists... Done Building dependency tree Reading state information... Done postgresql is already the newest version (12+214ubuntu0.1). The following NEW packages will be installed: postgresql-contrib 0 upgraded, 1 newly installed, 0 to remove and 9 not upgraded. Need to get 3,932 B of archives. After this operation, 67.6 kB of additional disk space will be used. Do you want to continue? [Y/n] y Get:1 http://mirrors.cloud.aliyuncs.com/ubuntu focal-updates/main amd64 postgresql-contrib all 12+214ubuntu0.1 [3,932 B] Fetched 3,932 B in 0s (41.1 kB/s) Selecting previously unselected package postgresql-contrib. (Reading database ... 116914 files and directories currently installed.) Preparing to unpack .../postgresql-contrib_12+214ubuntu0.1_all.deb ... Unpacking postgresql-contrib (12+214ubuntu0.1) ... Setting up postgresql-contrib (12+214ubuntu0.1) ...
再次执行apt-get install postgresql 指令，执行结果：
xxxxxxxx:/data/env/venv-site# apt-get install postgresql Reading package lists... Done Building dependency tree Reading state information... Done Suggested packages: postgresql-doc The following NEW packages will be installed: postgresql 0 upgraded, 1 newly installed, 0 to remove and 9 not upgraded. Need to get 0 B/3,924 B of archives. After this operation, 67.6 kB of additional disk space will be used. Selecting previously unselected package postgresql. (Reading database ... 116911 files and directories currently installed.) Preparing to unpack .../postgresql_12+214ubuntu0.1_all.deb ... Unpacking postgresql (12+214ubuntu0.1) ... Setting up postgresql (12+214ubuntu0.1) ... xxxxxxxxxx:/data/env/venv-site#
用apt-get remove postgresql --purge, apt-get remove postgresql-contrib --purge指令，把刚才装的东西全删除了。
2. 再次安装postgresql,老老实实到官网去下载。
2.1官网介绍的很详细：
复制一下代码，一步一步执行（添加存储库， 添加版本密钥，更新安装包清单，安装postporesql和postgresql-contrib
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get -y install postgresql
2.2 安装过程很顺利，postgresql 和postgresql安装成功。执行sudo su postgres,顺利进入postgresql界面，输入psql进入postgresql shell。
root@xxxxxxxxxx:~# sudo su postgres postgres@xxxxxxxxxx:/root$ psql could not change directory to "/root": Permission denied psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1), server 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1)) Type "help" for help.
2.3修改postgres密码 ALTER USER postgress WITH PASSWORD 'xxxxxxxx';
postgres=# ALTER USER postgres WITH PASSWORD 'xxxxxx'; ALTER ROLE postgres=#
2.4执行CREATE EXTENTION pg_trgm;扩展trigram算法，用于将来检索数据库中字符串相似度。最后执行exit指定推出postgresql shell。
3. 登录问题
3.1 在2.3-2.4中，用postgres账户登录一切正常，尝试切换用户登录，发现登录不了。创建用户blog,并设定密码(CREATE USER blog WITH PASSWORD 'xxxxx', CREATE DATAbase blogpro,GRANT ALL PRIVILEGES ON DATAbase blogpro TO blog;)
postgres=# CREATE USER blog WITH PASSWORD 'xxxxxx';
CREATE ROLE
postgres=# create database blogpro
postgres-# ;
CREATE DATAbase
postgres=# GRANT ALL PRIVILEGES ON DATAbase blogpro TO blog;
GRANT
postgres=# l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
blogpro | blog | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
退出postgresql，尝试用新创建的blog用户登录，发现提示：Peer authentication failed for user "blog"，无法登录。
root@ixxxxxxxx:/# su postgres postgres@ixxxxxxxx:/$ psql -U blog psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: Peer authentication failed for user "blog" postgres@ixxxxxxxx:/$
3.2 查了很多贴子，发现需要修改pg_hba.conf,对它做如下修改。
# TYPE DATAbase USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer # 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 peer 修改成下面： # 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
3.3 重新启动postgresql(systemctl restart posgrestsql),发现无法重新启动，提示：Failed to restart posgrestsql.service: Unit posgrestsql.service not loaded。通过systemctl enable postgresql,启动postgresql服务，可以正常start,stop, restart postgresql了。
root@xxxxxxxxxx:/# systemctl restart posgrestsql Failed to restart posgrestsql.service: Unit posgrestsql.service not loaded. root@xxxxxxxxxx:/# systemctl enable postgresql Synchronizing state of postgresql.service with SysV service script with /lib/systemd/systemd-sysv-install. Executing: /lib/systemd/systemd-sysv-install enable postgresql root@xxxxxxxxx:/# systemctl restart postgresql
3.4 登录问题解决。
root@xxxxxxxxx:/# su postgres postgres@xxxxxxxxx:/$ psql blogpro -U blog Password for user blog: psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1), server 12.9 (Ubuntu 12.9-0ubuntu0.20.04.1)) Type "help" for help. blogpro=>
4. Pyhon中安装pyscopg2（psycopg2 库是 python 用来操作 postgreSQL 数据库的第三方库)
4.1 直接使用 pip install pyscopg2安装，默认的是阿里云的镜像。http://mirrors.cloud.aliyuncs.com/pypi/packages/d1/1e/b450599a27b1809bccbd4e369f397cb18dc56b875778d961f9ae180b54b7/psycopg2-2.9.3.tar.gzhttp://mirrors.cloud.aliyuncs.com/pypi/packages/d1/1e/b450599a27b1809bccbd4e369f397cb18dc56b875778d961f9ae180b54b7/psycopg2-2.9.3.tar.gz 运行结果：安装失败。
:/data/env/venv-site# pip install psycopg2
Looking in indexes: http://mirrors.cloud.aliyuncs.com/pypi/simple/
Collecting psycopg2
Using cached http://mirrors.cloud.aliyuncs.com/pypi/packages/d1/1e/b450599a27b1809bccbd4e369f397cb18dc56b875778d961f9ae180b54b7/psycopg2-2.9.3.tar.gz (380 kB)
Preparing metadata (setup.py) ... done
Building wheels for collected packages: psycopg2
Building wheel for psycopg2 (setup.py) ... error
error: subprocess-exited-with-error
× python setup.py bdist_wheel did not run successfully.
│ exit code: 1
╰─> [40 lines of output
此处省略...
It appears you are missing some prerequisite to build the package from source.
You may install a binary package by installing 'psycopg2-binary' from PyPI.
If you want to install psycopg2 from source, please install the packages
required for the build and try again.
For further information please check the 'doc/src/install.rst' file (also at
).
error: command '/usr/bin/x86_64-linux-gnu-gcc' failed with exit code 1
[end of output]
note: This error originates from a subprocess, and is likely not a problem with pip.
error: legacy-install-failure
× Encountered error while trying to install package.
╰─> psycopg2
note: This is an issue with the package mentioned above, not pip.
hint: See above for output from the failure.
4.2 提示可以安装psycopg-binary(系统反馈提示：You may install a binary package by installing 'psycopg2-binary' from PyPI）
pip install psycopg2-binary (也可以指定镜像源，如： pip install psycopg2-binary -i https://pypi.tuna.tsinghua.edu.cn/simple，清华镜像源还是很好用）
(venv-site) xxxxxxxx:/data/env/venv-site# pip install psycopg2-binary
Looking in indexes: http://mirrors.cloud.aliyuncs.com/pypi/simple/
Collecting psycopg2-binary
Downloading http://mirrors.cloud.aliyuncs.com/pypi/packages/27/83/5bf62ca29fa23591582188f1b1aa00c4219b4f53cd39e288b8a2e4f5ee92/psycopg2_binary-2.9.3-cp38-cp38-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (3.0 MB)
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 3.0/3.0 MB 42.7 MB/s eta 0:00:00
Installing collected packages: psycopg2-binary
Successfully installed psycopg2-binary-2.9.3
经过以上步骤，Postegresql算是安装完成了。在Django项目里的setting.py设置相应的参数就可以取代Django自带的Sqlite3数据库了。
