Basic CLI Postgres Management

Let’s learn some basics and “to the point” examples about how to manage our databases in PostgreSQL using the command line.

obs: we assume you have postgres installed on your machine. If it is not the case, install it following the instructions here.

Set Password for the Default Account

1
2
3
4
5
psql postgres

\password postgres

# type the password

Create Users

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# connect to postgres
psql postgres

# see all users
\du

List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
ericdouglas | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

# CREATE ROLE username WITH LOGIN PASSWORD 'quoted password' [OPTIONS];
# obs: don't forget the semicolon! :)
CREATE ROLE eric WITH LOGIN PASSWORD 'somepass';

# check if the user was created
\du

List of roles
Role name | Attributes | Member of
-------------+------------------------------------------------------------+-----------
eric | | {}
ericdouglas | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Give Permissions to a User

1
2
# give permission for a user to create a new database
ALTER ROLE username CREATEDB;

Create a new Database

1
2
3
4
5
6
7
8
# connect to postgres with a "normal" user
psql postgres -U eric

# Now instead of `postgres=#` appear in the prompt
# you will se `postgres=>`, indicating you aren't logged with a super user account

# Create the new DB
CREATE DATABASE test_db;

Give Permissions in a Database

1
2
3
4
5
6
7
8
9
10
GRANT ALL PRIVILEGES ON DATABASE test_db TO eric;

# list all dbs
\list # or \l

List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
--------------------+-------------+----------+-------------+-------------+-----------------------
test_db | eric | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
(1 row)

Connect to a Database

1
2
3
4
5
6
7
8
psql postgres -U eric

# connect to a DB
\connect test_db # or \c
# You are now connected to database "test_db" as user "eric".

# list tables in the currently connected database
\d

Create a table

1
2
3
4
5
6
7
CREATE TABLE college (
cname varchar (128) PRIMARY KEY,
state varchar (2) check (state in ('CA', 'NY', 'MA')),
enrollment int
);

\d college

Change Table Data

1
2
3
4
5
ALTER TABLE college ADD COLUMN open bool;

ALTER TABLE college ALTER COLUMN open SET DEFAULT 'true';

ALTER TABLE college DROP COLUMN open;

Deleting Tables

1
DROP TABLE college;

Inserting Data

1
2
3
4
5
INSERT INTO college (cname, state, enrollment) VALUES
('Stanford', 'CA', 15000),
('Berkeley', 'CA', 36000),
('MIT', 'MA', 10000),
('Cornell', 'NY', 21000);

References