Running Postgres In The Cloud With Docker

Step 1 - Start an EC2 instance on AWS

• If you don’t already have one. create an AWS account and go to the EC2 Dashboard.
• Launch an instance
  • I am choosing Ubuntu 20.04 as an OS
    • There are no wrong choices here
    • I choose Ubuntu because it is the most popular Linux server distribution
    • It is hard to go wrong with Red hat enterprise linux or it’s cousin centos, or even Ubuntu’s older brother Debian.
    • Anyways, this is an article about databases, not Linux, so let’s move on.
  • Instance size T2 Micro (free tier)
  • No instance detail changes
  • No storage changes
  • No tags
  • Networking
    • In a normal situation, a database would not be exposed to the internet. Normally only the backend of a web application, or maybe some other internal services, would be able to talk to the database. However, in our case, we don’t have a backend or any internal services, so we are going to open this database up to the internet.
    • Create Postgres rule
    • Open up port 5432 to 0.0.0.0/0, which means this computer will accept connections from any IP address, to port 5432.
    • Port 5432 is significant because it is the default port that Postgres exposes itself on. So normally, you would open port 5432, or whatever port your database is listening on, to a TRUSTED ip address.
    • Port 5432 is so well known that AWS calls this a PostgreSQL type rule.
    • The other rule is port 22, which is the default port for what’s known as SSHing into the computer. SSHing, if you don’t know, is basically a way to connect to a remote server using the command line.
  • Launch
    • Create a key pair and download. Name it my_test_pair, or whatever else you want, just remember the name.

Step 2 - SSH into the EC2 instance

• Get the SSH key you download into the ~/.ssh directory
  • If you’re wondering what .ssh is, its a hidden directory, in your home directory, that holds SSH keys.
    • SSH keys are a file on your computer that proves you are who you say you are, and is the most common way to access computers in the cloud. Basically, unless we configure an SSH key, we won’t be able to access the server we just spun up.
  • SSH keys are only supposed to be read by you, not other users on the same computer. If you get error that the file isn’t secure enough - we can fix that by going to the ~/.ssh directory and running the following command: chmod 400 my_test_pair.pem
• SSH in
  • Get public IP address from EC2 dashboard
  • ssh -i ssh key, ubuntu@IP_ADDRESS

Step 3 - Install Docker on EC2 instance

• curl -fsSL https://get.docker.com -o get-docker.sh
• sh get-docker.sh
• sudo usermod -aG docker ubuntu
• log in the log back out (exit then re-ssh in ssh -i ssh key, ubuntu@IP_ADDRESS)

Step 4 - Run a PostgreSQL docker container

The following command will download and run a docker image containing PostgreSQL 13.1.

docker container run --name postgresdb -p 5432:5432 -v pgdata -e POSTGRES_PASSWORD='mypass12345' postgres:13.1

Let’s break it down:

• docker container run - This is the command to run a new docker container. You would use docker start to restart a container.
• --name postgresdb - Given the container a name. Unless you give the container a name, you’ll end up with some randomly generated letters that are impossible to remember.
• -p 5432:5432 - This is the line that makes our database accessible to the outside world - it maps port 5432 from the PostgreSQL image to the outside of the container. You can access PostgreSQL by pointing PSQL at this port, if you want. We’re not going to do that, but I think its good to mention.
• -v pgdata - This line creates a volume called pgdata. If we didn’t create a volume, we would lose all data in our database every time we shut down docker.
• -e POSTGRES_PASSWORD='mypass12345' - This line passes an environment variable to set the password needed to log in to the database. PostgreSQL will refuse to start if you don’t pass this environment variable!
• postgres13:1 - Here we specify the image (and tag, after the colon) we want to run. You can see the official PostgreSQL Dockerhub image here.

Step 5 - Connect to PostgreSQL

At this point, if you have PSQL on your computer, you can connect to the database by using PSQL.

We’re going to go use the PSQL that was automatically installed in the Docker container from our first tutorial - this makes it so we don’t have to install anything else on our computer.

• Open a new command line tab (or use Linux screen to keep Postgres running) and run:

  docker exec -it postgresdb basg
  

Let’s break that one down:

• docker exec -it postgresdb - The docker exec -it part specifies that we want to get inside of a docker image. postgresdb is the name of the container we want to get into.

• bash - This allows us to get a shell inside the Docker container - which is great, because we want to use the PSQL command line utility!

• Then, run postgresql://postgres@IP_ADDRESS_OF_EC2:5432
• Enter the password, and you should be good to go!

Step 4 - Do some data stuff!

• Create a database
• Create a table
• Insert some data
• Query the data
• Delete some data
• Drop a database

That’s it! Hope this helps you. Check out the previous video on running Postgres locally.