PostgreSQL源码包安装、开启归档、重要配置文件及参数设置

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  /optchmod 0700 /opt/pgdata /opt/pgwal /opt/pgarchive

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

su - postgrescd  /opt/soft./configure --prefix=/opt/pg12 --with-pgport=5432gmake worldgmake 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 --versionpostgres (PostgreSQL) 12.7

vi ~/.bash_profileexport PGPORT=5432export PGUSER=postgresexport PGHOME=/opt/pgsqlexport PGDATA=/opt/pgdataexport 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();

listen_addresses = '*'  #（关联配置文件pg_hba.conf）

port = 5866

max_connections = 100

shared_buffers

wal_buffers 

work_mem

maintenance_work_mem

logging_collector = on    

log_directory = 'log'        

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'  日志文件名字log_truncate_on_rotation = off    是否覆盖log_rotation_age = 1d             每天生成log_rotation_size = 10MB          每个日志大小

andom_page_cost = 2.5

autovacuum_max_workers = 10

checkpoint_completion_target = 0.7

archive_timeout =1800

archive_command = 'test ! -f /paic/dba/pgbackup/${PGNAME}/archlog/%f && pxz -2 < %p >/paic/dba/pgbackup/${PGNAME}/archlog/%f'

 SELECT pg_reload_conf();

 pg_ctl reload

ps -ef|grep -i postmaster|grep -v grep|xargs kill -HUP

show all;                       #查看所有数据库参数的值show shared_buffers;            #查看某个参数的当前值(可查看当前会话值)select current_setting('shared_buffers');

alter database mydb set work_mem='16MB';select * from pg_db_role_setting where setdatabase in (select oid from pg_database where datname in ('mydb')) ;

alter role user_t set work_mem='2MB';select * from pg_db_role_setting where setrole in (select usesysid from pg_user where usename in ('user_t'))order by setrole,setdatabase;

select name,setting,source from pg_settings where name='work_mem';