ANBOB™

这篇简单的记录PostGreSQL12 (以下简称PG)Binary packages on RHEL 7.3安装过程，与后期的PG DB 和RHEL7 OS字符集修改。

1， 下载PG安装介质
http://www.postgresql.org/download/

2, 创建PG owner系统用户

# useradd postgres
# id postgres
uid=54322(postgres) gid=54324(postgres) groups=54324(postgres)

3， 创建PG 安装目录

# mkdir /opt/pgsql
# chown postgres:postgres /opt/pgsql
# chmod 755 /opt/pgsql

4, 上传安装介质到服务器解压

# su - postgres
$ tar zxvf /home/postgres/postgresql-12beta1.tar.gz
$ ls -l
total 26280
-rw------- 1 postgres postgres     2816 Jun 13 23:46 logfile
drwxrwxr-x 6 postgres postgres     4096 Jun 13 23:10 postgresql-12beta1
-rw-r--r-- 1 postgres root     26900986 Jun 13 22:53 postgresql-12beta1.tar.gz

5, configure配置安装位置到/opt/pgsql

[postgres@localhost ~]$ cd postgresql-12beta1/
[postgres@localhost postgresql-12beta1]$ ls
aclocal.m4  config.status  contrib    GNUmakefile     INSTALL   src
config      configure      COPYRIGHT  GNUmakefile.in  Makefile
config.log  configure.in   doc        HISTORY         README
[postgres@localhost postgresql-12beta1]$ ./configure --prefix=/opt/pgsql

6, 处理configure错误

[postgres@localhost postgresql-12beta1]$ ./configure --prefix=/opt/pgsql

checking for inflate in -lz... no
configure: error: zlib library not found
If you have zlib already installed, see config.log for details on the
failure.  It is possible the compiler isn't looking in the proper directory.
Use --without-zlib to disable zlib support.

[postgres@localhost postgresql-12beta1]$ rpm -qa|grep zlib
zlib-1.2.7-17.el7.x86_64

[postgres@localhost postgresql-12beta1]$ mount /dev/cdrom /mnt

[postgres@localhost postgresql-12beta1]$ cd /mnt/Packages

[root@localhost Packages]# ls|grep zlib
zlib-1.2.7-17.el7.i686.rpm
zlib-1.2.7-17.el7.x86_64.rpm
zlib-devel-1.2.7-17.el7.i686.rpm
zlib-devel-1.2.7-17.el7.x86_64.rpm
[root@localhost Packages]# rpm -ivh zlib-devel-1.2.7-17.el7.x86_64.rpm
warning: zlib-devel-1.2.7-17.el7.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                          ################################# [100%]
Updating / installing...
   1:zlib-devel-1.2.7-17.el7          ################################# [100%]

7, make 安装

$ cd /home/postgres/postgresql-12beta1
$ make 
$ make install

[postgres@localhost pgsql]$ ls -lrt
total 20
drwxrwxr-x  6 postgres postgres 4096 Jun 13 23:30 include
drwxrwxr-x  6 postgres postgres 4096 Jun 13 23:30 share
drwxrwxr-x  2 postgres postgres 4096 Jun 13 23:30 bin
drwxrwxr-x  4 postgres postgres 4096 Jun 13 23:30 lib

8, 修改用户prfile for PG

[postgres@localhost ~]$ vi ~/.bash_profile
# append 
export PG_HOME=/opt/pgsql
export PATH=$PG_HOME/bin:$PATH
export LD_LIBRARY_PATH=$PG_HOME/lib
export PGDATA=/opt/pgsql/data

9, 启动配置

$ su - root
# cd  /home/postgres/postgresql-12beta1
# cp contrib/start-scripts/linux /etc/init.d/postgre
# chmod 755 /etc/init.d/postgres
# vi /etc/init.d/postgres
   --- begin ---
## EDIT FROM HERE

# Installation prefix
prefix=/opt/pgsql

# Data directory
PGDATA="/opt/pgsql/data"
 ---- end ---
# chkconfig --add postgres

Note: 当然也可以使用systemctl 的方式填加服务

10, 初始化变量

$ su - postgres
# initdb --no-locale -D /opt/pgsql/data

11， 安装需要修改配置文件

# cd /opt/pgpsql/data 
##　安装需要 postgresql.conf, pg_hba.conf， 后续再记录优化配置，这里只是测试安装使用默认

Note: 可以使用PGtune在线生成部分参数，https://pgtune.leopard.in.ua/#/
e.g.
# WARNING
# this tool not being optimal
# for very high memory systems

# DB Version: 11
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 128 GB
# CPUs num: 16
# Connections num: 1000
# Data Storage: ssd

max_connections = 1000
shared_buffers = 32GB
effective_cache_size = 96GB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4194kB
min_wal_size = 1GB
max_wal_size = 2GB
max_worker_processes = 16
max_parallel_workers_per_gather = 8
max_parallel_workers = 16

12， 启动PG

# service postgres start
or 
# pg_ctl start

[postgres@localhost data]$ pg_ctl start
waiting for server to start....2019-06-17 11:26:05.097 EDT [3833] LOG:  starting PostgreSQL 12beta1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
2019-06-17 11:26:05.097 EDT [3833] LOG:  listening on IPv6 address "::1", port 5432
2019-06-17 11:26:05.097 EDT [3833] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2019-06-17 11:26:05.103 EDT [3833] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2019-06-17 11:26:05.128 EDT [3834] LOG:  database system was shut down at 2019-06-17 11:26:03 EDT
2019-06-17 11:26:05.133 EDT [3833] LOG:  database system is ready to accept connections
 done
