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. Список баз данных можно получить командой \l, а переключиться на конкретную БД — командой \c dbname. Для отображения справки по командам скажите \?.
Важно отметить, что в 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 в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение.
Взято отсюда.
Чтобы установить 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. Список баз данных можно получить командой \l, а переключиться на конкретную БД — командой \c dbname. Для отображения справки по командам скажите \?.
Важно отметить, что в 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 в боевых условиях эти настройки обязательно нужно изменить под ваше железо и ваше приложение.
Взято отсюда.
