postgres+timescaledb--离线安装，centos7.9

操作系统是centos7.9，使用的hper-V,安装的虚拟机环境，安装好操作系统之后，让系统不连外网后直接按下方操作安装。

方式1，使用压缩包，复杂一点。（第一种方式暂时没有安装timescaledb）

装备安装包

1. postgresf服务安装包
   
2. 下载之后通过优盘或者ssh进入目标服务器，拷贝进去
   将下载的源码上传至目标服务器，这里我上传到：/usr/local/postgres14.5/src，我们可以创建这个目录，然后在上传到这个目录下

mkdir -p /usr/local/postgresql-16.4/src/

下图是我已经解压缩得到的文件夹postgresql-16.4：tar -xzvf postgresql-16.4.tar.gz

安装gcc环境

[roy@localhost my_gcc]$ gcc --version
gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44)
Copyright (C) 2015 Free Software Foundation, Inc.
This is free software; see the source for copying conditions.  There is NO
warranty; not even for MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.

安装icu库

[roy@localhost postgresql-16.4]$ rpm -q libicu
libicu-50.2-4.el7_7.x86_64

在这一步之前我是安装了libicud的，不过用的是libicu-50.2-4.el7_7.x86_64.rpm包，可能不需要上面这步，可以试试看

[roy@localhost postgresql-16.4]$ sudo rpm -Uvh /home/roy/libicu-devel-50.2-4.el7_7.x86_64.rpm
[sudo] password for roy:
Sorry, try again.
[sudo] password for roy:
warning: /home/roy/libicu-devel-50.2-4.el7_7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libicu-devel-50.2-4.el7_7        ################################# [100%]

安装zlib

这里需要注意的是安装zlib-devel库需要zlib库，但是系统之前就已经安装了zlib，需要先看看它的版本rpm -qa | grep zlib，不然下载的zlib-devel库会安装不好。

[roy@localhost postgresql-16.4]$ rpm -qa | grep zlib
zlib-1.2.7-18.el7.x86_64
[roy@localhost postgresql-16.4]$ sudo rpm -Uvh /home/roy/zlib-devel-1.2.7-18.el7.x86_64.rpm
[sudo] password for roy:
warning: /home/roy/zlib-devel-1.2.7-18.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:zlib-devel-1.2.7-18.el7          ################################# [100%]

安装postgres

[roy@localhost postgresql-16.4]$ ./configure --prefix=/usr/local/my_pgsql16.4 --with-pgport=5432

没有报错即可

接下来执行编译和安装（编译的时间有点长，等会儿）

最好使用管理员权限

make

make install

创建用户组，用户，修改相关文件夹权限。（这个方法中/run/media/postgres/data路径似乎在主机重启或者过一段时间就会被删除，暂时不确定是什么原因。）

[roy@localhost postgresql-16.4]$ groupadd postgres
groupadd: Permission denied.
groupadd: cannot lock /etc/group; try again later.
[roy@localhost postgresql-16.4]$ sudo groupadd postgres
[sudo] password for roy:
[roy@localhost postgresql-16.4]$ sudo useradd -g postgres postgres_dky
[roy@localhost postgresql-16.4]$ mkdir -p /run/media/postgres/data
mkdir: cannot create directory '/run/media': Permission denied
[roy@localhost postgresql-16.4]$ sudo mkdir -p /run/media/postgres/data
[roy@localhost postgresql-16.4]$ chmod -R 777 /run/media/
chmod: changing permissions of '/run/media/': Operation not permitted
chmod: changing permissions of '/run/media/postgres': Operation not permitted
chmod: changing permissions of '/run/media/postgres/data': Operation not permitted
[roy@localhost postgresql-16.4]$ sudo chmod -R 777 /run/media/
[roy@localhost postgresql-16.4]$ sudo chmod -R 777 /usr/local/my_pgsql16.4/bin/
[roy@localhost postgresql-16.4]$ sudo chown postgres_dky /run/media/postgres/data
[roy@localhost postgresql-16.4]$ chown postgres_dky /usr/local/my_pgsql16.4/bin/initdb
chown: changing ownership of '/usr/local/my_pgsql16.4/bin/initdb': Operation not permitted
[roy@localhost postgresql-16.4]$ sudo chown postgres_dky /usr/local/my_pgsql16.4/bin/initdb

