Начало работы с PostgreSQL

29 апреля 2013

PostgreSQL — это кроссплатформенная объектно-реляционная СУБД с открытым исходным кодом. Из этой статьи вы узнаете, как установить PostgreSQL в Ubuntu Linux, подключиться к нему и выполнить пару простых SQL-запросов, а также о том, как настроить резервное копирование.

Чтобы установить PostgreSQL 9.2 в Ubuntu 12.10, выполните следующие команды:

sudo apt-add-repository ppa:pitti/postgresql
sudo apt-get update
sudo apt-get install postgresql-9.2

Дополнение: Для других версий PostgreSQL, версий Ubuntu, а также других дистрибутивов Linux, последовательность шагов аналогичная. Только вместо приведенного PPA я бы все же рекомендовал использовать официальные репозитории пакетов, приведенные на postgresql.org. Также вас могут заинтересовать заметки Потоковая репликация в PostgreSQL и пример фейловера, Некоторые интересные отличия PostgreSQL от MySQL и Как спроектировать схему базы данных.

Попробуем поработать с СУБД через оболочку:

sudo -u postgres psql

Создадим тестовую базу данных и тестового пользователя:

CREATE DATABASE test_database;
CREATE USER test_user WITH password 'qwerty';
GRANT ALL ON DATABASE test_database TO test_user;

Для выхода из оболочки введите команду \q.

Теперь попробуем поработать с созданной базой данных от имени test_user:

psql -h localhost test_database test_user

Создадим новую таблицу:

CREATE SEQUENCE user_ids;
CREATE TABLE users (
  id INTEGER PRIMARY KEY DEFAULT NEXTVAL('user_ids'),
  login CHAR(64),
  password CHAR(64));

Обратите внимание, что в отличие от некоторых других СУБД, в PostgreSQL нет столбцов со свойством auto_increment. Вместо этого в постгресе используются последовательности (sequences). На данный момент достаточно знать, что с помощью функции nextval мы можем получать уникальные числа для заданной последовательности:

SELECT NEXTVAL('user_ids');

Прописав в качестве значения по умолчанию для поля id таблицы users значение NEXTVAL('user_ids'), мы добились того же эффекта, что дает auto_increment. При добавлении новых записей в таблицу мы можем не указывать id, потому что уникальный id будет сгенерирован автоматически. Несколько таблиц могут использовать одну и ту же последовательность. Таким образом мы сможем гарантировать, что значения некоторых полей у этих таблиц не пересекаются. В этом смысле последовательности более гибки, чем auto_increment.

Точно такую же таблицу можно создать и при помощи всего лишь одной команды:

CREATE TABLE users2 (
  id SERIAL PRIMARY KEY,
  login CHAR(64),
  password CHAR(64));

В этом случае последовательность для поля id создается автоматически.

Теперь с помощью команды \d можно ознакомиться со списком всех доступных таблиц, а с помощью \d users — увидеть описание таблицы users. Если вы не получили интересующую вас информацию, попробуйте \d+ вместо \d. Список баз данных можно получить командой \l, а переключиться на конкретную БД — командой \c dbname. Для отображения справки по командам скажите \?.

Важно отметить, что в PostgreSQL по умолчанию имена таблиц и столбцов приводятся к нижнему регистру. Если это поведение нежелательно, можно воспользоваться двойными кавычками:

CREATE TABLE "anotherTable" ("someValue" VARCHAR(64));

Еще одна особенность PostgreSQL, с которой могут возникнуть сложности в начале работы с этой СУБД — так называемые «схемы». Схема представляет собой что-то вроде пространства имен для таблиц, как бы каталог с таблицами внутри базы данных.

Создание схемы:

CREATE SCHEMA bookings;

Переключение на схему:

SET search_path TO bookings;

Просмотреть список существующих схем можно командой \dn. По умолчанию используется схема с именем public. В принципе, можно успешно использовать PostgreSQL, и не зная про существование схем. Но при работе с унаследованным кодом, а также в некоторых граничных случаях, знание о схемах может очень пригодиться.

В остальном работа с PostgreSQL мало чем отличается от работы с любой другой реляционной СУБД:

INSERT INTO users (login, password)
  VALUES ('afiskon', '123456');
SELECT * FROM users;

Если сейчас вы попытаетесь подключиться к постгресу с другой машины, то потерпите неудачу:

psql -h 192.168.0.1 test_database test_user

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?

Чтобы исправить это, добавьте строку:

listen_addresses = 'localhost,192.168.0.1'

… в файл /etc/postgresql/9.2/main/postgresql.conf, а также:

host    all    all    192.168.0.1/16    md5

… в файл /etc/postgresql/9.2/main/pg_hba.conf и скажите:

sudo service postgresql restart

Теперь все должно работать.

Резервное копирование в PostgreSQL выглядит примерно так:

pg_dump -c -h 192.168.0.1 -U test_user test_database > ./dump.sql

Если у вас большая база данных, обратите также внимание на поддержку утилитой pg_dump флага -Fc.

Восстановление из резервной копии:

cat dump.sql | psql -h 192.168.0.1 test_database test_user

Во время создания резервной копии вы можете получить ошибку вроде такой:

pg_dump: server version: 9.2.4; pg_dump version: 9.1.9
pg_dump: aborting because of server version mismatch

Насколько мне известно, единственное нормальное решение этой проблемы — честно держать всюду одну и ту же версию PostgreSQL.

Учтите, что настройки PostgreSQL по умолчанию предполагают, что вы пытаетесь запустить его на микроволновке. Перед использованием PostgreSQL в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение. Для быстрой настройки можно воспользоваться онлайн-приложениями Cybertec PostgreSQL Configurator или PgTune. Также есть консольная утилита pgtune. Генерируемый этими решениями конфиг будет далек от оптимального для конкретного приложения, но существенно лучше дэфолтного. Также заметьте, что во многих реальных инсталляциях используется PgBouncer.

Метки: , , .

Понравился пост? Узнайте, как можно поддержать развитие этого блога.

Также подпишитесь на RSS, Facebook, ВКонтакте, Twitter или Telegram.