Postgresql основные команды linux

📑 Шпаргалка по основным командам PostgreSQL

Вся работа с PostgreSQL осуществляется под пользователем postgres.

Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа — psql.

Основные команды PostgreSQL в интерактивном режиме:

• \connect db_name – подключиться к базе с именем db_name
• \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) – выход с программы

Работа с PostgreSQL из командной строки:

• -c (или –command) – запуск команды SQL без выхода в интерактивный режим
• -f file.sql — выполнение команд из файла file.sql
• -l (или –list) – выводит список доступных баз данных
• -U (или –username) – указываем имя пользователя (например postgres)
• -W (или –password) – приглашение на ввод пароля
• -d dbname — подключение к БД dbname
• -h – имя хоста (сервера)
• -s – пошаговый режим, то есть, нужно будет подтверждать все команды
• –S – однострочный режим, то есть, переход на новую строку будет выполнять запрос (избавляет от ; в конце конструкции SQL)
• -V – версия PostgreSQL без входа в интерактивный режим

Примеры:

psql -U postgres -d dbname -c «CREATE TABLE my(some_id serial PRIMARY KEY, some_text text);» — выполнение команды в базе dbname.

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

Утилиты (программы) PosgreSQL:

• createdb и dropdb – создание и удаление базы данных (соответственно)
• createuser и dropuser – создание и пользователя (соответственно)
• pg_ctl – программа предназначенная для решения общих задач управления (запуск, останов, настройка параметров и т.д.)
• postmaster – многопользовательский серверный модуль PostgreSQL (настройка уровней отладки, портов, каталогов данных)
• initdb – создание новых кластеров PostgreSQL
• initlocation – программа для создания каталогов для вторичного хранения баз данных
• vacuumdb – физическое и аналитическое сопровождение БД
• pg_dump – архивация и восстановление данных
• pg_dumpall – резервное копирование всего кластера PostgreSQL
• pg_restore – восстановление БД из архивов (.tar, .tar.gz)

Примеры создания резервных копий:

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

pg_dump -h localhost -p 5440 -U someuser -F c -b -v -f mydb.backup mydb

Создание бекапа базы mydb, в виде обычного текстового файла, включая команду для создания БД

pg_dump -h localhost -p 5432 -U someuser -C -F p -b -v -f mydb.backup mydb

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

pg_dump -h localhost -p 5432 -U someuser -F c -b -v -t *payments* -f payment_tables.backup mydb

Дамп данных только одной, конкретной таблицы. Если нужно создать резервную копию нескольких таблиц, то имена этих таблиц перечисляются с помощью ключа -t для каждой таблицы.

pg_dump -a -t table_name -f file_name database_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, —schema-only — дамп только схемы.
• -C, —create — добавляет команду для создания БД.
• -c — добавляет команды для удаления (drop) объектов (таблиц, видов и т.д.).
• -O — не добавлять команды для установки владельца объекта (таблиц, видов и т.д.).
• -F, —format — выходной формат дампа, custom, tar, или plain text.
• -t, —table=TABLE — указываем определенную таблицу для дампа.
• -v, —verbose — вывод подробной информации.
• -D, —attribute-inserts — дамп используя команду INSERT с списком имен свойств.

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

Восстановление таблиц из резервных копий (бэкапов):

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

Восстановление всего бекапа с игнорированием ошибок

psql -h localhost -U someuser -d dbname -f mydb.sql

Восстановление всего бекапа с остановкой на первой ошибке

psql -h localhost -U someuser —set ON_ERROR_STOP=on -f mydb.sql

Для восстановления из tar-арихива нам понадобиться сначала создать базу с помощью CREATE DATABASE mydb; (если при создании бекапа не была указана опция -C) и восстановить

pg_restore —dbname=mydb —jobs=4 —verbose mydb.backup

Восстановление резервной копии БД, сжатой gz

gunzip mydb.gz psql -U postgres -d mydb -f mydb

Источник

Шпаргалка: основные команды консоли PostgreSQL

Эта статья – моя шпаргалка по наиболее часто используемым командам в консоли PostgreSQL.

При установке PostgreSQL создает отдельного пользователя Linux с именем postgres . В моих примерах я буду запускать команды от имени этого пользователя. Хотя вы можете авторизоваться под ним и запускать команды непосредственно из сессии пользователя postgres.

Вывести список баз с расширенной информацией:

# sudo -u postgres psql -U postgres -l+

Создать текстовый дамп базы данных:

# sudo -u postgres pg_dump -U postgres dbtemp01 \
> ~/ dbtemp01.sql

Сжать дамп базы данных PostgreSQL на лету:

