Postgresql之基础

Postgresql:  https://www.postgresql.org/ 

 

[安装]

删除已经存在的pg:

yum remove -y postgresql* && rm -rf  /var/lib/pgsql && rm -rf  /usr/pgsql* && userdel -r postgres && groupdel postgres

 

 

yum:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
sudo yum install -y postgresql14-server
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb   # 初始化数据库
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14

 

 

 Docker:

 这里使用docker-compose.yml

 

复制代码
version: '3'
services:
  pgsql:
    image: postgres:14.2
    container_name: pg14
    restart: unless-stopped
    environment:
      POSTGRES_PASSWORD: 123456
      TZ: Asia/Shanghai
    ports:
      - 5432:5432
    volumes:
      - ./data:/var/lib/postgresql/data
    privileged: true
复制代码

 启动服务

 

docker exec -it pg14 psql -U postgres -d postgres

 

编译安装(生产环境推荐)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
-- 下载源码包
wget https://ftp.postgresql.org/pub/source/v14.2/postgresql-14.2.tar.gz --no-check-certificate
wget https://ftp.postgresql.org/pub/source/v13.3/postgresql-13.3.tar.gz
wget https://ftp.postgresql.org/pub/source/v12.7/postgresql-12.7.tar.gz
wget https://ftp.postgresql.org/pub/source/v11.12/postgresql-11.12.tar.gz
wget https://ftp.postgresql.org/pub/source/v10.17/postgresql-10.17.tar.gz
wget https://ftp.postgresql.org/pub/source/v9.6.22/postgresql-9.6.22.tar.gz
wget https://ftp.postgresql.org/pub/source/v9.4.26/postgresql-9.4.26.tar.gz
 
 
-- 一些依赖包
yum install -y cmake make gcc zlib zlib-devel gcc-c++ perl readline readline-devel  \
python36 tcl openssl ncurses-devel openldap pam flex
 
 
-- 创建用户
useradd pgsql
echo "lhr" | passwd --stdin pgsql
 
 
-- 创建目录
mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg14,soft}
chown -R pgsql:pgsql /postgresql
chmod -R 775 /postgresql
 
-- 编译
su - pgsql
cd /postgresql/soft
tar zxvf postgresql-14.2.tar.gz
cd postgresql-14.2
./configure --prefix=/postgresql/pg14
make -j 16 && make install
# 编译完成,最后一行显示:All of PostgreSQL successfully made. Ready to install.
 
-- 如果你希望编译所有能编译的东西,包括文档(HTML和手册页)以及附加模块(contrib),这样键入:
make world -j 16 && make install-world
#最后一行显示:PostgreSQL, contrib, and documentation successfully made. Ready to install.
-- 源码安装postgresql时,而make时又没有make world,就会导致的pg最终没有类似pg_stat_statements的扩展功能
 
 
-- 配置环境变量
cat >>  ~/.bash_profile <<"EOF"
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pg14
export LD_LIBRARY_PATH=$PGHOME/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
EOF
 
source  ~/.bash_profile
 
-- 初始化
su - pgsql
/postgresql/pg14/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
 
 
-- 修改参数
cat >> /postgresql/pgdata/postgresql.conf <<"EOF"
listen_addresses = '*'
port=5432
unix_socket_directories='/postgresql/pgdata'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF
 
 
 
cat   > /postgresql/pgdata/pg_hba.conf << EOF
# TYPE  DATABASE    USER    ADDRESS       METHOD
host      all       all    0.0.0.0/0        md5
EOF
 
 
 
-- 启动
su - pgsql
pg_ctl start
pg_ctl status
pg_ctl stop
 
 
 
-- 配置系统服务
cat > /etc/systemd/system/PG14.service <<"EOF"
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
After=network.target
 
[Service]
Type=forking
User=pgsql
Group=pgsql
Environment=PGPORT=5433
Environment=PGDATA=/postgresql/pgdata
OOMScoreAdjust=-1000
ExecStart=/postgresql/pg14/bin/pg_ctl start -D ${PGDATA} -s -o "-p ${PGPORT}" -w -t 300
ExecStop=/postgresql/pg14/bin/pg_ctl stop -D ${PGDATA} -s -m fast
ExecReload=/postgresql/pg14/bin/pg_ctl reload -D ${PGDATA} -s
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
 
[Install]
WantedBy=multi-user.target
EOF
 
systemctl daemon-reload
systemctl enable PG14
systemctl start PG14
systemctl status PG14
 
 
su - pgsql
psql
\password postgres
or:
alter user postgres with password 'lhr';
 
 
 
-- 安装插件
create extension pageinspect;
create extension pg_stat_statements;
 
select * from pg_extension ;
select * from pg_available_extensions order by name;

  

 

编译参数介绍:

--with-pgport=NUMBER
把NUMBER设置为服务器和客户端的默认端口。默认是 5432。这个端口可以在以后修改,不过如果你在这里声明,那么服务器和客户端将有相同的编译好了的默认值。这样会非常方便些。通常选取一个非默认值的理由是你企图在同一台机器上运行多个PostgreSQL服务器。

--with-openssl
编译SSL(加密)连接支持。这个选项需要安装OpenSSL包。configure将会检查所需的头文件和库以确保你的 OpenSSL安装足以让配置继续下去。

--with-perl
制作PL/Perl服务器端编程语言。

--with-python
制作PL/Python服务器端编程语言。

--with-blocksize=BLOCKSIZE
设置块尺寸,以 K 字节计。这是表内存储和I/O的单位。默认值(8K字节)适合于大多数情况,但是在特殊情况下可能其他值更有用。这个值必须是2的幂并且在 1 和 32 (K字节)之间。注意修改这个值需要一次 initdb。

--with-systemd
系统服务方式管理

更多编译选项请参考:http://postgres.cn/docs/13/install-procedure.html

 

 

 

 

 

 

 

[使用]

 

在默认配置下, 之允许本机访问Postgresql

复制代码
# 切换到postgres用户
su - postgres

Last login: Wed Mar 1 13:16:48 CST 2017 on pts/1
-bash-4.2$ psql

psql (9.2.18)
Type "help" for help.

postgres=#

此时就在数据库postgres中了。
复制代码

创建新用户:

create user dbuser with password '密码';
注意点:  密码要使用单引号

修改postgres密码

alter user postgres with encrypted password '密码';

 

 

创建用户数据库:

create database demo owner dbuser;

赋权给用户

postgres=# GRANT ALL PRIVILEGES ON DATABASE demo TO dbuser;
注意:这里跟MySQL不同 是需要使用关键字DATABASE

 

 

开启远程访问:

配置文件:/var/lib/pgsql/data/postgresql.conf

复制代码
listen_addresses = '0.0.0.0'

更多:

cat >> /var/lib/pgsql/14/data/postgresql.conf <<EOF
listen_addresses = '*'
port=5432
unix_socket_directories='/var/lib/pgsql/14/data'
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%a.log'
log_truncate_on_rotation = on
EOF

 
复制代码

 

/var/lib/pgsql/data/pg_hba.conf 

复制代码
添加/修改:允许任意用户从任意机器上以密码方式访问数据库,把下行添加为第一条规则:

host    all             all             0.0.0.0/0               md5

更多:

cat > /var/lib/pgsql/14/data/pg_hba.conf << EOF
# TYPE DATABASE USER ADDRESS METHOD
host all all 0.0.0.0/0 trust/md5
EOF

 

trust: 相当于免密登录

设置md5即可

复制代码

重启服务:

 sudo systemctl restart postgresql

 

数据库备份:

/usr/pgsql-9.6/bin/pg_dump -h 127.0.0.1 -U rw -d test > pgsql-20220819.sql

将整个库进行备份导出

 

 

 开启防火墙端口:

 

firewall-cmd --add-port=5432/tcp --permanent
firewall-cmd --reload
firewall-cmd --list-port

 

 

 

 

常用命令:

1
2
# 查看所有用户及权限
\du<br><br># 退出<br>\q<br><br><br># 进入数据库<br>\d  dbname<br><br>cli链接:<br>psql -U lanlang -h 120.xx.xx.xxx -d postgres<br>-U: 指定用户名<br>-h: 链接地址<br>-d: 指定认证数据库<br>之后 输入密码即可登录<br><br><br>-- 从Postgresql 9.2开始,还可以使用URI格式进行连接:psql postgresql://myuser:mypasswd@myhost:5432/mydb<br> psql postgresql://postgres:lhr@192.168.66.35:25432/postgres<br><br><br><br># 创建数据库<br>CREATE DATABASE dbname WITH OWNER=postgres ENCODINT='UTF-8';<br><br><br># 创建表<br>create table student (<br>  id integer not null,<br>  name character(32),<br>  number char(5),<br>  constraint student_pkey primary key (id)<br>);<br><br><br>

  

 

 

常见问题:

程序链接PostgreSQL 时报错"no pg_hba.conf entry"

这条错误的原因是因为客户端远程访问postgresql受限所致,因为postgresql默认情况下除本机外的机器是不能连接的。

默认情况下,postgresql本机的连接是信任连接,只要有用户名就可以连接,不用密码. 要改变这种默认设置需要更改postgresql安装目录下的两个配置文件(pg_hba.conf与postgresql.conf)


##Added by liu.chunming
local all all peer
host all all 127.0.0.1/32 md5
host all all ::1/128 md5
联接使用的认证方法:
trust
无条件地允许联接。这个方法允许任何可以与PostgreSQL数据库服务器联接的用户以他们期望的任意PostgreSQL 数据库用户身份进行联接,而不需要口令。

md5
要求客户端提供一个 MD5 加密的口令进行认证。

如果我想让10.86.12.0~10.86.12.154的IP段能访问PostgreSQL 数据库,需要增加下面一行:

host all all 10.86.12.0/24 trust

 

 

 

文档参考:

 

posted @   X-Wolf  阅读(153)  评论(0)    收藏  举报
 
点击右上角即可分享
微信分享提示