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

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.

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.

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

와 같이 나오면 됩니다.

그러면 이제 PECL command를 실행시켜 OCI8 module을 설치합니다. 이때 저는 sudo를 사용해야 됨에 따라 root 계정 환경변수에 먼저 Oracle 환경변수를 .bash_profiled에 넣어줬습니다.

sudo -i

root# vi .bash_profile

export ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=ORCL
PATH=$PATH:$ORACLE_HOME/bin

root# . ./bash_profile

root# pecl install oci8

다음에 php engine이 module을 load 할 수 있도록 php.ini에 등록시켜준다.

$vi /etc/php.ini

extension=oci8.so ; php.ini 뒷부분에 삽입해 준다.

OCI8이 재대로 설치되었는지 확인해본다.

$php -i|grep -E -i “(oci8)”

여기까지 하고 일단 apache를 재 기동시키고 phpinfo()를 통해 확인해 보면 oci8관련 자료가 올라와 있는것을 확인할 수 있다.

이거외에도 몇가지 환경설정관련 작업을 해줘야 된다고 되어 있는데 이부분은 차후 정리해서 올리겠습니다.

참고자료 : Antonio Musarra’s Blog

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

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

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

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

( 원문 Site 입니다.     ray_apps_blog )

Initial preparation

If you are a developer then I suppose you already have Xcode tools installed which are required also for Oracle installation. And I tried these steps on Mac OS X latest version 10.5.6.

Then you need to create oracle user as well as increase default kernel parameters. Open Terminal and switch to root user:

sudo -i

Create oinstall group and oracle user (I used group and user number 600 to ensure that they do not collide with existing groups and users):

dscl . -create /groups/oinstall
dscl . -append /groups/oinstall gid 600
dscl . -append /groups/oinstall passwd "*"

dscl . -create /users/oracle
dscl . -append /users/oracle uid 600
dscl . -append /users/oracle gid 600
dscl . -append /users/oracle shell /bin/bash
dscl . -append /users/oracle home /Users/oracle
dscl . -append /users/oracle realname "Oracle software owner"
mkdir /Users/oracle
chown oracle:oinstall /Users/oracle

Change password for oracle user:

passwd oracle

Change default kernel parameters:

vi /etc/sysctl.conf

and enter values recommended by Oracle:

kern.sysv.semmsl=87381
kern.sysv.semmns=87381
kern.sysv.semmni=87381
kern.sysv.semmnu=87381
kern.sysv.semume=10
kern.sysv.shmall=2097152
kern.sysv.shmmax=2197815296
kern.sysv.shmmni=4096
kern.maxfiles=65536
kern.maxfilesperproc=65536
net.inet.ip.portrange.first=1024
net.inet.ip.portrange.last=65000
kern.corefile=core
kern.maxproc=2068
kern.maxprocperuid=2068

After this reboot your computer so that these new kernel parameters would be taken into effect. After reboot open again Terminal and now login as oracle user:

su - oracle

Set shell settings in .bash_profile

vi .bash_profile

and enter

export DISPLAY=:0.0
export ORACLE_BASE=$HOME
umask 022
ulimit -Hn 65536
ulimit -Sn 65536

As you see I prefer to install all Oracle related files under home directory of oracle user therefore I am setting ORACLE_BASE to home directory. And also include ulimit settings – I forgot to do this initially and got strange TNS service errors because of that.

Now execute this script so that these settings are applied to current shell:

. ./.bash_profile

Now download db.zip installation archive and place it somewhere and unzip it:

mkdir Install
cd Install
# download db.zip to this directory
unzip db.zip
cd db/Disk1

Now you are ready to start installation:

./runInstaller

Installation

In installation wizard I selected the following options:

• Advanced Installation – so that I can change some default options
• Standard Edition – as I don’t need additional features of Enterprise Edition
• Create Database / General Purpose
• Global database name: orcl, SID: orcl
• Character set: UTF-8 AL32UTF8
• Create database with sample schemas
• Selected “Use the same password for all the accounts” – do not specify default “manager” password as it will not be allowed
• Password Management – selected this to unlock necessary sample accounts (e.g. HR schema account that I use as default test schema)

At the end of installation you will be instructed to run one shell script from root.
Hopefully installation will complete successfully.

Additional oracle user settings

If you will use oracle user later then add the following lines to .bash_profile of oracle user:

export ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
export DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_SID=ORCL
PATH=$PATH:$ORACLE_HOME/bin

After this relogin as oracle user and verify listener status:

lsnrctl status

and if it is down then start it with

lsnrctl start

and verify if you can connect to Oracle database with sample user (that I unlocked during installation)

sqlplus hr/hr@orcl

If it fails then do some investigation

Change listener to listen on localhost

As I need this Oracle database just as local development database on my computer then I want to change the listener so that it would listen just on localhost port 1521:

vi $ORACLE_HOME/network/admin/listener.ora

and change the contents of the file to:

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /Users/oracle/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /Users/oracle/oracle/product/10.2.0/db_1)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

Then also change ORCL alias definition in $ORACLE_HOME/network/admin/tnsnames.ora to:

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

After this change restart listener and try to connect with sqlplus to verify that these changes are successful.

Automatic startup of Oracle database

If you want that Oracle database is started automatically when your computer is booted then you need to create the following startup script. Start terminal and switch to root.

At first edit /etc/oratab and change N to Y at the end of line for ORCL database – this will be used by dbstart utility to find which databases should be started automatically.

Then create startup script for Oracle database:

mkdir /Library/StartupItems/Oracle
cd /Library/StartupItems/Oracle
vi Oracle

and enter the following:

#!/bin/sh

# Suppress the annoying "$1: unbound variable" error when no option
# was given
if [ -z $1 ] ; then
  echo "Usage: $0 [start|stop|restart] "
  exit 1
fi

# source the common startup script
. /etc/rc.common

# Change the value of ORACLE_HOME to specify the correct Oracle home
# directory for the installation
ORACLE_HOME=/Users/oracle/oracle/product/10.2.0/db_1
DYLD_LIBRARY_PATH=$ORACLE_HOME/lib
export ORACLE_HOME DYLD_LIBRARY_PATH

# change the value of ORACLE to the login name of the
# oracle owner at your site
ORACLE=oracle

PATH=$PATH:$ORACLE_HOME/bin

# Set shell limits for the Oracle Database
ulimit -Hu 2068
ulimit -Su 2068
ulimit -Hn 65536
ulimit -Sn 65536

StartService()
{
  ConsoleMessage "Starting Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbstart $ORACLE_HOME"
}

StopService()
{
  ConsoleMessage "Stopping Oracle Databases"
  su $ORACLE -c "$ORACLE_HOME/bin/dbshut $ORACLE_HOME"
}

RestartService()
{
  StopService
  StartService
}

RunService "$1"

and then make this script executable

chmod a+x Oracle

and in addition create properties file:

vi StartupParameters.plist

with the following contents:

{
  Description     = "Oracle Database Startup";
  Provides        = ("Oracle Database");
  Requires        = ("Disks");
  OrderPreference = "None";
}

Now you can verify that these scripts are working. Open new terminal and try

sudo /Library/StartupItems/Oracle/Oracle stop

to stop the database and

sudo /Library/StartupItems/Oracle/Oracle start

to start again the database. And later you can reboot your computer also to verify that Oracle database will be started automatically.

Hide oracle user from login window

After computer reboot you probably noticed that now you got oracle user in initial login window. To get rid of it execute this from terminal:

sudo defaults write /Library/Preferences/com.apple.loginwindow HiddenUsersList -array-add oracle

아이디어 참 좋다…..

시험운영

SET NAMES ‘euckr’;

다른 방법도 있겠지만 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 로 하면 된다.

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

$> 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