记一次|Centos7 安装postgres14.4

1、访问postgresql官网获取资源

外网环境官方给的安装方式：

# Install the repository RPM:
sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# Install PostgreSQL:
sudo yum install -y postgresql14-server

# Optionally initialize the database and enable automatic start:
sudo /usr/pgsql-14/bin/postgresql-14-setup initdb
sudo systemctl enable postgresql-14
sudo systemctl start postgresql-14
复制

本地环境下载安装：
https://www.postgresql.org/ftp/source/v14.4/
下载postgresql-14.4.tar.gz

[root@localhost ~]# wget https://ftp.postgresql.org/pub/source/v14.4/postgresql-14.4.t
--2023-02-28 22:43:15--  https://ftp.postgresql.org/pub/source/v14.4/postgresql-14.4.t
Resolving ftp.postgresql.org (ftp.postgresql.org)... 72.32.157.246, 87.238.57.227, 147
Connecting to ftp.postgresql.org (ftp.postgresql.org)|72.32.157.246|:443... connected.
WARNING: cannot verify ftp.postgresql.org's certificate, issued by ‘/C=US/O=Let's Encr
  Issued certificate has expired.
HTTP request sent, awaiting response... 200 OK
Length: 28948336 (28M) [application/octet-stream]
Saving to: ‘postgresql-14.4.tar.gz.1’