#切换用户
[roy@localhost postgresql-16.4]$ su - postgres_dky
Password:
su: Authentication failure
# 检查是否有用户 postgres_dky。可以用以下命令列出所有用户：
[roy@localhost postgresql-16.4]$ cat /etc/passwd | grep postgres_dky
postgres_dky:x:1001:1001::/home/postgres_dky:/bin/bash
# 有时用户账户可能被锁定。可以用如下命令检查账户状态：
# 用户 postgres_dky 的账户当前被锁定（Password locked）
[roy@localhost postgresql-16.4]$ sudo passwd -S postgres_dky
postgres_dky LK 2024-10-24 0 99999 7 -1 (Password locked.)
# 使用 sudo passwd 命令解除密码锁定状态
#但由于该用户没有设置密码，系统提示您密码将为空。您需要先设置一个密码，然后才能解锁用户
[roy@localhost postgresql-16.4]$ sudo passwd -u postgres_dky
Unlocking password for user postgres_dky.
passwd: Warning: unlocked password would be empty.
passwd: Unsafe operation (use -f to force)
[roy@localhost postgresql-16.4]$ su - postgres_dky
Password:
su: Authentication failure
# 使用以下命令设置一个新密码：
[roy@localhost postgresql-16.4]$ sudo passwd postgres_dky
Changing password for user postgres_dky.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
[roy@localhost postgresql-16.4]$ su - postgres_dky
Password:
Last failed login: Thu Oct 24 14:15:56 CST 2024 on pts/0
There were 4 failed login attempts since the last successful login.
# 切换成功
[postgres_dky@localhost ~]$

执行初始化命令

[postgres_dky@localhost ~]$ /usr/local/my_pgsql16.4/bin/initdb -D /run/media/postgres/data
The files belonging to this database system will be owned by user "postgres_dky".
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 /run/media/postgres/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
initdb: hint: 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/my_pgsql16.4/bin/pg_ctl -D /run/media/postgres/data -l logfile start

启动关闭数据库
注意，启动关闭需要保证是在我们postgres用户之下

[postgres_dky@localhost ~]$ /usr/local/my_pgsql16.4/bin/pg_ctl  -D /run/media/postgres/data -l logfile start
waiting for server to start.... done
server started

关闭数据库

/usr/local/my_pgsql16.4/bin/pg_ctl -D /run/media/postgres/data -l logfile stop

修改数据库密码

# 创建psql连接，这里需要在root用户下执行，否则会提示拒绝
[postgres_dky@localhost ~]$ su -
Password:
Last login: Thu Oct 24 09:35:54 CST 2024 on pts/0
[root@localhost ~]# ln -s /usr/local/my_pgsql16.4/bin/psql /usr/bin/psql
# 创建连接以后，在切回到pg数据库用户
[root@localhost ~]# su - postgres_dky
Last login: Thu Oct 24 14:17:13 CST 2024 on pts/0
[postgres_dky@localhost ~]$ psql -l
                                                             List of databases
   Name    |    Owner     | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |       Access privileges
-----------+--------------+----------+-----------------+-------------+-------------+------------+-----------+-------------------------------
 postgres  | postgres_dky | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           |
 template0 | postgres_dky | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres_dky              +
           |              |          |                 |             |             |            |           | postgres_dky=CTc/postgres_dky
 template1 | postgres_dky | UTF8     | libc            | en_US.UTF-8 | en_US.UTF-8 |            |           | =c/postgres_dky              +
           |              |          |                 |             |             |            |           | postgres_dky=CTc/postgres_dky
(3 rows)

使用psql工具访问数据库

[postgres_dky@localhost ~]$ psql -U postgres_dky -d postgres
psql (16.4)
Type "help" for help.

postgres=# alter role postgres_dky with password 'your new password';
ALTER ROLE
postgres= exit

数据库的配置文件postgresql.conf

[root@localhost ~]# ls /run/media/postgres/data
base    pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
global  pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid

开启远程访问
编辑postgresql.conf

编辑pg_hba.conf
修改pg_hba.conf 添加远程访问的认证方式，未尾添加 host all all 0.0.0.0/0 md5

重启数据库

# start stop restart
/usr/local/my_pgsql16.4/bin/pg_ctl -D /run/media/postgres/data -l logfile restart

开放端口,提供给外部访问。

#查看已开放的端口
firewall-cmd --list-ports

#开放
firewall-cmd --permanent --zone=public --add-port=5432/tcp

#重启防火墙
firewall-cmd --reload

参考：centos离线使用源码安装Postgre SQL

方式二：多个rpm包，并且安装timescaledb

装备安装包

你需要准备这些包，以及一个libicu-50.2-4.el7_7.x86_64.rpm包,安装postgresql15-15.8-1PGDG.rhel7.x86_64.rpm需要libicu

[roy@localhost 138server]$ cd PostgreSQL/
[roy@localhost PostgreSQL]$ ls
libzstd-1.5.2-1.el7.x86_64.rpm                    postgresql15-devel-15.8-1PGDG.rhel7.x86_64.rpm    postgresql15-server-15.8-1PGDG.rhel7.x86_64.rpm
postgresql15-15.8-1PGDG.rhel7.x86_64.rpm          postgresql15-libs-15.8-1PGDG.rhel7.x86_64.rpm
postgresql15-contrib-15.8-1PGDG.rhel7.x86_64.rpm  postgresql15-llvmjit-15.8-1PGDG.rhel7.x86_64.rpm

