注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

星期五

明天不上班

 
 
 

日志

 
 
关于我

一个特立独行的Java程序员,比较宅,上上网,写博客,听音乐,看电影。

网易考拉推荐

安装PostgreSQL  

2010-11-22 23:10:05|  分类: Postgresql |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |
如何安装PostgreSQL
Windows 下:
1.在http://www.postgresql.org/ftp/binary/v8.3.10/win32/下载 postgresql-8.3.10-1.zip
解压 打开README.txt
安装详细过程可以参考 http://pginstaller.projects.postgresql.org/
双击 postgresql-8.3.msi 文件
按照参考文档 一步一步安装
启动数据库服务后
打开pgAdmin III 或这在cmd中执行  psql -U postgres 命令  postgres是用户名



Linux下
Ubuntu 下安装
apt-get install postgreSQL
一切自动安装完成 postgres用户名自动生成,但没有初始化密码 必须设置
1.用passwd postgres  命令设置数据库的密码
2.启动服务 service postgresql start
3.切换用户 su - postgres
4.创建数据库 psql mydb
5.打开postgresql command  使用 psql命令
6.连接mydb数据库  \c mydb
7.剩下的就可以创建表 增删改查了

8.推出  \q

RedHat 的安装参考如下
(下面转载自http://www.yolinux.com/TUTORIALS/LinuxTutorialPostgreSQL.html

Red Hat 7.2 example:

  • Install (rpm -ivh package) or check for the following PostgreSQL RPMs (rpm -q package):
    • postgresql-7.1.3-2
    • postgresql-libs-7.1.3-2
    • postgresql-server-7.1.3-2
    • postgresql-perl-7.1.3-2
    Other packages available:
    • postgresql-odbc
    • postgresql-jdbc
    • postgresql-tcl
    • postgresql-tk
    • postgresql-python
    • postgresql-devel

    Install: rpm -ivh postgresql-7.1.3-2.i386.rpm postgresql-libs-7.1.3-2.i386.rpm postgresql-server-7.1.3-2.i386.rpm postgresql-perl-7.1.3-2.i386.rpm

  • The user "postgres" should have already been configured by the installation of the RPMs. Info:
    • User: postgres
    • Home directory: /var/lib/pgsql
    • Default shell: /bin/bash
    A password will be missing. As root issue the command: passwd postgres to assign a password for user postgres.

  • Login as user postgres: su - postgres
    This will execute the profile: /var/lib/pgsql/.bash_profile
    PGLIB=/usr/lib/pgsql
    PGDATA=/var/lib/pgsql/data
    export PGLIB PGDATA
  • Initialize PostgreSQL database server: initdb --pgdata=/var/lib/pgsql/data
    This creates a bunch of directories, a template directory and sets up the postgres configuration in the user directory /var/lib/pgsql/. Red Hat start command (service)/script (rc script) will perform this task if it has not already been performed. See next step - Starting the database.

  • Starting the database server: As root. (from most to least favorite method)
    service postgresql start
    (If the database has not already been initialized with initdb, this will be performed by the command)
    OR
    /etc/rc.d/init.d/postgresql start
    (If the database has not already been initialized with initdb, this will be performed by the script)
    OR
    /usr/bin/pg_ctl -D /var/lib/pgsql/data -p /usr/bin/postmaster -l logfile start &
    OR
    /usr/bin/postmaster -D /var/lib/pgsql/data &
    Notes:
    • Configuration file: /var/lib/pgsql/data/postgresql.conf
      By default there is no network access. See the directive tcpip_socket. (Required for ODBC,JDBC) Also see the postmaster directive "-i". Logging and tuning parameters are specified here.
    • Host Access file: /var/lib/pgsql/data/pg_hba.conf
    • Authentication/Identification file: /var/lib/pgsql/data/pg_ident.conf

  • Create a database: createdb bedrock
    (As Linux user postgres)

  • Connect to the database: psql bedrock
    Execute command as Linux user postgres
    You will now be at the PostgreSQL command line prompt.
    [prompt]$ psql             - or "psql bedrock"
    Welcome to psql, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help on internal slash commands
    \g or terminate with semicolon to execute query
    \q to quit

    bedrock=#
    bedrock=# \c bedrock
    You are now connected to database bedrock.
    bedrock=# create table employee (Name char(20),Dept char(20),jobTitle char(20));
    CREATE
    bedrock=# INSERT INTO employee VALUES ('Fred Flinstone','Quarry Worker','Rock Digger');
    INSERT 18733 1
    bedrock=# INSERT INTO employee VALUES ('Wilma Flinstone','Finance','Analyst');
    bedrock=# INSERT into employee values ('Barney Rubble','Sales','Neighbor');
    bedrock=# INSERT INTO employee VALUES ('Betty Rubble','IT','Neighbor');
    bedrock=# SELECT * from employee;
    name | dept | jobtitle
    ----------------------+----------------------+----------------------
    Fred Flinstone | Quarry Worker | Rock Digger
    Wilma Flinstone | Finance | Analyst
    Barney Rubble | Sales | Neighbor
    Betty Rubble | IT | Neighbor
    (4 rows)

    bedrock=#
    bedrock=# \q

  • Database discovery / Examine a database (as user postgres: su - postgres):
    [postgres]$ psql
    • \l :List databases
    • \c database-name :List databases
    • \d :List tables in database
    • \d table-name :Describe table
    • select * from table-name :List table contents

  • More info:
    • Create a user:
      • Command line: [prompt]$ createuser dude
      • SQL: CREATE USER dude WITH PASSWORD 'supersecret';
        Change with ALTER USER
    • Grant priveliges:
      • SQL: GRANT UPDATE ON table-name to dude
      • SQL: GRANT SELECT ON table-name to dude
      • SQL: GRANT INSERT ON table-name to dude
      • SQL: GRANT DELETE ON table-name to dude
      • SQL: GRANT RULE ON table-name to dude
      • SQL - Do it all: GRANT ALL PRIVILEGES ON table-name to public
    • Delete a user:
      • Command line: [prompt]$ dropuser SuperDude
    • Delete a database:
      • Command line:
        • [prompt]$ destroydb bedrock
        • [prompt]$ dropdb bedrock
      • SQL: DROP DATABASE bedrock;
    • Create a database:
      • Command line: [prompt]$ createdb bedrock -U user-name -W
        You will be prompted for a password. (or execute as Linux user postgres without -U and -W options)
      • SQL: CREATE DATABASE bedrock
    • Backup a database:
      • [prompt]$ pg_dumpall > outfile
      • [prompt]$ pg_dumpall -Fc dbname > outfile
    • Version Upgrades:
      • Dump: [prompt]$ postgresql-dump -t /var/lib/pgsql/backup/db.bak -p /var/lib/pgsql/backup/old -d
      • Restore: [prompt]$ psql -e template1 < /var/lib/pgsql/backup/db.bak
        The table template1 is the default administrative database.

If one would like to generate a GUI interface to the database, I would recommend a web form to a Java servlet back-end or use a JAVA GUI program and JDBC. See the YoLinux tutorial on accessing PostgreSQL with JDBC in a Tomcat servlet example.

Links/Info:


PostgreSQL Man Pages:

  • createdb - Create a new PostgreSQL database
  • createlang - Add a new programming language to a PostgreSQL database
  • createuser - Create a new PostgreSQL user
  • dropdb - Remove an existing PostgreSQL database
  • droplang - Remove a programming language from a PostgreSQL database
  • dropuser - Drops (removes) a PostgreSQL user
  • pg_dump - Extract a PostgreSQL database into a script file
  • pg_dumpall - Extract all PostgreSQL databases into a script file
  • psql - PostgreSQL interactive terminal
  • vacuumdb - Clean and analyze a PostgreSQL database

PostgreSQL SQL Statement Man Pages:





  评论这张
 
阅读(595)| 评论(0)
推荐

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017