100%[=================================================================================

2023-02-28 22:43:21 (6.01 MB/s) - ‘postgresql-14.4.tar.gz.1’ saved [28948336/28948336]
复制

2、安装依赖

[root@localhost ~]# yum install -y perl-ExtUtils-Embed readline-devel zlib-devel pam-devel libxml2-devel libxslt-devel openldap-devel python-devel gcc-c++ openssl-devel cmake
复制

3、安装postgres

在/usr/local新建pgsql文件夹，并将pgsql的压缩包移入;编译postgresql源码。

[root@localhost opt]# tar -zxvf /root/postgresql-14.4.tar.gz
postgresql-14.4/
[root@localhost opt]# cd /usr/local/
[root@localhost local]# ls
bin  etc  games  include  lib  lib64  libexec  sbin  share  src
[root@localhost local]# mkdir pgsql
[root@localhost local]# cd pgsql/
[root@localhost pgsql]# mv /opt/postgresql-14.4/ ./
[root@localhost pgsql]#  cd postgresql-14.4/
[root@localhost postgresql-14.4]# ls
aclocal.m4  configure     contrib    doc             HISTORY  Makefile  src
config      configure.ac  COPYRIGHT  GNUmakefile.in  INSTALL  README
[root@localhost postgresql-14.4]# pwd
/usr/local/pgsql/postgresql-14.4
[root@localhost postgresql-14.4]#  ./configure --prefix=/usr/local/pgsql/postgresql-14.4 && make && make instal
复制

4、创建用户组postgres并创建用户postgres；创建postgresql数据库的数据主目录并修改文件所有者。

[root@localhost pgsql]# groupadd postgres
[root@localhost pgsql]# useradd -g postgres postgres
[root@localhost pgsql]# id postgres
uid=1002(postgres) gid=1004(postgres) groups=1004(postgres)
[root@localhost pgsql]# passwd postgres
Changing password for user postgres.
New password:
Retype new password:
passwd: all authentication tokens updated successfully.
[root@localhost pgsql]# mkdir data
[root@localhost pgsql]# chown -R  postgres:postgres /usr/local/pgsql/
[root@localhost pgsql]# ls -lh
total 20K
drwxr-xr-x  2 postgres postgres 4.0K Feb 28 23:26 bin
drwxr-xr-x  2 postgres postgres    6 Feb 28 23:29 data
drwxr-xr-x  6 postgres postgres 4.0K Feb 28 23:26 include
drwxr-xr-x  4 postgres postgres 4.0K Feb 28 23:26 lib
drwxrwxrwx 10 postgres postgres 4.0K Feb 28 23:27 postgresql-14.4
drwxr-xr-x  6 postgres postgres 4.0K Feb 28 23:26 share
[root@localhost pgsql]# su - postgres
[postgres@localhost ~]$ vim .bash_profile
export PGHOME=/usr/local/pgsql/postgresql-14.4/
export PGDATA=/usr/local/pgsql/data
PATH=$PATH:$PGHOME/bin
export PATH
[postgres@localhost ~]$ source  .bash_profile
复制

5、使用initdb初使用化数据库

[postgres@localhost ~]$ ls /usr/local/pgsql/data/
[postgres@localhost ~]$ initdb
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 /usr/local/pgsql/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

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:

    pg_ctl -D /usr/local/pgsql/data -l logfile start

[postgres@localhost ~]$ ls /usr/local/pgsql/data/
base          pg_ident.conf  pg_serial     pg_tblspc    postgresql.auto.conf
global        pg_logical     pg_snapshots  pg_twophase  postgresql.conf
pg_commit_ts  pg_multixact   pg_stat       PG_VERSION
pg_dynshmem   pg_notify      pg_stat_tmp   pg_wal
pg_hba.conf   pg_replslot    pg_subtrans   pg_xact
复制

6、配置服务

修改/usr/local/pgsql/data/目录下的两个文件。
postgresql.conf 配置PostgreSQL数据库服务器的相应的参数;pg_hba.conf 配置对数据库的访问权限。

[postgres@localhost data]$ vim postgresql.conf
listen_addresses = '*'
port = 543
[postgres@localhost data]$ vim pg_hba.conf
host all all 0.0.0.0/0 trust
host all all 127.0.0.1/32 trust
复制

7、设置PostgreSQL开机自启动

PostgreSQL的开机自启动脚本位于PostgreSQL源码目录的contrib/start-scripts路径下。

linux文件即为linux系统上的启动脚本
1）修改linux文件属性，添加X属性

[postgres@localhost data]$ cd /usr/local/pgsql/postgresql-14.4/contrib/start-scripts
[postgres@localhost start-scripts]$ ls
freebsd  linux  macos
[postgres@localhost start-scripts]$ chmod a+x linux
复制

2. 复制linux文件到/etc/init.d目录下，更名为postgresql

[root@localhost pgsql]# cp /usr/local/pgsql/postgresql-14.4/contrib/start-scripts/linux  /etc/init.d/postgresql
复制

3）修改/etc/init.d/postgresql文件的两个变量
prefix设置为postgresql的安装路径：/usr/local/pgsql/postgresql/postgresql-14.4
PGDATA设置为postgresql的数据目录路径：/usr/local/pgsql/data

[root@localhost pgsql]# vim /etc/init.d/postgresql
prefix=/usr/local/pgsql/postgresql-14.4
# Data directory
PGDATA="/usr/local/pgsql/data"
复制

4）设置postgresql服务开机自启动
查看开机自启动服务设置成功。

[root@localhost pgsql]# vim /etc/init.d/postgresql
[root@localhost pgsql]# chkconfig --add postgresql
[root@localhost pgsql]# chkconfig

postgresql      0:off   1:off   2:on    3:on    4:on    5:on    6:off
复制

8、执行service postgresql start，启动PostgreSQL服务;查看PostgreSQL服务。

[root@localhost data]# service postgresql start
Starting PostgreSQL: ok
[root@localhost data]# service postgresql status
pg_ctl: server is running (PID: 21507)
/usr/local/pgsql/postgresql-14.4/bin/postgres "-D" "/usr/local/pgsql/data"
[root@localhost data]# ps -ef |grep postgres
postgres  21507      1  0 00:47 ?        00:00:00 /usr/local/pgsql/postgresql-14.4/bin/postmaster -D /usr/local/pgsql/data
postgres  21509  21507  0 00:47 ?        00:00:00 postgres: checkpointer
postgres  21510  21507  0 00:47 ?        00:00:00 postgres: background writer
postgres  21511  21507  0 00:47 ?        00:00:00 postgres: walwriter
postgres  21512  21507  0 00:47 ?        00:00:00 postgres: autovacuum launcher
postgres  21513  21507  0 00:47 ?        00:00:00 postgres: stats collector
postgres  21514  21507  0 00:47 ?        00:00:00 postgres: logical replication launcher
root      21547   1367  0 00:47 pts/1    00:00:00 grep --color=auto postgres
复制

9、修改密码，创建用户、数据库登录

[postgres@localhost ~]$ psql
psql (14.4)
Type "help" for help.
postgres=# \password
Enter new password for user "postgres":
Enter it again:

postgres=# create user csadmin with password 'Csadmin';
CREATE ROLE
postgres=# create user csadmin with password 'Csadmin';
ERROR:  role "csadmin" already exists
postgres=# alter role csadmin with superuser;
ALTER ROLE
postgres=# create database csdb ;
CREATE DATABASE
postgres=# \q
[postgres@localhost ~]$ psql -U csadmin -W csdb
Password:
psql (14.4)
Type "help" for help.
csdb=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 csdb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)
复制