1.环境准备
1.1 VM虚拟机
4vcpu 4G内存20+GB硬盘 系统ubuntu20.04.4
1.2 postgresql数据库安装包source-code源码编译版本
postgresql-14.5.tar.gz
下载地址：PostgreSQL: File Browser
或者选择在线安装：（ubuntu系统）
1.2.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'# 给apt安装sources源写入pgdg.list文件，并且输入apt 源的最近版本下载地址1.2.2
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -# wget 下载asc秘钥文件，运行apt-key 添加为密钥1.2.3
sudo apt-get update# apt-get 下载更新软件包sudo apt-get -y install postgresql# apt-get 安装pg数据库本文介绍source源码编译方式安装
2. 上传安装包，编译
2.1 sftp上传安装包
# windows下按WIN+R打开 C:\Windows\System32\cmd # 输入sftp 目标主机用户@目标主机ip:目标主机目录（上传地址，下载文件地址）sftp python@192.168.1.130:/home/python##########################python@192.168.1.130's password:Connected to 192.168.1.130.Changing to: /home/pythonsftp>############################put命令上传本地文件到服务器目录put C:\Users\xxx\Downloads\postgresql-14.5.tar.gz#############################sftp> put C:\Users\xxx\Downloads\postgresql-14.5.tar.gzUploading C:/Users/xxx/Downloads/postgresql-14.5.tar.gz to /home/python/postgresql-14.5.tar.gzC:/Users/xxx/Downloads/postgresql-14.5.tar.gz100% 28MB68.3MB/s 00:00sftp>#############################2.2 解压、编译
2.2.1 解压
cd ~# 找到文件目录sudo tar -zxvf ./postgresql-14.5.tar.gz -C /usr/local/# 解压到指定目录/usr/local 非root用户加sudocd /usr/localsudo mv postgresql-14.5 postgresql# 重命名文件夹cd ./postgresqlls -a#查看安装包内源文件##########################python@ubuntu:/usr/local/postgresql$ ls -a. config.logCOPYRIGHT .gitattributesGNUmakefile.inREADME..configure .dir-locals.el.git-blame-ignore-revsHISTORY srcaclocal.m4configure.acdoc .gitignoreINSTALLconfigcontrib .editorconfig .gitrevisionMakefile##########################2.2.2 configure配置确认
检查依赖的库文件。
./configure --prefix=/usr/local/postgresql报错：readline library not found
configure: error: readline library not found
If you have readline already installed, see config.log for details on the
failure. It is possible the compiler isn’t looking in the proper directory.
Use –without-readline to disable readline support.
执行sudo apt-get install libreadline-dev 可以解决
重新执行一次configure
configure: using compiler=gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0configure: using CFLAGS=-Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wimplicit-fallthrough=3 -Wcast-function-type -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -Wno-format-truncation -Wno-stringop-truncation -O2configure: using CPPFLAGS= -D_GNU_SOURCE configure: using LDFLAGS=-Wl,--as-neededconfigure: creating ./config.statusconfig.status: creating GNUmakefileconfig.status: creating src/Makefile.globalconfig.status: creating src/include/pg_config.hconfig.status: creating src/include/pg_config_ext.hconfig.status: creating src/interfaces/ecpg/include/ecpg_config.hconfig.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.sconfig.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.cconfig.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.cconfig.status: linking src/include/port/linux.h to src/include/pg_config_os.hconfig.status: linking src/makefiles/Makefile.linux to src/Makefile.port2.2.3 运行make
python@ubuntu:/usr/local/postgresql$ lsaclocal.m4config.statuscontribGNUmakefile INSTALL srcconfigconfigureCOPYRIGHTGNUmakefile.inMakefileconfig.logconfigure.ac docHISTORY README### make#开始编译make -C ./src/backend generated-headers
make[1]: 进入目录“/usr/local/postgresql/src/backend”
make -C catalog distprep generated-header-symlinks
make[2]: 进入目录“/usr/local/postgresql/src/backend/catalog”
make[2]: 对“distprep”无需做任何事。……
2.2.4 运行make install 执行安装
make[1]: 离开目录“/usr/local/postgresql/src”
make -C config install
make[1]: 进入目录“/usr/local/postgresql/config”
/usr/bin/mkdir -p ‘/usr/local/postgresql/lib/pgxs/config’
/usr/bin/install -c -m 755 ./install-sh ‘/usr/local/postgresql/lib/pgxs/config/install-sh’
/usr/bin/install -c -m 755 ./missing ‘/usr/local/postgresql/lib/pgxs/config/missing’
make[1]: 离开目录“/usr/local/postgresql/config”
python@ubuntu:/usr/local/postgresql$ ls aclocal.m4configconfig.statusconfigure.acCOPYRIGHTGNUmakefile HISTORYINSTALLMakefilesharebin config.logconfigurecontrib docGNUmakefile.inincludelibREADMEsrc# ls 能看到本地目录bin config share lib 目录# 安装完成2.2.5 创建postgres 用户、授权
python@ubuntu:/usr/local/postgresql$ sudo adduser postgres正在添加用户"postgres"...正在添加新组"postgres" (1001)...正在添加新用户"postgres" (1001) 到组"postgres"...创建主目录"/home/postgres"...正在从"/etc/skel"复制文件...新的 密码： 重新输入新的 密码： passwd：已成功更新密码正在改变 postgres 的用户信息请输入新值，或直接敲回车键以使用默认值全名 []: 房间号码 []: 工作电话 []: 家庭电话 []: 其它 []: 这些信息是否正确？ [Y/n] ysudo chown -R postgres:postgres /usr/local/postgresql# 修改用户和用户组 2.2.6 postgres加入sudoer组，环境变量配置
postgres加入sudoer组
su root #切换root用户vi /etc/sudoers# 新增一行 如下 为了更方便执行文件修改
环境变量
su postgres#切换到postgres用户 vi /home/postgres/.bashrc# 末尾添加如下export PGHOME=/usr/local/postgresqlexport PGDATA=$PGHOME/dataexport PGLOG=$PGHOME/logexport PATH=$PGHOME/bin:$PATHexport MANPATH=$PGHOME/share/man:$MANPATHexport LANG=zh_CN.UTF-8export DATE=`date +"%Y-%m-%d %H:%M:%S"`export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH# source一下source /home/postgres/.bashrc2.2.7 初始化数据库(文件)
su postgres# 切换到指定用户cd /usr/local/postgresql/bin/# 切换到指定目录./initdb -D /usr/local/postgresql/data# 执行数据库初始化指定数据库文件目录##############################################################Success. You can now start the database server using:./pg_ctl -D /usr/local/postgresql/data -l logfile start##############################################################postgres@ubuntu:/usr/local/postgresql/data$ lsbasepg_hba.confpg_notify pg_statpg_twophasepostgresql.auto.confglobalpg_ident.confpg_replslot pg_stat_tmpPG_VERSION postgresql.confpg_commit_tspg_logical pg_serial pg_subtranspg_walpg_dynshmem pg_multixact pg_snapshotspg_tblspcpg_xact###### data 数据目录 出现这些文件OK2.2.8 启动数据库实例、创建数据库
启动数据库实例服务（开启监听）
cd /usr/local/postgresql# 切换目录 执行pg_ctl./bin/pg_ctl -D /usr/local/postgresql/data -l /usr/local/postgresql/log/logfile start########################结果如下#####################################################postgres@ubuntu:./bin/pg_ctl -D /usr/local/postgresql/data -l /usr/local/postgresql/log/logfile startwaiting for server to start.... doneserver started#####################################################################################创建数据库对象
./bin/createdb test# 创建数据库对象./bin/psql test # 启动数据库 test################################psql (14.5)Type "help" for help.test=# test-# \lList of databases Name|Owner | Encoding | Collate |Ctype| Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres| postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 |template0 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres+ ||| | | postgres=CTc/postgres template1 | postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | =c/postgres+ ||| | | postgres=CTc/postgres test| postgres | UTF8 | zh_CN.UTF-8 | zh_CN.UTF-8 | (4 rows)test-# \q###############################OK \l 查看数据库列表\q 退出2.2.9 修改配置项
主要分为2个文件 ：
pg_hba.conf为数据库允许连接IP配置项
postgresql.conf 为数据库基础配置项，用于修改数据库缓存，端口，密码，等
cd /usr/local/postgresql/vi ./data/pg_hba.conf# IPV4新增一行 如下：# IPv4 local connections:hostall all 127.0.0.1/32trusthostall all 0.0.0.0/0 trust###########################################################################vi ./data/postgresql.conf# 取消注释并修改data_directory = '/usr/local/postgresql/data'hba_file = '/usr/local/postgresql/data/pg_hba.conf'ident_file = '/usr/local/postgresql/data/pg_ident.conf' listen_addresses = '*' port = 5432authentication_timeout = 1min password_encryption = scram-sha-256 # 其它为默认配置 可以自行根据需要修改pg_hba.conf 配置项：
TYPE：pg的连接方式，local：本地unix套接字，host：tcp/ip连接
DATABASE：指定数据库
USER：指定数据库用户
ADDRESS：ip地址，可以定义某台主机或某个网段，32代表检查整个ip地址，相当于固定的ip，24代表只检查前三位，最后一 位是0~255之间的任何一个
METHOD：认证方式，常用的有ident，md5，password，trust，reject。
md5是常用的密码认证方式。
password是以明文密码传送给数据库，建议不要在生产环境中使用。
trust是只要知道数据库用户名就能登录，建议不要在生产环境中使用。
reject是拒绝认证。
重启实例
cd /usr/local/postgresql./bin/pg_ctl status# 查看pg服务运行状态./bin/pg_ctl stop# 停止PG服务./bin/pg_ctl start# 启动服务################################################################waiting for server to start....2022-09-22 23:17:48.247 CST [19261] LOG:starting PostgreSQL 14.5 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit2022-09-22 23:17:48.248 CST [19261] LOG:listening on IPv4 address "0.0.0.0", port 54322022-09-22 23:17:48.248 CST [19261] LOG:listening on IPv6 address "::", port 54322022-09-22 23:17:48.250 CST [19261] LOG:listening on Unix socket "/tmp/.s.PGSQL.5432"2022-09-22 23:17:48.253 CST [19262] LOG:database system was shut down at 2022-09-22 23:17:42 CST2022-09-22 23:17:48.256 CST [19261] LOG:database system is ready to accept connections doneserver started#################################################################2.2.10 数据库常用命令和密码配置
进入默认数据库
# psql不输入db名称默认进入postgres数据库postgres@ubuntu:/usr/local/postgresql$ psql# 因为配置过环境变量所以不需要cd到指定目录运行./psql进入test数据库
# psql test 进入test数据库postgres@ubuntu:/usr/local/postgresql$ psql test\c postgres# 切换指定数据库alter user postgres with password '******';# 修改默认用户postgres密码查看所有用户 \du
查看用户 \du role_name查看所有表\d
查看表结构\d table_name
删除表drop table table_name;
查看表空间\db
查看所有数据库\l
查看数据库\l db_name
切换到数据库 \c db_name
创建数据库CREATE DATABASE table_name;
查看索引 \d indexCREATE TABLE l_demo(
id serial PRIMARY KEY NOT NULL,
last_name VARCHAR(20) NOT NULL,
first_name VARCHAR(30) NOT NULL,
hello boolean NOT NULL DEFAULT false,
fine TEXT NOT NULL
3. 连接
3.1 Navicat连接
3.2 Dbeaver连接
Dbeaver连接前默认下载jdbc驱动
需要在连接设置=>PostgreSQL勾选显示非缺省数据库，方能展示其它DB
3.3 python连接、建表写入数据
pip installpsycopg2
报错：
Error: pg_config executable not found.
If you prefer to avoid building psycopg2 from source, please install the PyPI
‘psycopg2-binary’ package instead.
原因：系统缺少依赖的postgresql-dev库
sudo apt-get install postgresql-dev*python玩一下pg数据库连接建表写数。
import psycopg2conn = psycopg2.connect(database="postgres", user="postgres", password="******", host='192.168.1.130', port='5432')cur = conn.cursor()def create_table_test(table_name, name_str, cnt):""":param table_name::param name_str::return:"""# 这里也可以查询tablename 给where条件sql_select = "select count(1) from pg_tables where schemaname='public' and tablename = '%s'" % table_namecur.execute(sql_select)lines = cur.fetchone()# 删同名表if lines[0] == 1:# 有这张表cur.execute('drop table %s' % table_name)conn.commit()else:passsql_create = 'create table %s (id int, name varchar(50), email varchar(50))' % table_namecur.execute(sql_create)conn.commit()# 记住 create\drop和insert delete等一定要提交事务 否则数据库查询不到这张表for a in range(1, int(cnt)+1):sql_insert = "insert into {} values ({}, '{}' , '{}@yahoo.com')".format(table_name, a, name_str + str(a), name_str + str(a))cur.execute(sql_insert)conn.commit()cur.execute('select count(1) from {}'.format(str(table_name)))result = cur.fetchone()if result[0] == int(cnt):# return Truereturn print('写入 {} 条数据成功'.format(int(cnt)))else:# return Falsereturn print('写入 {} 条数据失败'.format(int(cnt)))if __name__ == '__main__':create_table_test('test_aaa', 'theshy', 10000)# 查找schema为public下的所有表cur.execute("select tablename from pg_tables where schemaname='public'")print(cur.fetchall())类似于存储过程。几十万级别的数据行数还好，达到百万级，性能就差了，玩法类似于mysql源码安装
结果如下：
完~