Using PostgreSQL

Кстати, сегодня ведь ч0рная пятница! 2012-01-13 friday. Какой кошмар.

Итак, продолжим. Предыдущий пост был посвящен установке и первичной настройке СУБД PostgreSQL (вместе с PostGIS, которая пока никак не задействована, потом пригодится). В том посте я обещал нарисовать пример использования свежеустановленной БД по задаче «загрузить список файлов в таблицу и найти нужные файлы».

Приступим.

В наличии файл (размер под гигабайт, более 4 000 000 строк) вида CSV.

Пример данных, первые две строки из файла:

'mode';'ino';'dev';'nlink';'uid';'gid';'size';'atime';'mtime';'ctime';'fullName'
'33206';'0';'0';'0';'0';'0';'2924';'2012-01-12 03:17:04';'2011-12-20 17:38:39';'2011-12-20 17:38:39';'c:\arcgisserver\arcgiscache\topo500\Layers\conf.xml'

Для меня интерес представляет параметр mtime, то есть время последней модификации. Мне надо найти файлы, изменение которых проходило после некоей даты, которую еще надо определить исходя из нечеткой логики типа «в последнюю неделю старого года мы немного файлов изменяли, когда точно — не помню».

Файл был получен прогоном скрипта, где ключевые строки такие:

#!/usr/bin/env python ... csvWriter = csv.writer(fileObj, delimiter=';', quotechar="'", quoting=csv.QUOTE_ALL, lineterminator='\n') ... for (path, dirs, files) in os.walk(path): for fn in files: fullName = os.path.join(path, fn) (mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime) = ('', '', '', '', '', '', '', '', '', '') try: (mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime) = os.stat(fullName) except: log.error('os.stat failed', exc_info=True) csvWriter.writerow((mode, ino, dev, nlink, uid, gid, size, strFtime(atime), strFtime(mtime), strFtime(ctime), fullName))

Глядя на содержимое файла, я состряпал такую таблицу (в pgAdmin есть вполне приятный GUI создания таблиц)

CREATE TABLE flist ( fmode integer, fino integer, fdev integer, fnlink integer, fuid integer, fgid integer, fsize integer, fatime character varying(20), fmtime character varying(20), fctime character varying(20), "fullName" character varying(255), CONSTRAINT flist_pk PRIMARY KEY ("fullName") ) WITH ( OIDS = FALSE ); ALTER TABLE flist OWNER TO postgres;

видно, что с именем «fullName» я погорячился — теперь придется всю дорогу использовать форму записи имени с кавычками. Еще видно, что индексы я делать пока не стал, из соображений скорости загрузки. Правда сделал первичный ключ (без него как-то неуютно даже).

Теперь надо загрузить файл CSV в эту таблицу. Есть сведения, что с этим легко справится встроенная в PostgreSQL команда COPY. Но есть закавыка — если я на клиенте, где мне расположить файл? Я попробовал по разному и в итоге, лично мне проще оказалось выполнить команду в консоли сервера, подмонтировав файл в дерево каталогов сервера.

Сначала я тупо скопировал файл на сервер

postgres@postgis:~$ rsync v@sqz2:vshare/t/topo500/current/listfiles.csv ~/

получился файл /var/lib/postgresql/listfiles.csv

что многое говорит о профиле пользователя postgres на сервере.

А потом я подумал (бывает, сначала делаешь, потом думаешь) и решил сделать лучше — подключить Samba, ибо часто нужно использовать виндовые шари.

Получилось так:

от рута: 86 aptitude install cifs-utils 92 mkdir -p /home/v/mnt/vshare 94 chown -R v /home/v/mnt 98 mount -t cifs //sqz2/vshare /home/v/mnt/vshare -o username=v,uid=postgres postgres@postgis:~$ less /home/v/mnt/vshare/t/topo500/current/listfiles.csv

Что и требовалось - файл доступен для пользователя postgres на сервере БД.

Собственно импорт довольно прост:

postgres@postgis:~$ psql postgres=# copy flist(fmode, fino, fdev, fnlink, fuid, fgid, fsize, fatime, fmtime, fctime, "fullName")  from '/home/v/mnt/vshare/t/topo500/current/listfiles.csv' with DELIMITER as ';' CSV header quote as ''''; COPY 4260931

последняя строка показывает сколько строк скопировано. Число правильное. Жаль, время я тогда не засек, а тулза его не показывает. По ощущениям, данные заливаются раза в два дольше, чем если просто переписывать файл по сети. Возможно, удалив первичный ключ получилось бы быстрее.

Дальнейшая работа с таблицей целиком проходит на клиенте, pgAdmin query tool в моем случае.

Создать индекс на поле fmtime. Опять же, через GUI создания/редактирования таблиц

-- DROP INDEX flist_mod_idx; CREATE INDEX flist_mod_idx ON flist USING btree (fmtime COLLATE pg_catalog."default" );

в том GUI довольно много опций, надо бы в них поподробнее разобраться. А сейчас все опции дефолтные.

Запрос на выборку

SELECT flist."fullName" -- , flist.fctime -- , flist.fmtime FROM public.flist WHERE flist.fmtime > '2011-12-23 21:53:37' ORDER BY flist.fmtime ASC -- limit 1000 ; Total query runtime: 7962 ms. 70066 rows retrieved.

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

Тыща строк в секунду. Как я понял, погоняв немного разные варианты запросов, тормозом можно считать сетевой ввод/вывод. Ибо, при последующих прогонах одного и того же запроса дисковая активность практически нулевая, как и загруженность процессора (это я про сервер, хотя клиент тоже не загружен).

Ну и получив нужную мне выборку, в том же pgAdmin query tool я сохранил ее в файл, через меню File — Export. Очень удобно.

Задача решена, всем спасибо, все свободны.