¡ã PostgreSQL ȨÆäÀÌÁö
PostgreSQLÀº °·ÂÇÑ Â÷¼¼´ë °´Ã¼-°ü°èÇü DBMS·Î Berkeley Postgres µ¥ÀÌÅͺ£À̽º °ü¸® ½Ã½ºÅÛ¿¡¼ ÆÄ»ýµÇ¾ú´Ù. PostgreSQLÀº °·ÂÇÑ °´Ã¼-°ü°èÇü µ¥ÀÌÅÍ ¸ðµ¨°ú dzºÎÇÑ µ¥ÀÌÅÍ Å¸ÀÔ, ½¬¿î È®À强À» °¡Áö°í ÀÖÀ¸¸ç, PostQuelÁúÀÇ ¾ð¾î¸¦ È®ÀåµÈ SQLÀÇ ºÎºÐ ÁýÇÕÀ¸·Î ´ëüÇϰí ÀÖ´Ù. PostgreSQLÀº MySQLÀÇ ºÎÁ·ÇÑ ºÎºÐ°ú ¶óÀ̼±½º ¹®Á¦¸¦ ÇØ°áÇϱâ À§ÇØ ¸¹ÀÌ »ç¿ëµÇ¾ú´Ù. ´ëºÎºÐÀÇ ¸®´ª½º ¹èÆ÷ÆÇ¿¡¼ PostgreSQLÀ» ÆÐŰÁö·Î Á¦°øÇÑ´Ù. ¼³Ä¡´Â ÆÐŰÁö °ü¸® µµ±¸ÀÎ "yum"À¸·Î ¼³Ä¡ÇÏ°í ±âº» ¿î¿µ ¹× À¥¼ºñ½º¿Í ¿¬µ¿ÇÏ´Â ºÎºÐÀ» ¾Ë¾Æº¸ÀÚ.
16.4.1. PostgreSQL ¼³Ä¡
PostgreSQLÀº ´ëºÎºÐÀÇ ¸®´ª½º ¹èÆ÷ÆÇ¿¡¼ ÆÐŰÁöÇüÅ·ΠÁ¦°øÇÑ´Ù. CentOS7 ¶ÇÇÑ PostgreSQLÀ» Á¦°øÇϸç, ´ÙÀ½°ú °°ÀÌ "yum" ¸í·É¾î·Î PostgreSQLÀ» ¼³Ä¡ ÇØº¸ÀÚ.
~]# yum -y install postgresql-server
PostgreSQL ½Ã½ºÅÛ DB »ý¼º
PostgreSQLÀÇ ½Ã½ºÅÛ DB¸¦ ´ÙÀ½°ú ¸í·É¾î·Î »ý¼ºÇÑ´Ù. ½Ã½ºÅÛ DB¸¦ »ý¼ºÇÏÁö ¾ÊÀ¸¸é postgresql ¼ºñ½º°¡ ½ÃÀÛµÇÁö ¾Ê´Â´Ù.
~]# postgresql-setup initdb
Initializing database ... OK
16.4.2. PostgreSQL ¿î¿µ
CentOS7¿¡¼ Á¦°øÇÏ´Â ÆÐŰÁö·Î PostgreSQL¸¦ ¼³Ä¡ÇÏ¿´´Ù¸é ¸®´ª½ºÀÇ ¼ºñ½º·Î µî·ÏµÉ °ÍÀÌ´Ù. ´ÙÀ½°ú °°ÀÌ ¼¹ö ºÎÆÃ ½Ã ÀÚµ¿À¸·Î ½ÃÀÛµÇ°Ô ¼³Á¤ÇÑ´Ù.
~]# systemctl enable postgresql
ln -s '/usr/lib/systemd/system/postgresql.service' '/etc/systemd/system/multi-user.target.wants/postgresql.service'
systemctl ¸í·É¾î·Î PostgreSQL ½ÃÀÛÇÑ´Ù.
~]# systemctl start postgresql
PostgreSQL µ¿ÀÛ È®ÀÎ
~]# systemctl status postgresql
postgresql.service - PostgreSQL database server
Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled)
Active: active (running) since È 2015-04-14 23:23:02 KST; 26s ago
Process: 19572 ExecStart=/usr/bin/pg_ctl start -D ${PGDATA} -s -o -p ${PGPORT} -w -t 300 (code=exited, status=0/SUCCESS)
Process: 19566 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
Main PID: 19576 (postgres)
CGroup: /system.slice/postgresql.service
¦§¦¡19576 /usr/bin/postgres -D /var/lib/pgsql/data -p 5432
¦§¦¡19577 postgres: logger process
¦§¦¡19579 postgres: checkpointer process
¦§¦¡19580 postgres: writer process
¦§¦¡19581 postgres: wal writer process
¦§¦¡19582 postgres: autovacuum launcher process
¦¦¦¡19583 postgres: stats collector process
4¿ù 14 23:23:01 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
4¿ù 14 23:23:02 localhost.localdomain systemd[1]: Started PostgreSQL database server.
PostgreSQL Á¤Áö
~]# systemctl stop postgresql
PostgreSQL Á¢¼Ó Å×½ºÆ®su ¸í·É¾î·Î postgres »ç¿ëÀÚ·Î ÀüȯÇÑ ´ÙÀ½, psql ¸í·É¾î¸¦ ÅëÇØ DB¿¡ Á¢¼Ó
~]# su - postgres
-bash-4.2$ psql
psql (9.2.10)
Type "help" for help.
postgres=#
16.4.3. ¿¬µ¿ Å×½ºÆ®¸¦ À§ÇÑ DB ¹× »ç¿ëÀÚ »ý¼º
À¥°ú PostgreSQLÀ» ¿¬µ¿Çϱâ À§ÇØ PostgreSQLÀÇ DB¿Í »ç¿ëÀÚ¸¦ »ý¼ºÇØ¾ß ÇÑ´Ù. ´ÙÀ½¿¹Á¦´Â sulinux DB »ý¼º, sulinux »ç¿ëÀÚ¸¦ »ý¼º ¹× Á¢¼ÓÀ» À§ÇÑ ¼³Á¤À» ÇÏ´Â °ÍÀÌ´Ù.
ÀÎÁõ ¼³Á¤¼öÁ¤(/var/lib/pgsql/data/pg_hba.conf)
host all all 127.0.0.1/32 ident
#====> ´ÙÀ½°ú °°ÀÌ ¼öÁ¤ ==>
host all all 127.0.0.1/32 password
À§ ¼³Á¤Àº ·ÎÄÃÈ£½ºÆ®¿¡¼ DB¿¡ Á¢¼Ó ½Ã ºñ¹Ð¹øÈ£¸¦ »ç¿ëÇÑ´Ù´Â ¼³Á¤ÀÌ´Ù. ±âº» ¼³Á¤Àº ·ÎÄûç¿ëÀÚ¿Í DB»ç¿ëÀÚ°¡ °°¾Æ¾ß Á¢¼ÓµÇ´Â °ÍÀε¥, ÀÌ·¸°Ô ¼³Á¤Çϸé À¥ Á¢±ÙÀÌ µÇÁö ¾Ê¾Æ À§¿Í °°ÀÌ ¼³Á¤ÇÑ´Ù. À§¿Í °°ÀÌ ¼³Á¤ ÈÄ DB¸¦ Àç½ÃÀÛ ÇÑ´Ù.
~]# systemctl restart postgresql
DB»ç¿ëÀÚ »ý¼º
~]# su - postgres
-bash-4.2$ createuser -d -P sulinux
»õ ·ÑÀÇ ¾ÏÈ£: ºñ¹Ð¹øÈ£
¾ÏÈ£ È®ÀÎ: ºñ¹Ð¹øÈ£
postgres »ç¿ëÀÚ·Î ÀüȯÇϰí, createuser ¸í·É¾î¸¦ »ç¿ëÇÏ¿© sulinux »ç¿ëÀÚ¸¦ »ý¼ºÇÑ´Ù. ¿©±â¼ -d ¿É¼ÇÀ» ÁÖ¾î database¸¦ »ý¼ºÇÒ ¼ö ÀÖ°Ô Çϰí, -P ¿É¼ÇÀ» ÁÖ¾î »ý¼º°ú µ¿½Ã¿¡ »ç¿ëÀÚÀÇ ºñ¹Ð¹øÈ£ ÀÔ·ÂÀ» ¹Þ´Â´Ù. ÀÚ¼¼ÇÑ ¿É¼ÇÀº createuser --help¸¦ ÀÔ·ÂÇÏ¿© È®ÀÎÇÑ´Ù. ¸¸¾à »ç¿ëÀÚ »ý¼ºÀ» À߸øÇßÀ» °æ¿ì »èÁ¦¸¦ À§Çؼ´Â dropuser ¸í·É¾î¸¦ »ç¿ëÇÏ¸é µÈ´Ù.
sulinux »ç¿ëÀÚÀÇ DB(sulinux)¸¦ »ý¼ºÇÑ´Ù.
-bash-3.2$ createdb -U sulinux sulinux -h 127.0.0.1
¾ÏÈ£: sulinux»ç¿ëÀÚÀǺñ¹Ð¹øÈ£
postgres »ç¿ëÀÚ·Î ÀüȯÇϰí, createdb ¸í·É¾î¸¦ »ç¿ëÇÏ¿© sulinux DB¸¦ »ý¼ºÇÑ´Ù. ¿©±â¼ -U ¿É¼ÇÀ» ÁÖ¾î »ç¿ëÀÚ¸¦ ÁöÁ¤ÇÏ¿´°í, -h ¿É¼ÇÀ¸·Î Á¢¼ÓÇÒ ¼ö ÀÖ´Â IP¸¦ ¸í½ÃÇÏ¿´´Ù. ÀÚ¼¼ÇÑ ¿É¼ÇÀº createdb --help¸¦ ÀÔ·ÂÇÏ¿© È®ÀÎÇÏÀÚ. ¸¸¾à DB¸¦ À߸ø ¸¸µé¾ú´Ù¸é, dropdb ¸í·É¾î¸¦ »ç¿ëÇÏ¿© »ý¼ºµÈ DB¸¦ »èÁ¦ÇÒ ¼ö ÀÖ´Ù.
psql ¸í·É¾î¸¦ ÅëÇÑ DBÁ¢¼Ó ¹× DBÈ®ÀÎ
-bash-3.2$ psql -U sulinux -h 127.0.0.1
Password for user sulinux: ºñ¹Ð¹øÈ£
psql (9.2.10)
Type "help" for help.
sulinux=> \l
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
sulinux | sulinux | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 |
template0 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | ko_KR.UTF-8 | ko_KR.UTF-8 | =c/postgres +
| | | | | postgres=CTc/postgres
(4 rows)
sulinux=>
psql¸í·É¾î¿¡ -U ¿É¼ÇÀ» ÁÖ¾î »ç¿ëÀÚ¸¦ ÁöÁ¤Çϰí, -h ¿É¼ÇÀ» ÁÖ¾î ¼¹ö IP¸¦ ÁöÁ¤ÇÏ¿´´Ù. DB Á¢¼Ó ÈÄ¿¡´Â "\l"À» ÀÔ·ÂÇÏ¿©, »ý¼ºµÈ DB¸®½ºÆ®¸¦ È®ÀÎÇÏ¿´´Ù. "\?" ÀÔ·ÂÇϸé ÀÚ¼¼ÇÑ µµ¿ò¸»À» ¾òÀ» ¼ö ÀÖ´Ù.
16.4.4. PHP¿¡ PostgreSQL¸ðµâ ¿Ã¸®±â
CentOS7¿¡¼ Á¦°øµÇ´Â PHPÆÐŰÁö¸¦ rpmÀ¸·Î ¼³Ä¡Çß´Ù¸é, PostgreSQL ¿¬µ¿À» À§ÇØ php-pgsql ÆÐŰÁö¸¦ Ãß°¡ ¼³Ä¡ÇØ¾ß ¿¬µ¿ÀÌ °¡´ÉÇÏ´Ù. yumÀ» ÀÌ¿ëÇÏ¿© ¼³Ä¡ÇÏÀÚ.
~]# yum -y install php-pgsql
PostgreSQL °ü·Ã ¸ðµâÀÇ ·Îµå ¿©ºÎ¸¦ È®ÀÎÇϱâ À§ÇØ ´ÙÀ½°ú °°ÀÌ phpÀÇ "-i" ¿É¼ÇÀ» »ç¿ëÇÏ¿© È®ÀÎÇÑ´Ù. ¾Æ·¡¿Í °°ÀÌ pgsql ¼³Á¤ÀÌ ÀÖ´Ù¸é Á¤»ó ¼³Ä¡µÈ °ÍÀÌ´Ù.
~]# php -i |grep pgsql
/etc/php.d/pdo_pgsql.ini,
/etc/php.d/pgsql.ini,
PDO drivers => mysql, pgsql, sqlite
pdo_pgsql
pgsql
pgsql.allow_persistent => On => On
pgsql.auto_reset_persistent => Off => Off
pgsql.ignore_notice => Off => Off
pgsql.log_notice => Off => Off
pgsql.max_links => Unlimited => Unlimited
pgsql.max_persistent => Unlimited => Unlimited
PHP ¸ðµâ Ãß°¡ ¼³Ä¡ ¶Ç´Â ¼³Á¤ º¯°æ ÈÄ¿¡´Â ²À Apache À¥¼¹ö¸¦ Àç½ÃÀÛ ÇØ¾ß ÇÑ´Ù. systemctlÀ¸·Î ´ÙÀ½°ú °°ÀÌ Apache À¥¼¹ö¸¦ Àç½ÃÀÛ ÇÏÀÚ.
~]# systemctl restart httpd
16.4.5. DB ¿¬µ¿ Å×½ºÆ®
PHP¿¡¼ DB¸¦ ¿¬µ¿ÇÏ´Â ¾ÆÁÖ °£´ÜÇÑ ÇÁ·Î±×·¥À» ÀÛ¼ºÇÏ¿© Å×½ºÆ®ÇØ º¸µµ·Ï ÇϰڴÙ. "/var/www/html/pgsqltest.php" ÆÄÀÏÀ» ´ÙÀ½°ú °°ÀÌ ÀÛ¼ºÇØ º»´Ù.
<?php
echo "DB Connect<BR>";
$pgsql = pg_Connect("host=127.0.0.1 dbname=sulinux user=sulinux password=ÆÐ½º¿öµå");
echo "Create sutable table<BR>";
$result = pg_exec($pgsql,"CREATE TABLE sutable ( a text )");
echo "Insert data<BR>";
$result = pg_exec($pgsql,"INSERT INTO sutable VALUES('DataBase Test')");
echo "Select data<BR>";
$result = pg_exec($pgsql,"SELECT * FROM sutable");
echo "Fetch data<BR>";
$row = pg_fetch_array($result);
echo "Result : <FONT color=red>".$row["a"]."</FONT><BR>";
echo "Drop sutable table<BR>";
$result = pg_exec($pgsql,"DROP TABLE sutable");
?>
À§ ÇÁ·Î±×·¥Àº PostgreSQL DB¿¡ ¿¬°áÇϰí, sulinux DB¸¦ ¼±ÅÃÇϸç, "sutable"À̶ó´Â Å×À̺íÀ» »ý¼ºÇÑ´Ù. »ý¼ºµÈ Å×ÀÌºí¿¡ "DataBase Test"¶ó´Â °ªÀ» »ðÀÔÇϰí, »ðÀÔµÈ µ¥ÀÌÅÍ´Â SELECT ±¸¹®À» »ç¿ëÇÏ¿© »Ì¾Æ³½´Ù. »Ì¾Æ³½ µ¥ÀÌÅ͸¦ ¹è¿·Î ¸¸µç ´ÙÀ½, Ãâ·ÂÇÑ´Ù. Ãâ·Â ÈÄ¿¡´Â "sutable"À» Á¦°ÅÇÏ´Â °£´ÜÇÑ ÇÁ·Î±×·¥ÀÌ´Ù. À¥ºê¶ó¿ìÀú¿¡¼ "http://localhost/ pgsqltest.php"¸¦ È£ÃâÇÏ¸é ´ÙÀ½°ú °°Àº °á°ú¸¦ º¼ ¼ö ÀÖÀ» °ÍÀÌ´Ù.