Setting up Postgres on Ubuntu Linux

Hosting Postgres on Linux: A simple tutorial

Prerequisites

• An Ubuntu system (this guide uses Ubuntu 20.04 LTS, but should work for other recent versions)

• Sudo privileges on your Ubuntu system

• Basic familiarity with the command line

So, What is Postgres?

PostgreSQL (postgres) is a powerful open-source relational database system supporting a wide array of features including:

• ACID Compliance: Ensures reliable transactions.

• Complex Queries: Allows for sophisticated SQL queries, including joins, subqueries, and window functions.

• Extensibility: You can define custom data types, functions, and operators.

• Support for JSON: Handles unstructured data with robust JSON support. (Love this feature)

• Concurrency: Uses Multiversion Concurrency Control (MVCC) to manage multiple transactions simultaneously without locking.

Okay, but why Postgres?

The choice of a database system depends on your overall projects needs and requirements. Since I store different types of JSON data and Enums related to this website, I have deemed both SQLite and MySQL inadequate in meeting my storage needs. Since the beginning, this website's data has been stored in a free PostgreSQL database provided by Supabase which hosts my data on AWS cloud. Supabase also provides services on top of the hosted database, such as:

• Authentication: Easy user authentication and authorization options.

• APIs: Automatically generated RESTful and GraphQL APIs for interacting with your database.

• Storage: File storage for managing media and other assets.

• Auto scaling: Your database grows with your data allowing for more flexibility

• Real-time Subscriptions: Allows applications to receive real-time updates when data changes.

That's cool, but why self-host?

Typically, I would never self-host my databases since managing, keeping it secure, and scaling the database could become a chore. Unfortunately, nearly all of this website's article data vanished from Supabase's servers (likely due to inactivity), which is why I purchased a $4 Linux VPS for the sole purpose of hosting my own data. I found this method feasible since I do not expect to scale anytime in the future or fuss too hard at managing it. With that being said, let's begin.

Step 1: Update Your System

Before installing any new software, it's a good practice to update your system:

sudo apt update sudo apt upgrade -y

Step 2: Install PostgreSQL

Install PostgreSQL and the contrib package, which provides some additional utilities and functionality:

sudo apt install postgresql postgresql-contrib -y

Step 3: Verify the Installation

After the installation completes, PostgreSQL should start automatically. Verify the installation and service status:

sudo systemctl status postgresql

You should see output indicating that the service is active and running.

Step 4: Access PostgreSQL

By default, PostgreSQL creates a user account called postgres during installation. Switch to this account:

sudo -i -u postgres

You can now access the PostgreSQL prompt by typing:

psql

To exit the PostgreSQL prompt, type:

\q

Step 5: Create a New Database and User

It's generally not a good practice to use the default postgres user for your applications. Let's create a new database and user:

1. Access the PostgreSQL prompt:

   sudo -u postgres psql

2. Create a new database:

3. CREATE DATABASE myapp;

4. Create a new user:

5. CREATE USER myapp_user WITH PASSWORD 'your_password';

6. Grant privileges to the new user on the new database:

7. GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user;

8. Exit the PostgreSQL prompt:

9. \q

Step 6: Configure PostgreSQL for Remote Access (Optional)

By default, PostgreSQL only allows connections from localhost. If you need to allow remote connections (such as with my case):

1. Edit the PostgreSQL configuration file:

   sudo nano /etc/postgresql/12/main/postgresql.conf

   (Replace "12" with your postgres version)

   Find the line that says listen_addresses, uncomment it, and change it to:

2. listen_addresses = '*'

3. Edit the client authentication configuration file:

   sudo nano /etc/postgresql/12/main/pg_hba.conf

   Add the following line at the end of the file to allow connections from any IP address:

4. host all all 0.0.0.0/0 md5

   Note: This allows connections from any IP. In a production environment, you should restrict this to specific IP addresses or ranges.

5. Restart PostgreSQL to apply the changes:

   sudo systemctl restart postgresql

Step 7: Secure Your PostgreSQL Installation

1. Use strong passwords for all database users.

2. Regularly update PostgreSQL to the latest version.

3. Configure your firewall to only allow connections on the PostgreSQL port (default 5432) from trusted IP addresses.

4. Consider using SSL for encrypted connections (Tutorial on this later?).

Okay great. Now, you and I both have a PostgreSQL database installed and running on your Ubuntu system. You've created a new database and user, and configured it for remote access. Yes, this is a simple guide, but if you are looking for more detailed information you can always read the docs...but this will do for now at least for my purposes lol