[系统运维]linux安装postgresql14及python连接数据库

1.安装

官方安装方法，分别执行以下命令

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
#这里如果想安装其它版本则使用 postgresql-12 ，使用postgresql默认是安装最新版本
sudo apt-get -y install postgresql

成功后显示如下

fixing permissions on existing directory /var/lib/postgresql/14/main ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Asia/Shanghai
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
update-alternatives: using /usr/share/postgresql/14/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode
invoke-rc.d: could not determine current runlevel
Setting up postgresql (14+238.pgdg20.04+1) ...
Processing triggers for man-db (2.9.1-1) ...
Processing triggers for systemd (245.4-4ubuntu3.15) ...

2.启动服务

如果不启动，在执行 psql 时， 会报如下错误

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: No such file or directory
        Is the server running locally and accepting connections on that socket?

sudo service postgresql start
#或者
sudo service postgresql restart

3.创建用户和数据库

如果不创建，在执行 psql 时会报如下错误

psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "cq" does not exist
或者
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  database "cq" does not exist

sudo -u postgres createuser -s youusername; createdb youdbname

4.给用户设置密码

# 使用 psql youdbname进入数据库
cq@DESKTOP-PQUJJ65:~$ psql testdb
psql (14.2 (Ubuntu 14.2-1.pgdg20.04+1))
Type "help" for help.
#这里修改密码，用于远程或者程序调用用，如果不设置，则无法谁成功
testdb=# psql \password
Enter new password for user "cq":
Enter it again:
testdb-#

5.查看和设置连接地址

版本14是如下文件名，低版本可能有差异，详情见官网说明

地址：

http://www.postgres.cn/docs/14/auth-pg-hba-conf.html

cat /etc/postgresql/14/main/pg_hba.conf
#下面为文件内容 
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# 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            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

从上面得知127.0.0.1为连接地址

6.连接

import psycopg2 as pg

if __name__ == '__main__':
    cont = pg.connect(
        database="testdb",
        user="cq",
        password="test1234",
        host="127.0.0.1",
        port="5432"
    )
    cur = cont.cursor()
    cur.execute("select * from newtable where name='test1'")
    rows = cur.fetchall()
    for row in rows:
        print(f"name={row[0]}")
        print(f"age={row[1]}")