Gentoo и СУБД

Руководство по MySQL для начинающих

Ссылка на оригинал: http://www.gentoo.org/doc/ru/mysql-howto.xml

C версии: 1.5

1. Начало работы с MySQL

Введение

MySQL — это популярный сервер баз данных, используемый в различных областях. Аббревиатура SQL означает Structured Query Language (язык структурированных запросов), именно его использует MySQL для общения с другими программами. Более того, в MySQL есть собственные расширенные функции SQL, дающие дополнительные возможности пользователям. В этом руководстве рассматривается процесс начальной установки MySQL, настройка баз данных и таблиц, заведение новых пользователей. Итак, начнем с установки.

Установка MySQL

Сначала нужно установить MySQL. Если вам нужны определенные возможности MySQL, установите соответствующие флаги USE, которые позволяют точнее регулировать процесс установки.

(просмотр имеющихся флагов USE)
# emerge --pretend --verbose mysql
(установка MySQL)
# emerge mysql

По завершении установки вы увидите такое сообщение:

You might want to run:
"emerge --config =dev-db/mysql-[version]"
if this is a new install.

Рекомендуется запустить команду
"emerge --config =dev-db/mysql-[версия]"
после первоначальной установки MySQL.

Поскольку у нас первоначальная установка, запустим команду. Во время настройки базы данных MySQL вам потребуется по запросу нажать клавишу ENTER. В процессе настройки устанавливается главная база данных MySQL, содержащая служебные сведения о базах данных, таблицах, пользователях, правах доступа и т.д. При настройке будет рекомендовано как можно раньше изменить корневой пароль. Мы определенно это сделаем, чтобы кто-нибудь не смог улучшить момент и взломать наш сервер MySQL, настроенный по умолчанию.

# ebuild /var/db/pkg/dev-db/mysql-[version]/mysql-[version].ebuild config
 * MySQL DATADIR is /var/lib/mysql
 * Press ENTER to create the mysql database and set proper
 * permissions on it, or Control-C to abort now...

   Preparing db table
   Preparing host table
   Preparing user table
   Preparing func table
   Preparing tables_priv table
   Preparing columns_priv table
   Installing all prepared tables

   To start mysqld at boot time you have to copy support-files/mysql.server
   to the right place for your system

   PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
   To do so, issue the following commands to start the server
   and change the applicable passwords:
(обратите внимание на следующие 3 строки)
   /etc/init.d/mysql start
   /usr/bin/mysqladmin -u root -h pegasos password 'new-password'
   /usr/bin/mysqladmin -u root password 'new-password'
   Depending on your configuration, a -p option may be needed
   in the last command. See the manual for more details.

(различные сообщения, не относящиеся к установке, опущены для краткости)

   * For security reasons you should set your MySQL root
   * password as soon as possible.

Сценарий установки уже отобразил команды, нужные для установки пароля. Теперь запустим их.

# /etc/init.d/mysql start
 * Re-caching dependency info (mtimes differ)...
 * Starting mysqld (/etc/mysql/my.cnf) ...        [ ok ]
(вместо 'новый-пароль' укажите ваш пароль)
# /usr/bin/mysqladmin -u root -h localhost password 'новый-пароль'

Теперь вы можете убедиться в работоспособности нового пароля, попытавшись войти на свой сервер MySQL:

$ mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4 to server version: 4.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Параметр -u указывает пользователя, который будет входить в систему. Параметр -h указывает узел сети. Обычно это localhost, если вы настраиваете не удаленный сервер. Наконец, -p сообщает программе-клиенту mysql, что для доступа к базе данных будет вводится пароль. Обратите внимание на приглашение mysql>. Именно здесь вы будете вводить все свои команды. Теперь, находясь в среде mysql в качестве корневого пользователя, мы можем начать настройку базы данных.

2.  Настройка базы данных

Создание базы данных

Мы вошли, и на экране — приглашение mysql. Сначала взглянем на список уже имеющихся баз данных. Для этого введем команду SHOW DATABASES.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| mysql    |
| test     |
+----------+
2 rows in set (0.09 sec)

Несмотря на то, что база данных test уже создана, создадим свою собственную. Базы данных создаются командой CREATE DATABASE. Мы назовем свою gentoo.

mysql> CREATE DATABASE gentoo;
Query OK, 1 row affected (0.08 sec)

