Начало работы с PostgreSQL
29 апреля 2013
PostgreSQL — это кроссплатформенная объектно-реляционная СУБД с открытым исходным кодом. Из этой статьи вы узнаете, как установить PostgreSQL в Ubuntu Linux, подключиться к нему и выполнить пару простых SQL-запросов, а также о том, как настроить резервное копирование.
Чтобы установить PostgreSQL 9.2 в Ubuntu 12.10, выполните следующие команды:
sudo apt-get update
sudo apt-get install postgresql-9.2
Дополнение: Для других версий PostgreSQL, версий Ubuntu, а также других дистрибутивов Linux, последовательность шагов аналогичная. Только вместо приведенного PPA я бы все же рекомендовал использовать официальные репозитории пакетов, приведенные на postgresql.org. Также вас могут заинтересовать заметки Потоковая репликация в PostgreSQL и пример фейловера, Некоторые интересные отличия PostgreSQL от MySQL и Как спроектировать схему базы данных.
Попробуем поработать с СУБД через оболочку:
Создадим тестовую базу данных и тестового пользователя:
CREATE USER test_user WITH password 'qwerty';
GRANT ALL ON DATABASE test_database TO test_user;
Для выхода из оболочки введите команду \q.
Теперь попробуем поработать с созданной базой данных от имени test_user:
Создадим новую таблицу:
CREATE TABLE users (
id INTEGER PRIMARY KEY DEFAULT NEXTVAL('user_ids'),
login CHAR(64),
password CHAR(64));
Обратите внимание, что в отличие от некоторых других СУБД, в PostgreSQL нет столбцов со свойством auto_increment. Вместо этого в постгресе используются последовательности (sequences). На данный момент достаточно знать, что с помощью функции nextval мы можем получать уникальные числа для заданной последовательности:
Прописав в качестве значения по умолчанию для поля id таблицы users значение NEXTVAL('user_ids'), мы добились того же эффекта, что дает auto_increment. При добавлении новых записей в таблицу мы можем не указывать id, потому что уникальный id будет сгенерирован автоматически. Несколько таблиц могут использовать одну и ту же последовательность. Таким образом мы сможем гарантировать, что значения некоторых полей у этих таблиц не пересекаются. В этом смысле последовательности более гибки, чем auto_increment.
Точно такую же таблицу можно создать и при помощи всего лишь одной команды:
id SERIAL PRIMARY KEY,
login CHAR(64),
password CHAR(64));
В этом случае последовательность для поля id создается автоматически.
Теперь с помощью команды \d можно ознакомиться со списком всех доступных таблиц, а с помощью \d users — увидеть описание таблицы users. Если вы не получили интересующую вас информацию, попробуйте \d+ вместо \d. Список баз данных можно получить командой \l, а переключиться на конкретную БД — командой \c dbname. Для отображения справки по командам скажите \?.
Важно отметить, что в PostgreSQL по умолчанию имена таблиц и столбцов приводятся к нижнему регистру. Если это поведение нежелательно, можно воспользоваться двойными кавычками:
Еще одна особенность PostgreSQL, с которой могут возникнуть сложности в начале работы с этой СУБД — так называемые «схемы». Схема представляет собой что-то вроде пространства имен для таблиц, как бы каталог с таблицами внутри базы данных.
Создание схемы:
Переключение на схему:
Просмотреть список существующих схем можно командой \dn. По умолчанию используется схема с именем public. В принципе, можно успешно использовать PostgreSQL, и не зная про существование схем. Но при работе с унаследованным кодом, а также в некоторых граничных случаях, знание о схемах может очень пригодиться.
В остальном работа с PostgreSQL мало чем отличается от работы с любой другой реляционной СУБД:
VALUES ('afiskon', '123456');
SELECT * FROM users;
Если сейчас вы попытаетесь подключиться к постгресу с другой машины, то потерпите неудачу:
psql: could not connect to server: Connection refused
Is the server running on host "192.168.0.1" and accepting
TCP/IP connections on port 5432?
Чтобы исправить это, добавьте строку:
… в файл /etc/postgresql/9.2/main/postgresql.conf, а также:
… в файл /etc/postgresql/9.2/main/pg_hba.conf и скажите:
Теперь все должно работать.
Резервное копирование в PostgreSQL выглядит примерно так:
Если у вас большая база данных, обратите также внимание на поддержку утилитой pg_dump флага -Fc.
Восстановление из резервной копии:
Во время создания резервной копии вы можете получить ошибку вроде такой:
pg_dump: aborting because of server version mismatch
Насколько мне известно, единственное нормальное решение этой проблемы — честно держать всюду одну и ту же версию PostgreSQL.
Учтите, что настройки PostgreSQL по умолчанию предполагают, что вы пытаетесь запустить его на микроволновке. Перед использованием PostgreSQL в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение. Для быстрой настройки можно воспользоваться онлайн-приложениями Cybertec PostgreSQL Configurator или PgTune. Также есть консольная утилита pgtune. Генерируемый этими решениями конфиг будет далек от оптимального для конкретного приложения, но существенно лучше дэфолтного. Также заметьте, что во многих реальных инсталляциях используется PgBouncer.
Дополнение: Вас также может заинтересовать пост Внутренности PostgreSQL: XID wraparound и далее по ссылкам.
Метки: Linux, PostgreSQL, СУБД.
Вы можете прислать свой комментарий мне на почту, или воспользоваться комментариями в Telegram-группе.