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
编译安装(生产环境推荐)
-- 下载源码包
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
常用命令:
# 查看所有用户及权限 \du
# 退出
\q
# 进入数据库
\d dbname
cli链接:
psql -U lanlang -h 120.xx.xx.xxx -d postgres
-U: 指定用户名
-h: 链接地址
-d: 指定认证数据库
之后 输入密码即可登录
-- 从Postgresql 9.2开始，还可以使用URI格式进行连接：psql postgresql://myuser:mypasswd@myhost:5432/mydb
psql postgresql://postgres:lhr@192.168.66.35:25432/postgres
# 创建数据库
CREATE DATABASE dbname WITH OWNER=postgres ENCODINT='UTF-8';
# 创建表
create table student (
id integer not null,
name character(32),
number char(5),
constraint student_pkey primary key (id)
);
常见问题:
程序链接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
文档参考:
