Postgresql安装及使用

一、链接

官网 https://www.postgresql.org/
下载 https://www.postgresql.org/download/
中文社区 http://www.postgres.cn/index.php/v2/home
中文网 https://postgres.fun/
易百教程 https://www.yiibai.com/postgresql
原文链接：https://blog.youkuaiyun.com/llwy1428/article/details/95444151

二、源码安装postgresql-11.4

2.1 安装

默认端口

5432

2.1.1 安装依赖

yum install -y vim lrzsz tree wget gcc gcc-c++ make readline-devel readline zlib-devel zlib ncurses-devel
#ncurses-devel是readline-devel的依赖包

2.1.2 下载解压

mkdir /opt/postgresql
cd /opt/postgresql
wget https://ftp.postgresql.org/pub/source/v11.4/postgresql-11.4.tar.gz
tar -zxvf postgresql-11.4.tar.gz

[root@localhost postgresql-11.4]# ll
总用量 748
-rw-r--r--.  1 1107 1107    522 6月  18 2019 aclocal.m4
drwxrwxrwx.  2 1107 1107   4096 6月  18 2019 config
-rwxr-xr-x.  1 1107 1107 561752 6月  18 2019 configure
-rw-r--r--.  1 1107 1107  84451 6月  18 2019 configure.in
drwxrwxrwx. 56 1107 1107   4096 6月  18 2019 contrib
-rw-r--r--.  1 1107 1107   1192 6月  18 2019 COPYRIGHT
drwxrwxrwx.  3 1107 1107    107 6月  18 2019 doc
-rw-r--r--.  1 1107 1107   3848 6月  18 2019 GNUmakefile.in
-rw-r--r--.  1 1107 1107    284 6月  18 2019 HISTORY
-rw-r--r--.  1 1107 1107  74257 6月  18 2019 INSTALL
-rw-r--r--.  1 1107 1107   1682 6月  18 2019 Makefile
-rw-r--r--.  1 1107 1107   1212 6月  18 2019 README
drwxrwxrwx. 16 1107 1107   4096 6月  18 2019 src

2.1.3 编译安装

./configure --prefix=/usr/local/postgresql
make && make install
cd /usr/local/postgresql/
[root@localhost postgresql]# ll
总用量 16
drwxr-xr-x. 2 root root 4096 11月 16 20:53 bin
drwxr-xr-x. 6 root root 4096 11月 16 20:53 include
drwxr-xr-x. 4 root root 4096 11月 16 20:53 lib
drwxr-xr-x. 6 root root 4096 11月 16 20:53 share

创建data和log目录
mkdir /usr/local/postgresql/log
mkdir /usr/local/postgresql/data

2.1.4 配置环境变量

cat << eof >> /etc/profile
export PGHOME=/usr/local/postgresql
export PGDATA=/usr/local/postgresql/data
export PATH=\$PATH:\$HOME/.local/bin:\$HOME/bin:\$PGHOME/bin
eof

source /etc/profile

2.1.5 添加用户并赋权

useradd postgres
chown -R postgres:root /usr/local/postgresql/

2.1.6 初始化数据库

注意：不能在 root 用户下初始数据库，否则会报错

su postgres
/usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data/

输出以下信息表示成功
[postgres@localhost postgresql]$ /usr/local/postgresql/bin/initdb -D /usr/local/postgresql/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /usr/local/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default timezone ... Asia/Shanghai
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the option -A, or
--auth-local and --auth-host, the next time you run initdb.

Success. You can now start the database server using:

    /usr/local/postgresql/bin/pg_ctl -D /usr/local/postgresql/data/ -l logfile start

2.1.7 编辑配置开启远程连接

cd /usr/local/postgresql/data
cp postgresql.conf postgresql.conf-bak
cp pg_hba.conf pg_hba.conf-bak
vim postgresql.conf
listen_addresses = '*'
port = 5432

vim pg_hba.conf
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     trust
host    replication     all             127.0.0.1/32            trust
host    replication     all             ::1/128                 trust
加入以下一行
host    all             all             0.0.0.0/0               md5

说明：
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是拒绝认证。

2.1.8 启动登录并设置密码

启动
pg_ctl start -l /usr/local/postgresql/log/pg_server.log
登录并设置数据库postgres用户密码
[postgres@localhost log]$ psql 
psql (11.4)
Type "help" for help.
postgres=# \password

三、使用

3.1 修改postgres密码

psql -U postgres 登录数据库，执行后提示符变为 'postgres=#'  -U 指定用户
ALTER USER postgres WITH PASSWORD 'postgres';  设置postgres用户密码为postgres

3.2 命令

