postgre数据库

时间：2025-02-28 07:26:11

目 录

  • 一、post数据库安装
    • 1.1 rpm包安装方式
    • 1.2 开启远程访问
    • 1.3 pg的连接方式
    • 1.4 pg使用shell执行sql
  • 二、一键安装pg
    • 21. 安装
    • 2.2 安装之后的使用方式

一、post数据库安装

1.1 rpm包安装方式

下载rmp的的网址，下载需要安装的4个rmp包，然后安装
rmp包下载网址

yum install -y libicu
yum install -y libicu-devel
rpm -ivh postgresql10-libs-10.19-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql10-10.19-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql10-server-10.19-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql10-devel-10.19-1PGDG.rhel7.x86_64.rpm

1.2 开启远程访问

需要修改两个配置文件
1.修改
修改成 listen_addresses = ‘*’
2.修改pg_hba.conf
vim /var/lib/pgsql/10/data/pg_hba.conf
在 # IPv4 local connections:下面输入，trust表示不需要密码，md5的话表示需要密码

host    all             all             192.168.0.1/16          trust

3.重启
systemctl restart postgresql-10

1.3 pg的连接方式

# 远程连接方式1
psql -U postgres -d postgres -h 192.168.0.10 -p 5432

# 远程连接方式2
psql "host=192.168.2.76 port =5432 user = postgres password = Qaz123456! dbname=postgres"

1.4 pg使用shell执行sql

psql "host=192.168.0.123 port =5432 user = treece password =1123 dbname=amt" -f ./

pg使用shell执行sql
参考

二、一键安装pg

21. 安装

参考1.1下载pg文件

tar -zxvf 
cd pg10
# 安装
yum install -y libicu
yum install -y libicu-devel
rpm -ivh postgresql10-libs-10.19-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql10-10.19-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql10-server-10.19-1PGDG.rhel7.x86_64.rpm
rpm -ivh postgresql10-devel-10.19-1PGDG.rhel7.x86_64.rpm

# 启动
/usr/pgsql-10/bin/postgresql-10-setup initdb
sudo systemctl start postgresql-10
sudo systemctl enable 
sleep 1
echo '----------1、postgresql install success !!!----------'

# 修改密码
su - postgres -c "echo \"ALTER USER postgres WITH PASSWORD 'Qaz123456!';\" >> update_password.sql "
su - postgres -c "psql  -f ./update_password.sql"

systemctl restart postgresql-10
sleep 1
echo '----------2、postgresql change PASSWORD success!!!----------'

# 开启远程连接
echo "listen_addresses = '*'" >> /var/lib/pgsql/10/data/
sed -i '/IPv4 local connections:/a\host    all             all             192.168.0.1/16          md5' /var/lib/pgsql/10/data/pg_hba.conf
sed -i '/IPv4 local connections:/a\host    all             all             100.0.0.1/8             md5' /var/lib/pgsql/10/data/pg_hba.conf
sed -i '/IPv4 local connections:/a\host    all             all             172.0.0.1/8             md5' /var/lib/pgsql/10/data/pg_hba.conf

systemctl restart postgresql-10

cp /root/postgres/ /root/postgres/ /var/lib/pgsql/

sleep 1
echo '----------3、postgresql change conf success!!!----------'
echo '----------4、install done!!!----------'

2.2 安装之后的使用方式

# 创建sql
CREATE DATABASE dtstest1;
\c dtstest1;
CREATE TABLE COMPANY(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Lily', 10, 'California', 40000.00);

CREATE TABLE COMPANY2(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY2 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY2 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Lily', 10, 'California', 40000.00);


CREATE DATABASE dtstest2;
\c dtstest2;
CREATE TABLE COMPANY1(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY1 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY1 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Lily', 10, 'California', 40000.00);


CREATE TABLE COMPANY2(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(50),
   SALARY         REAL
);
INSERT INTO COMPANY2 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (1, 'Paul', 32, 'California', 20000.00);
INSERT INTO COMPANY2 (ID,NAME,AGE,ADDRESS,SALARY) VALUES (2, 'Lily', 10, 'California', 40000.00);

运行sql

# 安装方法
tar -zxvf 
cd postgres/
bash 

# 创建数据
su - postgres -c " psql -f ./"
su - postgres -c " psql -f ./"

# test 连接
ip='192.168.2.76'
psql "host=${ip} port =5432 user = postgres password = Qaz123456! dbname=postgres"

相关文章