HowTo create a PostgreSQL database on AWS

  1. SSH to your Linux System
    user: ec2-user
    IP Address: 10.0.0.99
    Key: aws_pem.pem
    [ec2-user@ip-10-0-0-99~]$ sudo su
    [root@ip-10-0-0-99 tmp]# ssh -i "aws_pem.pem" ec2-user@ip-10-0-0-199.ec2.internal
  2. Install the repository RPM
    [ec2-user@ip-10-0-0-199~]$ sudo yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-redhat10-10-2.noarch.rpm
  3. Install the client package
    [ec2-user@ip-10-0-0-199~]$ sudo yum install postgresql10
  4. Connect to PostgreSQL and create the schema and user account
    [root@ip-10-0-0-199 ec2-user]# sudo -u postgres psql postgres
    postgres=#\du
    create schema MyDB;
    create user MyUser0 password 'MyPassw0rd';
    grant all on schema MyDB to MyUser0;
  5. Check if port 5432 is listening
    ss -nlt | grep 5432
    OR
    psql -h 127.0.0.1 -p 5432 -c "select 1 as res" -U postgres
    res
    ----------
    1
    (1 row)
  6. Check again to ensure account is created
    [root@ip-10-0-0-199 data]# su - postgres
    -bash-4.2$ psql
    psql (10.3)
    Type "help" for help.
    postgres=# \du
    List of roles
    Role name | Attributes | Member of
    -------------+------------------------------------------------------------+-----------
    MyUser0| Superuser | {}
    postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
    postgres=#
    postgres=# select version();
    version
    ------------------------------------------------------------------------------------------------------
    PostgreSQL 10.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-16), 64-bit
    (1 row)
    ALTER USER MyUser WITH PASSWORD 'MyNewPassw0rd';
  7. Done
SHARE