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 и Как спроектировать схему базы данных.
Попробуем поработать с СУБД через оболочку:
Создадим тестовую базу данных и тестового пользователя:
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 мало чем отличается от работы с любой другой реляционной СУБД:
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 в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение.