【新手】【备忘】在EC2上安装PostgreSQL 12.3
在EC2上安装PostgreSQL 12.3
安装repo
想要确认 EC2 的发行版和版本吗？
# cat /etc/system-release
Amazon Linux release 2 (Karoo)
由于Amazon Linux基于RHEL7，因此从此处安装repo文件。
https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
请点击上述链接来获取最新版的pgdg-redhat软件仓库rpm文件，该文件适用于EL-7-x86_64操作系统。
下载repo文件。
使用EC2的root用户进行操作。
# wget --no-check-certificate https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
使用–nodeps选项进行安装，忽略对/etc/redhat-release文件的依赖关系
# rpm -Uvh --nodeps ./pgdg-redhat-repo-latest.noarch.rpm
查看repo
# yum repolist
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
27 packages excluded due to repository priority protections
repo id repo name status
!amzn2-core/2/x86_64 Amazon Linux 2 core repository 26,253
amzn2extra-docker/2/x86_64 Amazon Extras repo for docker 46
pgdg-common/x86_64 PostgreSQL common RPMs for RHEL/CentOS 7 - x86_64 320+6
pgdg10/x86_64 PostgreSQL 10 for RHEL/CentOS 7 - x86_64 972+7
pgdg11/x86_64 PostgreSQL 11 for RHEL/CentOS 7 - x86_64 1,075+4
pgdg12/x86_64 PostgreSQL 12 for RHEL/CentOS 7 - x86_64 657+1
pgdg13/x86_64 PostgreSQL 13 for RHEL/CentOS 7 - x86_64 390+1
pgdg14/x86_64 PostgreSQL 14 for RHEL/CentOS 7 - x86_64 120
pgdg96/x86_64 PostgreSQL 9.6 for RHEL/CentOS 7 - x86_64 933+8
treasuredata/x86_64 TreasureData 5
repolist: 30,771
编辑存储库
如果保持这样，yum install 将无法正常工作，所以需要编辑 yum.repos.d 文件。
据说 $releasever 变量会存储 RHEL 或 CentOS 的版本号（如 7 或 8），但对于 EC2 的 Amazon Linux，它会存储为 system-release 字符串，导致 yum install 无法正常工作。
参考：【CentOS7】如何获取 $releasever、$basearch 和 $infra 的值。
替换的 7 应该表示 RHEL/CentOS 的版本 7。这会生成一个路径，要求使用针对版本 7 的二进制文件。
# sed --in-place -e "s/\$releasever/7/g" /etc/yum.repos.d/pgdg-redhat-all.repo
请确认仓库中是否有12.3版本。
#yum --showduplicates search postgresql12-server
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
27 packages excluded due to repository priority protections
=========================== N/S matched: postgresql12-server ===========================
postgresql12-server-12.1-2PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.2-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.2-2PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.3-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.3-5PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.3-5PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.4-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.5-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.6-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.7-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
postgresql12-server-12.8-1PGDG.rhel7.x86_64 : The programs needed to create and run a
: PostgreSQL server
安装PostgreSQL
安装PostgreSQL 12.3服务器-12.3。
# yum install postgresql12-server-12.3
有点麻烦。应该是说AWS上用RDS就可以了吧。
进行 postgres 的安装配置
初始化
# /usr/pgsql-12/bin/postgresql-12-setup initdb
PostgreSQL的启动设置
确认是否在systemd中注册。
# systemctl list-unit-files |grep postgres
postgresql-12.service disabled
我有一个单元文件，但是自动启动设置被禁用了。我想要将其设置为自动启动。
# systemctl enable postgresql-12.service
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql-12.service to /usr/lib/systemd/system/postgresql-12.service.
已启用
# systemctl list-unit-files |grep postgres
postgresql-12.service enabled
确认自动启动脚本
在/etc/systemd/system/multi-user.target.wants/目录下创建了一个指向postgres脚本的链接。
# ll /etc/systemd/system/multi-user.target.wants/ | grep postgres
lrwxrwxrwx 1 root root 45 Oct 4 20:45 postgresql-12.service -> /usr/lib/systemd/system/postgresql-12.service
启动PostgreSQL。
我来检查一下状态。
# systemctl status postgresql-12.service
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: inactive (dead)
Docs: https://www.postgresql.org/docs/12/static/
试着启动
# systemctl start postgresql-12.service
我来检查一下状态
# systemctl status postgresql-12.service
● postgresql-12.service - PostgreSQL 12 database server
Loaded: loaded (/usr/lib/systemd/system/postgresql-12.service; enabled; vendor preset: disabled)
Active: active (running) since Mon 2021-10-04 20:47:53 JST; 18s ago
Docs: https://www.postgresql.org/docs/12/static/
Process: 2245 ExecStartPre=/usr/pgsql-12/bin/postgresql-12-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 2252 (postmaster)
CGroup: /system.slice/postgresql-12.service
├─2252 /usr/pgsql-12/bin/postmaster -D /var/lib/pgsql/12/data/
├─2255 postgres: logger
├─2257 postgres: checkpointer
├─2258 postgres: background writer
├─2259 postgres: walwriter
├─2260 postgres: autovacuum launcher
├─2261 postgres: stats collector
└─2262 postgres: logical replication launcher
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal systemd[1]: Starting P...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal postmaster[2252]: 2021...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal postmaster[2252]: 2021...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal postmaster[2252]: 2021...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal postmaster[2252]: 2021...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal postmaster[2252]: 2021...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal postmaster[2252]: 2021...
Oct 04 20:47:53 ip-172-31-30-1.ap-northeast-1.compute.internal systemd[1]: Started Po...
Hint: Some lines were ellipsized, use -l to show in full.
尝试使用PostgreSQL客户端进行连接。
用psql命令从postgres用户连接。
# su - postgres
-bash-4.2$ psql
psql (12.3)
Type "help" for help.
postgres=#
连接上了！！
您可以通过\q退出客户端。
从他人的服务器等进行连接设置
-
- postgresユーザのパスワード設定
- デフォルトの/var/lib/pgsql/12/data/pg_hba.confはlocalからの接続はpeer認証になっているので、OSのpostgresユーザから接続する
local all all peer
# su - postgres
Last login: Tue Oct 5 19:35:49 JST 2021 on pts/0
-bash-4.2$ psql
psql (12.3)
Type "help" for help.
postgres=#
postgres=# ALTER USER postgres with encrypted password 'xxxxxxxx';
- md5認証に変更
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all md5
host replication all 127.0.0.1/32 md5
host replication all ::1/128 md5
- 接続設定も変更/var/lib/pgsql/12/data/postgresql.conf
listen_addresses = '*'
- 設定反映
# systemctl reload postgresql-12.service
- 接続確認
# psql -U postgres -W
Password:
psql (12.3)
Type "help" for help.
postgres=#
