# ubuntu/debian package install 

sudo apt update
sudo apt -y upgrade
sudo apt -y install vim bash-completion wget

# 添加PostgreSQL的APT源
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
# 添加公钥签名
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
# 更新包列表
sudo apt update

# Install the latest version of PostgreSQL.
# If you want a specific version, use 'postgresql-12' or similar instead of 'postgresql':
sudo apt install postgresql-13 postgresql-client-13

systemctl status postgresql.service 

Ver  Cluster    Port   Status   Owner      Data directory                    Log file
13    main      5432   down     postgres   /var/lib/postgresql/13/main       /var/log/postgresql/postgresql-13-main.log

pg_ctlcluster 13 main start

apt-get install bison flex libsystemd-dev   libxml2-utils

# git clone源代码
git clone https://git.postgresql.org/git/postgresql.git 


# 官网下载二进制，一般下比较新的版本
wget https://ftp.postgresql.org/pub/source/v13.2/postgresql-13.2.tar.gz -P /usr/local/src/
wget https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v14.4/postgresql-14.4.tar.gz -P /usr/local/src/
wget https://mirrors.ustc.edu.cn/postgresql/source/v14.4/postgresql-14.4.tar.gz -P /usr/local/src/
cd /usr/local/src/  && tar -zxvf postgresql-14.4.tar.gz


# 创建用于运行postgre的操作系统账号，并设置强密码
groupadd postgres &&  useradd -g postgres postgres
echo "postgres@123" | passwd --stdin postgres

# 创建安装目录
mkdir -p /data/postgres/data
chown -R postgres:postgres /data/postgres/


cd /usr/local/src/postgresql-14.4
# 如果不自定义目录，后面的安装默认会安到/usr/local/pgsql/bin中
./configure --prefix=/data/postgres/14.4/  --with-systemd
make world
make install


su - postgres

# 初始化，初始化必须要切到postgres用户，不能用root用户执行初始化命令
# 在初始化数据库系统时有一个预定义的超级用户，这个用户的名称与初始化该数据库的操作系统用户名相同。


# 参数
/data/postgres/14.4/bin/initdb -D /data/postgres/14.4/data



# 启动
# bin目录中的postgres就是postgre server进程的二进制可执行文件

# 直接前台启动，-D指定数据目录，如果没有-D，则用PGDATA环境变量。
postgres -D /usr/local/pgsql/data

# pg_ctl是一个包装命令，可以直接后台启动postgre进程
/usr/local/pgsql/bin/pg_ctl start -l logfile -D /usr/local/pgsql/data

# 登录
./psql  -h localhost -p 54321 -U postgres -w password -d postgres

# 修改密码
ALTER USER postgres WITH PASSWORD 'postgres@123';



# 注册成系统服务，/etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Type=notify
User=postgres
ExecStart=/usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=infinity
[Install]
WantedBy=multi-user.target

configuration_parameter = value 或 configuration_parameter = 'value'

# 切换到Linux操作系统上的postgre用户
su postgres 

# 登陆: 主机，端口，用户名，密码，使用的数据库
psql  -h localhost -p 54321 -U postgres -w password -d postgres
#
password postgres    

-- 创建普通用户
CREATE ROLE david   WITH PASSWORD '123456';　   -- 默认不带LOGIN属性
CREATE USER fengzhao WITH PASSWORD '123456';　　-- 默认具有LOGIN属性

-- 在PostgreSQL 里没有区分用户和角色的概念，"CREATE USER" 为 "CREATE ROLE" 的别名
-- 这两个命令几乎是完全相同的，唯一的区别是"CREATE USER"命令创建的用户默认带有LOGIN属性，而"CREATE ROLE"命令创建的用户默认不带LOGIN属性
-- (CREATE USER is equivalent to CREATE ROLE except that CREATE USER assumes LOGIN by default, while CREATE ROLE does not)

-- 用户sandy可以登录，角色david不可以登录。
-- 角色就相当于岗位：角色可以是经理，助理。
-- 用户就是具体的人：比如陈XX经理，朱XX助理，王XX助理。

-- PostgreSQL安装后默认用户名和数据库也是"postgres"，默认没有密码。在安装PostgreSQL之后可以以默认用户登录，也可以创建新当用户名。
-- 强烈建议安装后第一件事就是给默认的postgres用户创建一个

-- 官方建议是这样的：

-- 在管理员创建一个具体数据库后，应该为所有可以连接到该数据库的用户分别创建一个与用户名相同的模式，然后，将search_path设置为"$user"(即缺省模式为与用户名相同的模式)。
-- 这样任何当某个用户连接上来后，会默认将查找或者定义的对象都定位到与之同名的模式中。这是一个好的设计架构。

-- 不建议业务项目的表存放在public下：
--  1、是数据安全；
--  2、表存放混乱；不利于后期维护等等。


SELECT rolname from pg_roles ;
SELECT usename from pg_user;  -- 角色david创建时没有分配login权限，所以没有创建用户



create database dbtest;

