创建pgsql的用户，并设置密码为123456 [root@pg1 ~]# useradd postgres [root@pg1 ~]# passwd postgres 返回结果： Changing password for user postgres. New password: 输入密码 BAD PASSWORD: The password is shorter than 8 characters Retype new password: 再次输入密码 passwd: all authentication tokens updated successfully. 安装依赖包 [root@pg1 ~]# yum groupinstall -y "Development Tools" "Legacy UNIX Compatibility" -y [root@pg1 ~]# yum install -y bison flex readline* zlib-devel gcc* gmake -y 创建目录 [root@pg1 ~]# mkdir -p /usr/local/pg12 [root@pg1 ~]# mkdir -p /pgdata/12/data [root@pg1 ~]# mkdir -p /archive 并授权目录的属主和属组 [root@pg1 ~]# chown -R postgres. /pgdata [root@pg1 ~]# chown -R postgres. /archive [root@pg1 ~]# chown -R postgres. /usr/local/pg12 [root@pg1 ~]# chmod 700 /pgdata/12/data -R 系统优化 [root@pg1 ~]# vi /etc/sysctl.conf 写入一下内容，在最后一行添加 kernel.shmmax = 68719476736 kernel.shmall = 4294967296 kernel.shmmni = 4096 kernel.sem = 50100 64128000 50100 1280 fs.file-max = 7672460 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 1048576 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 [root@pg1 ~]# vi /etc/security/limits.conf 写入一下内容，在最后一行添加 * soft nofile 131072 * hard nofile 131072 * soft nproc 131072 * hard nproc 131072 * soft core unlimited * hard core unlimited * soft memlock 50000000 * hard memlock 50000000 让配置生效 [root@pg1 ~]# sysctl -p 返回结果： kernel.shmmax = 68719476736 kernel.shmall = 4294967296 kernel.shmmni = 4096 kernel.sem = 50100 64128000 50100 1280 fs.file-max = 7672460 net.ipv4.ip_local_port_range = 9000 65000 net.core.rmem_default = 1048576 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576 ##建议关闭numa ##设置IO策略为deadline(机械)或者noop(SSD) 源码安装pg，上传软件至/opt，并解压 解压 [root@pg1 ~]# tar xf postgresql-12.6.tar.gz 进入到解压目录 [root@pg1 ~]# cd postgresql-12.6/ 编译安装，安装到/usr/local/pg12 [root@pg1 postgresql-12.6]# ./configure --prefix=/usr/local/pg12 --with-pgport=1921 [root@pg1 postgresql-12.6]# gmake world [root@pg1 postgresql-12.6]# gmake install-world 切换到postgres用户 [root@pg1 postgresql-12.6]# su - postgres 添加环境变量 [postgres@pg1 ~]$ vim .bash_profile 写入一下内容，在最后一行添加 export PGDATA=/pgdata/12/data$ export.utf8$ export PGHOME=/usr/local/pg12$ export LD_LIBRARY_PATH=$PGHOME/lib:/lib64:/usr/lib64:/usr/local/lib64:/lib:/usr/lib:/usr/local/lib:$LD_LIBRARY_PATH$ export DATE=`date +"%Y%m%d%H%M"`$ export PATH=$PGHOME/bin:$PATH:.$ export MANPATH=$PGHOME/share/man:$MANPATH$ export PGUSER=postgre 让配置环境变量生效 [postgres@pg1 ~]$ source .bash_profile 测试环境变量是否生效 [postgres@pg1 ~]$ psql --version psql (PostgreSQL) 12.6 初始化环境 [postgres@pg1 ~]$ initdb -A md5 -D $PGDATA -E utf8 --locale=C -W 返回结果： 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 "C". The default text search configuration will be set to "english". Data page checksums are disabled. Enter new superuser password: 123456 Enter it again: 123456 fixing permissions on existing directory /pgdata/12/data ... ok creating subdirectories ... ok selecting dynamic shared memory implementation ... posix selecting default max_connections ... 100 selecting default shared_buffers ... 128MB selecting default time zone ... Asia/Shanghai creating configuration files ... ok running bootstrap script ... ok performing post-bootstrap initialization ... ok syncing data to disk ... ok Success. You can now start the database server using: pg_ctl -D /pgdata/12/data -l logfile star 启动pgsql [postgres@pg1 ~]$ pg_ctl -D /pgdata/12/data/ start -l logfile 返回结果： waiting for server to start.... done server started 进入到pgsql [postgres@pg1 ~]$ psql Password for user postgres: 123456 psql (12.6) Type "help" for help. postgres=# 创建edu的数据库 postgres=# create database edu; CREATE DATABASE 查看edu，进入到edu,并创建t1表 进入到edu postgres=# \c edu You are now connected to database "edu" as user "postgres". 创建t1表 edu=# create table t1 (id int); CREATE TABLE 插入t1表 edu=# insert into t1 values(1); INSERT 0 1 查看t1表 edu=# select * from t1; 返回结果： id ---- 1 (1 row) 查看所以库 edu=# \l List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+---------+-------+----------------------- edu | postgres | UTF8 | C | C | postgres | postgres | UTF8 | C | C | template0 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | C | C | =c/postgres + | | | | | postgres=CTc/postgr edu=# \d List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row) edu=# \dt List of relations Schema | Name | Type | Owner --------+------+-------+---------- public | t1 | table | postgres (1 row) 启动关闭 手工方式 pg_ctl -D /pgdata/12/data/ -l logfile start pg_ctl -D /pgdata/12/data/ stop -ms pg_ctl -D /pgdata/12/data/ stop -mf pg_ctl -D /pgdata/12/data/ stop -mi pg_ctl restart -mf 脚本方式 /opt/postgresql-12.6/contrib/start-scripts/linux
