Как создать графическую таблицу в консоли postgresql. Основные команды 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/postgresql

3. Проверяем состояние сервера

$ /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 ok

5. Как посмотреть какая версия 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?

8. Как удалить базу в PostgreSQL?

9. Пользуемя встроенным хелпом к командам

Команда \? отобразит строку помощи для команда 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;

В утилите sudo, используемой для организации выполнения команд от имени других пользователей, выявлена уязвимость (CVE-2019-18634), которая позволяет повысить свои привилегии в системе. Проблема […]

Выпуск WordPress 5.3 улучшает и расширяет представленный в WordPress 5.0 редактор блоков новым блоком, более интуитивным взаимодействием и улучшенной доступностью. Новые функции в редакторе […]

После девяти месяцев разработки доступен мультимедиа-пакет FFmpeg 4.2, включающий набор приложений и коллекцию библиотек для операций над различными мультимедиа-форматами (запись, преобразование и […]

Новые функции в Linux Mint 19.2 Cinnamon

Linux Mint 19.2 является выпуском с долгосрочной поддержкой, который будет поддерживаться до 2023 года. Он поставляется с обновленным программным обеспечением и содержит доработки и множество новых […]

Вышел дистрибутив Linux Mint 19.2

Представлен релиз дистрибутива Linux Mint 19.2, второго обновления ветки Linux Mint 19.x, формируемой на пакетной базе Ubuntu 18.04 LTS и поддерживаемой до 2023 года. Дистрибутив полностью совместим […]

Доступны новые сервисные релизы BIND, которые содержат исправления ошибок и улучшения функций. Новые выпуски могут быть скачано со страницы загрузок на сайте разработчика: […]

Exim — агент передачи сообщений (MTA), разработанный в Кембриджском университете для использования в системах Unix, подключенных к Интернету. Он находится в свободном доступе в соответствии с […]

После почти двух лет разработки представлен релиз ZFS on Linux 0.8.0, реализации файловой системы ZFS, оформленной в виде модуля для ядра Linux. Работа модуля проверена с ядрами Linux c 2.6.32 по […]

Комитет IETF (Internet Engineering Task Force), занимающийся развитием протоколов и архитектуры интернета, завершил формирование RFC для протокола ACME (Automatic Certificate Management Environment) […]

Некоммерческий удостоверяющий центр Let’s Encrypt, контролируемый сообществом и предоставляющий сертификаты безвозмездно всем желающим, подвёл итоги прошедшего года и рассказал о планах на 2019 год. […]

Альтернативой работе с базой данных через консоль является более интуитивно понятная среда любого клиента с графическим интерфейсом. Например, pgAdmin . Установить его очень просто, выполняем с правами суперпользователя:

sudo apt-get install pgadmin3

Теперь можно запустить pgAdmin через графический интерфейс. Выбираем в списке локальный сервер БД (у него дефолтный порт 5432) и добавляем уже созданную нами БД с теми параметрами, которые мы задали.

pgAdmin

В это локальном сервере помимо узла Databases можно найти также узел под названием Login Roles - все доступные роли.

Выберем из всех доступных баз данных ту, которую мы создали при . В базе данных содержится множество различных типов объектов. Среди особое внимание следует уделить таблицам и последовательностям (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 .

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";`

Читайте:

Как в Chrome включить или упаковать в CRX-архив расширение не из Chrome Web Store Плавная прокрутка в браузерах Плавная прокрутка в яндекс браузере плагин Английским телефонным будкам подарили вторую жизнь Красные телефонные будки в лондоне на английском Бесплатные файлообменники — как залить фото и получить ссылку на картинку Сервис для хранения изображений Как вырезать человека в фотошопе

Читайте:

Новое

Как востановить менструальный цикл после родов: