Purpose
These are my notes to help jog my memory1; 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
- 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[↩]
Comments