1.获取源码

从PostgreSQL: File Browser获取tar.bz2或者tar.gz源码

2.解压

tar xf postgresql-version.tar.bz2

root@hwz-VMware-Virtual-Platform:/usr/local# tar xf postgresql-16.6.tar.bz2
root@hwz-VMware-Virtual-Platform:/usr/local# ll
总计 24324
drwxr-xr-x 12 root root     4096  2月  6 11:35 ./
drwxr-xr-x 12 root root     4096 10月  9 21:16 ../
drwxr-xr-x  2 root root     4096 10月  9 21:16 bin/
drwxr-xr-x  2 root root     4096 10月  9 21:16 etc/
drwxr-xr-x  2 root root     4096 10月  9 21:16 games/
drwxr-xr-x  2 root root     4096 10月  9 21:16 include/
drwxr-xr-x  3 root root     4096 10月  9 21:16 lib/
drwxr-xr-x  2 root root     4096 10月  9 21:16 libexec/
lrwxrwxrwx  1 root root        9 10月  9 21:16 man -> share/man/
drwxrwxrwx  6 1107 1107     4096 11月 19 04:48 postgresql-16.6/
-rw-r--r--  1 root root 24856956  2月  6 11:34 postgresql-16.6.tar.bz2
drwxr-xr-x  2 root root     4096 10月  9 21:16 sbin/
drwxr-xr-x  7 root root     4096 10月  9 21:19 share/
drwxr-xr-x  2 root root     4096 10月  9 21:16 src/

3.编译构建程序

root@hwz-VMware-Virtual-Platform:/usr/local# cd postgresql-16.6/
root@hwz-VMware-Virtual-Platform:/usr/local/postgresql-16.6# ./configure --prefix=/usr/local/pgsql16

可能会缺少库，按需安装即可 

make&make install

编译后生成目录

root@hwz-VMware-Virtual-Platform:/usr/local/pgsql16# ll
总计 24
drwxr-xr-x  6 root root 4096  2月  7 09:04 ./
drwxr-xr-x 13 root root 4096  2月  7 09:04 ../
drwxr-xr-x  2 root root 4096  2月  7 09:04 bin/
drwxr-xr-x  6 root root 4096  2月  7 09:04 include/
drwxr-xr-x  4 root root 4096  2月  7 09:04 lib/
drwxr-xr-x  6 root root 4096  2月  7 09:04 share/

4.初始化数据库

 先增加一个postgresql用户

useradd postgres
passwd postgres

创建数据目录并让它属于postgres用户

root@hwz-VMware-Virtual-Platform:/usr/local/pgsql16# useradd -m postgres
root@hwz-VMware-Virtual-Platform:/usr/local/pgsql16# chown postgres . /pgdata/16/data

官方useradd postgres 不加-m参数 Ubuntu可能不会创建用户家目录

root@hwz-VMware-Virtual-Platform:/usr/local/pgsql16# su - postgres
postgres@hwz-VMware-Virtual-Platform:~$ ll

这种前面只有$是因为该用户shell为/bin/sh，需要改为/bin/bash 

 4.1 设置环境变量

postgres@hwz-VMware-Virtual-Platform:~$ vim .bash_profile

export PGDATA=/pgdata/16/data
export LANG=en_US.utf8
export PGHOME=/usr/local/pgsql16
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=postgres

测试是否成功

postgres@hwz-VMware-Virtual-Platform:~$ psql --version
psql (PostgreSQL) 16.

 4.2 初始化

postgres@hwz-VMware-Virtual-Platform:~$ initdb -D /pgdata/16/data/
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

initdb: error: invalid locale settings; check LANG and LC_* environment variables

如果出现这个错误是因为 当前用户环境使用的编码和系统编码不一致

