Теперь попробуем поработать с созданной базой данных от имени test_user:
Psql -h localhost test_database test_user
Создадим новую таблицу:
Test_database=> CREATE SEQUENCE user_ids; CREATE SEQUENCE test_database=> CREATE TABLE users (id INTEGER PRIMARY KEY DEFAULT NEXTVAL("user_ids"), login CHAR(64), password CHAR(64)); NOTICE: CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "users_pkey" FOR TABLE "users" CREATE TABLE
— повторить предыдущие команды для каждой базы в кластере
DROP ROLE doomed_role;
Бекап и восстановление таблиц
В PostgreSQL есть две утилиты для бекапа pg_dump и pg_dumpall . pg_dump используется для бекапа одной базы, pg_dumpall для бекапа всех баз и сервера в целом (необходимо запускать под postgresql-суперпользователем).
Создание бекапа базы mydb , в сжатом виде
Pg_dump -h localhost -p 5432 -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 {c|t|p} — выходной формат дампа, custom, tar, или plain text .
T, --table=TABLE — указываем определенную таблицу для дампа.
V, --verbose — вывод подробной информации.
D, --attribute-inserts — дамп используя команду INSERT с списком имен свойств.
Бекап всех баз данных используя команду pg_dumpall .
Pg_dumpall > all.sql # проверка бекапа grep "^[\]connect" all.sql \connect db1 \connect db2
В PostgreSQL есть две утилиты для восстановления базы из бекапа.
Восстановление базы данных и
#pg_restore -v -e -d dbname dbname.dump
Восстановление всего бекапа с игнорированием ошибок
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
Начиная с версии 9.2 можно восстановить только структуру таблиц с помощью опции --section
# создаем БД CREATE DATABASE mydb2; # восстанавливаем pg_restore --dbname=mydb2 --section=pre-data --jobs=4 mydb.backupОбслуживание таблицы
VACUUM ANALYZE table; REINDEX DATABASE dbName; REINDEX TABLE tabName;Перенос директории с данным (data directory)
Узнать текущий путь
# способ 1 $ su - postgres $ psql psql > SHOW data_directory; # способ 2 $ ps ax | grep "postgres -D"Создадим новую директорию, назначим пользователя и инициализируем
mkdir -p /pathto/postgresql/data chown -R postgres:postgres /pathto/postgresql su - postgres initdb -D /pathto/postgresql/dataТеперь надо подправить файл с сервисом, который стартует postgresql
# под arch linux sudo vim /etc/systemd/system/multi-user.target.wants/postgresql.service Environment =PGROOT=/pathto/postgresql/ PIDFile =/pathto/postgresql/data/postmaster.pidОчищение таблицы
Очищение таблицы tablename и обнуление счетчика с ID.
TRUNCATE TABLE tablename RESTART IDENTITY CASCADE ;CASCADE нужен на случай если tablename связана с другой таблицей.
Удаление NULL у поля
ALTER TABLE movies ALTER COLUMN year DROP NOT NULL ;Альтернативой работе с базой данных через консоль является более интуитивно понятная среда любого клиента с графическим интерфейсом. Например, pgAdmin
. Установить его очень просто, выполняем с правами суперпользователя:
sudo apt-get install pgadmin3Теперь можно запустить pgAdmin через графический интерфейс. Выбираем в списке локальный сервер БД (у него дефолтный порт 5432) и добавляем уже созданную нами БД с теми параметрами, которые мы задали.
pgAdmin |
Выберем из всех доступных баз данных ту, которую мы создали при . В базе данных содержится множество различных типов объектов. Среди особое внимание следует уделить таблицам и последовательностям (sequences ).
Чаще всего обычная таблица содержит в себе первичный ключ в виде положительного числового значения. Для каждой строки это значение должно быть уникальным, поэтому чтобы каждый раз не задавать его напрямую в запросе, можно установить в качестве дефолтного значения последовательность.
Для начала создадим последовательность. Выбираем Sequences - New Sequence . На первой вкладке вводим имя и указываем в качестве Owner ту роль, которую мы создали. Для других ролей эта последовательность будет недоступна. На второй вкладке в поля Increment и Start вводим по единице (если вам не требуется другое). На последней вкладке диалогового окна можно посмотреть результирующий SQL запрос, который будет выполнен к БД.
После того, как последовательность создана, приступим к созданию таблицы. Также указываем её имя и владельца (owner). На четвёртой вкладке Columns добавляем прежде всего первичный ключ. Кнопка Add , в появившемся окне указываем название столбца, например, id . В качестве типа данных выбираем bigint . На второй вкладке Definition в поле Default Value указываем нашу последовательность. В поле должно быть значение вида nextval("message_id_seq"::regclass) . То есть каждый раз при добавлении новой строки из последовательности будет браться следующее значение. Добавили другие столбцы по необходимости. Наконец, на вкладке Constraints добавим ограничение на первичный ключ (Primary Key ). На последней вкладке можем полюбоваться на результирующий SQL код, который сгенерировал pgAdmin. После нажатия ОК таблица будет создана.
Вместо bigint в качестве первичного ключа можно указать в качестве типа столбца bigserial . Этот тип инкрементируется автоматически при добавлении каждой новой строки, поэтому создавать последовательность для него не нужно. То есть в самом простом случае, если у вас нет каких-либо особых правил для формирования id записи, можно порекомендовать использовать bigserial.
Давайте посмотрим содержимое нашей таблицы. Для этого правой кнопкой мыши нажмём на неё и выберем в контекстном меню View Data - View 100 rows .
В этом же окне можно быстро отредактировать значение любой ячейки таблицы. Если в вашей таблице больше 100 записей, измените количество отображаемых записей в выпадающем списке в верху окна. Вы можете отобразить 100, 500, 1000 или все записи. Но это не более чем способ быстро посмотреть содержимое таблицы и привыкать к нему не стоит. Если у вас в таблице десятки тысяч записей, отображать все записи сразу я бы не рекомендовал - в таком случае лучше писать запрос с использованием параметров limit и offset .
В этой статье я покажу 15 наиболее полезных команд для управления postgreSQL .
Изменение пароля для обычного пользователя происходит таким же образом. Пользователь root может поменять пароль любому пользователю.
# ALTER USER username WITH PASSWORD ‘tmppassword’;
Для этого существуют два метода..
Метод 1: Создаем пользователя в через PSQL шелл, командой CREATE USER.
# CREATE USER ramesh WITH password ‘tmppassword’; CREATE ROLE
Метод 2: Создаем пользователя в через шелл команду createuser.
$ /usr/local/pgsql/bin/createuser 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
Для этого существует 2 метода.
Метод 1: Создаем базу черезе PSQL шелл, с помощью команды CREATE DATABASE.
# CREATE DATABASE mydb WITH OWNER ramesh; CREATE DATABASE
Метод 2: Используем команду createdb.
$ /usr/local/pgsql/bin/createdb mydb -O ramesh CREATE DATABASE
Команда \? отобразит строку помощи для команда PSQL. \h CREATE покажет хелп для всех команд который начинаются с CREATE.
# \? # \h CREATE # \h CREATE INDEX
Для пустой базы вы получите сообщение “No relations found.”
# \timing — после выполения данной команды каждый последующий запрос будет показывать время выполнения.
# \timing Timing is on. # SELECT * from pg_catalog.pg_attribute ; Time: 9.583 ms
Этот вопрос довольно велик и я опубликую его позднее отдельной статьей.
Для того чтобы получить список доступных функций, скажите \df+
# \df # \df+
\e откроет редактор, в котором вы можете отредактировать запрос и сохранить его.
Подобно файлу ~/.bash_history, postgreSQL хранит все sql команды в файле ~/.psql_history.
$ cat ~/.psql_history alter user postgres with password ‘tmppassword’; \h alter user select version(); create user ramesh with password ‘tmppassword’; \timing select * from pg_catalog.pg_attribute;
Все привет сегодня хочу сделать небольшую памятку про основные команды PostgreSQL . Работать с PosgreSQL можно как в интерактивном режиме, так и из командной строки. Программа – psql. Я уверен, что данный список окажется очень вам полезен и сэкономит время на поиск по разным ресурсам. Напоминаю, что это open-source проект, на основе СУБД Postgres, вышла в 1986 году, она разрабатывается всемирной группой разработчиков PGDG, по суте это 5-8 человек, но несмотря на это она, очень интенсивно развивается, вводя все новые функции и испраляя старые баги и ошибки.
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-файл.
Создание бекапа базы 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
Бекап всех баз данных используя команду pg_dumpall.
Pg_dumpall > all.sql
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
psql -U postgres -d mydb -f mydb
Я думаю база данных postgresql теперь будет более понятна вам. Надеюсь данный список команд PostgreSQLбыл для вас полезным.
Последнее обновление: 17.03.2018
Для создания таблиц применяется команда CREATE TABLE , после которой указывается название таблицы. Также с этой командой можно использовать ряд операторов, которые определяют столбцы таблицы и их атрибуты. Общий синтаксис создания таблицы выглядит следующим образом:
CREATE TABLE название_таблицы (название_столбца1 тип_данных атрибуты_столбца1, название_столбца2 тип_данных атрибуты_столбца2, ................................................ название_столбцаN тип_данных атрибуты_столбцаN, атрибуты_таблицы);
После названия таблицы в скобках перечисляется спецификация для всех столбцов. Причем для каждого столбца надо указывается название и тип данных, который он будет представлять. Тип данных определяет, какие данные (числа, строки и т.д.) может содержать столбец.
Например, создадим таблицу в базе данных через pgAdmin. Для этого вначале выберем в pgAdmin целевую базу данных, нажмем на нее правой кнопкой мыши и в контекстном меню выберем пункт Query Tool... :
После этого откроется поле для ввода кода на SQL. Причем таблица будет создаваться именно для той базы данных, для которой мы откровыем это поле для ввода SQL.
CREATE TABLE customers (Id SERIAL PRIMARY KEY, FirstName CHARACTER VARYING(30), LastName CHARACTER VARYING(30), Email CHARACTER VARYING(30), Age INTEGER);
В данном случае в таблице Customers определяются пять столбцов: Id, FirstName, LastName, Age, Email. Первый столбец - Id представляет идентификатор клиента, он служит первичным ключом и поэтому имеет тип SERIAL . Фактически данный столбец будет хранить числовое значение 1, 2, 3 и т.д., которое для каждой новой строки будет автоматически увеличиваться на единицу.
Следующие три столбца представляют имя, фамилию клиента и его электронный адрес и имеют тип CHARACTER VARYING(30) , то есть представляют строку длиной не более 30 символов.
Последний столбец - Age представляет возраст пользователя и имеет тип INTEGER , то есть хранит числа.
И после выполнения этой команды в выбранную базу данных будет добавлена таблица customers.
Для удаления таблиц используется команда DROP TABLE , которая имеет следующий синтаксис:
DROP TABLE table1 [, table2, ...];
Например, удаление таблицы customers.