¥Ê¥Ó¥²¡¼¥·¥ç¥ó
FrontPage
FreeBSD
Linux
Citrix XenServer
VMware ESXi
Unix¥³¥Þ¥ó¥É
MySQL
PostgreSQL
Windows
CMS/CRM
¥¯¥é¥¦¥É
ÍѸì
PHP¥¹¥¯¥ê¥×¥È
Bash¥·¥§¥ë¥¹¥¯¥ê¥×¥È
Ruby¥¹¥¯¥ê¥×¥È
³«È¯Æüµ
¸¡º÷
¥Ä¡¼¥ë¥Ü¥Ã¥¯¥¹
¿·¤·¤¤¥Ú¡¼¥¸¤ÎºîÀ®
¥Ð¥Ã¥¯¥¢¥Ã¥×¤Îɽ¼¨
³°Éô¤Î¥ê¥ó¥¯¸µ
ºÇ¶á¹¹¿·¤·¤¿¥Ú¡¼¥¸
Á´¥Ú¡¼¥¸
¥Ø¥ë¥×
Åà·ë
¥¢¥Ã¥×¥í¡¼¥É
¥Ú¡¼¥¸Ì¾¤ÎÊѹ¹
ºÇ¿·¤Î40·ï
2016-08-17
Linux/NagiosQL
Linux/CentOS¥Á¥å¡¼¥Ë¥ó¥°
2016-02-23
Linux/¥½¥Õ¥È¥¦¥§¥¢RAIDÉüµì¼ê½ç
2016-02-18
FreeBSD/DHCP
FreeBSD/squid
2016-02-15
Unix¥³¥Þ¥ó¥É/file
2016-02-05
FreeBSD/DynaBook
FreeBSD/Linux compatibility
2016-01-21
PHP¥¹¥¯¥ê¥×¥È/PEAR DB¥¤¥ó¥¹¥È¡¼¥ë¤Ç¤¤Ê¤¤
PHP¥¹¥¯¥ê¥×¥È/PEAR DB
2016-01-20
PHP/¥Õ¥ì¡¼¥à¥ï¡¼¥¯/Zend Framework
PHP/¥Õ¥ì¡¼¥à¥ï¡¼¥¯
FreeBSD/Postfix+amavisd+clamav
2016-01-15
FreeBSD/sl
2015-12-04
Unix¥³¥Þ¥ó¥É/dig
Unix¥³¥Þ¥ó¥É/nslookup
Unix¥³¥Þ¥ó¥É/traceroute
Linux/rsync
FreeBSD/rsync
Unix¥³¥Þ¥ó¥É/vipw
2015-11-25
Linux/Postfix+MySQL+Postfix Admin¤Ë¤è¤ë¥Ð¡¼¥Á¥ã¥ëSMTP¥µ¡¼¥Ð¤Î¹½ÃÛ
Linux/Postfix + postgrey
2015-11-24
Linux/Postfix + amavisd + clamav
2015-05-25
Unix¥³¥Þ¥ó¥É/ifconfig
2015-05-23
Unix¥³¥Þ¥ó¥É/netstat
Unix¥³¥Þ¥ó¥É/route
Unix¥³¥Þ¥ó¥É/lsof
Unix¥³¥Þ¥ó¥É/disown
Unix¥³¥Þ¥ó¥É/time
Unix¥³¥Þ¥ó¥É/crontab
2015-05-22
Unix¥³¥Þ¥ó¥É/nohup
Unix¥³¥Þ¥ó¥É/fg¤Èbg
Unix¥³¥Þ¥ó¥É/nice
Unix¥³¥Þ¥ó¥É/kill
Unix¥³¥Þ¥ó¥É/jobs
Unix¥³¥Þ¥ó¥É/restor
Unix¥³¥Þ¥ó¥É/dump
Unix¥³¥Þ¥ó¥É/uncompress
Unix¥³¥Þ¥ó¥É/compress
Unix¥³¥Þ¥ó¥É/zcat
total:
15482
today:
1
yesterday:
0
now:
1
ËÜʸ
¥Î¡¼¥È
?
ÊÔ½¸
º¹Ê¬
°ìÍ÷
FreeBSD/PostgreSQL¥³¥Þ¥ó¥É ¤ÎÊÔ½¸
*PostgreSQL¥³¥Þ¥ó¥É [#ya542f07] [[dokuwiki.fl8.jpžºÜºÑ>http://dokuwiki.fl8.jp/doku.php/02_freebsd/04_database/02_posgresql/postgresql%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89]] #contents **¥Ñ¥¹¥ï¡¼¥ÉÉÕ¤Ç¥í¥°¥¤¥ó¤¹¤ë [#h761c778] $ psql ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾ -U ¥æ¡¼¥¶¡¼Ì¾ -W **PostgreSQL¤Î¥æ¡¼¥¶ºîÀ® [#k7be7697] PostgreSQL¤Ë¥æ¡¼¥¶¤òcreateuser¥³¥Þ¥ó¥É¤ò»ÈÍѤ·¤ÆºîÀ®¤¹¤ë¤¬~ PostgreSQL¤Ë¥æ¡¼¥¶ºîÀ®¤¹¤ë¤Î¤Ï¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¤Ç¹Ô¤Ê¤¤¤Þ¤¹¡£~ ¢¨¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¤È¤Ïpg_shadow ¥Æ¡¼¥Ö¥ë¤Ç usesuper ¤¬ÀßÄꤵ¤ì¤Æ¤¤¤ë¥æ¡¼¥¶¤ò»Ø¤·¤Æ¤¤¤ë¡£~ createuser -a -d -U postgres -P ¥æ¡¼¥¶¡¼Ì¾ -¥ª¥×¥·¥ç¥ó |-a(--adduser)|¿·¤·¤¤PostgreSQL¥æ¡¼¥¶¤Ï¡¢Â¾¤Î¥æ¡¼¥¶¤òºîÀ®¤¹¤ë¤³¤È¤¬µö²Ä¡£| |~|¢¨¤³¤Î¥ª¥×¥·¥ç¥ó¤òÉÕ¤±¤ë¤È¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¡£ | |-d(--createdb)|¿·¤·¤¤PostgreSQL¥æ¡¼¥¶¤Ï¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¤ÎºîÀ®¤¬µö²Ä¤µ¤ì¤Þ¤¹¡£ | |-U|[Àܳ¥æ¡¼¥¶Ì¾]| |(--username)|PostgreSQL¤ËÀܳ¤¹¤ë¥æ¡¼¥¶Ì¾¤ò»ØÄꤷ¤Þ¤¹¡£| |~|¾åµ¥³¥Þ¥ó¥É¤Ç¤Ïpostgres(¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶)¤ÇÀܳ¤·¤Æ¤¤¤Þ¤¹¡£ | |-P(--pwprompt)|¿·¤·¤¤PostgreSQL¥æ¡¼¥¶¤Î¥Ñ¥¹¥ï¡¼¥É¤Î¥×¥í¥ó¥×¥È¤òɽ¼¨¤·¤Þ¤¹¡£ | ***¥æ¡¼¥¶¤Î³Îǧ [#udce6dc8] postgres=# select * from pg_shadow ; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+-------------------------------------+----------+------------ postgres | 1 | t | t | t | | | matsui | 100 | t | f | f | md5a88cf805de7149ac382f054f03935580 | | (2 rows) **PostgreSQL¤Î¥æ¡¼¥¶ºï½ü [#la0580a3] dropuser ¥æ¡¼¥¶Ì¾ ¡ÊÎã¡Ë dropuser matsui **PostgreSQL¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤ÎºîÀ®(createdb¤Î»ÈÍÑ) [#l5cf8518] createdb -E [¥¨¥ó¥³¡¼¥É] -O [¥ª¡¼¥Ê¡¼] -U [Àܳ¥æ¡¼¥¶Ì¾] [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] -¥ª¥×¥·¥ç¥ó |-E(--encording)|[¥¨¥ó¥³¡¼¥Ç¥£¥ó¥°Ì¾]|ºîÀ®¤¹¤ë¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Ç»ÈÍѤ¹¤ëʸ»ú¥¨¥ó¥³¡¼¥ÉÊý¼°¤ò»ØÄꤷ¤Þ¤¹¡£| |-O(--owner)|[¥ª¡¼¥Ê̾]|ºîÀ®¤¹¤ë¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î½êͼԤȤʤë¥æ¡¼¥¶¤ò»ØÄꤷ¤Þ¤¹¡£| |-U(--username)|[Àܳ¥æ¡¼¥¶Ì¾]|PostgreSQL¤ËÀܳ¤¹¤ë¥æ¡¼¥¶Ì¾¤ò»ØÄꤷ¤Þ¤¹¡£| **PostgreSQL¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤Îºï½ü(dropdb) [#w30948dc] dropdb [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] **¥Ð¥Ã¥¯¥¢¥Ã¥×¡¦¥ê¥¹¥È¥¢(pg_dump) [#i4aa29c2] [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾]¤Ë¤Æ¥Ð¥Ã¥¯¥¢¥Ã¥×¤ò¼è¤ë¤³¤È¤¬²Äǽ¡£¤½¤ÎºÝ¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î½êͼԤËsu¤·¤Æ¤ª¤¤¤¿Êý¤¬¤è¤¤¡£ -¼ç¤Ê¥ª¥×¥·¥ç¥ó -a ¥Ç¡¼¥¿¤Î¤ß¤ò¥À¥ó¥× -b ¥é¡¼¥¸¥ª¥Ö¥¸¥§¥¯¥È¤â¥À¥ó¥×¤¹¤ë¡£¡ÊÃí: ¥Ç¥Õ¥©¥ë¥È¤Ç¤Ï¥À¥ó¥×¤µ¤ì¤Þ¤»¤ó¡Ë -d INSERT¥³¥Þ¥ó¥É¤Ë¤Æ¥À¥ó¥×¡ÊÄ̾ï¤ÏCOPY¥³¥Þ¥ó¥É) -D ¥«¥é¥à̾¤ò´Þ¤á¤¿INSERT¥³¥Þ¥ó¥É¤Ë¤Æ¥À¥ó¥× -s ¥Æ¡¼¥Ö¥ë¤Î¤ß¥À¥ó¥× -t ÆÃÄê¤Î¥Æ¡¼¥Ö¥ë¤Î¤ß¥À¥ó¥× -F ¥¢¥¦¥È¥×¥Ã¥È¥Õ¥¡¥¤¥ë¤Î·Á¼°¤ò»ØÄê¡Êc¤¬¥«¥¹¥¿¥à¡¢t¤¬tar¡¢p¤¬¥Æ¥¥¹¥È) -Îã(¥Ð¥Ã¥¯¥¢¥Ã¥×) pg_dump database_name > database_name.sql -Îã(¥ê¥¹¥È¥¢) createdb ¤Ë¤Ædatabase¤òºîÀ®¤·¡¢Æ±¤¸¥æ¡¼¥¶¤Ç°Ê²¼¤Î¤è¤¦¤Ë¤¹¤ì¤Ð¤è¤¤¡£ psql database_name < database_name.sql &color(Red){¢¨¤¹¤Ç¤Ëdatabase¤¬Â¸ºß¤¹¤ë¾ì¹ç¤Ï¡¢Àè¤Ëdropdb¤¹¤ëɬÍפ¬¤¢¤ë¡£}; **¥Ð¥Ã¥¯¥¢¥Ã¥×¡¦¥ê¥¹¥È¥¢(pg_dumpall) [#m31f0ec8] #¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥Ð¥Ã¥¯¥¢¥Ã¥× $ pg_dumpall > allbackup.db #¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥ê¥¹¥È¥¢ ¡Êpg_dump¤È¤Ï°ã¤¤¥æ¡¼¥¶¤Ê¤É¤âºîÀ®¤·¤Æ¤¯¤ì¤Þ¤¹¡Ë $ psql -f allbackup.db template1 **PostgreSQL¤Ç¤Î¥¼¥í¥Ñ¥Ç¥£¥ó¥° [#f1bced1b] # select lpad(id,4,'0') as id from t_images order by id desc; ¤³¤ó¤Ê´¶¤¸¤Çɽ¼¨¤µ¤ì¤ë id ------ 0017 0015 0014 0012 0011 0010 0009 0008 0007 0006 0005 0003 0002 0001 (14 rows) -PostgreSQL¤Î¥æ¡¼¥¶ºîÀ®¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹ºîÀ®Åù¤Î´ðËÜÁàºî~ [[http://www.develop-memo.com/database/postgresql/postgresqloperate.html]] -PostgreSQL¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥³¥Þ¥ó¥É~ [[http://y-kit.jp/saba/xp/postgrecmd.htm]] **[DEFAULT¤ÎÀßÄꡦÊѹ¹] [#xe8d3e10] ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 0; **[DEFAULT¤Îºï½ü] [#m4e0ce32] ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT; **¥Õ¥£¡¼¥ë¥É̾¤ÎÊѹ¹ [#l6ebf26c] ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) RENAME after_fild TO (Êѹ¹¸å¥Õ¥£¡¼¥ë¥É̾) *PostgreSQL¤Î¥³¥Þ¥ó¥É [#h9ca9bbb] ¥Ç¡¼¥¿¥Ù¡¼¥¹ºîÀ® ¡¡ createdb [-D ¥Ñ¥¹] [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] ¥Ç¡¼¥¿¥Ù¡¼¥¹ºï½ü ¡¡ dropdb [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] ¥¤¥ó¥¿¥é¥¯¥Æ¥£¥Ö¤ÊSQL¼Â¹Ô ¡¡ psql [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] SQL¼Â¹Ô ¡¡ psql [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] < SQL¥Õ¥¡¥¤¥ë ¥À¥ó¥× ¡¡ pg_dumpall > ¥Õ¥¡¥¤¥ë̾ pg_dump [-t ¥Æ¡¼¥Ö¥ë̾] ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾ > ¥Õ¥¡¥¤¥ë̾ ¥ì¥¹¥È¥¢ ¡¡ psql template1 < ¥Õ¥¡¥¤¥ë̾ psql -d template1 -f ¥Õ¥¡¥¤¥ë̾ pg_restore [ -d ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] ¥Õ¥¡¥¤¥ë̾ ¥¨¥¯¥¹¥Ý¡¼¥È ¡¡ psql [-d ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] -t -c "SQL" > ¥Õ¥¡¥¤¥ë̾ **psql¤Î¥³¥Þ¥ó¥É [#sbb59674] ¥³¥Þ¥ó¥É°ìÍ÷ ¡¡ \? ½ªÎ» ¡¡ \q ¥Ç¡¼¥¿¥Ù¡¼¥¹°ìÍ÷ ¡¡ \l ÁȤ߹þ¤ß´Ø¿ô°ìÍ÷ ¡¡ \df ¥Æ¡¼¥Ö¥ë°ìÍ÷ ¡¡ \d ½¸Ìó´Ø¿ô°ìÍ÷ ¡¡ \da ¥Æ¡¼¥Ö¥ë¾ÜºÙ ¡¡ \d ¥Æ¡¼¥Ö¥ë̾ ʸ»ú¥³¡¼¥Éɽ¼¨ ¡¡ \encoding ¥Æ¡¼¥Ö¥ë¸¢¸Â ¡¡ \z ¡¡ **SQL [#ifcce372] PostgreSQL¤Î¥Ð¡¼¥¸¥ç¥ó ¡¡ SELECT version(); -------------------------------------------------------------------------------- ¥Æ¡¼¥Ö¥ë¤ÎºîÀ® ¡¡ CREATE TABLE ¥Æ¡¼¥Ö¥ë̾; ¥Æ¡¼¥Ö¥ë¤Îºï½ü ¡¡ DROP TABLE ¥Æ¡¼¥Ö¥ë̾; ¥Æ¡¼¥Ö¥ë̾¤ÎÊѹ¹ ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ RENAME TO ¿·¥Æ¡¼¥Ö¥ë̾; -------------------------------------------------------------------------------- Îó¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ADD COLUMN Îó¤ÎÄêµÁ; Îó¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ DROP COLUMN Îó̾; Îó̾¤ÎÊѹ¹ ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ RENAME COLUMN Îó̾ TO ¿·Îó̾; -------------------------------------------------------------------------------- À©Ìó¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ADD CONSTRAINT À©Ìó̾ UNIQUE ( Îó̾ [, Îó̾]... ); NOT-NULLÀ©Ìó¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ SET NOT NULL; À©Ìó¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ DROP CONSTRAINT À©Ìó̾; NOT-NULLÀ©Ìó¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ DROP NOT NULL; ¥Ç¥Õ¥©¥ë¥È¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ SET DEFAULT ¥Ç¥Õ¥©¥ë¥ÈÃÍ; ¥Ç¥Õ¥©¥ë¥È¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ DROP DEFAULT; **postgreSQL¤Ç¥Ç¡¼¥¿·¿¤ÎÊѹ¹ [#n159e845] ¢¨postgreSQL¤Ç¤Ï¥Ç¡¼¥¿·¿¤ÎÊѹ¹¤¬¤Ç¤¤Þ¤»¤ó¡£~ ¿·¤¿¤Ë¥Õ¥£¡¼¥ë¥É¤òºî¤Ã¤Æ¥Ç¡¼¥¿¤ò¼Ì¤·¤Æ¥Ç¡¼¥¿¥Õ¥£¡¼¥ë¥É¤òÊѹ¹¤¹¤ë¤È¤¤¤¦¤á¤ó¤É¤¯¤µ¤¤½èÍý¤¬¤¤¤ê¤Þ¤¹¡£~ ¡ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) ADD COLUMN after_fild ¡ÊÊѹ¹¸å¥Ç¡¼¥¿·¿¡Ë ¢UPDATE (¥Æ¡¼¥Ö¥ë̾) SET after_fild = (Êѹ¹Á°¥Õ¥£¡¼¥ë¥É̾) £ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) DROP (Êѹ¹Á°¥Õ¥£¡¼¥ë¥É̾); ¤ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) RENAME after_fild TO (Êѹ¹¸å¥Õ¥£¡¼¥ë¥É̾) **postgresql¥Á¥Ã¥×¥¹ [#y4093422] [[http://ash.jp/db/pg_tips.htm>http://ash.jp/db/pg_tips.htm]]
¥¿¥¤¥à¥¹¥¿¥ó¥×¤òÊѹ¹¤·¤Ê¤¤
*PostgreSQL¥³¥Þ¥ó¥É [#ya542f07] [[dokuwiki.fl8.jpžºÜºÑ>http://dokuwiki.fl8.jp/doku.php/02_freebsd/04_database/02_posgresql/postgresql%E3%82%B3%E3%83%9E%E3%83%B3%E3%83%89]] #contents **¥Ñ¥¹¥ï¡¼¥ÉÉÕ¤Ç¥í¥°¥¤¥ó¤¹¤ë [#h761c778] $ psql ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾ -U ¥æ¡¼¥¶¡¼Ì¾ -W **PostgreSQL¤Î¥æ¡¼¥¶ºîÀ® [#k7be7697] PostgreSQL¤Ë¥æ¡¼¥¶¤òcreateuser¥³¥Þ¥ó¥É¤ò»ÈÍѤ·¤ÆºîÀ®¤¹¤ë¤¬~ PostgreSQL¤Ë¥æ¡¼¥¶ºîÀ®¤¹¤ë¤Î¤Ï¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¤Ç¹Ô¤Ê¤¤¤Þ¤¹¡£~ ¢¨¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¤È¤Ïpg_shadow ¥Æ¡¼¥Ö¥ë¤Ç usesuper ¤¬ÀßÄꤵ¤ì¤Æ¤¤¤ë¥æ¡¼¥¶¤ò»Ø¤·¤Æ¤¤¤ë¡£~ createuser -a -d -U postgres -P ¥æ¡¼¥¶¡¼Ì¾ -¥ª¥×¥·¥ç¥ó |-a(--adduser)|¿·¤·¤¤PostgreSQL¥æ¡¼¥¶¤Ï¡¢Â¾¤Î¥æ¡¼¥¶¤òºîÀ®¤¹¤ë¤³¤È¤¬µö²Ä¡£| |~|¢¨¤³¤Î¥ª¥×¥·¥ç¥ó¤òÉÕ¤±¤ë¤È¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶¡£ | |-d(--createdb)|¿·¤·¤¤PostgreSQL¥æ¡¼¥¶¤Ï¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¤ÎºîÀ®¤¬µö²Ä¤µ¤ì¤Þ¤¹¡£ | |-U|[Àܳ¥æ¡¼¥¶Ì¾]| |(--username)|PostgreSQL¤ËÀܳ¤¹¤ë¥æ¡¼¥¶Ì¾¤ò»ØÄꤷ¤Þ¤¹¡£| |~|¾åµ¥³¥Þ¥ó¥É¤Ç¤Ïpostgres(¥¹¡¼¥Ñ¡¼¥æ¡¼¥¶)¤ÇÀܳ¤·¤Æ¤¤¤Þ¤¹¡£ | |-P(--pwprompt)|¿·¤·¤¤PostgreSQL¥æ¡¼¥¶¤Î¥Ñ¥¹¥ï¡¼¥É¤Î¥×¥í¥ó¥×¥È¤òɽ¼¨¤·¤Þ¤¹¡£ | ***¥æ¡¼¥¶¤Î³Îǧ [#udce6dc8] postgres=# select * from pg_shadow ; usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig ----------+----------+-------------+----------+-----------+-------------------------------------+----------+------------ postgres | 1 | t | t | t | | | matsui | 100 | t | f | f | md5a88cf805de7149ac382f054f03935580 | | (2 rows) **PostgreSQL¤Î¥æ¡¼¥¶ºï½ü [#la0580a3] dropuser ¥æ¡¼¥¶Ì¾ ¡ÊÎã¡Ë dropuser matsui **PostgreSQL¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤ÎºîÀ®(createdb¤Î»ÈÍÑ) [#l5cf8518] createdb -E [¥¨¥ó¥³¡¼¥É] -O [¥ª¡¼¥Ê¡¼] -U [Àܳ¥æ¡¼¥¶Ì¾] [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] -¥ª¥×¥·¥ç¥ó |-E(--encording)|[¥¨¥ó¥³¡¼¥Ç¥£¥ó¥°Ì¾]|ºîÀ®¤¹¤ë¥Ç¡¼¥¿¥Ù¡¼¥¹Æâ¤Ç»ÈÍѤ¹¤ëʸ»ú¥¨¥ó¥³¡¼¥ÉÊý¼°¤ò»ØÄꤷ¤Þ¤¹¡£| |-O(--owner)|[¥ª¡¼¥Ê̾]|ºîÀ®¤¹¤ë¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î½êͼԤȤʤë¥æ¡¼¥¶¤ò»ØÄꤷ¤Þ¤¹¡£| |-U(--username)|[Àܳ¥æ¡¼¥¶Ì¾]|PostgreSQL¤ËÀܳ¤¹¤ë¥æ¡¼¥¶Ì¾¤ò»ØÄꤷ¤Þ¤¹¡£| **PostgreSQL¤Î¥Ç¡¼¥¿¥Ù¡¼¥¹¤Îºï½ü(dropdb) [#w30948dc] dropdb [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] **¥Ð¥Ã¥¯¥¢¥Ã¥×¡¦¥ê¥¹¥È¥¢(pg_dump) [#i4aa29c2] [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾]¤Ë¤Æ¥Ð¥Ã¥¯¥¢¥Ã¥×¤ò¼è¤ë¤³¤È¤¬²Äǽ¡£¤½¤ÎºÝ¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î½êͼԤËsu¤·¤Æ¤ª¤¤¤¿Êý¤¬¤è¤¤¡£ -¼ç¤Ê¥ª¥×¥·¥ç¥ó -a ¥Ç¡¼¥¿¤Î¤ß¤ò¥À¥ó¥× -b ¥é¡¼¥¸¥ª¥Ö¥¸¥§¥¯¥È¤â¥À¥ó¥×¤¹¤ë¡£¡ÊÃí: ¥Ç¥Õ¥©¥ë¥È¤Ç¤Ï¥À¥ó¥×¤µ¤ì¤Þ¤»¤ó¡Ë -d INSERT¥³¥Þ¥ó¥É¤Ë¤Æ¥À¥ó¥×¡ÊÄ̾ï¤ÏCOPY¥³¥Þ¥ó¥É) -D ¥«¥é¥à̾¤ò´Þ¤á¤¿INSERT¥³¥Þ¥ó¥É¤Ë¤Æ¥À¥ó¥× -s ¥Æ¡¼¥Ö¥ë¤Î¤ß¥À¥ó¥× -t ÆÃÄê¤Î¥Æ¡¼¥Ö¥ë¤Î¤ß¥À¥ó¥× -F ¥¢¥¦¥È¥×¥Ã¥È¥Õ¥¡¥¤¥ë¤Î·Á¼°¤ò»ØÄê¡Êc¤¬¥«¥¹¥¿¥à¡¢t¤¬tar¡¢p¤¬¥Æ¥¥¹¥È) -Îã(¥Ð¥Ã¥¯¥¢¥Ã¥×) pg_dump database_name > database_name.sql -Îã(¥ê¥¹¥È¥¢) createdb ¤Ë¤Ædatabase¤òºîÀ®¤·¡¢Æ±¤¸¥æ¡¼¥¶¤Ç°Ê²¼¤Î¤è¤¦¤Ë¤¹¤ì¤Ð¤è¤¤¡£ psql database_name < database_name.sql &color(Red){¢¨¤¹¤Ç¤Ëdatabase¤¬Â¸ºß¤¹¤ë¾ì¹ç¤Ï¡¢Àè¤Ëdropdb¤¹¤ëɬÍפ¬¤¢¤ë¡£}; **¥Ð¥Ã¥¯¥¢¥Ã¥×¡¦¥ê¥¹¥È¥¢(pg_dumpall) [#m31f0ec8] #¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥Ð¥Ã¥¯¥¢¥Ã¥× $ pg_dumpall > allbackup.db #¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥ê¥¹¥È¥¢ ¡Êpg_dump¤È¤Ï°ã¤¤¥æ¡¼¥¶¤Ê¤É¤âºîÀ®¤·¤Æ¤¯¤ì¤Þ¤¹¡Ë $ psql -f allbackup.db template1 **PostgreSQL¤Ç¤Î¥¼¥í¥Ñ¥Ç¥£¥ó¥° [#f1bced1b] # select lpad(id,4,'0') as id from t_images order by id desc; ¤³¤ó¤Ê´¶¤¸¤Çɽ¼¨¤µ¤ì¤ë id ------ 0017 0015 0014 0012 0011 0010 0009 0008 0007 0006 0005 0003 0002 0001 (14 rows) -PostgreSQL¤Î¥æ¡¼¥¶ºîÀ®¡¢¥Ç¡¼¥¿¥Ù¡¼¥¹ºîÀ®Åù¤Î´ðËÜÁàºî~ [[http://www.develop-memo.com/database/postgresql/postgresqloperate.html]] -PostgreSQL¥Ç¡¼¥¿¥Ù¡¼¥¹¤Î¥³¥Þ¥ó¥É~ [[http://y-kit.jp/saba/xp/postgrecmd.htm]] **[DEFAULT¤ÎÀßÄꡦÊѹ¹] [#xe8d3e10] ALTER TABLE table_name ALTER COLUMN column_name SET DEFAULT 0; **[DEFAULT¤Îºï½ü] [#m4e0ce32] ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT; **¥Õ¥£¡¼¥ë¥É̾¤ÎÊѹ¹ [#l6ebf26c] ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) RENAME after_fild TO (Êѹ¹¸å¥Õ¥£¡¼¥ë¥É̾) *PostgreSQL¤Î¥³¥Þ¥ó¥É [#h9ca9bbb] ¥Ç¡¼¥¿¥Ù¡¼¥¹ºîÀ® ¡¡ createdb [-D ¥Ñ¥¹] [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] ¥Ç¡¼¥¿¥Ù¡¼¥¹ºï½ü ¡¡ dropdb [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] ¥¤¥ó¥¿¥é¥¯¥Æ¥£¥Ö¤ÊSQL¼Â¹Ô ¡¡ psql [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] SQL¼Â¹Ô ¡¡ psql [¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] < SQL¥Õ¥¡¥¤¥ë ¥À¥ó¥× ¡¡ pg_dumpall > ¥Õ¥¡¥¤¥ë̾ pg_dump [-t ¥Æ¡¼¥Ö¥ë̾] ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾ > ¥Õ¥¡¥¤¥ë̾ ¥ì¥¹¥È¥¢ ¡¡ psql template1 < ¥Õ¥¡¥¤¥ë̾ psql -d template1 -f ¥Õ¥¡¥¤¥ë̾ pg_restore [ -d ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] ¥Õ¥¡¥¤¥ë̾ ¥¨¥¯¥¹¥Ý¡¼¥È ¡¡ psql [-d ¥Ç¡¼¥¿¥Ù¡¼¥¹Ì¾] -t -c "SQL" > ¥Õ¥¡¥¤¥ë̾ **psql¤Î¥³¥Þ¥ó¥É [#sbb59674] ¥³¥Þ¥ó¥É°ìÍ÷ ¡¡ \? ½ªÎ» ¡¡ \q ¥Ç¡¼¥¿¥Ù¡¼¥¹°ìÍ÷ ¡¡ \l ÁȤ߹þ¤ß´Ø¿ô°ìÍ÷ ¡¡ \df ¥Æ¡¼¥Ö¥ë°ìÍ÷ ¡¡ \d ½¸Ìó´Ø¿ô°ìÍ÷ ¡¡ \da ¥Æ¡¼¥Ö¥ë¾ÜºÙ ¡¡ \d ¥Æ¡¼¥Ö¥ë̾ ʸ»ú¥³¡¼¥Éɽ¼¨ ¡¡ \encoding ¥Æ¡¼¥Ö¥ë¸¢¸Â ¡¡ \z ¡¡ **SQL [#ifcce372] PostgreSQL¤Î¥Ð¡¼¥¸¥ç¥ó ¡¡ SELECT version(); -------------------------------------------------------------------------------- ¥Æ¡¼¥Ö¥ë¤ÎºîÀ® ¡¡ CREATE TABLE ¥Æ¡¼¥Ö¥ë̾; ¥Æ¡¼¥Ö¥ë¤Îºï½ü ¡¡ DROP TABLE ¥Æ¡¼¥Ö¥ë̾; ¥Æ¡¼¥Ö¥ë̾¤ÎÊѹ¹ ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ RENAME TO ¿·¥Æ¡¼¥Ö¥ë̾; -------------------------------------------------------------------------------- Îó¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ADD COLUMN Îó¤ÎÄêµÁ; Îó¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ DROP COLUMN Îó̾; Îó̾¤ÎÊѹ¹ ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ RENAME COLUMN Îó̾ TO ¿·Îó̾; -------------------------------------------------------------------------------- À©Ìó¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ADD CONSTRAINT À©Ìó̾ UNIQUE ( Îó̾ [, Îó̾]... ); NOT-NULLÀ©Ìó¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ SET NOT NULL; À©Ìó¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ DROP CONSTRAINT À©Ìó̾; NOT-NULLÀ©Ìó¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ DROP NOT NULL; ¥Ç¥Õ¥©¥ë¥È¤ÎÄɲà ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ SET DEFAULT ¥Ç¥Õ¥©¥ë¥ÈÃÍ; ¥Ç¥Õ¥©¥ë¥È¤Îºï½ü ¡¡ ALTER TABLE ¥Æ¡¼¥Ö¥ë̾ ALTER COLUMN Îó̾ DROP DEFAULT; **postgreSQL¤Ç¥Ç¡¼¥¿·¿¤ÎÊѹ¹ [#n159e845] ¢¨postgreSQL¤Ç¤Ï¥Ç¡¼¥¿·¿¤ÎÊѹ¹¤¬¤Ç¤¤Þ¤»¤ó¡£~ ¿·¤¿¤Ë¥Õ¥£¡¼¥ë¥É¤òºî¤Ã¤Æ¥Ç¡¼¥¿¤ò¼Ì¤·¤Æ¥Ç¡¼¥¿¥Õ¥£¡¼¥ë¥É¤òÊѹ¹¤¹¤ë¤È¤¤¤¦¤á¤ó¤É¤¯¤µ¤¤½èÍý¤¬¤¤¤ê¤Þ¤¹¡£~ ¡ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) ADD COLUMN after_fild ¡ÊÊѹ¹¸å¥Ç¡¼¥¿·¿¡Ë ¢UPDATE (¥Æ¡¼¥Ö¥ë̾) SET after_fild = (Êѹ¹Á°¥Õ¥£¡¼¥ë¥É̾) £ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) DROP (Êѹ¹Á°¥Õ¥£¡¼¥ë¥É̾); ¤ALTER TABLE (¥Æ¡¼¥Ö¥ë̾) RENAME after_fild TO (Êѹ¹¸å¥Õ¥£¡¼¥ë¥É̾) **postgresql¥Á¥Ã¥×¥¹ [#y4093422] [[http://ash.jp/db/pg_tips.htm>http://ash.jp/db/pg_tips.htm]]
¥Æ¥¥¹¥ÈÀ°·Á¤Î¥ë¡¼¥ë¤òɽ¼¨¤¹¤ë
¥í¥°¥¤¥ó¤Þ¤¿¤Ï¥¢¥«¥¦¥ó¥ÈºîÀ®