Quickstart PostgreSQL

PostgreSQL is a powerful, open-source relational database management system (RDBMS) known for its robust features and reliability. This quickstart guide will walk you through the essential steps to get started with PostgreSQL on macOS using the Brew package manager. You’ll learn how to install PostgreSQL, access its command-line interface, create users and databases, and even connect to the database using a graphical client called TablePlus. Additionally, we’ll cover the basics of database management and introduce you to migration operations to help you efficiently manage and interact with PostgreSQL databases on your macOS system. Whether you’re new to PostgreSQL or just need a refresher, this guide will provide you with the fundamental knowledge to start using PostgreSQL effectively.

Step 1: Install PostgreSQL

First, install PostgreSQL using Brew:

brew install postgresql

Then, start PostgreSQL:

brew services start postgresql@14

Step 2: Access the PostgreSQL Command Line

To access the PostgreSQL command line, use the following command:

psql postgres

This will open an interactive command-line environment for PostgreSQL.

Step 3: List Users and Create a New User

Now, you can use the following command to see which users are installed on the PostgreSQL database server:

postgres=# \du

To create a new user, use the following command. Replace ‘okantest’ with your desired username and specify a secure password within the single quotes:

CREATE ROLE okantest WITH LOGIN PASSWORD 'quoted password' [OPTIONS]

You can also grant the ‘okantest’ user the ability to create databases with the following command:

postgres=# ALTER ROLE okan CREATEDB;

Step 4: Create a Database

After creating a new user, you can create a database. Let’s assume ‘lego’ as an example database name:

CREATE DATABASE lego;

Step 5: Install TablePlus Client

TablePlus is a handy client to access PostgreSQL and other databases. Install TablePlus using Brew with the following command:

brew install --cask tableplus

Step 6: Connect with TablePlus

To connect to the PostgreSQL database using TablePlus, use the following values. Replace your username, password and database with the values you specified earlier.

Using psql Command for PostgreSQL

The psql command is the command-line interface to PostgreSQL, allowing you to interact with your PostgreSQL database directly. In the command psql postgres -U okantest:

• psql initiates the PostgreSQL interactive terminal.
• postgres is the name of the database you're connecting to (you can replace it with your database name).
• -U okantest specifies the PostgreSQL username for the connection (replace "okantest" with your username).

Once executed, psql prompts for your password. After successful authentication, you can perform various PostgreSQL operations, including running SQL queries, managing databases, and administering database objects, all from the command line.

psql postgre -U okantest

Step 7: Database Management and Migration

Once successfully connected to the PostgreSQL database using TablePlus, you can manage your database and perform migration operations. Migration operations are particularly useful for updating or modifying your database. Here’s an example migration command:

migrate --source=file://. --database=postgres://okantest:123456@localhost:5432/lego\?sslmode=disable up

This command will use migration files to update the ‘lego’ database. Replace ‘okantest’ and ‘password’ with your own PostgreSQL username and password.

By following these steps, you can install PostgreSQL, perform basic database management, and access your database using TablePlus.