jog memory: PostgreSQL

Purpose

These are my notes to help jog my memory¹; it is a living document that will be revised as needed.

Nearly everything in this document has been referenced at one time or another at https://www.postgresql.org/docs/

Connection Settings

By default, Postgres will not accept connections over TCP/IP unless listen_addresses = '*' is enabled. The * is a wildcard for any source. A list of IP addresses may be used instead.

The default port is, 5432

Management Tools

PostgresSQL interactive terminal (psql).

psql is a terminal-based front-end to PostgreSQL. It enables you to type in queries interactively, issue them to PostgreSQL, and see the query results. Alternatively, input can be from a file or from command line arguments. In addition, psql provides a number of meta-commands and various shell-like features to facilitate writing scripts and automating a wide variety of tasks.

https://www.postgresql.org/docs/current/app-psql.html

Example: <shell> psql dbname -U username

Within psql you can use the following to help

• Get a list of available commands use \?
• Get help for a command use \? [commands]

pgAdmin

pgAdmin is the most popular and feature rich Open Source administration and development platform for PostgreSQL, the most advanced Open Source database in the world.

https://www.pgadmin.org

pgAdmin has helped me visually learn my way around PostgreSQL RDMS.

Currently running this from a Docker container, dpage/pgadmin4

docker run -p 80:80 \
    --name pgAdmin \
    --network dbnet \
    -e 'PGADMIN_DEFAULT_EMAIL=dev@null.io' \
    -e 'PGADMIN_DEFAULT_PASSWORD=goodone' \
    -d dpage/pgadmin4

Get A List Of Databases

Use the \l to list databases

<shell> psql dbname -U username
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# \l
                                                 List of databases
    Name     |  Owner   | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |   Access privileges   
-------------+----------+----------+------------+------------+------------+-----------------+-----------------------
 dummy_data  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 dummy_data2 | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 postgres    | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | 
 template0   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
             |          |          |            |            |            |                 | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_US.utf8 | en_US.utf8 |            | libc            | =c/postgres          +
             |          |          |            |            |            |                 | postgres=CTc/postgres
(5 rows)

postgres=# 

Connect To A Different Database

Move between databases using the \c command

<shell> psql dbname -U username
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# \c dummy_data
You are now connected to database "dummy_data" as user "postgres".
dummy_data=#

Get A List Of Tables

Use the \d to list tables

<shell> psql dbname -U username
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

dummy_data=# \d
                  List of relations
 Schema |         Name         |   Type   |  Owner   
--------+----------------------+----------+----------
 public | doc_content          | table    | postgres
 public | first_names          | table    | postgres
 public | first_names_name_seq | sequence | postgres
 public | last_names           | table    | postgres
 public | last_names_name_seq  | sequence | postgres
(5 rows)

dummy_data=# 

Get Table Column and Type

Use the \d tablename to get column names and type info.

psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# \d amazon_trans
                                      Table "public.amazon_trans"
   Column    |          Type          | Collation | Nullable |                 Default                  
-------------+------------------------+-----------+----------+------------------------------------------
 id          | integer                |           | not null | nextval('amazon_trans_id_seq'::regclass)
 date        | date                   |           |          | 
 ordernumber | character varying(255) |           |          | 
 amount      | money                  |           |          | 
 who         | character varying(255) |           |          | 
 christmas   | character varying(255) |           |          | 
Indexes:
    "amazon_trans_pkey" PRIMARY KEY, btree (id)

postgres=# 

Import CSV File To Table

Given a file with a dataset in comma separated values (csv), import it from the filesystem into the Postgres table.

The below example was run from within a Docker container. This is why the csv file’s path is shown with '/amazon.csv'. The first row of the csv file is a header, Date,OrderNumber,Amount,Who,Christmas.

Further documentation for the below is here, https://www.postgresql.org/docs/current/sql-copy.html

psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# COPY amazon_trans (Date,OrderNumber,Amount,Who,Christmas) FROM '/amazon.csv' DELIMITER ',' CSV HEADER;
COPY 31

Run Postgres In Docker Container

Following is how I’m running Postgres in a container using Docker Desktop on MacOS (MacBookPro M1 Max MacBookPro18,4)

For my needs, I treat the container and associated storage as persistent.

Any tooling that will connect to the database will reside locally on my computer and not over any external network.

Setup

The following block of commands are run once from a terminal. Controlling the container, starting and stopping, is done with Docker Desktop GUI. It is just easier to point and click than to type out a bunch of commands (I’m lazy).

# Create persistent storage.
docker volume create postgres_data

# Create network
docker network create dbnet

# Run container
docker run -d \
  -v postgres_data:/var/lib/postgresql/data \
  -p 5432:5432 \
  --network dbnet \
  --name pgdb \
  -e POSTGRES_PASSWORD=*** \
  postgres:15 

Accessing psql In Container

The command is, docker exec -ti pgdb psql -U postgres

docker exec -ti pgdb psql -U postgres
psql (15.1 (Debian 15.1-1.pgdg110+1))
Type "help" for help.

postgres=# 
postgres=# select version();
                                                              version                                                              
-----------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 15.1 (Debian 15.1-1.pgdg110+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit
(1 row)

postgres=# 

Accessing bash In Container

When you need to use the command-line tools that come with Postgres, access bash within the container using the following Docker command.

docker exec -ti pgdb bash

Document Revision History

Date	Summary of Changes
11/26/2022	First draft and publish

Endnotes / Footnotes / Sources

---

Footnotes and Sources

1. To cause one to remember something. jog memory. (n.d.) Farlex Dictionary of Idioms. (2015). Retrieved January 27 2022 from https://idioms.thefreedictionary.com/jog+memory[↩]

---