随笔 - 404  文章 - 4  评论 - 0  阅读 - 25万

【PgSQL日常管理】PostgreSQL12.3部署

  • 一、服务器环境信息

软件版本：postgresql12.3

二进制安装：postgresql-12.3.tar.gz.tar.gz

postgresql-12.3.tar.gz.md5：4aeff45d4180d8d8cdb907a0e4690da2 postgresql-12.3.tar.gz

postgresql-12.3.tar.gz.sha256：708fd5b32a97577679d3c13824c633936f886a733fc55ab5a9240b615a105f50 postgresql-12.3.tar.gz

操作系统信息：

# cat /etc/redhat-release
CentOS Linux release 7.4.1708 (Core)

# free -m
total used free shared buff/cache available
Mem: 7983 295 7013 16 674 7403
Swap: 8191 0 8191

# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 4
On-line CPU(s) list: 0-3
Thread(s) per core: 1
Core(s) per socket: 1
Socket(s): 4
NUMA node(s): 1 

  • 二、安装依赖包

yum -y install readline readline-devel zlib-devel

  • 三、创建相应的文件目录、用户及修改环境变量

  • 添加postgresql用户及用户组

#groupadd -g 5432 postgres
#useradd -u 5432 -g 5432 -m -d /home/postgres -s /bin/bash  postgres
#passwd postgres

  • 系统数据目录、日志目录、表空间目录、wal日志目录

mkdir -p /pgsql/{pg_home,pg_base/{pgdata,pg_log,tbsdata,pg_arch}}

#pg_home：postgresql软件目录、编译安装目录

#pg_base：postgresql数据目录，包括数据目录、日志目录、表空间目录、归档日志目录

chown -R postgres.postgres /pgsql

chmod 700 /pgsql/pg_base/pgdata

#这里业务库为小库，或未挂在/pgsql盘的情况下，可选/home/postgres进行安装部署，即配置/home/postgres/{pg_home,pg_base/{pgdata,pg_log,tbsdata,pg_arch}}

  • 配置postgresql环境变量

su - postgres

export PGHOME=/pgsql/pg_home
export PGDATA=/pgsql/pg_base/pgdata
export LD_LIBRARY_PATH=/pgsql/pg_home/lib

若配置物理备份pg_rman，需增加pg_rman的环境变量配置，需新增备份盘/pgshare
#for pg_rman
export BACKUP_PATH=/pgshare/rman
export ARCLOG_PATH=/pgsql/pg_base/pg_arch
export SRVLOG_PATH=/pgsql/pg_base/pg_log

export PATH=$PATH:$HOME/.local/bin:$HOME/bin:$PGHOME/bin 

  • 四、root用户编译安装PostgreSQL

解压软件安装包：tar -zxvf postgresql-12.3.tar.gz -D /pgdata/

mv postgresql-12.3 pg_home

cd  /pgsql/pg_home
./configure
make && make install

自定义安装目录编译方式，如：

方式一：

#./configure --prefix=/pgsql/pg_home  --with-libedit-preferred --with-perl --with-python --with-openssl --with-libxml --with-libxslt --enable-thread-safety --enable-nls=en_US.UTF-8

方式二：提前定义wal segment方式

#./configure --prefix=/pgsql/pg_home --with-segsize=16 --with-blocksize=32 --with-wal-segsize=64 --with-wal-blocksize=64 --with-libedit-preferred --with-perl --with-python --with-openssl --with-libxml --with-libxslt --enable-thread-safety --enable-nls=en_US.UTF-8
#PostgreSQL默认安装目录为 /usr/local/pgsql/

#默认端口：5432
修改属性权限

chown -R postgres.postgres /pgsql/pg_home

  • 五、初始化启动数据库

su - postgres
/pgsql/pg_home/bin/initdb -D /pgsql/pg_base/pgdata
$ /pgsql/pg_home/bin/initdb -D /pgsql/pg_base/pgdata/

复制代码
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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/pgsql_5432/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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/pgsql/bin/pg_ctl -D /data/pgsql_5432/pgdata/ -l logfile start
View Code
复制代码

  • 启动数据库

/pgsql/pg_home/bin/pg_ctl -D /pgsql/pg_base/pgdata/ -l /pgsql/pg_base/pg_log/postgres.log start

ln -s /pgsql/pg_home/bin/psql /usr/bin/psql
ln -s /pgsql/pg_home/bin/pg_ctl /usr/bin/pg_ctl

#数据库启停命令

pg_ctl -D /pgsql/pg_base/pgdata status
pg_ctl -D /pgsql/pg_base/pgdata stop -m fast
pg_ctl -D /pgsql/pg_base/pgdata start

#修改postgres用户登录密码
ALTER user postgres with password 'xxxxxx';

#编辑postgresql.conf

复制代码
listen_addresses = '*'
port = 5432
max_connections = 2000

logging_collector = on
log_directory = '/data/pgsql_5432/logs/'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S'

shared_buffers = 4096MB
temp_buffers = 16MB
work_mem = 32MB
effective_cache_size = 10GB
maintenance_work_mem = 128MB
#max_stack_depth = 2MB
dynamic_shared_memory_type = posix
View Code
复制代码

编辑pg_hba.conf

local   all             all                                      md5
host    pgdb            srv_pgdb        0.0.0.0/0                md5
host    all             all             ::1/128                  ident
View Code

 

#创建业务用户srv_pgtest
create user srv_pgtest with ENCRYPTED password 'srv_pgtest';
#创建schema，并赋予所有者为用户srv_pgtest
CREATE SCHEMA pgtest;
ALTER SCHEMA pgtest OWNER to srv_pgtest;
#创建所有者是srv_qhms的数据库,owner是role，不是schema
CREATE DATABASE pgtest WITH OWNER srv_pgtest ENCODING UTF8 TEMPLATE template0;

 

  • 六、多实例部署-部署1921端口实例

一、创建系统数据目录、日志目录、表空间目录、wal日志目录
mkdir -p /pgsql/pg_1921/{pgdata,pg_log,tbsdata,pg_arch}
chown -R postgres.postgres /pgsql/pg_1921/

chmod 700 /pgsql/pg_1921/pgdata

初始化数据库
su - postgres
/bin/initdb -D /pgsql/pg_1921/pgdata/

复制代码
$ /bin/initdb -D /pgsql/pg_1921/pgdata/
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 "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /data/pgsql_1921/pgdata ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... PRC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

initdb: 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:

    /pgsql/pg_home/bin/pg_ctl -D /pgsql/pg_1921/pgdata/ -l logfile start
复制代码

配置postgresql.conf

 

启动数据库

$/bin/pg_ctl -D /pgsql/pg_1921/pgdata/ -l /pgsql/pg_1921/pg_log/postgres.log start

 连接1921端口PG实例：

$ psql -h localhost -p 1921
psql (12.3)
Type "help" for help.

postgres=#

 

posted on   HelonTian  阅读(1254)  评论(0)  编辑  收藏  举报
编辑推荐：
· SQL Server 2025 AI相关能力初探
· Linux系列：如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列（二）：开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
阅读排行：
· 阿里最新开源QwQ-32B，效果媲美deepseek-r1满血版，部署成本又又又降低了！
· SQL Server 2025 AI相关能力初探
· AI编程工具终极对决：字节Trae VS Cursor，谁才是开发者新宠？
· 开源Multi-agent AI智能体框架aevatar.ai，欢迎大家贡献代码
· Manus重磅发布：全球首款通用AI代理技术深度解析与实战指南
< 2025年3月 >
日 一 二 三 四 五 六
23 24 25 26 27 28 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 31 1 2 3 4 5

Powered by: 博客园 Copyright © 2025 HelonTian
Powered by .NET 9.0 on Kubernetes
点击右上角即可分享
微信分享提示
AI IDE Trae