Apr 27 2010

Install Postgresql and pgadmin3 in Ubuntu

분류: Database, PostgreSQL, Server최 태현 @ 6:21 오후

PostgreSQL 8.2 version will be installed in Ubuntu 7.10 (Gutsy Gibbon)

sudo apt-get install postgresql-8.2 postgresql-client-8.2 postgresql-contrib-8.2

sudo apt-get install pgadmin3

This will install the database server/client, some extra utility scripts and the pgAdmin GUI application for working with the database.

Configuring postgresql in Ubuntu

Now we need to reset the password for the ‘postgres’ admin account for the server

sudo su postgres -c psql template1
template1=# ALTER USER postgres WITH PASSWORD ‘password’;
template1=# \q

That alters the password for within the database, now we need to do the same for the unix user ‘postgres’:

sudo passwd -d postgres

sudo su postgres -c passwd

Now enter the same password that you used previously.

from here on in we can use both pgAdmin and command-line access (as the postgres user) to run the database server. But before you jump into pgAdmin we should set-up the PostgreSQL admin pack that enables better logging and monitoring within pgAdmin. Run the following at the command-line

we need to open up the server so that we can access and use it remotely - unless you only want to access the database on the local machine. To do this, first, we need to edit the postgresql.conf file:

sudo gedit /etc/postgresql/8.2/main/postgresql.conf

Now, to edit a couple of lines in the ‘Connections and Authentication’ section

Change the line

#listen_addresses = ‘localhost’

to

listen_addresses = ‘*’

and also change the line

#password_encryption = on

to

password_encryption = on

Then save the file and close gedit.

Now for the final step, we must define who can access the server. This is all done using the pg_hba.conf file.

sudo gedit /etc/postgresql/8.2/main/pg_hba.conf

Comment out, or delete the current contents of the file, then add this text to the bottom of the file

DO NOT DISABLE!
# If you change this first entry you will need to make sure that the
# database
# super user can access the database using some other method.
# Noninteractive
# access to all databases is required during automatic maintenance
# (autovacuum, daily cronjob, replication, and similar tasks).
#
# Database administrative login by UNIX sockets
local all postgres ident sameuser
# TYPE DATABASE USER CIDR-ADDRESS METHOD

# “local” is for Unix domain socket connections only
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
host all all [ip address] [subnet mask] md5

and in the last line, add in your subnet mask (i.e. 255.255.255.0) and the IP address of the machine that you would like to access your server (i.e. 138.250.192.115). However, if you would like to enable access to a range of IP addresses, just substitute the last number for a zero and all machines within that range will be allowed access (i.e. 138.250.192.0 would allow all machines with an IP address 138.250.192.x to use the database server).

That’s it, now all you have to do is restart the server

sudo /etc/init.d/postgresql-8.2 restart

That’s it you can start using postgresql in Ubuntu

Create a Database from command line

You can also use pgadmin3 for all postgresql related

To create a database with a user that have full rights on the database, use the following command

sudo -u postgres createuser -D -A -P mynewuser

sudo -u postgres createdb -O mynewuser mydatabase


Apr 22 2010

phpPgAdmin 사용하기

분류: .Mac, Database, How to, PHP, PostgreSQL최 태현 @ 5:50 오후

If your PostgreSQL server is running on the same server as the webserver, phpPgAdmin’s default configuration will work “out-of-the-box”; if not, you’ll need to edit the configuration file located at conf/config.inc.php. It should be self-explanatory. There’s also a “backup” copy of the installation file “conf/config.inc.php-dist“.

One problem which occurs quite often is the error message “Login disallowed for security reasons“, which occurs when trying to login as a user with an empty password, a common situation on (presumably well-protected) development machines. To enable this kind of login, set the option $conf['owned_only'] in conf/config.inc.php to true.

‘extra_login_security’ =

If extra login security is true, then logins via phpPgAdmin with no password or certain usernames (pgsql, postgres, root, administrator)
will be denied.


Apr 20 2010

12 Ways Libraries Are Good for the Country

분류: Library최 태현 @ 5:30 오후

MOST AMERICANS KNOW what they can expect from a library. And librarians know what it takes to provide comprehensive access to every recorded detail of human existence. It takes support.

Libraries are ready when they are needed, ready to enrich our minds and defend our right to know, just as other institutions protect our safety and property. Without sound minds, however, the American dream of safe streets and secure homes will never be fulfilled.

Libraries safeguard our freedom and keep democracy healthy. To library advocates everywhere—Friends, trustees, board members, patrons, and volunteers—American Libraries offers this gift of 12 ideals toward which we strive. It will take all of us, in a spirit of pride and freedom, to maintain libraries as a living reality in a free nation into the 21st century.

1. Libraries inform citizens. Democracy vests supreme power in the people. Libraries make democracy work by providing access to information so that citizens can make the decisions necessary to govern themselves. The public library is the only institution in American society whose purpose is to guard against the tyrannies of ignorance and conformity, and its existence indicates the extent to which a democratic society values knowledge, truth, justice, books, and culture.

2. Libraries break down boundaries. Libraries provide free family literacy programs for low-literate, illiterate, and non-English-speaking people. In addition, hundreds of librarians across America lead outreach programs that teach citizenship and develop multilingual and multicultural materials for their patrons. Libraries serve the homebound elderly, prisoners, and other institutionalized individuals, the homeless, and the blind and hearing-impaired.

3. Libraries level the playing field. Economists have cited a growing income inequity in America, with the gap between the richest and poorest citizens becoming wider year by year. By making all its resources equally available to all members of its community, regardless of income, class, or other factors, the library levels the playing field. Once users have access to the library’s materials, they have the opportunity to level the playing field outside the library by learning to read, gaining employment, or starting a business.

4. Libraries value the individual. Library doors swing open for independent thinking without prejudgment. Libraries offer alternatives to the manipulations of commercialism, from the excellence of public-television productions to the freethinking of renegade publishers and the vision of poets and artists outside the mainstream business of art and literature.

5. Libraries nourish creativity. In the library we are all children. By stimulating curiosity—parent to the twin forces of creativity and imagination—even the most focused and specialized library serves the purpose of lifting the mind beyond its horizons. Libraries store ideas that may no longer work but can serve as the raw material that, cross-fertilized in the innovative mind, may produce answers to questions not yet asked.

6. Libraries open kids’ minds. Bringing children into a library can transport them from the commonplace to the extraordinary. From story hours for preschoolers to career planning for high schoolers, children’s librarians make a difference because they care about the unique developmental needs of every individual who comes to them for help. Children get a handle on personal responsibility by holding a library card of their own, a card that gives them access to new worlds in books, videos, audiotapes, computers, games, toys, and more.

7. Libraries return high dividends. What do Gallo wines, the I Can’t Believe It’s Yogurt chain, and billboard-sign giant Metromedia have in common? Libraries made millionaires out of each of these companies’ grateful owners by providing crucial start-up information when they were no more than wannabe business titans. Libraries are there to help people with more personal goals, too. The seed money expended for these and other success stories? Less than $20 per capita per year in tax dollars.

8. Libraries build communities. No narrow definition of community will work in a library. Each community has its libraries and its special collections. Libraries validate and unify; they save lives, literally and by preserving the record of those lives. Community-building means libraries link people with information. Librarians have become experts at helping others navigate the Internet. Before there was talk of cyberspace, there were libraries, paving the way for the superhighway.

9. Libraries make families friendlier. The American family’s best friend, the library, offers services guaranteed to hone coping skills. Homework centers, literacy training, parenting materials, after-school activities, summer reading programs, outreach—like the families they serve, libraries everywhere are adapting to meet new challenges.

10. Libraries offend everyone. Children’s librarian Dorothy Broderick contends that every library in the country ought to have a sign on the door reading: “This library has something offensive to everyone. If you are not offended by something we own, please complain.” This willingness and duty to offend connotes a tolerance and a willingness to look at all sides of an issue that would be good for the nation in any context; it is particularly valuable when combined with the egalitarianism and openness that characterize libraries.

11. Libraries offer sanctuary. Like synagogues, churches, mosques, and other sacred spaces, libraries can create a physical reaction, a feeling of peace, respect, humility, and honor that throws the mind wide open and suffuses the body with a near-spiritual pleasure. But why? Perhaps it is because in the library we are answerable to no one; alone with our private thoughts, fantasies, and hopes, we are free to nourish what is most precious to us with the silent companionship of others we do not know.