3.2.1 停止

#-D /usr/local/postgresql/data/ 可以不加因为环境变量中已经指定了
pg_ctl stop -l /usr/local/postgresql/log/pg_server.log -D /usr/local/postgresql/data/

3.2.2 启动

#-D /usr/local/postgresql/data/ 可以不加因为环境变量中已经指定了
pg_ctl start -l /usr/local/postgresql/log/pg_server.log -D /usr/local/postgresql/data/

3.2.3 重新加载配置文件

su - postgres
pg_ctl -D /usr/local/postgresql/data/ reload

3.3 命令远程连接

# -h指定主机IP -U指定用户 -d 指定数据  postgres是默认数据库
psql -h 192.168.8.10 -U postgres -d postgres

3.4 创建数据库

psql -h 192.168.8.10 -U postgres -d postgres
#创建
postgres=# create database hxl;
CREATE DATABASE
#进入hxl数据库
postgres=# \c hxl

3.5 基本命令

3.5.1 查看系统中现存的数据库

template1=# \l 

3.5.2 退出客户端程序psql

template1=# \q 

3.5.3 切换到某个数据库

template1=# \c 从一个数据库中转到另一个数据库中，如template1=# \c sales 从template1转到sales

3.5.4 查看当前数据库的表

template1=# \dt

3.5.5 查看表结构

template1=# \d 表名
template1=# \di 查看索引

3.5.5 查看索引

template1=# \di

3.6 创建表

3.6.1 表定义字段并插入信息

project=# create table ([字段名1] [类型1] <references 关联表名(关联的字段名)>;,[字段名2] [类型2],......<,primary key (字段名m,字段名n,...)>;);
project=# create table person(id integer, name text);
project=# insert into person values (1, 'hxl');
project=# select * from person;

3.7 查询

3.7.1 查询用户表

select * from pg_shadow;

3.7.2 查询当前数据库

select current_database();

3.7.3 查询当前登录用户信息

select * from pg_stat_activity where state = 'active';

3.8 删除数据库

 drop database [数据库名];

3.9 删除表

drop table [表名]; 

3.10 重命名表

alter table [表名A] rename to [表名B];

3.11 表内基本操作

3.11.1 在已有的表里添加字段

alter table [表名] add column [字段名] [类型];

3.11.2 删除表中的字段

alter table [表名] drop column [字段名];

3.11.3 重命名一个字段

alter table [表名] rename column [字段名A] to [字段名B];

3.11.4 给一个字段设置缺省值

alter table [表名] alter column [字段名] set default [新的默认值];

3.11.5 去除缺省值

alter table [表名] alter column [字段名] drop default;

3.11.6 在表中插入数据

insert into 表名 ([字段名m],[字段名n],......) values ([列m的值],[列n的值],......);

3.11.7 修改表中的某行某列的数据

update [表名] set [目标字段名]=[目标值] where [该行特征];

3.11.8 删除表中某行数据

delete from [表名] where [该行特征];

删空整个表

delete from [表名];

四、解释

4.1 数据目录结构

base	每个 database 会在 base 目录下有一个子目录3
global	Postgres 自己的 meta 数据库存放的地方（全局 DB）
PG_VERSION  #pg版本，如10
base/  #每个 database 会在 base 目录下有一个子目录，存储数据库文件
global/   #存放的文件用于存储全局的系统表信息和全局控制信息
pg_commit_ts/  #包含已提交事务的时间
pg_dynshmem/  #包含动态共享内存子系统使用的文件
pg_hba.conf
pg_ident.conf
pg_logical/  #包含逻辑解码的状态数据
pg_multixact/  #包含多事务状态数据（等待锁定的并发事务
pg_notify/   #包含LISTEN/NOTIFY状态数据
pg_replslot/  #包含复制槽数据
pg_serial/   #包含了已经提交的序列化事务的有关信息
pg_snapshots/  #包含导出的快照
pg_stat/  #包含统计子系统的永久文件
pg_stat_tmp/  #包含统计子系统的临时文件
pg_subtrans/  #包含子事务状态数据
pg_tblspc/   #包含表空间的符号链接
pg_twophase/   #包含预备事务的状态文件
pg_wal/  #包含wal日志
pg_xact/
postgresql.auto.conf   #一个用于存储由ALTER SYSTEM 设置的配置参数的文件
postgresql.conf
postmaster.opts   #一个记录服务器最后一次启动时使用的命令行参数的文件
server.log  #pg操作日志，如果有报错也会记录在这里

4.2 配置文件

pg_hba.conf：配置对数据库的访问权限;
postgresql.conf：配置PostgreSQL数据库服务器的相应的参数