安装libzstd

[roy@localhost PostgreSQL]$ sudo rpm -ivh libzstd-1.5.2-1.el7.x86_64.rpm
[sudo] password for roy:
Preparing...                          ################################# [100%]
Updating / installing...
   1:libzstd-1.5.2-1.el7              ################################# [100%]
[roy@localhost PostgreSQL]$ sudo rpm -ivh postgresql15-libs-15.8-1PGDG.rhel7.x86_64.rpm
warning: postgresql15-libs-15.8-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql15-libs-15.8-1PGDG.rhel################################# [100%]
[roy@localhost PostgreSQL]$ sudo rpm -ivh postgresql15-15.8-1PGDG.rhel7.x86_64.rpm
warning: postgresql15-15.8-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
error: Failed dependencies:
        libicu is needed by postgresql15-15.8-1PGDG.rhel7.x86_64

安装libicu

[roy@localhost v16]$ sudo rpm -ivh libicu-50.2-4.el7_7.x86_64.rpm
warning: libicu-50.2-4.el7_7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID f4a80eb5: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:libicu-50.2-4.el7_7              ################################# [100%]

安装postgresql15-15.8-1PGDG和postgresql15-server-15.8-1PGDG

[roy@localhost PostgreSQL]$ sudo rpm -ivh postgresql15-15.8-1PGDG.rhel7.x86_64.rpm
warning: postgresql15-15.8-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql15-15.8-1PGDG.rhel7    ################################# [100%]
[roy@localhost PostgreSQL]$ sudo rpm -ivh postgresql15-server-15.8-1PGDG.rhel7.x86_64.rpm
warning: postgresql15-server-15.8-1PGDG.rhel7.x86_64.rpm: Header V4 RSA/SHA1 Signature, key ID 73e3b907: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:postgresql15-server-15.8-1PGDG.rh################################# [100%]

到/usr/pgsql-15/bin/路径下，初始化服务

[roy@localhost PostgreSQL]$ cd /usr/pgsql-15/bin/
[roy@localhost bin]$ ls
clusterdb   dropdb    pg_archivecleanup  pg_checksums    pg_ctl      pg_isready     pg_restore     pg_test_timing   pg_waldump                  postgresql-15-setup  reindexdb
createdb    dropuser  pg_basebackup      pg_config       pg_dump     pg_receivewal  pg_rewind      pg_upgrade       postgres                    postmaster           vacuumdb
createuser  initdb    pgbench            pg_controldata  pg_dumpall  pg_resetwal    pg_test_fsync  pg_verifybackup  postgresql-15-check-db-dir  psql
[roy@localhost bin]$ postgresql-15-setup initdb
Initializing database ... mkdir: cannot create directory ‘/var/lib/pgsql’: Permission denied
failed, see /var/lib/pgsql/15/initdb.log

[roy@localhost bin]$ sudo postgresql-15-setup initdb
Initializing database ... OK

启动服务

systemctl enable postgresql-15  -- 设置为开机自启动

systemctl start postgresql-15

查看服务状态

进入 /var/lib/pgsql/15/data/ 目录，更改pg_hba.conf 配置

在IP4 下增加

host    all            all            0.0.0.0/0            scram-sha-256

更改postgresql.conf 配置，开启如下配置

listen_addresses = '*'
port = 5432

[roy@localhost bin]$ sudo ls /var/lib/pgsql/15/data/
base              global  pg_commit_ts  pg_hba.conf    pg_logical    pg_notify    pg_serial     pg_stat      pg_subtrans  pg_twophase  pg_wal   postgresql.auto.conf  postmaster.opts
current_logfiles  log     pg_dynshmem   pg_ident.conf  pg_multixact  pg_replslot  pg_snapshots  pg_stat_tmp  pg_tblspc    PG_VERSION   pg_xact  postgresql.conf       postmaster.pid
[roy@localhost bin]$ sudo vi /var/lib/pgsql/15/data/pg_hba.conf
[roy@localhost bin]$ sudo vi /var/lib/pgsql/15/data/postgresql.conf

重启服务

systemctl restart postgresql-15  -- 重启

开放端口

firewall-cmd --permanent --add-port=5432/tcp

firewall-cmd --reload

修改密码

要找出 postgres 用户的密码，您可以尝试以下方法：

1. 默认密码

在很多情况下，PostgreSQL 的默认用户（postgres）没有设置密码。如果您是通过包管理器安装的 PostgreSQL，通常可以直接使用：

su - postgres

进入 postgres 用户后，可以在 PostgreSQL 命令行界面中设置密码。

2. 重置密码

如果您不知道密码，可以通过以下步骤重置它：

1. 切换至 postgres 用户：

   sudo su - postgres
   

   如果没有 sudo 权限，您可能需要先用 root 用户登录。

2. 进入 PostgreSQL 命令行界面 ，修改密码：

ALTER USER postgres WITH PASSWORD '123456'; 

然后输入新的密码。

3. 退出 psql：

   \q
   

重启服务

完成。

安装timescaledb

要使用timescaldb的rpm包下载网站，选择自己对应系统的文件，网站上方可以过滤查询条件

点击想要的文件，进到详情页面，右上角可以下载到本地，也可以根据它的提示使用相关命令，我这下到本地再拷贝到对应服务器上的，也可以看见这个包需要什么依赖，以及里面提供了哪些文件。这里如果需要tsl文件，就可以很明显看见，在原下载位置 下载的文件中就缺少了对应的tsl文件，导致后面使用timescaledb扩展出现问题。

安装timescaledb是针对某一个数据库的，单独的数据库要专门创建timescaledb扩展。但是都是要先安装timescaledb服务。

安装timescaledb服务

在上面方式二的步骤安装postgres之后就可以直接安装。没有出现依赖问题。

[roy@localhost postgres+timescaledb15]$ sudo rpm -ivh timescaledb-tools-0.14.3-0.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:timescaledb-tools-0.14.3-0.el7   ################################# [100%]
[roy@localhost postgres+timescaledb15]$ sudo rpm -ivh timescaledb-2-loader-postgresql-15-2.15.2-0.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:timescaledb-2-loader-postgresql-1################################# [100%]
Using pg_config located at /usr/pgsql-15/bin/pg_config to finish installation...
[roy@localhost postgres+timescaledb15]$ sudo rpm -ivh timescaledb-2-postgresql-15-2.15.2-0.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:timescaledb-2-postgresql-15-2.15.################################# [100%]
Using pg_config located at /usr/pgsql-15/bin/pg_config to finish installation...

TimescaleDB has been installed. You need to update your postgresql.conf file
to load the library by adding 'timescaledb' to your shared_preload_libraries.
The easiest way to do this (and more configuration) is to use timescaledb-tune:

timescaledb-tune --pg-config=/usr/pgsql-15/bin/pg_config

更改postgresql.conf 配置，开启如下配置

可以根据上门输出操作：

sudo timescaledb-tune --pg-config=/usr/pgsql-15/bin/pg_config

或者手动修改配置

shared_preload_libraries = 'timescaledb'

重启服务

systemctl restart postgresql-15  -- 重启

测试的话，先可以给postgres数据库创建

1. 切换到postgres用户

 sudo su - postgres
 

增加扩展

CREATE EXTENSION IF NOT EXISTS timescaledb;

出现下图代表扩展成功。

参考：CentOS 7离线安装Postgresql15.2+Timescaledb

问题解决：

1. 已经安装了timescaledb扩展，需要更换版本

# 列出所有相关包,强制卸载找到的软件包（忽略依赖检查）
sudo rpm -qa | grep timescaledb | xargs sudo rpm -e --nodeps

# 验证卸载
rpm -qa | grep timescaledb  # 应该无输出
# 删除库文件
sudo rm -f /usr/pgsql-15/lib/timescaledb*.so

# 删除扩展文件
sudo rm -rf /usr/pgsql-15/share/extension/timescaledb*

# 删除配置文件
sudo rm -f /etc/ld.so.conf.d/timescaledb.conf

然后重新安装

[roy@localhost postgres+timescaledb15]$ sudo rpm -ivh timescaledb-tools-0.14.3-0.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:timescaledb-tools-0.14.3-0.el7   ################################# [100%]
[roy@localhost postgres+timescaledb15]$ sudo rpm -ivh timescaledb-2-loader-postgresql-15-2.15.2-0.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:timescaledb-2-loader-postgresql-1################################# [100%]
Using pg_config located at /usr/pgsql-15/bin/pg_config to finish installation...
[roy@localhost postgres+timescaledb15]$ sudo rpm -ivh timescaledb-2-postgresql-15-2.15.2-0.el7.x86_64.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:timescaledb-2-postgresql-15-2.15.################################# [100%]
Using pg_config located at /usr/pgsql-15/bin/pg_config to finish installation...

TimescaleDB has been installed. You need to update your postgresql.conf file
to load the library by adding 'timescaledb' to your shared_preload_libraries.
The easiest way to do this (and more configuration) is to use timescaledb-tune:

timescaledb-tune --pg-config=/usr/pgsql-15/bin/pg_config