CentOS 8 安装部署Postgresql 13

本文详细介绍了在CentOS8上安装PostgreSQL13数据库的步骤,包括启用PGDG仓库、安装依赖、初始化数据库、创建用户和数据库、配置远程访问以及卸载和设置国内镜像的方法。此外,还展示了如何通过Linux普通用户远程连接到数据库并进行数据操作。
摘要由CSDN通过智能技术生成
安装:
# sudo dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# sudo dnf -qy module disable postgresql

# sudo dnf install -y postgresql13-server
或者 # yum -y install postgresql13-server

Dependencies resolved.
================================================================================
 Package                  Arch        Version                 Repository   Size
================================================================================
Installing:
 postgresql13-server      x86_64      13.3-2PGDG.rhel8        pgdg13      5.5 M
Installing dependencies:
 libicu                   x86_64      60.3-2.el8_1            baseos      8.8 M
 postgresql13             x86_64      13.3-2PGDG.rhel8        pgdg13      1.5 M
 postgresql13-libs        x86_64      13.3-2PGDG.rhel8        pgdg13      413 k

Transaction Summary
================================================================================
Install  4 Packages

[root@centos8 ~]# sudo /usr/pgsql-13/bin/postgresql-13-setup initdb
Initializing database ... OK

[root@centos8 ~]# sudo systemctl enable postgresql-13
Created symlink /etc/systemd/system/multi-user.target.wants/postgresql-13.service → /usrrvice.
[root@centos8 ~]# sudo systemctl start postgresql-13

说明:
postgresql-client	libraries and client binaries
postgresql-server	core database server
postgresql-contrib	additional supplied modules
postgresql-devel	libraries and headers for C language development


-- 切换用户 创建用户和数据库:
[root@centos8 ~]# su - postgres
[postgres@centos8 ~]$ psql
psql (13.3)
Type "help" for help.

postgres=# create user root with password 'oracle';
CREATE ROLE

postgres=# create database product owner root;
CREATE DATABASE
postgres=# grant all privileges on database product to root;
GRANT

--退出:
postgres=# \q
postgres=# quit


配置postgresql 可以远程访问:
#添加/修改:在所有IP地址上监听,从而允许远程连接到数据库服务器
# vim /var/lib/pgsql/13/data/postgresql.conf
listening_address:'*'
#listen_addresses = 'localhost'         # what IP address(es) to listen on;
                                        # comma-separated list of addresses;
                                        # defaults to 'localhost'; use '*' for all
                                        # (change requires restart)
#port = 5432                            # (change requires restart)
max_connections = 100                   # (change requires restart)

#添加/修改:允许任意用户从任意机器上以密码方式访问数据库,把下行添加为第一条规则:
加入:
# vim /var/lib/pgsql/13/data/pg_hba.conf
host    all           all             0.0.0.0/0                md5

重启服务器:
# sudo systemctl restart postgresql-13





-- 卸载数据库postgresql:
# rpm -qa | grep -i postgresql13 | xargs rpm -e --nodeps
# rpm -qa | grep -i postgresql13

-- 设置pg国内的镜像:
# cat pg.repo
[pgdg13]
name=PostgreSQL 13 for RHEL/CentOS $releasever - $basearch
baseurl=https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/13/redhat/rhel-$releasever-$basearch
#baseurl=https://download.postgresql.org/pub/repos/yum/13/redhat/rhel-$releasever-$basearch
# https://mirrors.tuna.tsinghua.edu.cn/postgresql/repos/yum/13/redhat/rhel-8-x86_64/
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-PGDG

可以使用centos早前的版本命令:
# yum -y install postgresql13-server


-- 检查是否可以远程访问:
$ netstat -nultp | grep -i post
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      3944/postmaster
tcp6       0      0 ::1:5432                :::*                    LISTEN      3944/postmaster

可以看到地址 127.0.0.1 表示只能本地访问。

若遇到此种 则表示可以远程访问:
#  netstat -nultp | grep -i post
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN      4143/postmaster
tcp6       0      0 :::5432                 :::*                    LISTEN      4143/postmaster


# su - postgres
$ psql -p 5432 -U postgres
psql (13.3)
Type "help" for help.

postgres=#

-- 远程访问:
$ psql -h 192.168.8.180 -p 5432
Password for user postgres:

提示信息:
$ psql
psql: error: FATAL:  no pg_hba.conf entry for host "[local]", user "postgres", database "postgres", SSL off



 #创建Linux普通用户,与刚才新建的数据库用户同名,如 dbuser:
adduser admin
passwd admin
[root@centos8 ~]# su - admin

[admin@centos8 ~]$ psql -d orderdb
psql (13.3)
Type "help" for help.

orderdb=>
orderdb=> create table t(username varchar(20),age int);
CREATE TABLE

orderdb=> insert into t(username,age)values('AA',10),('BB',20);
INSERT 0 2

orderdb=> select * from t;
 username | age
----------+-----
 AA       |  10
 BB       |  20
(2 rows)

-- 测试远程登录:
# psql --host=192.168.8.180 --port=5432 --username=admin --password orderdb
Password:
psql (13.3)
Type "help" for help.

orderdb=>

--简单版本
# psql -h 192.168.8.180 -p 5432 -U admin orderdb
Password for user admin:
psql (13.3)
Type "help" for help.

orderdb=>


默认登录是使用root账号和默认的端口5432:

[root@centos8 ~]# psql -h 192.168.8.180 productdb
Password for user root:
psql (13.3)
Type "help" for help.

productdb=>



参考:

https://vkingnew.blog.csdn.net/article/details/83190304

https://www.postgresql.org/download/linux/redhat/

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值