Postgres

For CLI help, see cheatsheets:

postgres

Resources

Homepage on Postgres homepage.

PostgreSQL: The World’s Most Advanced Open Source Relational Database
About on Postgres homepage.

Docs and tutorials

Installation

Choose an option below.

Go to Download page.

Follow the Ubuntu Postgres download instructons. Make sure you install ls_release and wget first if needed.

  $ sudo apt-get install lsb-release wget -y

  # Create the file repository configuration:
  sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

  # Import the repository signing key:
  wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

  sudo apt-get update

  # Latest
  sudo apt-get -y install postgresql
  # OR target a version.
  sudo apt-get -y install postgresql-12

Show

Show database

\l

source

In the CLI:

$ psql -U postgres -l

Recommended - specify a user as the default is root and you’ll get an error if that does not exist.

Tables

Show all tables:

\d

Tables and views:

\dt

More info:

\dt+

Add a table name to filter:

\d table_name

Use database

If you omit database name argument, the default of postgres will be used.

$ psql -U postgres DB_NAME

Quit

\q

Or CTRL+d.

Users

Here we use a user named postgres:

$ psql -U postgres

The console will then look like this:

postgres=#

Create user

CREATE USER foo

Recommended - use the optional ENCRYPTED modified.

CREATE USER foo WITH ENCRYPTED PASSWORD 'bar';

See create user docs.

Grant priviledges:

GRANT ALL PRIVILEGES ON DATABASE my-db TO foo;

Or create with the shell:

$ sudo -u postgres createuser foo

Create role

CREATE ROLE adds a new role to a PostgreSQL database cluster.

A role is an entity that can own database objects and have database privileges; a role can be considered a “user”, a “group”, or both depending on how it is used.

See create role docs.

Create a role that can log in, but don’t give it a password:

CREATE ROLE my-role LOGIN;

Create a role with a password:

CREATE USER my-user WITH PASSWORD 'abce1234';

CREATE USER is the same as CREATE ROLE except that it implies LOGIN.

Give permissions to the role.

CREATE ROLE foo WITH CREATEDB;
CREATE ROLE foo WITH CREATEDB CREATEROLE;

Drop:

DROP ROLE foo;

Change password

ALTER USER my_user WITH PASSWORD 'my_password';

Or encrypt it:

ALTER USER my_user WITH ENCRYPTED PASSWORD 'my_password';

See alter user docs.

Set it interactively:

postgres=# \password

From question on forum

You cannot read the original value of an encryped password. But you can set a new one.

List users

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

More details:

postgres=# \du+

List users PG tutorial.

Connect

$ psql

You might need to switch users first:

$ sudo su postgres

Set user, database and host with example below.

$ psql -U my-user -d my-db -h abc.eu-central-1.rds.amazonaws.com

From the docs.

$ psql "service=myservice sslmode=require"
$ psql postgresql://dbmaster:5433/mydb?sslmode=require

Password will be requested interactively. Otherwise use pgpass or set with env variable in shell config as:

PGPASSWORD=my-password

Avoid using this in the terminal directory as it is part of the history.

Run query using CLI

See Connect above for other flags to add.

See psql docs.

Interactive console:

$ psql

Run query in string:

$ psql -c 'SELECT COUNT(*) FROM my_table'

Or you can pass the name of a .sql file.

$ psql -f query.sql

$ psql < query.sql

Passwords

If you database is password-protected.

You can get prompted in the CLI to enter a password.

Or enter password using an approach here.

Env vars

$ PGPASSWORD=123 psql ...

env vars manual.

PG pass

Use a .pgpass file to store the password. pgpass manual.

Trust authentication

For that specific user:

Auth trust.

Connection URI

Use a connection string that contains everything:

Connect in manual.

Create, drop, dump and restore

Overview

Binary dumps

Using compress binary archive:

pg_dump -U postgres DB_NAME --format=custom

dropdb -U postgres DB_NAME
pg_restore -U postgres db.pgdump

Convert an existing dump to plain text:

pg_restore db.pgdump -f db.sql

Text dump

Using plain text SQL file - larger as it is not compressed but it is easy to read.

pg_dump -U postgres DB_NAME -fp

dropdb -U postgres DB_NAME
createdb -U postgres DB_NAME
psql -U postgres DB_NAME < db.sql

Create DB

$ createdb -U my-user my-db

See createdb docs.

Drop DB

$ dropdb -U my-user my-db

See dropdb docs.

Dump

Create a dump from a DB:

$ pg_dump -Fc db_name -f db_name.pgdump
$ # OR
$ pg_dump -Fc db_name > db_name.pgdump

See the pg_dump docs.

pg_dump — extract a PostgreSQL database into a script file or other archive file

pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).

pg_dump [connection-option...] [option...] [dbname]

-F, --format FORMAT - The default format is p or plain for plain text SQL. Use c or custom as a compressed archive which is the most flexible option. Also d or directory for a directory of files, and t or tar are options. e.g. -Fc (note no equals sign) or --format=custom.
-f, --file PATH - path to write to, otherwise will write to stdout.

e.g.

$ pg_dump -U my-user -d db-name -Fc -h abcdef.eu-central-1.rds.amazonaws.com -f db.pgdump

Restore

Drop the database and recreate it from a dump:

$ dropdb db_name
$ pg_restore -C -d db_name db_name.dump

See pg_restore docs.

pg_restore — restore a PostgreSQL database from an archive file created by pg_dump

pg_restore is a utility for restoring a PostgreSQL database from an archive created by pg_dump in one of the non-plain-text formats

pg_restore [connection-option...] [option...] [filename]

-C, --create - Create the database before restoring into it.
-c, --clean - drop the DB first.
--if-exists - to be used with the clean flag.

The database comes from within the dump (unlike with plain SQL) so you don’t need to specify it.