Setting up a managed PostgreSQL database using AWS Lightsail

What is AWS Lightsail?

Amazon Lightsail is the easiest way to get started with Amazon Web Services (AWS) for anyone who needs to build websites or web applications. It includes everything you need to launch your project quickly - instances (virtual private servers), container services, managed databases, content delivery network (CDN) distributions, load balancers, SSD-based block storage, static IP addresses, DNS management of registered domains, and resource snapshots (backups) - for a low, predictable monthly price.

What is PostgreSQL?

PostgreSQL is a powerful, open source object-relational database system with over 35 years of active development that has earned it a strong reputation for reliability, feature robustness, and performance.

Why use a managed database?

Using a managed database simplifies setup and maintenance, offering automated backups, disaster recovery, and high availability. It provides scalability to handle increased traffic, robust security features, and performance optimization for responsive user experiences, streamlining development and ensuring data reliability.

Step 1: Sign up for an AWS Account

Start by creating your own AWS account. This will give you access to a variety of powerful tools and resources provided by Amazon Web Services, allowing you to easily build, deploy, and manage your applications in the cloud.

Step 2: Create a PostgreSQL managed database

1. Sign in into the Lightsail console tab: Lightsail Console
2. On the Lightsail Homepage, click on the Databases tab

1. Then click on Create database
2. Choose the AWS Region and Availability Zone for your database

1. If you want to change the AWS Region and Availability Zone then click on: Change AWS Region and Availability Zone

2. Pick your database:

   • For this tutorial I am choosing PostgreSQL v13.14 as it is compatible to my Strapi project

1. Specify Database options like username, password and database name

   • This step is optional, you can specify these for yourselves, or AWS Lightsail will create and manage these for you.

1. Choose your database plan

   • For this, you can choose between Standard or High Availability plan:
   • Standard plans are generally used for dev/test environments.
   • High Availability plans are used for production workloads
   • For this I am going to choose the Standard plan

The good news is that AWS Lightsail offers first 3 months free for the Database instance.

1. Choose a name for your database

   • Use tags to filter and organize your resources in the Lightsail console. Key-value tags can also be used to organize your billing, and to control access to your resources.

1. Click on create database

• In just minutes, your Lightsail database is up and running, ready for configuration or immediate connection using a database client. Get started effortlessly with data import and seamless connectivity to kickstart your project!

Step 3: Connect to your database

• Pre-requisite

  • Install PgAdmin: PG Admin Download which is available for Windows, Linux and MacOS.

• To get the connection details for your database

  1. Choose the database name you want to connect to

     1. For me it is PortoBlog so I will click on that

• 

• On the connect tab note the endpoint and port number.

• On the connect tab note the username and password of the database.

• Configure the public availability of your database

  1. You must enable public mode for your database to connect to it externally, or from a Lightsail instance in a different Region than your database. With public mode enabled, anyone with the database user name and password can connect to your database.
  2. To do this click on the Networking tab and enable Public Mode

  

• Configure database client to connect to your PostgreSQL Database:

  1. Open pgAdmin

  

  1. Right click on Servers → Register → Server

  

  1. Add a name for the server

  

  1. Choose the Connection tab, then enter the following information into the form that displays

  

  • Host name/address — Enter the database endpoint that you obtained earlier. If you copied the database endpoint from the Lightsail console, and it’s still in your clipboard, press Ctrl+V if you’re using Windows, or Cmd+V if you’re using macOS, to paste it.

  • Port — Enter the port for your database that you obtained earlier. The default port for PostgreSQL is 5432.

  • Maintenance database — Specify the name of the initial database to which the client will connect. This is the primary database name that you specified when you created your PostgreSQL database in Lightsail.

    Enter postgres if you can’t remember the name of your primary database. Every PostgreSQL managed database has a postgres database that you can connect to, after which you’ll be able to access all other databases on the PostgreSQL managed database.

  • Username — Enter the database user name that you obtained earlier.

  • Password — Enter your database password that you obtained earlier. If you copied your password from the Lightsail console, and it’s still in your clipboard, press Ctrl+V if you’re using Windows, or Cmd+V if you’re using macOS, to paste it. Choose Save password to save your password.

  • Role and Service — Leave these fields blank.

  1. Click on Save to save the new server details
  2. To connect to your database, double-click your new database connection. If the connection is successful, you will see a list of available resources for that database.

  

Conclusion

Conclusively, with Lightsail, setting up and accessing your database is a breeze. Whether you're configuring it for data import or connecting through a database client, Lightsail offers simplicity and efficiency, empowering you to swiftly embark on your development journey with confidence.