PostgresSQL CLI command cheatsheet

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
# To start manually:
pg_ctl -D /usr/local/var/postgres start

# To stop manually:
pg_ctl -D /usr/local/var/postgres stop

#To start PostgreSQL server now and relaunch at login:
brew services start postgresql

#And stop PostgreSQL:
brew services stop postgresql

Connect to PostgreSQL database

 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
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
# connects to a database under a specific user
psql -d database -U  user -W

# connect to a database that resides on another host
psql -h host -d database -U user -W

# use SSL mode for the connection
psql -U user -h host "dbname=db sslmode=require"

# Dump data with pg_dump
pg_dump -h <host> -U <user> <db_name> > /path/to/filename.tar # .dump

# Restore data -c for recreate
pg_restore -c -U <user> -d <db_name> -v "/path/to/filename.tar" -W
# -c to clean the database
# -U to force a user
# -d to select the database
# -v verbose mode, don't know why
# -W to force asking for the password to the user (postgres)

# В локальную базу с помощью psql
psql <db_name> < /path/to/file/filename.tar

# Из контейнера
docker exec -t your-db-container pg_dumpall -c -U postgres > dump_`date +%d-%m-%Y"_"%H_%M_%S`.sql

#To save some space on disk you might want to pipe the dump to gzip:  docker exec -t your-db-container
pg_dumpall -c -U postgres | gzip > /var/data/postgres/backups/dump_date +%d-%m-%Y"_"%H_%M_%S.gz

# В контейнер
cat /path/to/filename.tar | docker exec -i <container_name> psql -U <user>

# to restore a compressed sql:
gunzip < your_dump.sql.gz | docker exec -i your-db-container psql -U your-db-user -d your-db-name

# copy dump into container
docker cp local/path/to/db.dump CONTAINER_ID:/db.dump

# shell into container
docker exec -it CONTAINER_ID bash

# restore it from within
pg_restore -U postgres -d DB_NAME --no-owner -1 /db.dump

Создание юзера

1
CREATE ROLE postgres WITH LOGIN PASSWORD 'postgres';

Права на создание БД

1
ALTER ROLE postgres CREATEDB;

Создание БД

1
CREATE DATABASE db_name;

Экспорт таблицы (столбцов) в .csv

1
2
COPY table_name(col1,col2,col3)
TO 'path/file.csv' DELIMITER ',' CSV HEADER;

Экспорт результатов запроса в .csv файл

1
\copy (SELECT * FROM organisation where id in (20,34)) to '/full/path/filename.csv' with csv header

\du – список пользователей

\dp (или \z) – список таблиц, представлений, последовательностей, прав доступа к ним

\di – индексы

\ds – последовательности

\dt – список таблиц

\dt+ — список всех таблиц с описанием

\dt s — список всех таблиц, содержащих s в имени

\dv – представления

\dS – системные таблицы

\d+ – описание таблицы

\o – пересылка результатов запроса в файл

\l – список баз данных

\i – читать входящие данные из файла

\e – открывает текущее содержимое буфера запроса в редакторе (если иное не указано в окружении переменной EDITOR, то будет использоваться по умолчанию vi)

\d “table_name” – описание таблицы

\i запуск команды из внешнего файла, например \i /my/directory/my.sql

\pset – команда настройки параметров форматирования

\echo – выводит сообщение

\set – устанавливает значение переменной среды. Без параметров выводит список текущих переменных (\unset – удаляет)

? – справочник psql

\help – справочник SQL

\q (или Ctrl+D) – выход из программы

\timing - Turn on query execution time

Полезное:

[Backup/Restore a dockerized PostgreSQL database](https://github.com/anuraghazra/github-readme-stats)

Установка и настройка PostgreSQL в Docker

How to install multiple Postgresql versions on macOS?

Running multiple PostgreSQL versions on MacOS