# sudo -u postgres pg_dump -U postgres dbtemp01 \
| pigz > ~/dbtemp01.sql.gz

Восстановить базу данных из дампа в новую БД:

# sudo -u postgres createdb -U postgres \
-T template0 dbtemp02

# sudo -u postgres psql -U postgres dbtemp02 \
< ~/ dbtemp01.sql

Создать нового пользователя PostgreSQL:

# sudo -u postgres createuser -U postgres usrsite1

Задать пароль пользователя

# sudo -u postgres psql -U postgres -c \
«ALTER USER usrsite1 PASSWORD ‘SecRet2pwd'»

Вывести список пользователей:

# sudo -u postgres psql -U postgres -c \
«select * from pg_user»

Дать полные права на базу:

# sudo -u postgres psql -U postgres -c \
«GRANT ALL PRIVILEGES ON DATABASE sitedb to usrsite1»

Назначить пользователя владельцем базы:

# sudo -u postgres psql -U postgres -c \
«ALTER DATABASE sitedb OWNER TO usrsite1»

Выполнить очистку ( -f ) и анализ ( -z ) базы данных (Postgres Pro):

# sudo -u postgres vacuumdb -U postgres -f -z -d sitedb

# sudo -u postgres reindexdb -U postgres -d sitedb

# sudo -u postgres psql -U postgres -c \
«DROP DATABASE sitedb»

Вывести права пользователя:

select * from INFORMATION_SCHEMA.table_privileges WHERE grantee = »;

Права на группы, членом которых является пользователь USER_NAME:

select * from INFORMATION_SCHEMA.role_table_grants WHERE grantee = »;

select relacl from pg_catalog.pg_class where relname=»;

Еще несколько популярных команд для интерактивного режима psql:

\connect db_name – подключиться к базе данных

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

\dp — вывести список таблиц, представлений, последовательностей, прав доступа к ним

Источник

PostgreSQL Cheat Sheet for Ubuntu Linux

This is a work-in-progress collection of commands, queries and information on working with PostgreSQL databases on Ubuntu Linux. The commands and queries were tested on Ubuntu 15.10 with PostgreSQL 9.4 installed.

In the example commands on this page placeholder names, such as database names, that need to be replaced with actual values are written in all caps and enclosed in underscores. Names of tools that are used to perform different tasks are in italic type.

Terminal commands

The commands in this section are executed in a terminal window such as xterm or Gnome terminal.

Create a database

sudo -u postgres createdb _DBNAME_ 

Delete a database

sudo -u postgres dropdb _DBNAME_ 

Database backup

Backing up a complete database.

sudo -u postgres pg_dump _DBNAME_ > _DBNAME_.sql 

Backing up a single table of a database.

sudo -u postgres pg_dump -t _TABLENAME_ _DBNAME_ > _DBNAME_TABLENAME_.sql 

Show information about all clusters

The command below shows meta and status information about all PostgreSQL clusters, including the Postgres version, owners, online/offline status and the directories, where the data and log files are stored.

Database commands and queries

Except for the login step, the commands in this section are executed in the PostgreSQL interactive terminal psql.

Log in to a database

sudo -u postgres psql _DBNAME_ 

Show database tables

Describe a single table

Show size of a single database

SELECT pg_database_size('_DBNAME_'); 

Show all databases

This following command shows a list of all databases stored on your system, including names, owners, encodings and their sizes.

Show all tables including PostgreSQL system tables

SELECT * FROM pg_catalog.pg_tables; 

List all user defined data types

Show all items of type enum

Add a new option to an enum type

ALTER TYPE _MYTYPE_ ADD VALUE '_NEW_OPTION_' AFTER '_EXISTING_OPTION_'; 

Close all database connections

The query below closes all database connections, except your current psql session. This is useful to get rid of stale connections and when you need to perform changes, such as adding, dropping or modifying columns, tables or the whole database.

SELECT pg_terminate_backend(pg_stat_activity.pid) FROM pg_stat_activity WHERE pg_stat_activity.datname = _DBNAME_ AND pid <> pg_backend_pid(); 

Optimize tables

The following commands run in succession can be used to optimize a table and potentially reduce its size significantly.

VACUUM FULL; REINDEX TABLE _TABLENAME_; 

Published: April 28, 2016 by Ramiro Gómez.
If you want to be notified about new content, click here to subscribe to the newsletter or RSS feed.

Disclosure: External links on this page may contain affiliate IDs, which means that I earn a commission if you make a purchase via such a link. This allows me to operate this site and offer hopefully valuable content that is freely accessible. More information about affiliate programs.

Источник