PostgreSQL 基础

PostgreSQL系列

一: postgreSQL简介

二：postgreSQL 在行业的应用

三：postgreSQL的发展历史

四：postgreSQL的版本的特性

postgreSQL 12 版本特性

五：postgreSQL 数据库 与其它数据的对比

六:postgreSQL 的 体系结构

七： postgresql的 逻辑结构

八： postgreSQL 的存储结构

create user flyfish with password '123.com' nocreatedb;
create database flyfishdb with owner=flyfish;

psql.exe -h 192.168.100.90 -p 5432 -U flyfish -d flyfishdb
create table flyfishtable(name varchar(50));
insert into flyfishtable values('haha');
commit ;
select * from flyfishtable

九：postgresql 的数据库表：

十：postgresql 的安装部署：

10.1 postgresql 在windows 下面的安装

windows server 2019x64 准备
  下载 postgresql
  https://www.enterprisedb.com/download-postgresql-binaries

10.2 postgresql 在Linux 下面的安装

环境准备：
echo "export LANG=en_US.UTF8" >> ~/.bash_profile
cat ~/.bash_profile
source ~/.bash_profile
mount /dev/cdrom /mnt
cd /etc/yum.repos.d
mkdir bk
mv *.repo bk/
echo "[EL7]" >> itpux.repo
echo "name = linux 7.6 dvd" >> itpux.repo
echo "baseurl=file://mnt" >> itpux.repo
echo "gpgcheck=0" >> itpux.repo
echo "enabled=1" >> itpux.repo
yum list
systemctl set-default multi-user.target
systemctl get-default
echo "vm.swappiness=10" >> /etc/sysctl.conf
echo "fs.aio-max-nr = 1048576" >> /etc/sysctl.conf
echo "fs.file-max = 6815744" >> /etc/sysctl.conf
echo "net.ipv4.ip_local_port_range = 9000 65500" >> /etc/sysctl.conf
echo "net.core.rmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.rmem_max = 4194304" >> /etc/sysctl.conf
echo "net.core.wmem_default = 262144" >> /etc/sysctl.conf
echo "net.core.wmem_max = 1048586" >> /etc/sysctl.conf
echo "kernel.shmmax = 1288490188" >> /etc/sysctl.conf
echo "kernel.shmall = 314572" >> /etc/sysctl.conf
echo "kernel.shmmni = 4096" >> /etc/sysctl.conf
echo "kernel.sem = 4096 2147483647 2147483646 512000" >> /etc/sysctl.conf
sysctl -p
echo "* - nproc unlimited" > /etc/security/limits.d/90-nproc.conf
echo "SELINUX=disable" >/etc/selinux/config
echo "#SELINUXTYPE=trageted" >> /etc/selinux/config
cat /etc/selinux/config
setenforce 0
systemctl status firewalld.service
systemctl stop firewalld.service
systemctl disable firewalld.service
echo "session required pam_limits.so" >> /etc/pam.d/login
cat /etc/pam.d/login
echo "* soft nproc unlimited" >>
/etc/security/limits.conf
echo "* hard nproc unlimited" >>
/etc/security/limits.conf
echo "* soft nofile 16384" >>
/etc/security/limits.conf
echo "* hard nofile 65536" >>
/etc/security/limits.conf
echo "* soft stack unlimited" >> /etc/security/limits.conf
echo "* hard stack unlimited" >>
/etc/security/limits.conf
cat /etc/security/limits.conf
rm -f /etc/security/limits.d/*20*
*/
vi /etc/default/grub
GRUB_CMDLINE_LINUX="crashkernel=auto rhgb quiet numa=off
transparent_hugepage=never"
grub2-mkconfig -o /etc/grub2.cfg
numastat
numactl --show
numactl --hardware
shutdown -r now
rm -f /etc/udev/rules.d/70-persistent-net.rules
hostname itpuxpg61
echo "itpuxpg61" > /etc/hostname
vi /etc/sysconfig/network-scripts/ifcfg-ens33
改 IP、删 UUID
reboot

安装postgreSQL 
安装依赖包
yum install -y cmake make gcc zlib gcc-c++ perl readline readline-devel zlib zlib-devel perl python36 tcl openssl ncurses-devel openldap pam

创建 PostgreSQL 用户
groupadd -g 60000 pgsql
useradd -u 60000 -g pgsql pgsql
echo "pgsql" |passwd --stdin pgsql
创建安装目录
mkdir -p /postgresql/{pgdata,archive,scripts,backup,pg12,soft}
chown -R pgsql:pgsql /postgresql
chmod -R 775 /postgresql

编译postgresql 
cd /postgresql/soft 
tar -zxvf postgresql-12.2.tar.gz 
cd postgresql-12.2 
./configure --prefix=/postgresql/pg12 --without-readline  (表示不显示历史)
make 
make install 

su - pgsql 
vim /root/.bash_profile
---
export LANG=en_US.UTF8
export PS1="[`whoami`@`hostname`:"'$PWD]$'
export PGPORT=5432
export PGDATA=/postgresql/pgdata
export PGHOME=/postgresql/pgl2
export
LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/l
ib:/usr/local/lib:$LD_LIBRARY_PATH
export PATH=$PGHOME/bin:$PATH:.
export DATE=`date +"%Y%m%d%H%M"`
export MANPATH=$PGHOME/share/man:$MANPATH
export PGHOST=$PGDATA
export PGUSER=postgres
export PGDATABASE=postgres
---
source /etc/profile

初始化
su - pgsql
/postgresql/pg12/bin/initdb -D /postgresql/pgdata -E UTF8 --locale=en_US.utf8 -U postgres
可有这个命令启动：
   /postgresql/pg12/bin/pg_ctl -D /postgresql/pgdata -l logfile start

cd /postgresql/pgdata/
vim postgresql.conf 
----
listen_addresses = '*'         
port = 5432                         
max_connections = 500
logging_collector = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_truncate_on_rotation = on
shared_buffers = 1024MB
max_wal_size = 1GB
min_wal_size = 80MB
----
vim pg_hba.conf 
---
host    all             all             0.0.0.0/0            md5
----

cd /postgresql/pg12/bin/
./pg_ctl start  启动
./pg_ctl stop   停止
/postgresql/pg12/bin/pg_ctl -D /postgresql/pgdata -l logfile start 启动
/postgresql/pg12/bin/pg_ctl -D /postgresql/pgdata -l logfile stop 停止
---
vi /etc/systemd/system/postgresql.service
[Unit]
Description=PostgreSQL database server
Documentation=man:postgres(1)
[Service]
Type=notify
User=pgsql
ExecStart= /postgresql/pg12/bin/postgres -D /postgresql/pgdata
ExecReload=/bin/kill -HUP $MAINPID
KillMode=mixed
KillSignal=SIGINT
TimeoutSec=0
[Install]
WantedBy=multi-user.target
----
chmod +x /etc/systemd/system/postgresql.service
service postgresql start 
数据库 一般不 做 开机启动

连接数据
psql -h127.0.0.1 -p5432

设置密码：
\password postgres   (设置密码：123.com)
或者：
  alter user postgres with password '123.com';

psql -h192.168.100.11 -U postgres -p5432

create user flyfish with password '123.com' nocreatedb;
create database flyfishdb with owner=flyfish;

psql -h 192.168.100.11 -p 5432 -U flyfish -d flyfishdb
create table flyfishtable(name varchar(50));
insert into flyfishtable values('haha');
commit ;
select * from flyfishtable;

\c flyfishdb flyfish 切换 数据库
\dt +   查看 数据库下面的表
\dt flyfishtable  查看 表的 信息
\d flyfishtable   查看表结构