PG入门（1）postgreSQL源码包安装及开启归档

下载源码包

https://www.postgresql.org/ftp/source/v12.7/

安装必要的包

yum -y install readline readline-devel zlib zlib-devel gettext gettext-devel openssl openssl-devel pam pam-devel libxml2 libxml2-devel libxslt libxslt-devel perl perl-devel tcl-devel uuid-devel gcc gcc-c++ make flex bison perl-ExtUtils*

配置目录

userdel -r postgres
#源文件目录
 mkdir -p /opt/soft
#安装文件目录
  mkdir -p /opt/pg12
#数据目录
 mkdir -p /opt/pgdata
#WAL 目录
 mkdir -p /opt/pgwal
#归档目录
mkdir -p /opt/pgarchive

chown -R postgres:postgres  /opt
chmod 0700 /opt/pgdata /opt/pgwal /opt/pgarchive

解压

cd /opt/soft
tar -xzvf postgresql-12.7.tar.gz

编译安装

su - postgres
cd  /opt/soft
./configure --prefix=/opt/pg12 --with-pgport=5432
gmake world
gmake install-world

数据库初始化和启动

软链接
ln -s /opt/pg12 /opt/pgsql

初始化：输入密码123456
/opt/pgsql/bin/initdb -D/opt/pgdata -X/opt/pgwal -EUTF8 -Upostgres -W

启动
/opt/pgsql/bin/pg_ctl -D /opt/pgdata -l logfile start

查看版本
/opt/pg12/bin/postgres --version
postgres (PostgreSQL) 12.7

配置环境变量

vi ~/.bash_profile
export PGPORT=5432
export PGUSER=postgres
export PGHOME=/opt/pgsql
export PGDATA=/opt/pgdata
export PATH=$PGHOME/bin:$PATH

source ~/.bash_profile

数据库启停

查看数据库运行状态
pg_ctl -D /opt/pgdata status

启动数据库
pg_ctl -D /opt/pgdata start &
 
查看数据库运行状态
pg_ctl -D /opt/pgdata status
 
停止数据库
pg_ctl -D /opt/pgdata stop

postgres=# select version();

重启数据库
pg_ctl -D /opt/pgdata restart &

连接

netstat -lp --protocol=unix | grep postgres

psql -U postgres -h localhost -p 5432

psql -U postgres -h1.15.57.253  -p 5432

开启归档

show archive_command ;

cd $PGDATA
[postgres@VM-0-9-centos pgdata]$ cat postgresql.conf |grep archive_mode
#archive_mode = off             # enables archiving; off, on, or always
[postgres@VM-0-9-centos pgdata]$ vi postgresql.conf 

archive_mode = on           
archive_command = 'cp %p /opt/arch/%f'     

切换归档
select pg_switch_wal();