12. Libraries preserve the past. Libraries preserve the record; a nation, a culture, a community that does not understand its own past is mired in its own mistakes. Libraries enable us to communicate through distance and time with the living and the dead. It is a miracle kept available by the meticulous sorting, storing, indexing, and preservation that still characterizes library work—work that will carry, in the electronic environment, challenges and a price tag yet unknown.

Adapted from “12 Ways Libraries Are Good for the Country,” American Libraries 26 (December 1995): 1113–19.


Apr 08 2010

Mac OS X Snow Leopard : PHP + OCI8

분류: .Mac, Apache2, Oracle, PHP, Server최 태현 @ 10:55 오전

Mac OS X Snow Leopard 에서 Oracle 10.g 설치하기 를 통해 Oracle을 설치했다는 전제하에 PHP에 OCI8을 설치하는 방법입니다.

먼저 OCI8 module이 제대로 설치되어 있는지 확인합니다.

env  |  grep  -­E  ”(ORACLE)|(DYLD)”

ORACLE_SID=y2love
ORACLE_BASE=/Users/Y2Love
DYLD_LIBRARY_PATH=/Users/oracle/oracle/product/10.2.0/db_1/lib
ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1

와 같이 나오면 됩니다.

Continue reading “Mac OS X Snow Leopard : PHP + OCI8″


Apr 08 2010

Mac OS X Snow Leopard 에서 Oracle 10.g 설치하기

분류: .Mac, How to, Oracle최 태현 @ 12:29 오전

1996년인가  대전광역시 중소기업지원정보시스템 구축사업을 하면서 대전광역시에 오라클을 처음으로 도입했었습니다.

그당시 Ingres DB가 행정망용으로 사용되고 있었는데  Sun Enterprise 에 오라클 DB를 올렸던 기억이 납니다. 그 당시만해도

오라클사에서는 도입만해줘도 무슨 요구든지 들어줬는데 지금은 너무 배짱을 부리는것 같습니다. 유지보수비가 장난이 아니더군요. 그것도 유지보수계약 체결하기 전까지의 모든 비용을 요구하면서… 입맛이 씁쓸합니다.

물론 1998년인가 1999년도에 대전광역시에 시정도우미 라는 내부 인트라넷 서비스를 만들면서 Linux ( 당시 알짜 리눅스 였던것 같은데..)에 MySQL, PHP 를 386PC에 설치하면서 Free Software로 방향을 전환했습니다.

오째든 장난삼아 Oracle을 MacBook에 올려봤습니다.

( 원문 Site 입니다.     ray_apps_blog )

Continue reading “Mac OS X Snow Leopard 에서 Oracle 10.g 설치하기”


Mar 26 2010

재미있는 ponebook

분류: Ubuntu최 태현 @ 8:21 오전

아이디어 참 좋다…..

Tags: , ,


Mar 01 2010

안드로이드 구입

분류: Ubuntu최 태현 @ 1:25 오후

image

시험운영


Jan 09 2010

WorkBench에서 Encoding 변환해서 보기

분류: Database, mysql최 태현 @ 12:21 오전

SQL Editor에서 한글이 깨질경우 다음과 같이 화면 encoding을 변환해서 실행한다.

SET NAMES ‘euckr’;


Jan 08 2010

신성동 작은도서관 DB 분석

분류: Database, mysql최 태현 @ 12:47 오전

책갈피 프로그램이 MySQL DB를 사용하고 있어서 Dump를 받아왔는데 latin-1으로 charset이 되어 있어서 utf-8로 전환하였다.

다른 방법도 있겠지만 Dump를 받아온것이라 vim 을 통해 간단히 전환했다.

일단 vim으로 euc-kr로 인코딩되어 있는 dump 파일을 Loading 하면  문자가 깨져 보일것이다. 이때 다음과 같이 인코딩을 바꾸면 제대로 보인다.

:e ++enc=euc-kr

이 상태에서 인코딩을 바꿔서 저장하려면

:set fileencoding=utf-8

이렇게 하면 간단히 문자 인코딩을 변경할 수 있다.

이상태에서 다음과 같이 Dump File을 DB로 올려봤지만 …..

mysql -u root -p slibrary < sinsung.sql

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes.

