Install PSQL client on EC2 instance
1. We need to first add the pgdg repository in Amazon Linux
$ amazon-linux-extras install postgresql10 vim epel
2. Now, install postgresql 10
$ yum install -y postgresql-server postgresql-devel
/usr/bin/postgresql-setup –-initdb
4. Start the postgres service
systemctl enable postgresql
systemctl start postgresql
5. To check if the postgres service is running or not
systemctl status postgresql
Now we are all good to connect to our database cluster using psql client tool
Fire the below command to connect to the rds cluster
[root@ip-172-31-81-123 ~]# amazon-linux-extras install postgresql10 vim epel
Installing vim-minimal, epel-release, postgresql, vim-enhanced
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
Cleaning repos: AmazonCorretto amzn2-core amzn2extra-docker amzn2extra-epel amzn2extra-postgresql10 amzn2extra-vim epel epel-apache-maven
27 metadata files removed
12 sqlite files removed
0 metadata files removed
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
AmazonCorretto | 2.9 kB 00:00:00
amzn2-core | 3.7 kB 00:00:00
amzn2extra-docker | 3.0 kB 00:00:00
amzn2extra-epel | 3.0 kB 00:00:00
amzn2extra-postgresql10 | 3.0 kB 00:00:00
amzn2extra-vim | 3.0 kB 00:00:00
epel/x86_64/metalink | 15 kB 00:00:00
epel | 4.7 kB 00:00:00
epel-apache-maven | 3.3 kB 00:00:00
(1/16): AmazonCorretto/x86_64/primary_db | 27 kB 00:00:00
(2/16): amzn2-core/2/x86_64/group_gz | 2.5 kB 00:00:00
(3/16): amzn2-core/2/x86_64/updateinfo | 373 kB 00:00:00
(4/16): amzn2extra-epel/2/x86_64/primary_db | 1.8 kB 00:00:00
(5/16): amzn2extra-postgresql10/2/x86_64/updateinfo | 76 B 00:00:00
(6/16): amzn2extra-postgresql10/2/x86_64/primary_db | 43 kB 00:00:00
(7/16): amzn2extra-vim/2/x86_64/updateinfo | 76 B 00:00:00
(8/16): amzn2extra-vim/2/x86_64/primary_db | 13 kB 00:00:00
(9/16): amzn2extra-docker/2/x86_64/updateinfo | 76 B 00:00:00
(10/16): amzn2extra-epel/2/x86_64/updateinfo | 76 B 00:00:00
(11/16): amzn2extra-docker/2/x86_64/primary_db | 78 kB 00:00:00
(12/16): epel/x86_64/group_gz | 96 kB 00:00:00
(13/16): epel/x86_64/updateinfo | 1.0 MB 00:00:00
(14/16): epel-apache-maven/x86_64/primary_db | 5.0 kB 00:00:00
(15/16): epel/x86_64/primary_db | 6.9 MB 00:00:00
(16/16): amzn2-core/2/x86_64/primary_db | 53 MB 00:00:02
221 packages excluded due to repository priority protections
Package epel-release-7-11.noarch already installed and latest version
Package 2:vim-enhanced-8.1.1602-1.amzn2.x86_64 already installed and latest version
Package 2:vim-minimal-8.1.1602-1.amzn2.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package postgresql.x86_64 0:10.16-1.amzn2.0.2 will be installed
--> Processing Dependency: postgresql-libs(x86-64) = 10.16-1.amzn2.0.2 for package: postgresql-10.16-1.amzn2.0.2.x86_64
--> Processing Dependency: libpq.so.5()(64bit) for package: postgresql-10.16-1.amzn2.0.2.x86_64
--> Running transaction check
---> Package postgresql-libs.x86_64 0:10.16-1.amzn2.0.2 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
postgresql x86_64 10.16-1.amzn2.0.2 amzn2extra-postgresql10 1.5 M
Installing for dependencies:
postgresql-libs x86_64 10.16-1.amzn2.0.2 amzn2extra-postgresql10 293 k
Transaction Summary
=============================================================================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 1.7 M
Installed size: 6.1 M
Is this ok [y/d/N]: y
Is this ok [y/d/N]: y
Downloading packages:
(1/2): postgresql-libs-10.16-1.amzn2.0.2.x86_64.rpm | 293 kB 00:00:00
(2/2): postgresql-10.16-1.amzn2.0.2.x86_64.rpm | 1.5 MB 00:00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 7.9 MB/s | 1.7 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-libs-10.16-1.amzn2.0.2.x86_64 1/2
Installing : postgresql-10.16-1.amzn2.0.2.x86_64 2/2
Verifying : postgresql-libs-10.16-1.amzn2.0.2.x86_64 1/2
Verifying : postgresql-10.16-1.amzn2.0.2.x86_64 2/2
Installed:
postgresql.x86_64 0:10.16-1.amzn2.0.2
Dependency Installed:
postgresql-libs.x86_64 0:10.16-1.amzn2.0.2
Complete!
0 ansible2 available \
[ =2.4.2 =2.4.6 =2.8 =stable ]
2 httpd_modules available [ =1.0 =stable ]
3 memcached1.5 available \
[ =1.5.1 =1.5.16 =1.5.17 ]
5 postgresql9.6 available \
[ =9.6.6 =9.6.8 =stable ]
6 postgresql10=latest enabled [ =10 =stable ]
9 R3.4 available [ =3.4.3 =stable ]
10 rust1 available \
[ =1.22.1 =1.26.0 =1.26.1 =1.27.2 =1.31.0 =1.38.0
=stable ]
11 vim=latest enabled [ =8.0 =stable ]
15 php7.2 available \
[ =7.2.0 =7.2.4 =7.2.5 =7.2.8 =7.2.11 =7.2.13 =7.2.14
=7.2.16 =7.2.17 =7.2.19 =7.2.21 =7.2.22 =7.2.23
=7.2.24 =7.2.26 =stable ]
17 lamp-mariadb10.2-php7.2 available \
[ =10.2.10_7.2.0 =10.2.10_7.2.4 =10.2.10_7.2.5
=10.2.10_7.2.8 =10.2.10_7.2.11 =10.2.10_7.2.13
=10.2.10_7.2.14 =10.2.10_7.2.16 =10.2.10_7.2.17
=10.2.10_7.2.19 =10.2.10_7.2.22 =10.2.10_7.2.23
=10.2.10_7.2.24 =stable ]
18 libreoffice available \
[ =5.0.6.2_15 =5.3.6.1 =stable ]
19 gimp available [ =2.8.22 ]
20 docker=latest enabled \
[ =17.12.1 =18.03.1 =18.06.1 =18.09.9 =stable ]
21 mate-desktop1.x available \
[ =1.19.0 =1.20.0 =stable ]
22 GraphicsMagick1.3 available \
[ =1.3.29 =1.3.32 =1.3.34 =stable ]
23 tomcat8.5 available \
[ =8.5.31 =8.5.32 =8.5.38 =8.5.40 =8.5.42 =8.5.50
=stable ]
24 epel=latest enabled [ =7.11 =stable ]
25 testing available [ =1.0 =stable ]
26 ecs available [ =stable ]
27 corretto8 available \
[ =1.8.0_192 =1.8.0_202 =1.8.0_212 =1.8.0_222 =1.8.0_232
=1.8.0_242 =stable ]
28 firecracker available [ =0.11 =stable ]
29 golang1.11 available \
[ =1.11.3 =1.11.11 =1.11.13 =stable ]
30 squid4 available [ =4 =stable ]
31 php7.3 available \
[ =7.3.2 =7.3.3 =7.3.4 =7.3.6 =7.3.8 =7.3.9 =7.3.10
=7.3.11 =7.3.13 =stable ]
32 lustre2.10 available \
[ =2.10.5 =2.10.8 =stable ]
33 java-openjdk11 available [ =11 =stable ]
34 lynis available [ =stable ]
35 kernel-ng available [ =stable ]
36 BCC available [ =0.x =stable ]
37 mono available [ =5.x =stable ]
38 nginx1 available [ =stable ]
39 ruby2.6 available [ =2.6 =stable ]
40 mock available [ =stable ]
41 postgresql11 available [ =11 =stable ]
42 php7.4 available [ =stable ]
43 livepatch available [ =stable ]
44 python3.8 available [ =stable ]
45 haproxy2 available [ =stable ]
46 collectd available [ =stable ]
47 aws-nitro-enclaves-cli available [ =stable ]
48 R4 available [ =stable ]
49 kernel-5.4 available [ =stable ]
50 selinux-ng available [ =stable ]
51 php8.0 available [ =stable ]
52 tomcat9 available [ =stable ]
53 unbound1.13 available [ =stable ]
54 mariadb10.5 available [ =stable ]
55 kernel-5.10 available [ =stable ]
56 redis6 available [ =stable ]
[root@ip-172-31-81-123 ~]#
[root@ip-172-31-81-123 ~]# yum install -y postgresql-server postgresql-devel
yum install -y postgresql-server postgresql-devel
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
221 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package postgresql-devel.x86_64 0:10.16-1.amzn2.0.2 will be installed
---> Package postgresql-server.x86_64 0:10.16-1.amzn2.0.2 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
postgresql-devel x86_64 10.16-1.amzn2.0.2 amzn2extra-postgresql10 1.3 M
postgresql-server x86_64 10.16-1.amzn2.0.2 amzn2extra-postgresql10 4.8 M
Transaction Summary
=============================================================================================================================================================================================================================================
Install 2 Packages
Total download size: 6.1 M
Installed size: 25 M
Downloading packages:
(1/2): postgresql-devel-10.16-1.amzn2.0.2.x86_64.rpm | 1.3 MB 00:00:00
(2/2): postgresql-server-10.16-1.amzn2.0.2.x86_64.rpm | 4.8 MB 00:00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 16 MB/s | 6.1 MB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : postgresql-devel-10.16-1.amzn2.0.2.x86_64 1/2
Installing : postgresql-server-10.16-1.amzn2.0.2.x86_64 2/2
Verifying : postgresql-server-10.16-1.amzn2.0.2.x86_64 1/2
Verifying : postgresql-devel-10.16-1.amzn2.0.2.x86_64 2/2
Installed:
postgresql-devel.x86_64 0:10.16-1.amzn2.0.2 postgresql-server.x86_64 0:10.16-1.amzn2.0.2
Complete!
[root@ip-172-31-81-123 ~]#
[ec2-user@ip-172-31-81-123 myapp]$ sudo yum install postgresql-contrib
Loaded plugins: extras_suggestions, langpacks, priorities, update-motd
221 packages excluded due to repository priority protections
Resolving Dependencies
--> Running transaction check
---> Package postgresql-contrib.x86_64 0:10.16-1.amzn2.0.2 will be installed
--> Processing Dependency: libossp-uuid.so.16()(64bit) for package: postgresql-contrib-10.16-1.amzn2.0.2.x86_64
--> Running transaction check
---> Package uuid.x86_64 0:1.6.2-26.amzn2.0.1 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
=============================================================================================================================================================================================================================================
Package Arch Version Repository Size
=============================================================================================================================================================================================================================================
Installing:
postgresql-contrib x86_64 10.16-1.amzn2.0.2 amzn2extra-postgresql10 778 k
Installing for dependencies:
uuid x86_64 1.6.2-26.amzn2.0.1 amzn2-core 56 k
Transaction Summary
=============================================================================================================================================================================================================================================
Install 1 Package (+1 Dependent package)
Total download size: 834 k
Installed size: 2.5 M
Is this ok [y/d/N]: y
Downloading packages:
(1/2): uuid-1.6.2-26.amzn2.0.1.x86_64.rpm | 56 kB 00:00:00
(2/2): postgresql-contrib-10.16-1.amzn2.0.2.x86_64.rpm | 778 kB 00:00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 3.4 MB/s | 834 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : uuid-1.6.2-26.amzn2.0.1.x86_64 1/2
Installing : postgresql-contrib-10.16-1.amzn2.0.2.x86_64 2/2
Verifying : postgresql-contrib-10.16-1.amzn2.0.2.x86_64 1/2
Verifying : uuid-1.6.2-26.amzn2.0.1.x86_64 2/2
Installed:
postgresql-contrib.x86_64 0:10.16-1.amzn2.0.2
Dependency Installed:
uuid.x86_64 0:1.6.2-26.amzn2.0.1
Complete!
[ec2-user@ip-172-31-81-123 myapp]$
[root@ip-172-31-81-123 ~]# /usr/bin/postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@ip-172-31-81-123 ~]#
[ec2-user@ip-172-31-81-123 myapp]$ sudo su -
sudo su -
Last login: Tue Jun 8 00:48:18 UTC 2021 on pts/1
[root@ip-172-31-81-123 ~]# which postgresql-setup
/usr/bin/postgresql-setup
[root@ip-172-31-81-123 ~]# /usr/bin/postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@ip-172-31-81-123 ~]# systemctl enable --now postgresql
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[root@ip-172-31-81-123 ~]# exit
logout
[ec2-user@ip-172-31-81-123 myapp]$ sudo -u postgres psql -c "SELECT version();"
could not change directory to "/home/ec2-user/myapp": Permission denied
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 10.16 on x86_64-koji-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)
[ec2-user@ip-172-31-81-123 myapp]$
[root@ip-172-31-81-123 ~]# /usr/bin/postgresql-setup initdb
WARNING: using obsoleted argument syntax, try --help
WARNING: arguments transformed to: postgresql-setup --initdb --unit postgresql
* Initializing database in '/var/lib/pgsql/data'
* Initialized, logs are in /var/lib/pgsql/initdb_postgresql.log
[root@ip-172-31-81-123 ~]# systemctl enable --now postgresql
Created symlink from /etc/systemd/system/multi-user.target.wants/postgresql.service to /usr/lib/systemd/system/postgresql.service.
[root@ip-172-31-81-123 ~]# exit
logout
[ec2-user@ip-172-31-81-123 myapp]$ sudo -u postgres psql -c "SELECT version();"
could not change directory to "/home/ec2-user/myapp": Permission denied
version
------------------------------------------------------------------------------------------------------------
PostgreSQL 10.16 on x86_64-koji-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
(1 row)
[ec2-user@ip-172-31-81-123 myapp]$ sudo systemctl start postgresql
[ec2-user@ip-172-31-81-123 myapp]$ sudo systemctl status postgresql
● postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
Active: active (running) since Tue 2021-06-08 00:59:33 UTC; 2min 55s ago
Process: 6635 ExecStartPre=/usr/libexec/postgresql-check-db-dir %N (code=exited, status=0/SUCCESS)
Main PID: 6637 (postmaster)
CGroup: /system.slice/postgresql.service
├─6637 /usr/bin/postmaster -D /var/lib/pgsql/data
├─6640 postgres: logger process
├─6642 postgres: checkpointer process
├─6643 postgres: writer process
├─6644 postgres: wal writer process
├─6645 postgres: autovacuum launcher process
├─6646 postgres: stats collector process
└─6647 postgres: bgworker: logical replication launcher
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal systemd[1]: Starting PostgreSQL database server...
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal postmaster[6637]: 2021-06-08 00:59:33.319 UTC [6637] LOG: listening on IPv4 address "127.0.0.1", port 5432
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal postmaster[6637]: 2021-06-08 00:59:33.321 UTC [6637] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal postmaster[6637]: 2021-06-08 00:59:33.324 UTC [6637] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal postmaster[6637]: 2021-06-08 00:59:33.335 UTC [6637] LOG: redirecting log output to logging collector process
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal postmaster[6637]: 2021-06-08 00:59:33.335 UTC [6637] HINT: Future log output will appear in directory "log".
Jun 08 00:59:33 ip-172-31-81-123.ec2.internal systemd[1]: Started PostgreSQL database server.
[ec2-user@ip-172-31-81-123 myapp]$
Configure to be accessible anywhere
[root@thermalite data]# cat postgresql.conf | sed '/^$/d' | sed -e 's/^[[:space:]]*//' | grep -v "^#"
listen_addresses = '*'
port = 5432 # (change requires restart)
max_connections = 100 # (change requires restart)
shared_buffers = 128MB # min 128kB
dynamic_shared_memory_type = posix # the default is the first option
logging_collector = on # Enable capturing of stderr and csvlog
log_filename = 'postgresql-%a.log' # log file name pattern,
log_truncate_on_rotation = on # If on, an existing log file with the
log_rotation_age = 1d # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_timezone = 'America/New_York'
datestyle = 'iso, mdy'
timezone = 'America/New_York'
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'
[root@thermalite data]#
[root@thermalite data]# cat pg_hba.conf | sed '/^$/d' | sed -e 's/^[[:space:]]*//' | grep -v "^#"
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 ident
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
host all all 0.0.0.0/0 md5
host all all ::/0 md5
host all all 192.168.1.0/24 md5
host all all 192.168.1.0/16 ident map=thermalite
[root@thermalite data]#
[root@thermalite data]# vi pg_hba.conf
# Put your actual configuration here
# ----------------------------------
#
# If you want to allow non-local connections, you need to add more
# "host" records. In that case you will also need to make PostgreSQL
# listen on a non-local interface via the listen_addresses
# configuration parameter, or via the -i or -h command line switches.
# TYPE DATABASE USER ADDRESS METHOD
# "local" is for Unix domain socket connections only
#local all all peer
local all all trust
# IPv4 local connections:
#host all all 127.0.0.1/32 ident
host all all 127.0.0.1/32 trust
# IPv6 local connections:
host all all ::1/128 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
local replication all peer
host replication all 127.0.0.1/32 ident
host replication all ::1/128 ident
# Accept from anywhere
host all all 0.0.0.0/0 md5
host all all ::/0 md5
# Accept from trusted subnet
host all all 192.168.1.0/24 md5
# Allow users from 192.168.1.x hosts to connect to any database, if
# they pass the ident check. If, for example, ident says the user is
# "malex" and he requests to connect as PostgreSQL user "dbuser", the
# connection is allowed if there is an entry in pg_ident.conf for map
# "thermalite" that says "malex" is allowed to connect as "dbuser".
#
# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.1.0/16 ident map=thermalite
[root@thermalite data]# vi pg_ident.conf
# Put your actual configuration here
# ----------------------------------
# MAPNAME SYSTEM-USERNAME PG-USERNAME
thermalite malex dbuser
thermalite malex postgres
[root@thermalite data]# cat pg_ident.conf | sed '/^$/d' | sed -e 's/^[[:space:]]*//' | grep -v "^#"
thermalite malex dbuser
thermalite malex postgres
[root@thermalite data]#
Monday, June 7, 2021
Install PostgreSQL Server on EC2 instance
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment