Linux安装Postgresql数据库，配置、启动、连接、运行脚本

最新推荐文章于 2024-09-09 10:14:53 发布

weixin_42332638

最新推荐文章于 2024-09-09 10:14:53 发布

阅读量9.2k

点赞数 1

文章标签： linux 运维服务器

本文链接：https://blog.csdn.net/weixin_42332638/article/details/126997880

版权

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/python
sftp>
###########################
#put命令上传本地文件到服务器目录
put C:\Users\xxx\Downloads\postgresql-14.5.tar.gz

#############################
sftp> put C:\Users\xxx\Downloads\postgresql-14.5.tar.gz
Uploading C:/Users/xxx/Downloads/postgresql-14.5.tar.gz to /home/python/postgresql-14.5.tar.gz
C:/Users/xxx/Downloads/postgresql-14.5.tar.gz                                          100%   28MB  68.3MB/s   00:00
sftp>
#############################

2.2 解压、编译

2.2.1 解压

cd ~
# 找到文件目录
sudo tar -zxvf ./postgresql-14.5.tar.gz -C /usr/local/
# 解压到指定目录/usr/local 非root用户加sudo
cd /usr/local
sudo mv postgresql-14.5 postgresql
# 重命名文件夹
cd ./postgresql
ls -a
#查看安装包内源文件

##########################
python@ubuntu:/usr/local/postgresql$ ls -a
.           config.log    COPYRIGHT       .gitattributes          GNUmakefile.in  README
..          configure     .dir-locals.el  .git-blame-ignore-revs  HISTORY         src
aclocal.m4  configure.ac  doc             .gitignore              INSTALL
config      contrib       .editorconfig   .gitrevision            Makefile
##########################

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.0
configure: 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 -O2
configure: using CPPFLAGS= -D_GNU_SOURCE 
configure: using LDFLAGS=  -Wl,--as-needed
configure: creating ./config.status
config.status: creating GNUmakefile
config.status: creating src/Makefile.global
config.status: creating src/include/pg_config.h
config.status: creating src/include/pg_config_ext.h
config.status: creating src/interfaces/ecpg/include/ecpg_config.h
config.status: linking src/backend/port/tas/dummy.s to src/backend/port/tas.s
config.status: linking src/backend/port/posix_sema.c to src/backend/port/pg_sema.c
config.status: linking src/backend/port/sysv_shmem.c to src/backend/port/pg_shmem.c
config.status: linking src/include/port/linux.h to src/include/pg_config_os.h
config.status: linking src/makefiles/Makefile.linux to src/Makefile.port

2.2.3 运行make

python@ubuntu:/usr/local/postgresql$ ls
aclocal.m4  config.status  contrib    GNUmakefile     INSTALL   src
config      configure      COPYRIGHT  GNUmakefile.in  Makefile
config.log  configure.ac   doc        HISTORY         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.m4  config      config.status  configure.ac  COPYRIGHT  GNUmakefile     HISTORY  INSTALL  Makefile  share
bin         config.log  configure      contrib       doc        GNUmakefile.in  include  lib      README    src

# 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] y

sudo 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/postgresql
export PGDATA=$PGHOME/data
export PGLOG=$PGHOME/log
export PATH=$PGHOME/bin:$PATH
export MANPATH=$PGHOME/share/man:$MANPATH
export LANG=zh_CN.UTF-8
export DATE=`date +"%Y-%m-%d %H:%M:%S"`
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH

# source一下
source /home/postgres/.bashrc

2.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$ ls
base          pg_hba.conf    pg_notify     pg_stat      pg_twophase  postgresql.auto.conf
global        pg_ident.conf  pg_replslot   pg_stat_tmp  PG_VERSION   postgresql.conf
pg_commit_ts  pg_logical     pg_serial     pg_subtrans  pg_wal
pg_dynshmem   pg_multixact   pg_snapshots  pg_tblspc    pg_xact
###
### data 数据目录 出现这些文件  OK

2.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 start
waiting for server to start.... done
server started
#####################################################################################

创建数据库对象

./bin/createdb test
# 创建数据库对象

./bin/psql test 
# 启动数据库 test

################################
psql (14.5)
Type "help" for help.
test=# 
test-# \l
                                  List 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:
host    all             all             127.0.0.1/32            trust
host    all             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 = 5432

authentication_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-bit
2022-09-22 23:17:48.248 CST [19261] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2022-09-22 23:17:48.248 CST [19261] LOG:  listening on IPv6 address "::", port 5432
2022-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 CST
2022-09-22 23:17:48.256 CST [19261] LOG:  database system is ready to accept connections
 done
server 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 index

CREATE 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 install psycopg2

报错：

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 psycopg2

conn = 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_name
    cur.execute(sql_select)
    lines = cur.fetchone()
    # 删同名表
    if lines[0] == 1:
        # 有这张表
        cur.execute('drop table %s' % table_name)
        conn.commit()
    else:
        pass
    sql_create = 'create table %s (id int, name varchar(50), email varchar(50))' % table_name
    cur.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 True
        return print('写入 {} 条数据成功'.format(int(cnt)))
    else:
        # return False
        return 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源码安装

结果如下：