위와 같은 메시지가 나오면서 설치가 중단되는데,  그 이유는 mysql 이 key 의 길이를 1000바이트로 제한하기 때문이다.

mysql 측에서는 이걸 버그라 보지 않고 “제한”이라고 봐서 당분간은 처리할 용의가 없답니다.

latin-1 에서는 1글자가 1바이트이지만 utf-8 에서는 한글자가 3바이트이기 때문에 크기 계산에 주의해야 한다.

latin1 = 1 byte  = 1 chararcter
uft8    = 3 byte = 1 chararcter

즉, varchar(255)에서 255는 255 “바이트”가 아니라 “글자”이기 때문이다. utf-8일 경우 한글은 한 글자당 3바이트이므로 255 * 3 * 2 > 1000 이다.

해결방법은

CREATE TABLE bookadd_tbl (
S_ID varchar(20) NOT NULL default ”,
ADD_SEQ varchar(14) NOT NULL default ”,
ADD_NM1 varchar(200) default NULL,
ADD_AUTH varchar(128) default NULL,
ADD_CHUL varchar(128) default NULL,
ADD_YEAR varchar(60) default NULL,
ADD_TYPE varchar(100) default NULL,
ADD_LENGTH varchar(100) default NULL,
ADD_PAGE varchar(100) default NULL,
ADD_ETC varchar(255) default NULL,
ADD_DATE varchar(24) default NULL,
ADD_MARC longtext,
PRIMARY KEY  (S_ID,ADD_SEQ),
KEY ADD_TYPE (ADD_TYPE),
KEY ADD_NM1 (ADD_NM1,ADD_AUTH,ADD_CHUL)
) TYPE=MyISAM;

에서 TYPE… 부분을 ENGINE=InnoDB DEFAULT CHARSET=utf8 로 하면 된다.

왜 되는지는 정확히 모르겠다.


Jan 07 2010

MySQL Dump뜨는 방법 및 복구 방법

분류: mysql최 태현 @ 1:00 오전

1. 덤프뜨기

$> mysqldump -u[사용자아이디] -p 데이터베이스명 [테이블명] > 저장될 파일명
예) mysqldump -ukamkami -p mydatabase > kamkami.pe.kr.sql

이렇게 하면 디비(mydatabase)를 몽땅 덤프를 뜨게된다.

테이블만 덤프를 뜨고 싶다면

예) mysqldump -ukamkami -p mydatabase member_table > kamkami.pe.kr.member_table.sql

이렇게 하면 테이블만 덤프를 뜰 수 있다.

2. 복구하기

덤프 파일을 가지고 복구를 하는 방법도 간단하다.
$> mysql -u[사용자아이디] -p [디비명] < 덤프파일명

예) mysql -ukamkami -p < kamkami.pe.kr.sql
예) mysql -ukamkami -p mydatabase < kamkami.pe.kr.member_table.sql

1.특정 db의 특정 table에서 원하는 값만 덤프받기

>> edu라는 디비에 a,b,c라는 테이블이 있다. 여기서 a라는 테이블에서 no가 7번이상이고 10번
이하인 값만 덤프를 받고자 한다. 어떻게 하겠는가?
여기서 사용되는 옵션은 -w 이다.그럼 위 질문의 sql문은 아래와 같다

mysqldump -u mysql_admin -p edu a -w’no=>7 and no=<10′ > edu_a_cond.sql

위와같이 하면 no가 7~10번까지가 덤프될것이다.
위에서 조건문은 -w 다음에 싱글쿼테이션으로 묶어준다. sql에서 사용하는 조건문이 다 될듯싶

다. 모두 테스트를 해보진 않았다.

2.디비 스키마(Schema)만 백업받기

>>초기에 작성해 놓은 테이블 스키마가 없을때 어떻게 하겠는가?
만약 하나의 테이블이라면 desc 해서 일일이 다 삽질을 하면 되것지만 만약 테이블이 100개라

면 ..크억…이럴때 사용하는 mysqldump옵션이 있습니다.
-d 입니다.

!.edu라는 디비의 모든 테이블 스키마를 백업받으려면

mysqldump -u mysql_admin -p -d edu > edu_db.sql

!.edu라는 디비의 a라는 테이블 스키마를 백업받으려면

mysqldump -u mysql_admin -p -d edu a> edu_a_table.sql


뒷 쪽 »