ubuntu20.04二进制部署PostgreSQL 15.2

创建文件存储库配置

echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list

导入存储库签名密钥

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

更新包列表

apt-get update

安装PostgreSQL

安装最新版本PostgreSQL

apt-get -y install postgresql

安装指定版本PostgreSQL

apt-get install postgresql-12

查看PostgreSQL状态

systemctl status postgresql.service

● postgresql.service - PostgreSQL RDBMS
     Loaded: loaded (/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: active (exited) since Wed 2023-03-01 10:26:31 CST; 19min ago
   Main PID: 868499 (code=exited, status=0/SUCCESS)
      Tasks: 0 (limit: 18883)
     Memory: 0B
     CGroup: /system.slice/postgresql.service

Mar 01 10:26:31 hb1B-daop-relapp-01 systemd[1]: Starting PostgreSQL RDBMS...
Mar 01 10:26:31 hb1B-daop-relapp-01 systemd[1]: Finished PostgreSQL RDBMS.

查看PostgreSQL端口

netstat -tnlp |grep postgres

tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN      868821/postgres

PostgreSQL默认配置

egrep -v "^$|^#|^\s" /etc/postgresql/15/main/postgresql.conf

data_directory = '/var/lib/postgresql/15/main'          # use data in another directory
hba_file = '/etc/postgresql/15/main/pg_hba.conf'        # host-based authentication file
ident_file = '/etc/postgresql/15/main/pg_ident.conf'    # ident configuration file
external_pid_file = '/var/run/postgresql/15-main.pid'                   # write an extra PID file
listen_addresses = '0.0.0.0'
port = 5432                             # (change requires restart)
max_connections = 100                   # (change requires restart)
unix_socket_directories = '/var/run/postgresql' # comma-separated list of directories
ssl = on
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
shared_buffers = 128MB                  # min 128kB
dynamic_shared_memory_type = posix      # the default is usually the first option
max_wal_size = 1GB
min_wal_size = 80MB
log_line_prefix = '%m [%p] %q%u@%d '            # special values:
log_timezone = 'Asia/Shanghai'
cluster_name = '15/main'                        # added to process titles if nonempty
datestyle = 'iso, mdy'
timezone = 'Asia/Shanghai'
lc_messages = 'en_US.UTF-8'                     # locale for system error message
lc_monetary = 'en_US.UTF-8'                     # locale for monetary formatting
lc_numeric = 'en_US.UTF-8'                      # locale for number formatting
lc_time = 'en_US.UTF-8'                         # locale for time formatting
default_text_search_config = 'pg_catalog.english'
include_dir = 'conf.d'                  # include files ending in '.conf' from

连接PostgreSQL

su - postgres -c "psql -U postgres -p 5432"

psql (15.2 (Ubuntu 15.2-1.pgdg20.04+1))
Type "help" for help.

postgres=#

查看databases

postgres=# \l

                                                 List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    | ICU Locale | Locale Provider |   Access privileges   
-----------+----------+----------+-------------+-------------+------------+-----------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |            | libc            | =c/postgres          +
           |          |          |             |             |            |                 | postgres=CTc/postgres
(3 rows)

修改postgres密码

修改密码

postgres=# alter user postgres with password 'postgres';

ALTER ROLE

测试连接

su - postgres -c "psql -U postgres -h 127.0.0.1 -p 5432"

Password for user postgres: 
psql (15.2 (Ubuntu 15.2-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# 

允许远程连接 

postgresql.conf

sed -i "s@#listen_addresses = 'localhost'@listen_addresses = '0.0.0.0'@g"  /etc/postgresql/15/main/postgresql.conf

pg_hba.conf

echo "host    all             all             0.0.0.0/0            scram-sha-256" >> /etc/postgresql/15/main/pg_hba.conf

重启服务

systemctl restart postgresql.service

测试远程连接

su - postgres -c "psql -U postgres -h 192.168.1.23 -p 5432"

Password for user postgres: 
psql (15.2 (Ubuntu 15.2-1.pgdg20.04+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=# 

参考文档

https://www.postgresql.org/docs/