Ответ дает понять, что команда выполнена без каких-либо ошибок. В данном случае, изменилась одна строка. Это относится к главной базе данных mysql, в которой содержится список всех баз данных. Но вам не нужно слишком беспокоиться о второстепенных подробностях. Последнее число означает время выполнения запроса. Убедиться, что база данных создана, мы можем, снова запустив команду SHOW DATABASES.

mysql> SHOW DATABASES;
+----------+
| Database |
+----------+
| gentoo   |
| mysql    |
| test     |
+----------+
3 rows in set (0.00 sec)

Разумеется, наша база данных создана. Чтобы заняться созданием таблиц в новой базе данных gentoo, нам потребуется установить ее как текущую. Для этого используем команду USE. Параметром этой команды указывается название базы данных, которую нужно сделать текущей. Еще текущую базу можно устанавливать в командной строке, указывая ее название после параметра -D. Давайте продолжим и переключимся на базу данных gentoo.

mysql> USE gentoo;
Database changed

Теперь текущей является только что созданная база данных gentoo. Теперь, используя ее, мы можем заняться созданием таблиц и наполнением их информацией.

3.  Работа с таблицами в MySQL

Создание таблицы

В структуру MySQL входят базы данных, таблицы, записи и поля. В базах данных собраны таблицы, в таблицах собраны записи, в записях — поля, в которых, в свою очередь, хранится собственно информация. Такая структура позволяет пользователям выбирать, каким образом обращаться к своей информации. Только что мы работали с базами данных, теперь давайте поработаем с таблицами. Для начала, список таблиц можно вывести так же, как и список баз данных, используя команду SHOW TABLES. Сейчас в нашей базе данных gentoo еще нет таблиц, как показывает эта команда:

mysql> SHOW TABLES;
Empty set (0.00 sec)

Значит, нужно создать несколько таблиц. Чтобы это сделать, используем команду CREATE TABLE. Однако, эта команда довольно сильно отличается от простой команды CREATE DATABASE. Ей передается список аргументов следующего вида:

CREATE TABLE [имя_таблицы] ([имя_поля] [тип_данных_поля]([размер]));

имя_таблицы — имя создаваемой таблицы. В данном случае, давайте создадим таблицу с названием developers (разработчики). В таблице будут находится имена разработчиков, адреса электронной почты и род занятий. имя_поля будет содержать имена полей. Нам нужны три имени: name (имя), email (почта) и job (род занятий). В параметре тип_данных_поля укажем тип заносимой информации. Перечень возможных форматов находится на странице описания типов столбцов MySQL (англ.). Здесь мы для всех полей укажем тип VARCHAR. VARCHAR — один из простейших типов данных, предназначенный для работы со строками. размер указывает, сколько данных можно сохранить в одном поле. Укажем 128. Это значит, что поле сможет содержать 128 байт данных типа VARCHAR. Cейчас можно упрощенно считать, что это 128 знаков текста, хотя на вышеупомянутом сайте представлено более точное описание. Теперь, зная, какую мы собираемся создать таблицу, сделаем это.

mysql> CREATE TABLE developers ( name VARCHAR(128), email VARCHAR(128), job VARCHAR(128));
Query OK, 0 rows affected (0.11 sec)

Похоже, таблица создалась нормально. Давайте проверим это командой SHOW TABLES:

mysql> SHOW TABLES;
+------------------+
| Tables_in_gentoo |
+------------------+
| developers       |
+------------------+
1 row in set (0.00 sec)

Да, это она! Но не показано никакой информации о введенных типах полей. Для этого используем команду DESCRIBE (или ее краткий вариант, DESC), параметром которой указывается имя таблицы. Посмотрим, что она выдаст для таблицы developers.

mysql> DESCRIBE developers;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| name  | varchar(128) | YES  |     | NULL    |       |
| email | varchar(128) | YES  |     | NULL    |       |
| job   | varchar(128) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Показаны различные поля и их типы. Видно также несколько дополнительных атрибутов, выходящих за рамки этого руководства. За дополнительными сведениями можно обратиться к Руководству по MySQL (англ.). Теперь у нас есть рабочая таблица. Давайте двинемся дальше и наполним ее.

Заполнение базы данных MySQL

Таблица заполняется (данные добавляются) командой INSERT. Как и у команды CREATE TABLE, у нее есть определенный формат:

INSERT INTO имя_таблицы (столбец1, столбец2, ...) VALUES('знач1','знач2',...); 

