PostgresSQL CLI command cheatsheet

Размещено в категории Заметки и шпаргалки

21.05.2021 04.10.2022 805 слов 4 минут

Замечание

Последний раз данная статья обновлялась 04.10.2022, информация может быть устаревшей.

 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;

Создание БД с параметрами локали и кодировки¹

Кусок из документации про template0

По факту команда CREATE DATABASE выполняет копирование существующей базы данных. По умолчанию копируется стандартная системная база template1. Таким образом, template1 это шаблон, на основе которого создаются новые базы. Если добавить объекты в template1, то впоследствии они будут копироваться в новые базы данных. Это позволяет внести изменения в стандартный набор объектов. Например, если в template1 установить процедурный язык PL/Perl, то он будет доступен в новых базах без дополнительных действий.

Также существует вторая системная база template0. При инициализации она содержит те же самые объекты, что и template1, предопределённые в рамках устанавливаемой версии Postgres Pro. Не нужно вносить никаких изменений в template0 после инициализации кластера. Если в команде CREATE DATABASE указать на необходимость копирования template0 вместо template1, то на выходе можно получить «чистую» пользовательскую базу данных без изменений, внесённых в template1. Это удобно, когда производится восстановление из дампа данных с помощью утилиты pg_dump: скрипт дампа лучше выполнять в чистую базу, во избежание каких-либо конфликтов с объектами, которые могли быть добавлены в template1.

Другая причина, для копирования template0 вместо template1 заключается в том, что можно указать новые параметры локали и кодировку при копировании template0, в то время как для копий template1 они не должны меняться. Это связано с тем, что template1 может содержать данные в специфических кодировках и локалях, в отличие от template0.

1
CREATE DATABASE db_name TEMPLATE template0 ENCODING UTF8 LC_COLLATE "ru_RU.utf8" LC_CTYPE "ru_RU.utf8";

Создание БД

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

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