Show Menu
Cheatography

изменить root пароль в PostgreSQL

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

Create user PostgreSQL

Метод 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

Help

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

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

список всех таблиц в базе

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

psql команды

$ 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-файл.
Все команды запуск­аются под пользо­вателем postgres (postg­res­ql-­суп­ерп­оль­зов­атель)

Backup

В 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.
 

Check server status

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

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

Mac OS:
ps auxwww | grep postgres

Create database

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 означает что пользо­ватель является владельцем базы данных]

удалить базу в PostgreSQL

# \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

psql команды

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

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

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

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

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

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

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

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

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

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

Recovery

В 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

Run/St­op/­Restart

# 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

список всех баз в Postgresql

# \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

файл истории postgreSQL

$ 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;

Other

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

Очищение таблицы 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

Help Us Go Positive!

We offset our carbon usage with Ecologi. Click the link below to help us!

We offset our carbon footprint via Ecologi
 

Comments

No comments yet. Add yours below!

Add a Comment

Your Comment

Please enter your name.

    Please enter your email address

      Please enter your Comment.