server started

13, 修改timezone
setting timezone in postgresql.conf just sets the default for clients without this setting. If you see other value, means client sets it to some local value.

postgres=# SHOW timezone ;
or
postgres==# select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name            | TimeZone
setting         | UTC
unit            |
category        | Client Connection Defaults / Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      |
context         | user
vartype         | string
source          | configuration file
min_val         |
max_val         |
enumvals        |
boot_val        | GMT
reset_val       | UTC
sourcefile      | /opt/pgsql/data/postgresql.conf
sourceline      | 556
pending_restart | f 

-- 查找一个本地timezone名字
postgres=# SELECT * FROM pg_timezone_names;
               name               | abbrev | utc_offset | is_dst
----------------------------------+--------+------------+--------
 Africa/Maseru                    | SAST   | 02:00:00   | f
 Africa/Mbabane                   | SAST   | 02:00:00   | f
 Africa/Asmera                    | EAT    | 03:00:00   | f
 Africa/Timbuktu                  | GMT    | 00:00:00   | f
 Africa/Algiers                   | CET    | 01:00:00   | f

Note:
pending_restart false means you don’t need to restart postgres, but you still need to reload config after change.

#　vi /opt/pgsql/data/postgresql.conf

timezone = 'America/New_York'
修改成
timezone = 'Asia/Shanghai'
或
timezone = 'PRC'

如果有安装orafce, 要配置orafce.timezone = 'PRC'

 
postgres=# select pg_reload_conf();

postgres=#  select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---+----------------------------------------------------------------
name            | TimeZone
setting         | Asia/Shanghai
unit            |
category        | Client Connection Defaults / Locale and Formatting
short_desc      | Sets the time zone for displaying and interpreting time stamps.
extra_desc      |
context         | user
vartype         | string
source          | configuration file
min_val         |
max_val         |
enumvals        |
boot_val        | GMT
reset_val       | Asia/Shanghai
sourcefile      | /opt/pgsql/data/postgresql.conf
sourceline      | 655
pending_restart | f


[local]:5432 postgres@postgres=# select oracle.sysdate(),now(),current_timestamp,clock_timestamp();
       sysdate       |              now              |       current_timestamp       |        clock_timestamp
---------------------+-------------------------------+-------------------------------+-------------------------------
 2023-01-04 17:47:37 | 2023-01-04 17:47:37.088778+08 | 2023-01-04 17:47:37.088778+08 | 2023-01-04 17:47:37.090245+08
(1 row)



Note:
另外timezone 还可以有sql 级，user级和db 级. e.g. ALTER DATABASE postgres SET timezone TO 'Europe/Berlin';

PG修改后发现使用select now(); 时间还是有转换，下面记录如何修改RHEL 7操作系统timezone.

[root@localhost Asia]# date
Mon Jun 17 10:27:16 EDT 2019

[root@localhost Asia]# ls /etc/localtime
/etc/localtime

[root@localhost ~]# cd /usr/share/zoneinfo/
[root@localhost zoneinfo]# ls
Africa      Chile    GB         Indian       MST         PRC        UTC
America     CST6CDT  GB-Eire    Iran         MST7MDT     PST8PDT    WET
Antarctica  Cuba     GMT        iso3166.tab  Navajo      right      W-SU
Arctic      EET      GMT0       Israel       NZ          ROC        zone.tab
Asia        Egypt    GMT-0      Jamaica      NZ-CHAT     ROK        Zulu
Atlantic    Eire     GMT+0      Japan        Pacific     Singapore
Australia   EST      Greenwich  Kwajalein    Poland      Turkey
Brazil      EST5EDT  Hongkong   Libya        Portugal    UCT
Canada      Etc      HST        MET          posix       Universal
CET         Europe   Iceland    Mexico       posixrules  US
[root@localhost zoneinfo]# cd Asia/
[root@localhost Asia]# ls
Aden       Calcutta     Hong_Kong    Kuala_Lumpur  Pyongyang      Tel_Aviv
Almaty     Chita        Hovd         Kuching       Qatar          Thimbu
Amman      Choibalsan   Irkutsk      Kuwait        Qyzylorda      Thimphu
Anadyr     Chongqing    Istanbul     Macao         Rangoon        Tokyo
Aqtau      Chungking    Jakarta      Macau         Riyadh         Tomsk
Aqtobe     Colombo      Jayapura     Magadan       Saigon         Ujung_Pandang
Ashgabat   Dacca        Jerusalem    Makassar      Sakhalin       Ulaanbaatar
Ashkhabad  Damascus     Kabul        Manila        Samarkand      Ulan_Bator
Baghdad    Dhaka        Kamchatka    Muscat        Seoul          Urumqi

[root@localhost Asia]# cp /etc/localtime /etc/localtime_bak

[root@localhost Asia]# cp Shanghai /etc/localtime
cp: overwrite ‘/etc/localtime’? yes

[root@localhost Asia]# date
Mon Jun 17 22:29:23 CST 2019

打赏

微信扫一扫，打赏作者吧～