pg Cheat Sheet by fggreen

$ */psql postgres postgres
Password: (oldpa­ssword)
# ALTER USER postgres WITH PASSWORD ‘tmppa­ssw­ord’;
$ */psql postgres postgres
Password: (tmppa­ssword)

Метод 1: Создаем пользо­вателя в через PSQL шелл, командой CREATE USER.
# CREATE USER ramesh WITH password ‘tmppa­ssw­ord’;
CREATE ROLE


Метод 2: Создаем пользо­вателя в через шелл команду create­user.
$ */crea­teuser sathiya
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE

Команда \? отобразит строку помощи для команда PSQL. \h CREATE покажет хелп для всех команд который начинаются с CREATE.

# \?
# \h CREATE
# \h CREATE INDEX

# \d
Для пустой базы вы получите сообщение “No relations found.”

$ su - postgres
psql -l - список баз данных.

psql -d dbname - подклю­чение к БД dbname.

psql -f file.sql - выполнение команд из файла file.sql.

psql -U postgres -d dbname -c "­CREATE TABLE test(s­ome_id serial PRIMARY KEY, some_text text);­" - выполнение команды в базе dbname.

psql -d dbname -H -c "­SELECT * FROM test" -o test.html - вывод результата запроса в html-файл.

В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dum­pall. pg_dump исполь­зуется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необх­одимо запускать под postgr­esq­l-с­упе­рпо­льз­ова­телем).

Создание бекапа базы mydb, в сжатом виде

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -f mydb.b­ackup mydb
Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.b­ackup mydb
Создание бекапа базы mydb, в сжатом виде, с таблицами которые содержат в имени payments

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t payments -f paymen­t_t­abl­es.b­ackup mydb
Дамп данных только одной, конкретной таблицы. Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечи­сляются с помощью ключа -t для каждой таблицы.

pg_dump -a -t table_name -f file_name databa­se_name
Создание резервной копии с сжатием в gz

pg_dump -h localhost -O -F p -c -U postgres mydb | gzip -c > mydb.gz
Список наиболее часто исполь­зуемых опций:

-h host - хост, если не указан то исполь­зуется localhost или значение из переменной окружения PGHOST.

-p port - порт, если не указан то исполь­зуется 5432 или значение из переменной окружения PGPORT.

-u - пользо­ватель, если не указан то исполь­зуется текущий пользо­ватель, также значение можно указать в переменной окружения PGUSER.

-a, --data­-only - дамп только данных, по-умо­лчанию сохран­яются данные и схема.

-b - включать в дамп большие объекты (blog'и).

-s, --sche­ma-only - дамп только схемы.

-C, --create - добавляет команду для создания БД.

-c - добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).

-O - не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).

-F, --format {c|t|p} - выходной формат дампа, custom, tar, или plain text.

-t, --tabl­e=TABLE - указываем опреде­ленную таблицу для дампа.

-v, --verbose - вывод подробной информ­ации.

-D, --attr­ibu­te-­inserts - дамп используя команду INSERT с списком имен свойств.

Бекап всех баз данных используя команду pg_dum­pall.

Linux:
$ status
Password:
pg_ctl: server is running (PID: 6171)
[Замеч­ание: Это сообщение говорит о том, что сервер запущен и работаеn нормально]

$ status
Password:
pg_ctl: no server running
[Замеч­ание: Это сообщение говорит о том, что сервер не запущен]

Mac OS:
ps auxwww | grep postgres

Linux
Метод 1: Создаем базу черезе PSQL шелл, с помощью команды CREATE DATABASE.
# CREATE DATABASE mydb WITH OWNER ramesh;
CREATE DATABASE

Метод 2: Используем команду createdb.
$ */createdb mydb -O ramesh
CREATE DATABASE

Mac OS
createdb -Otunn­elsup -Eutf8 mysite­_de­vel­opment

[-O означает что пользо­ватель является владельцем базы данных]

# \l
List of databases
Name | Owner | Encoding
———-+—­——-­+———-
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
# DROP DATABASE mydb;
DROP DATABASE

\c dbname - подсое­динение к БД dbname.

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

\dt - список всех таблиц.

\d table - структура таблицы table.

\du - список всех пользо­вателей и их привил­егий.

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

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

\i FILE - выполнить команды из файла FILE.

\o FILE - сохранить результат запроса в файл FILE.

\a - перекл­ючение между режимами вывода: с/без выравн­ивания.

В PostgreSQL есть две утилиты для восста­нов­ления базы из бекапа.

psql - восста­нов­ление бекапов, которые хранятся в обычном текстовом файле (plain text);
pg_restore - восста­нов­ление сжатых бекапов (tar);
Восста­нов­ление всего бекапа с игнори­ров­анием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql
Восста­нов­ление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser --set ON_ERR­OR_­STOP=on -f mydb.sql
Для восста­нов­ления из tar-ар­ихива нам понадо­биться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восста­новить

pg_restore --dbna­me=mydb --jobs=4 --verbose mydb.b­ackup
Восста­нов­ление резервной копии БД, сжатой gz

gunzip mydb.gz
psql -U postgres -d mydb -f mydb
Начиная с версии 9.2 можно восста­новить только структуру таблиц с помощью опции --section

# создаем БД
CREATE DATABASE mydb2;

# восста­нав­ливаем
pg_restore --dbna­me=­mydb2 --sect­ion­=pr­e-data --jobs=4 mydb.b­ackup

# service postgresql stop
Stopping Postgr­eSQL: server stopped
ok

# service postgresql start
Starting Postgr­eSQL:
ok

# service postgresql restart
Restarting Postgr­eSQL: server stopped
ok

# \l
List of databases
Name | Owner | Encoding
———-+—­——-­+———-
backup | postgres | UTF8
mydb | ramesh | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8

# \timing — после выполения данной команды каждый послед­ующий запрос будет показывать время выполн­ения.

# \timing
Timing is on.
# SELECT * from pg_cat­alo­g.p­g_a­ttr­ibute;
Time: 9.583 ms

$ cat ~/.psq­l_h­istory
alter user postgres with password ‘tmppa­ssw­ord’;
\h alter user
select version();
create user ramesh with password ‘tmppa­ssw­ord’;
\timing
select * from pg_cat­alo­g.p­g_a­ttr­ibute;

Очищение таблицы

Очищение таблицы tablename и обнуление счетчика с ID.

TRUNCATE TABLE tablename RESTART IDENTITY CASCADE;
CASCADE нужен на случай если tablename связана с другой таблицей.

Удаление NULL у поля

ALTER TABLE movies ALTER COLUMN year DROP NOT NULL;
Утилиты

pgcli утилита командной строки с авто-д­опо­лнениям и подсветкой синтак­сиса.

Установка

pip install pgcli
Запуск

pgcli -U postgres -W dbname