postgres@hwz-VMware-Virtual-Platform:~$ locale
locale: Cannot set LC_CTYPE to default locale: No such file or directory
locale: Cannot set LC_MESSAGES to default locale: No such file or directory
locale: Cannot set LC_ALL to default locale: No such file or directory
LANG=en_US.utf8
LANGUAGE=
LC_CTYPE="en_US.utf8"
LC_NUMERIC="en_US.utf8"
LC_TIME="en_US.utf8"
LC_COLLATE="en_US.utf8"
LC_MONETARY="en_US.utf8"
LC_MESSAGES="en_US.utf8"
LC_PAPER="en_US.utf8"
LC_NAME="en_US.utf8"
LC_ADDRESS="en_US.utf8"
LC_TELEPHONE="en_US.utf8"
LC_MEASUREMENT="en_US.utf8"
LC_IDENTIFICATION="en_US.utf8"
LC_ALL=

 查看系统编码

postgres@hwz-VMware-Virtual-Platform:~$ vim /etc/locale.conf

LANG=zh_CN.UTF-8

系统用的CN的UTF-8，用户环境是US的UTF-8

现在修改用户环境的编码为CN UTF-8

postgres@hwz-VMware-Virtual-Platform:~$ vim .bashrc

 后面添加

export LC_ALL="zh_CN.UTF-8"

postgres@hwz-VMware-Virtual-Platform:~$ source .bashrc 
postgres@hwz-VMware-Virtual-Platform:~$ locale
LANG=zh_CN.UTF-8
LANGUAGE=
LC_CTYPE="zh_CN.UTF-8"
LC_NUMERIC="zh_CN.UTF-8"
LC_TIME="zh_CN.UTF-8"
LC_COLLATE="zh_CN.UTF-8"
LC_MONETARY="zh_CN.UTF-8"
LC_MESSAGES="zh_CN.UTF-8"
LC_PAPER="zh_CN.UTF-8"
LC_NAME="zh_CN.UTF-8"
LC_ADDRESS="zh_CN.UTF-8"
LC_TELEPHONE="zh_CN.UTF-8"
LC_MEASUREMENT="zh_CN.UTF-8"
LC_IDENTIFICATION="zh_CN.UTF-8"
LC_ALL=zh_CN.UTF-8

 重新初始化成功

postgres@hwz-VMware-Virtual-Platform:~$ initdb -D /pgdata/16/data/
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 "zh_CN.UTF-8".
The default database encoding has accordingly been set to "UTF8".
initdb: could not find suitable text search configuration for locale "zh_CN.UTF-8"
The default text search configuration will be set to "simple".

Data page checksums are disabled.

fixing permissions on existing directory /pgdata/16/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:

    pg_ctl -D /pgdata/16/data/ -l logfile start

 5.启动数据库

postgres@hwz-VMware-Virtual-Platform:~$ pg_ctl -D/pgdata/16/data/ -l logfile start
waiting for server to start.... done
server started

 查看运行状态

postgres@hwz-VMware-Virtual-Platform:~$ pg_ctl status
pg_ctl: server is running (PID: 94176)
/usr/local/pgsql16/bin/postgres "-D" "/pgdata/16/data"

 6.测试

postgres@hwz-VMware-Virtual-Platform:~$ createdb test
postgres@hwz-VMware-Virtual-Platform:~$ psql test
psql (16.6)
Type "help" for help.

test=# \l
                                                       List of databases
   Name    |  Owner   | Encoding | Locale Provider |   Collate   |    Ctype    | ICU Locale | ICU Rules |   Access privileges   
-----------+----------+----------+-----------------+-------------+-------------+------------+-----------+-----------------------
 hwz2      | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
 postgres  | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
 template0 | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 template1 | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | =c/postgres          +
           |          |          |                 |             |             |            |           | postgres=CTc/postgres
 test      | postgres | UTF8     | libc            | zh_CN.UTF-8 | zh_CN.UTF-8 |            |           | 
(5 rows)

test=# create table user(id int, name varchar(255));
ERROR:  syntax error at or near "user"
LINE 1: create table user(id int, name varchar(255));
                     ^
test=# create table user1(id int, name varchar(255));
CREATE TABLE
test=# insert into user1 values(1,'hwz'),(2,'www');
INSERT 0 2
test=# select * from user1
test-# ;
 id | name 
----+------
  1 | hwz
  2 | www
(2 rows)

test=# \q
postgres@hwz-VMware-Virtual-Platform:~$

7.关闭数据库

postgres@hwz-VMware-Virtual-Platform:~$ pg_ctl -D /pgdata/16/data/ stop
waiting for server to shut down.... done
server stopped