Эту команду используют для вставки записей в таблицу. Сначала указывается имя таблицы, в которую нужно добавить информацию. Затем может идти список столбцов, в которые добавляются данные, а в VALUES указываются значения, которые добавляются в таблицу. Можно опустить список полей, если значения добавляются в каждое в том же порядке, в каком определены поля в таблице. Сейчас мы добавим данные в теблицу developers. Вставьте записи как в примере:

mysql> INSERT INTO developers VALUES('Joe Smith', 'joesmith@gentoo.org', 'toolchain');
Query OK, 1 row affected (0.06 sec)
(Если вы не знаете порядок полей в таблице или хотите добавить
неполную запись)
mysql> INSERT INTO developers (job, name) VALUES('outsourced', 'Jane
Doe');
Query OK, 1 row affected (0.01 sec)

Согласно полученному ответу, запись, похоже, добавилась правильно. Но что если требуется ввести более одной записи? Именно здесь пригодиться команда LOAD DATA. Она загружает записи из файла, разделенного знаками табуляции. Попробуем создать файл с записями в своем домашнем каталоге. Назовем его records.txt. Вот пример:

John Doe        johndoe@gentoo.org      portage
Chris White     chriswhite@gentoo.org   documentation
Sam Smith       samsmith@gentoo.org     amd64

Вообще, у команды LOAD DATA весьма пространное определение, но сейчас мы используем простейшую форму.

LOAD DATA LOCAL INFILE '/путь/к/файлу' INTO TABLE имя_таблицы;

Здесь тоже все просто. Укажите путь к файлу и имя таблицы. В нашем случае — это файл ~/records.txt и таблица developers.

mysql> LOAD DATA LOCAL INFILE '~/records.txt' INTO TABLE developers;
Query OK, 3 rows affected (0.00 sec)
Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

Сработало. Но эта команда просто добавляет записи и не дает проконтролировать работу MySQL. Множество веб-приложений используют сценарии sql для быстрой и легкой настройки базы данных MySQL. Если вы хотите использовать сценарий sql, вам понадобится запустить mysql в пакетном режиме (в качестве ввода используется файл со сценарием). Вот пример запуска mysql в пакетном режиме:

$ mysql -u root -h localhost -p < sqlfile

Как и в случае с LOAD DATA, убедитесь, что знаете, что именно выполняет файл sqlfile. Не делая этого, вы можете подвергнуть вашу базу данных серьезному риску утечки! Другой путь заключается в использовании команды source. Эта команда запускает команды mysql из файла, когда mysql находится в интерактивном режиме. Вот как взять команды sql из файла:

mysql> source sqlfile;

Если вы столкнетесь с веб-приложением, которому нужно, чтобы вы запустили файл с командами sql, можно использовать одну из двух команд, показанных выше. Итак, наша таблица сформирована, но как же работать с полями? Это делается поиском в таблице при помощи запросов.

Запросы к таблицам в MySQL

Одна из главных функций любой базы данных SQL — запросы. Они помогают обратить данные из таблиц во что-то полезное. Большинство запросов выполняется командой SELECT. Эта команда довольно сложна, поэтому в этом документе мы рассмотрим лишь три основных ее формы.

(Выборка всех записей в таблице)
SELECT * FROM имя_таблицы;
(Выборка конкретных записей в таблице)
SELECT * FROM имя_таблицы WHERE поле=значение;
(Выборка конкретных полей)
SELECT поле1,поле2,поле3 FROM имя_таблицы [WHERE поле=значение];

Посмотрим на первую форму. Она относительно проста и позволяет взглянуть на всю таблицу. Попробуем: запустим эту команду, чтобы увидеть, что находится в нашей таблице.

mysql> SELECT * FROM developers;
+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Видно данные, не только вставленные путем INSERT, но и вставленные командой LOAD DATA. Теперь, к примеру, нужно посмотреть запись для Chris White. Это можно сделать, используя вторую форму выборки.

mysql> SELECT * FROM developers WHERE name = 'Chris White';
+-------------+-----------------------+---------------+
| name        | email                 | job           |
+-------------+-----------------------+---------------+
| Chris White | chriswhite@gentoo.org | documentation |
+-------------+-----------------------+---------------+
1 row in set (0.08 sec)

Как и ожидалось, отобрана нужная искомая запись. Теперь, допустим, что нужно знать только род занятий и адрес электронной почты, но не имя. Это делается при помощи третьей формы SELECT, как показано ниже.

mysql> SELECT email,job FROM developers WHERE name = 'Chris White';
+-----------------------+---------------+
| email                 | job           |
+-----------------------+---------------+
| chriswhite@gentoo.org | documentation |
+-----------------------+---------------+
1 row in set (0.04 sec)

Этот способ выборки легче в управлении, особенно при больших объемах информации, как показано далее. Сейчас, войдя как суперпользователь, вы обладаете неограниченными правами на любые действия в MySQL. Пользователь с такими привилегиями в серверной среде может быть достаточно трудноуправляемым. Чтобы управлять тем, кто и что может делать с базами данных, надо устанавливать привилегии.

4.  Привилегии в MySQL

Предоставление привилегий командой GRANT

Привилегиями определяются возможности доступа пользователей к базам данных, таблицам... почти ко всему. Сейчас только суперпользователь root из MySQL может обращаться к базе данных gentoo, согласно данным разрешениям. Давайте создадим двух самых обычных пользователей, например, guest (гостя) и admin (администратора), которые будут обращаться к базе данных gentoo и работать с информацией из нее. Пусть пользователь guest будет ограничен в правах, и все, что он сможет — это получать информацию из базы данных. admin получит те же права доступа, что и root, но только к базе данных gentoo (не к основным базам mysql). Но прежде, чем начать, давайте взглянем на несколько упрощенный формат команды GRANT.

GRANT [привилегии] ON база_данных.* TO '[пользователь]'@'[узел]'
IDENTIFIED BY '[пароль]';

Теперь нужны привилегии, которые можно присваивать. Используя все вышесказанное, можно устанавливать следующие привилегии:

• ALL - дается полный доступ ко всей базе данных

• CREATE - пользователям позволяется создавать таблицы

• SELECT - пользователям позволяется делать запросы к таблицам

• INSERT - пользователям позволяется вставлять данные в таблицу

• SHOW DATABASES - разрешается выводить список баз данных

• USAGE - у пользователя нет привилегий

• GRANT OPTION - пользователям разрешается давать привилегии

Для нашего пользователя admin подойдет ALL. А для пользователя guest привилегии SELECT будет достаточно для доступа на чтение. В качестве базы данных укажем gentoo, на нее будут установлены разрешения. .* означает «все таблицы». Если потребуется, можно установить права доступа к отдельным таблицам. Дальше идет имя пользователя и имя узла, с которого будет работать пользователь. В большинстве случаев это будет узел localhost. Наконец, задается пароль пользователя. Исходя из этого, давайте создадим пользователей.

(admin)
mysql> GRANT ALL ON gentoo.* TO 'admin'@'localhost' IDENTIFIED BY 'пароль';
(guest)
mysql> GRANT SELECT ON gentoo.* TO 'guest'@'localhost' IDENTIFIED BY 'пароль';

Итак, пользователи созданы; теперь протестируем их. Сначала выйдем из mysql, написав quit в строке приглашения:

mysql> quit

Теперь мы снова в консоли. Пользователи настроены, давайте посмотрим, что они могут делать.

Проверка прав пользователей

Попробуем зайти как пользователь guest. В данный момент у пользователя guest есть только право выборки (SELECT). В основном, это сводится к возможности поиска и ничему другому. Зайдем как пользователь guest.

$ mysql -u guest -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6 to server version: 4.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Теперь нам нужно проверить его ограничения. Переключимся на базу данных gentoo:

mysql> USE gentoo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

Теперь попытаемся сделать что-нибудь недозволенное. Попробуем создать таблицу.

mysql> CREATE TABLE test (test VARCHAR(20), foobar VARCHAR(2));
ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

Как видно, это не удалось, из-за того, что у пользователя недостаточно прав. Но ему дано право на использование SELECT. Давайте проверим:

mysql> SELECT * FROM developers;
+-------------+-----------------------+----------------+
| name        | email                 | job            |
+-------------+-----------------------+----------------+
| Joe Smith   | joesmith@gentoo.org   | toolchain      |
| John Doe    | johndoe@gentoo.org    | portage        |
| Chris White | chriswhite@gentoo.org | documentation  |
| Sam Smith   | samsmith@gentoo.org   | amd64          |
| Jane Doe    | NULL                  | Outsourced job |
+-------------+-----------------------+----------------+
5 rows in set (0.00 sec)

Команда успешно отработала, а получили представление о том, на что способны права пользователей. Но мы ведь еще создали пользователя admin. Он создавался для демонстрации того, что даже у пользователей, которым даны все права, могут быть ограничения. Выйдите из MySQL и зайдите как пользователь admin.

mysql> quit
Bye
$ mysql -u admin -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Для начала, попробуем создать новую базу данных как пользователь admin. У пользователя admin схожие права с учетной записью root в MySQL, и он может вносить любые изменения, выбрав базу данных gentoo. Мы проверим права его доступа к главной базе данных MySQL. Вспомним, что ранее мы дали ему права только для доступа к конкретной базе данных.

mysql> CREATE DATABASE gentoo2;
ERROR 1044: Access denied for user: 'admin@localhost' to database 'gentoo2'

Разумеется, пользователь admin не может создавать базы данных в основной базе MySQL, несмотря на все права на базу данных gentoo. Но admin все еще может изменять базу данных gentoo, как показывает вставка данных в следующем примере.

mysql> USE gentoo;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO developers VALUES('Bob Simmons', 'bobsimmons@gentoo.org', 'python');
Query OK, 1 row affected (0.08 sec)

Пользователь admin может обращаться к базе gentoo, как захочет. Но иногда требуется лишить пользователя прав. Это может быть что угодно, начиная с проблематичного пользоватля, и заканчивая ушедшим сотрудником. Давайте разберемся, как отзывать разрешения с помощью команды REVOKE.

Удаление прав пользователя командой REVOKE

Команда REVOKE позволяет запретить доступ пользователю. Можно либо запретить любой доступ, либо только определенный. В самом деле, формат очень похож на GRANT.

REVOKE [привилегии] ON база_данных.* FROM '[пользователь]'@'[узел]';

Параметры объясняются в разделе команды GRANT. А сейчас мы запретим пользователю любой вид доступа. Скажем, мы выяснили, что учетная запись guest вызывает проблемы с безопасностью. Мы решаем отозвать все права. Заходим как root делаем необходимое.

mysql> REVOKE ALL ON gentoo.* FROM 'guest'@'localhost';
Query OK, 0 rows affected (0.00 sec)

Давайте выйдем и попробуем зайти как пользователь guest.

$ mysql -u guest -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 9 to server version: 4.0.25

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Хотя нам удалось войти, доступ к базе gentoo уже пропал.

mysql> USE gentoo;
ERROR 1044: Access denied for user: 'guest@localhost' to database 'gentoo'

И наш проблематичный пользователь больше не может обращаться к базе gentoo. Заметьте, что пользователь сохранил возможность входа. Это потому, что он остается в основной базе данных MySQL. Теперь взглянем, как полностью удалить учетную запись командой DELETE, и посмотрим на таблицу пользователей MySQL.

Удаление учетных записей командой DELETE

Таблица пользователей MySQL — это список пользователей и информации о них. Убедитесь, что вы зашли как root. Используйте основную базу данных MySQL.

mysql> USE mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

Посмотрим, какие в базе mysql существуют таблицы:

mysql> SHOW TABLES;
+-----------------+
| Tables_in_mysql |
+-----------------+
| columns_priv    |
| db              |
| func            |
| host            |
| tables_priv     |
| user            |
+-----------------+
6 rows in set (0.00 sec)

Таблица user — та, что нужна. В ней 30 различных полей, и ее сложно читать. Для облегчения чтения используем третью форму команды SELECT. Искомые поля — Host (узел) и User (пользователь).

mysql> SELECT Host,User FROM user WHERE User = 'guest';
+-----------+-------+
| Host      | User  |
+-----------+-------+
| localhost | guest |
+-----------+-------+
1 row in set (0.00 sec)

Теперь, получив информацию, мы можем избавиться от пользователя guest. Это делается командой DELETE; вот ее синтаксис:

DELETE FROM имя_таблицы WHERE поле='значение';

Вы могли заметить, что формат DELETE чем-то схож с форматом SELECT. Укажем поле User и значение guest. Это удалит запись из таблицы user, где пользователь — guest, удаляя нашу гостевую учетную запись. Сделаем так:

mysql> DELETE FROM user WHERE User='guest';
Query OK, 1 row affected (0.07 sec)
(Команда FLUSH PRIVILEGES нужна, чтобы обновить разрешения)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

Похоже, сработало. Проверим: выйдите и попробуйте зайти как пользователь guest.

mysql> quit
Bye
$ mysql -u guest -h localhost -p
Enter password:
ERROR 1045: Access denied for user: 'guest@localhost' (Using password: YES)

Все, пользователь успешно удален!

Заключение

В этом руководстве в основном рассматривалась настройка MySQL из командной строки. Существует несколько альтернатив с графическим интерфейсом:

• phpMyAdmin  — популярное средство администрирования MySQL, написанное на php.

• mysqlnavigator  — оболочка MySQL для Qt.

• gmyclient — клиент MySQL для GNOME.

• knoda — клиент MySQL для KDE.

На этом заканчивается введение в MySQL. Надеюсь, что оно помогло вам лучше разобраться в основах MySQL и настройке базы данных. Пожалуйста, присылайте свои соображения мне по адресу Chris White.

PostgreSQL Guide

Ссылка на оригинал: http://www.gentoo.org/doc/en/postgres-howto.xml

C версии: 1.5

1.  Introduction

PostgreSQL introduction

When talking to most developers about the different database solutions to use, two major databases will usually form the answer. One would be MySQL, and the other is what this document will refer to, PostgreSQL. The advantages of one over the other is a somewhat long winded debate, however it is just to say that PostgreSQL has had a more firm grasp on true relational database structure than MySQL. Most of the standard features such as FOREIGN KEY was only just added in MySQL 5. However, whatever the case may be, this document assumes that you have selected PostgreSQL as the database to use. The first place to start is the emerge process. In the next section, the installation process through emerge will be described, as well as the basic configuration.

PostgreSQL installation

To begin, we must first emerge the PostgreSQL package. To do so, run the following code to first ensure that the options for it are properly set:

# emerge -pv postgresql

These are the packages that I would merge, in order:

Calculating dependencies ...done!
[ebuild  N    ] dev-db/postgresql-8.0.4  -doc -kerberos +nls +pam +perl -pg-intdatetime +python +readline (-selinux) +ssl -tcl +xml +zlib 0 kB

Here's a list of what the different build options indicate:

Once you've customized PostgreSQL to meet your specific needs, go ahead and start the emerge:

# emerge postgresql
(Output shortened)
>>> /usr/lib/libecpg.so.5 -> libecpg.so.5.0
>>> /usr/bin/postmaster -> postgres
 * Make sure the postgres user in /etc/passwd has an account setup with /bin/bash as the shell
 *
 * Execute the following command
 * emerge --config =postgresql-8.0.4
 * to setup the initial database environment.
 *
>>> Regenerating /etc/ld.so.cache...
>>> dev-db/postgresql-8.0.4 merged.

As shown by the einfo output, there is some post setup that must be done. The next chapter will look at the actual configuration of PostgreSQL.

2.  PostgreSQL configuration

Setting up the initial database environment

As noted in the earlier emerge output, the initial database environment must be setup. However, before this is done, one thing needs to be considered. Unlike, say MySQL, PostgreSQL's "root" password is the password of the actual user. However, only the user is created by the ebuild not the password. So before we can begin, the password must be set for the postgres user:

# passwd postgres
New UNIX password:
Retype new UNIX password:
passwd: password updated successfully

Now that this is setup, the creation of the initial database environment can occur:

# emerge --config =postgresql-8.0.4


Configuring pkg...

 * Creating the data directory ...
 * Initializing the database ...
The files belonging to this database system will be owned by user "postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating directory /var/lib/postgresql/data/global ... ok
creating directory /var/lib/postgresql/data/pg_xlog ... ok
creating directory /var/lib/postgresql/data/pg_xlog/archive_status ... ok
creating directory /var/lib/postgresql/data/pg_clog ... ok
creating directory /var/lib/postgresql/data/pg_subtrans ... ok
creating directory /var/lib/postgresql/data/base ... ok
creating directory /var/lib/postgresql/data/base/1 ... ok
creating directory /var/lib/postgresql/data/pg_tblspc ... ok
selecting default max_connections ... 100
selecting default shared_buffers ... 1000
creating configuration files ... ok
creating template1 database in /var/lib/postgresql/data/base/1 ... ok
initializing pg_shadow ... ok
enabling unlimited row size for system tables ... ok
initializing pg_depend ... ok
creating system views ... ok
loading pg_description ... ok
creating conversions ... ok
setting privileges on built-in objects ... ok
creating information schema ... ok
vacuuming database template1 ... ok
copying template1 to template0 ... ok

WARNING: enabling "trust" authentication for local connections
You can change this by editing pg_hba.conf or using the -A option the
next time you run initdb.

Success. You can now start the database server using:

    /usr/bin/postmaster -D /var/lib/postgresql/data
or
    /usr/bin/pg_ctl -D /var/lib/postgresql/data -l logfile start

 *
 * You can use /etc/init.d/postgresql script to run PostgreSQL instead of pg_ctl.
 *

Now the initial database environment is setup. The next section will look at verifying the install and setting up users to access the database.

PostgreSQL database setup

Now that PostgreSQL is setup, it's a good idea at this point to verify the installation. First, make sure the service starts up ok:

# /etc/init.d/postgresql start
* Starting PostgreSQL ...                                          [ ok ]

Once this is verified working, it's also a good idea to add it to the default runlevel so it starts at boot:

# rc-update add postgresql default
* postgresql added to runlevel default

Now that the service has started, it's time to try setting up a test database. To start out, let's create a test database by using the createdb command. We'll also pass along the -U option to set the user (it defaults to the current user name if you don't), and the -W option to request the password we created earlier. Finally we give it the name of the database we want to create:

$ createdb -U postgres -W test
Password:
CREATE DATABASE

The database was successfully created, and we can confirm that the database can run basic tasks. We'll go ahead and drop this database (remove it) with the dropdb command:

$ dropdb -U postgres -W test
Password:
DROP DATABASE

Right now, only the postgres user can run commands. Obviously this is not the sort of setup one would like in a multi-user environment. The next section will look at working with user accounts.

Setting up database user accounts

As mentioned earlier, having to login as the postgres user is somewhat undesirable in a mult-user environment. In most cases there will be various users and services accessing the server, and each have different permission requirements. So, to handle this, the createuser command can be used. This command is an alternative to running a few SQL queries, and is a lot more flexible from an admin standpoint. We'll go ahead and create two users, a 'superuser' that can add other users and administer the db, and a standard user:

(replace chris with the username you'd like to use)
$ createuser -a -d -P -E -U postgres -W chris
Enter password for new user:
Enter it again:
Password:
CREATE USER

There, we've created the superuser. The command line option -a specifies that this user can add other users. -d means that this user can create databases. -P let's you enter a password for the user and -E will encrypt it for security purposes. Now then, we'll test this new user's permissions out by setting up our standard user:

(replace chris with the username you've just created)
$ createuser -A -D -P -E -U chris -W testuser
Enter password for new user:
Enter it again:
Password:
CREATE USER

Success! Our new user was created using the previously created superuser. The -A and -D options do the opposite of -a and -d, and instead deny the user the ability to create other users and databases. Now that there are users to work with, the next chapter will look at using the new database.

3.  Using PostgreSQL

Setting up permissions

Now there is a user that can create databases and add other users, and the main postgres user that can do anything. The user created earlier can currently login to the server, and that's about it. In general, users need to be able to insert data and retrieve data, and sometimes any other number of tasks. So, for this new user to be able to do anything, they must be setup with the proper permissions. This can easily be done by passing the -O parameter to createdb. We'll start by making a new database, MyDB with our superuser that will be owned by the previous testuser:

$ createdb -O testuser -U chris -W MyDB
Password:
CREATE DATABASE

Alright, now we have a new MyDB database, and a testuser that can access it. To test this out, we'll login as the testuser to the new MyDB database. We'll do this with the psql program. This program is what's used to connect to the PostgreSQL database from command line. So connect to the new database like so:

$ psql -U testuser -W MyDB
Password:
Welcome to psql 8.0.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help with psql commands
       \g or terminate with semicolon to execute query
       \q to quit

MyDB=>

So, the testuser is now logged into the database, and can begin to initiate some commands. To get a feel for using PostgreSQL, the next section will take a look at some of the basic commands in navigating the psql client.

Basic PostgreSQL commands and creating a table

For those who are used to MySQL, this is somewhat of a definite read. This is where PostgreSQL may get somewhat unique with regards to running commands. To start, here is a list of some commands that will be discussed:

With the exception of \c[onnect], all the commands shown will be used later on in the section. So right now the database is empty. That said, we need to insert some data. The first step to inserting data, however, is to put it in a table. Right now there are no tables in the database, so we need to create one. This is done with the CREATE TABLE command. We'll make a table of items. They will contain a Product ID, Description, and price:

MyDB=> CREATE TABLE products (
MyDB(>   product_id SERIAL,
MyDB(>   description TEXT,
MyDB(>   price DECIMAL
MyDB(> );
NOTICE:  CREATE TABLE will create implicit sequence "products_product_id_seq"
for serial column "products.product_id"
CREATE TABLE

You can ignore the NOTICE, it's perfectly harmless. Looking at the last line of the function, CREATE TABLE seems to indicate that the command has succeeded. However, let's go ahead and verify that the table was indeed successfully created with the \d command:

MyDB=> \d products
                                 Table "public.products"
   Column    |  Type   |                            Modifiers
-------------+---------+------------------------------------------------------------------
 product_id  | integer | not null default nextval('public.products_product_id_seq'::text)
 description | text    |
 price       | numeric |

Indeed the table was successfully created. Now that the table is created, it needs to be populated with data. The next section will look at populating the database with data.

Inserting data into the database

This section will look at the two ways of populating the newly created table with data. First let's look at the most basic command, INSERT:

INSERT INTO [tablename] (column1,column2,column3) VALUES(value1,value2,value3)

tablename contains the name of the table to insert the data into. (column1,column2,column3) lets you specify the specific columns to insert the values into. VALUES(value1,value2,value3) is the listing of values. The values are inserted into the same order as the columns (column1 gets value1, column2 gets value2, column3 gets value3). These counts must be the same. So let's go ahead and insert an item into the table:

MyDB=> INSERT INTO products (description,price) VALUES('A test product', 12.00);
INSERT 17273 1

The last line needs a bit of explaining. The return of an insert command is an OID (Object Identifier) and the number of rows inserted. OID's are a bit beyond the scope of this guide, and the PostgreSQL manual has some good information on it. Now, for a situation where you have 20,000 products, these insert statements can be a little tedious. However, not all is lost. The COPY command can be used to insert data into a table from a file or stdin. In this example, let's assume that you have a csv (comma separated values) file, which contains the product id, description, and price. The file looks like this:

2,meat,6.79
3,soup,0.69
4,soda,1.79

Now we'll use the COPY command to populate our data:

MyDB=> COPY products FROM STDIN WITH DELIMITER AS ',';
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 2,meat,6.79
>> 3,soup,0.69
>> 4,soda,1.79
>> \.

Unfortunately, this line doesn't return the same status information as the INSERT INTO statement. How do we know the data was inserted? The next section will look at running queries to check our data.

Using PostgreSQL queries

This section will look at using the SELECT statement to view data in our tables. The basic SELECT format looks like this:

SELECT (column1,column2|*) FROM (table) [WHERE (conditionals)]

There are two ways to select columns. The first is using * to select all columns, and the second is to specify a list of specific columns you wish to see. The second is quite handy when you want to find a specific column in a rather large list of them. Let's start out with using SELECT with * to specify all columns:

MyDB=> SELECT * FROM products;
 product_id |  description   | price
------------+----------------+-------
          1 | A test product | 12.00
          2 | meat           |  6.79
          3 | soup           |  0.69
          4 | soda           |  1.79
(4 rows)

As shown here, all the data we inserted earlier is indeed in the table. Now let's say we only want to see the description and the price, and don't care about the product id. In this case we'll use the column specific SELECT form:

MyDB=> SELECT description,price FROM products;
  description   | price
----------------+-------
 A test product | 12.00
 meat           |  6.79
 soup           |  0.69
 soda           |  1.79
(4 rows)

Now only the product and price is shown, letting us focus on only the important data. Now let's say that we want to see only the items that are greater than $2.00. Here's where the WHERE clause comes in handy:

MyDB=> SELECT description,price FROM products WHERE price > 2.00;
  description   | price
----------------+-------
 A test product | 12.00
 meat           |  6.79
(2 rows)

Now a listing of products over $2.00 is displayed, focusing the data even more. These forms of querying for information are very powerful, and can help create extremely useful reports.

Conclusion

This concludes the PostgreSQL Guide. A big thanks goes to Masatomo Nakano, the previous Gentoo PostgreSQL maintainer for his help in answering my questions. Any suggestions on this guide should be sent to Chris White. For more extensive documentation, see the PostgreSQL website.