schema_element
CREATE SCHEMA schema_name [AUTHORIZATION username]   [schema_element [ ... ]]
-- schema_name，创建方案的名称，如果省略，则当前用户名被用作方案名。该名字不能以 pg_ 开头，因为这些名字是为系统目录方案保存的。
-- rolename，拥有该方案的角色的名称。如果省略，默认为执行该命令的角色。只有超级用户可以创建属于其他角色的方案，除了他们自己。
-- schema_element ，定义在方案中创建对象的 SQL 语句。比如 CREATE TABLE、CREATE VIEW、CREATE INDEX 等语句

# 查看帮助
help

# 查看 psql 命令详情
\?

# 查看数据库（全部）
\l

# 查看数据库（指定）
\l <database-name>

# 连接数据库
\c <database-name>

# 查看方法
\df

# 查看表（全部）
\d

# 查看表（只看表）
\dt

# 查看表（指定）
\d <table-name>

# 执行 sql 命令
\i <filepath>/<filename>

# 打开拓展视图
\x

# 导出至 CSV
\copy (SELECT * FROM person LEFT JOIN car ON person.car_id = car.id) TO 'path/to/output.csv' DELIMITER ',' CSV HEADER;

# 退出
\q

CREATE ROLE  david 
    SUPERUSER|NOSUPERUSER       /*是否超级用户，默认否*/     
    CREATEDB|NOCREATEDB         /*是否能建DB，默认否*/     
    CREATEROLE|NOCREATEROLE     /*是否能建ROLE，默认否*/     
    INHERIT|INHERIT             /*继承*/
    LOGIN|NOLOGIN               /*是否能登录，默认否*/    /*能登陆的role等价于user*/
    REPLICATION|NOREPLICATION   
ENCRYPTED PASSWORD 'david@123';



CREATE ROLE david;　　/**默认不带LOGIN属性**/
CREATE USER sandy;　　/**默认具有LOGIN属性**/
-- 用户sandy可以登录，角色david不可以登录。

SELECT rolname from pg_roles ;
SELECT usename from pg_user;  --角色david创建时没有分配login权限，所以没有创建用户

-- 其中，privilege_list 权限列表可以是 SELECT、INSERT、UPDATE、DELETE、TRUNCATE等，ALL表示表上的所有权限。
GRANT privilege_list | ALL  ON [ TABLE ] table_name   TO role_name;

-- ALL TABLES IN SCHEMA 表示某个模式中的所有表，可以方便批量授权操作。
GRANT privilege_list | ALL  ON ALL TABLES IN SCHEMA schema_name   TO role_name;

-- 创建一个组角色，按照习惯，组角色通常不具有 LOGIN 特权，也就是不能作为一个用户登录。
CREATE ROLE group_name;


-- 使用与对象授权操作相同的 GRANT 和 REVOKE 语句为组添加和删除成员

#/bin/bash

OPT_BASE=/opt
PGVERSION=15.0
SOURCE_BINARY_FILE=https://mirrors.tuna.tsinghua.edu.cn/postgresql/source/v${PGVERSION}/postgresql-${PGVERSION}.tar.gz 
PGBASE=$OPT_BASE/local/pgsql
PGHOME=$OPT_BASE/local/pgsql/$PGVERSION
PGDATA=$OPT_BASE/var/pgsql/$PGVERSION

### 检查是否 root 用户
[ $(id -u) != "0" ] && { echo "${CFAILURE}Error: You must be root to run this script${CEND}"; exit 1; }

### 检查是否存在某个文件，是否存在可执行的二进制文件
if  [ ! -e '/usr/bin/wget' ]; then
    echo "Error: wget command not found. You must be install wget command at first."
    exit 1
fi

if [ -d $PGHOME ]; then
  rm -rf $PGHOME
elif [ ! -d $PGBASE ]; then
  mkdir -p $PGBASE
fi

if [ ! -d $PGDATA ]; then
  mkdir -p $PGDATA
fi

echo "DownLoading PostgreSQL"
wget -P ${OPT_BASE}  ${SOURCE_BINARY_FILE}
md5_sum=$(curl -s https://ftp.postgresql.org/pub/source/v${PGVERSION}/postgresql-${PGVERSION}.tar.gz.md5)


echo "Install PostgreSQL"
cd /opt/
tar zxf $PG_SOFT_TAR -C $PGBASE
mv $PGBASE/pgsql $PGHOME
cp pg-pwfile $PGHOME

echo "Init PostgreSQL"
pushd $PGHOME
./bin/initdb --pgdata="$PGDATA" --auth=ident --auth-host=md5 --encoding=UTF-8 --locale=zh_CN.UTF-8 --username=postgres --pwfile=pg-pwfile
rm -f pg-pwfile
popd

cp pg_hba.conf $PGDATA
cp postgresql.conf $PGDATA
chmod 600 $PGDATA/*.conf

echo "Start PostgreSQL"
$PGHOME/bin/pg_ctl -D $PGDATA -l logfile start
sleep 5
#cp .pgpass ~/
$PGHOME/bin/psql -h localhost -U postgres -d postgres -f pg_init.sql