Последнее обновление: 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.
postgres=# CREATE DATABASE test_database; CREATE DATABASE postgres=# CREATE USER test_user WITH password "qwerty"; CREATE ROLE postgres=# GRANT ALL privileges ON DATABASE test_database TO test_user; GRANTДля выхода из оболочки введите команду \q.
Теперь попробуем поработать с созданной базой данных от имени 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
Удаление пользователя
Чтобы удалить пользователя необходимо передать его права другому, а затем только удалить
REASSIGN OWNED BY doomed_role TO successor_role; DROP OWNED BY doomed_role;— повторить предыдущие команды для каждой базы в кластере
DROP ROLE doomed_role;
Скрипт по табличного бэкапа postgres.
#!/bin/bash DBNAMES="web"; USER="postgres"; DB_NAME="web"; NEW_OWNER="user_remote"; DATE_Y=`/bin/date "+%y"` DATE_M=`/bin/date "+%m"` DATE_D=`/bin/date "+%d"` SERVICE="pgdump" BACKUP_DIR="/var/backup_db/20${DATE_Y}/${DATE_M}/${DATE_D}" mkdir -p $BACKUP_DIR; for tbl in `psql -qAt -c "select tablename from pg_tables where schemaname = "public";" ${DB_NAME}` \ `psql -qAt -c "select sequence_name from information_schema.sequences where sequence_schema = "public";" ${DB_NAME}` \ `psql -qAt -c "select table_name from information_schema.views where table_schema = "public";" ${DB_NAME}` ; do echo "Exporting table $tbl from db ${DB_NAME} to file tables3/$tbl.backup" #pg_dump --format p --verbose --table public.$tbl ${DB_NAME} > $BACKUP_DIR/$tbl pg_dump --format p --verbose --table public.$tbl ${DB_NAME} | gzip > $BACKUP_DIR/$tbl #pg_dump -a -d -t public.$tbl ${DB_NAME} > tables3/$tbl.sql done ##################БЭКАП ФУНКЦИЙ POSTGRES # Делаем dump базы без даты, для того что дальше извлечь их нее функции pg_dump -Fc -s -f $BACKUP_DIR/db_dump ${DB_NAME} /bin/sleep 4; # Создаем список функция pg_restore -l $BACKUP_DIR/db_dump | grep FUNCTION > $BACKUP_DIR/function_list ##Как восстановить функции ######################### #pg_restore -h localhost -U username -d имя_базы -L function_list db_dump ########################Скрипт по табличного бэкапа postgres. написан на perl
Исполнять из под user’a — postgres. Если в кронах — то также из под юзера postgresql.
#!/usr/bin/env perl use strict; use warnings; my $database_name = "book_library"; my $query = <<"EOT"; SELECT n.nspname as table_schema, c.relname as table_name FROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind IN ("r","") AND n.nspname NOT IN ("pg_catalog", "information_schema") AND n.nspname NOT LIKE "^pg_%" ; EOT $query =~ s/\n\s*/ /g; my @results = `echo "$query" | psql -At $database_name`; foreach (@results) { chomp; my ($schema, $table) = split /\|/, $_; next unless ($schema && $table); my $cmd = "pg_dump -U postgres -Fp -t $schema.$table -f $schema.$table.dump $database_name"; system($cmd); } #If you wanted to restore only a single function: ##pg_restore -U $username --dbname=$dbname --function=$functionname(args) #If you wanted to restore only a single table: ##pg_restore -U $username --dbname=$dbname --table=$tablenameСловари полнотекстового поиска в postgresql
Бэкап базы с удаленной машины
PGPASSWORD="PASSWORD" pg_dump -h $HOSTNAME -U databaseuser -Fc --verbose "database.itc-life.ru" | gzip > databasename.gzБекап и восстановление таблиц
В 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 есть две утилиты для восстановления базы из бекапа.
- psql — восстановление бекапов, которые хранятся в обычном текстовом файле (plain text);
- pg_restore — восстановление сжатых бекапов (tar);
Восстановление базы данных и
#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 ;Запуск pgbouncer
su -s /bin/sh - postgres -c "/usr/sbin/pgbouncer -d --verbose /etc/pgbouncer/pgbouncer.ini"Отсоединить пользователей от базы данных
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname = "mydb";`В этой статье я покажу 15 наиболее полезных команд для управления postgreSQL .
1. Как изменить root пароль в PostgreSQL?
$ /usr/local/pgsql/bin/psql postgres postgres Password: (oldpassword) # ALTER USER postgres WITH PASSWORD ‘tmppassword’; $ /usr/local/pgsql/bin/psql postgres postgres Password: (tmppassword)Изменение пароля для обычного пользователя происходит таким же образом. Пользователь root может поменять пароль любому пользователю.
# ALTER USER username WITH PASSWORD ‘tmppassword’;
2. Как установить PostgreSQL в автозапуск?
$ su - root # tar xvfz postgresql-8.3.7.tar.gz # cd postgresql-8.3.7 # cp contrib/start-scripts/linux /etc/rc.d/init.d/postgresql # chmod a+x /etc/rc.d/init.d/postgresql3. Проверяем состояние сервера
$ /etc/init.d/postgresql status Password: pg_ctl: server is running (PID: 6171) /usr/local/pgsql/bin/postgres “-D” “/usr/local/pgsql/data” [Замечание: Это сообщение говорит о том, что сервер запущен и работате нормально] $ /etc/init.d/postgresql status Password: pg_ctl: no server running [Замечание: Это сообщение готоворит о том, что сервер не запущен]4. Как запустить, остановить, перезапустить PostgreSQL?
# service postgresql stop Stopping PostgreSQL: server stopped ok # service postgresql start Starting PostgreSQL: ok # service postgresql restart Restarting PostgreSQL: server stopped ok5. Как посмотреть какая версия PostgreSQL запущена?
$ /usr/local/pgsql/bin/psql test Welcome to psql 8.3.7, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit test=# select version(); version —————————————————————————————————- PostgreSQL 8.3.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-42) (1 row) test=#5. Как создать пользователя в PostgreSQL?
Для этого существуют два метода..
Метод 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
6. Как создать базу в PostgreSQL ?
Для этого существует 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
7. Получаем список всех баз в Postgresql?
# \l List of databases Name | Owner | Encoding ———-+———-+———- backup | postgres | UTF8 mydb | ramesh | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF88. Как удалить базу в 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 DATABASE9. Пользуемя встроенным хелпом к командам
Команда \? отобразит строку помощи для команда PSQL. \h CREATE покажет хелп для всех команд который начинаются с CREATE.
# \? # \h CREATE # \h CREATE INDEX
10. Как получить список всех таблиц в базе данный в Postgresql?
# \dДля пустой базы вы получите сообщение “No relations found.”
11. Как узнать время выполнения запроса?
# \timing — после выполения данной команды каждый последующий запрос будет показывать время выполнения.
# \timing Timing is on. # SELECT * from pg_catalog.pg_attribute ; Time: 9.583 ms
12. Как бэкапить и восстанавливать базы и таблицы в PostgreSQL?
Этот вопрос довольно велик и я опубликую его позднее отдельной статьей.
13. Как посмотреть список доступных функций в PostgreSQL ?
Для того чтобы получить список доступных функций, скажите \df+
# \df # \df+
14. Как отредактировать запрос к PostgreSQL в редакторе?
# \e\e откроет редактор, в котором вы можете отредактировать запрос и сохранить его.
15. Где я могу найти файл истории postgreSQL?
Подобно файлу ~/.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;
Операционные системы