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

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

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

sudo -u postgres psql

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

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

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

test_database=> SELECT NEXTVAL('user_ids');
 NEXTVAL 
---------
       1
(1 ROW)

test_database=> SELECT NEXTVAL('user_ids');
 NEXTVAL 
---------
       2
(1 ROW)

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

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

test_database=> CREATE TABLE users2 (id SERIAL PRIMARY KEY, login CHAR(64), password CHAR(64));
NOTICE:  CREATE TABLE will CREATE implicit SEQUENCE "users2_id_seq" FOR serial COLUMN "users2.id"
NOTICE:  CREATE TABLE / PRIMARY KEY will CREATE implicit INDEX "users2_pkey" FOR TABLE "users2"
CREATE TABLE

Как видите, последовательность для поля id была создана автоматически.

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

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

test_database=> CREATE TABLE "anotherTable" ("someValue" VARCHAR(64));
CREATE TABLE

test_database=> SELECT * FROM anotherTable;
ERROR:  relation "anothertable" does NOT exist
LINE 1: SELECT * FROM anotherTable;
                      ^

test_database=> SELECT * FROM "anotherTable";
 someValue
-----------
(0 ROWS)

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

test_database=> INSERT INTO users (login, password) VALUES ('afiskon', '123456');
INSERT 0 1

test_database=> 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 в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение.