postgresql 入门使用记录_sql

概述查看postgresql 安装情况[root@localhost hive]# rpm -qa | grep postgresqlpostgresql-9.2.4-1.fc19.x86_64postgresql-libs-9.2.4-1.fc19.x86_64postgresql-server-9.2.4-1.fc19.x86_64# yum install postgresql为

查看postgresql 安装情况[root@localhost hive]# rpm -qa | grep postgresqlpostgresql-9.2.4-1.fc19.x86_64postgresql-libs-9.2.4-1.fc19.x86_64postgresql-server-9.2.4-1.fc19.x86_64# yum install postgresql为 hive 提供 postgresql 驱动[root@localhost Downloads]# cp postgresql-9.2-1003.jdbc4.jar /usr/lib/hive/lib/使用 超级用户 postgres 为 hive 创建用户和数据库[yuming@localhost all-in-one]$ sudo -u postgres psql[sudo] password for yuming: Could not change directory to "/home/yuming/Downloads/all-in-one"psql (9.2.4)Type "help" for help.postgres=# CREATE USER hive WITH PASSWORD 'hvIE';CREATE RolEpostgres=# create database metastore owner=hive;CREATE DATABASEpostgres=# grant all on database metastore to hive;GRANTpostgres=# \c metastore You are Now connected to database "metastore" as user "postgres".metastore=# \i /usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql重起数据库，如果不是 root 用户需要先切换到 root 用户 [root@localhost data]# su postgres bash-4.2$ /usr/bin/pg_ctl -D /var/lib/pgsql/data restartwaiting for server to shut down.... doneserver stoppedserver starting

怎么老错：

bash-4.2$ sudo -u hive pgsql
[sudo] password for postgres:
Sorry,try again.

改个密码

[root@localhost hive]# sudo -u postgres psql postgresCould not change directory to "/home/yuming/hive"psql (9.2.4)Type "help" for help.postgres=# \password postgresEnter new password: Enter it again: postgres=# [root@localhost hive]# sudo -u hive psql metastoreCould not change directory to "/home/yuming/hive"psql (9.2.4)Type "help" for help.metastore=> \tShowing only tuples.metastore=> \db pg_default | postgres |  pg_global  | postgres | metastore=> \dt public | BUCKETING_ColS            | table | hive public | CDS                       | table | hive

HDFS 中没有表salerecord对应的目录，metastore中有就可以了

[root@localhost ~]# hive -e "load data local inpath '/root/sal.txt' OVERWRITE into table salerecord partition (reco='20');"

导数据时会自动创建：

drwxr-xr-x   - root   hadoop          0 2013-07-30 14:10 /user/hive/warehouse/salerecorddrwxr-xr-x   - root   hadoop          0 2013-07-30 14:10 /user/hive/warehouse/salerecord/reco=20130525-rw-r--r--   3 root   hadoop  977161793 2013-07-30 14:10 /user/hive/warehouse/salerecord/reco=20130525/sal.txt

但还不知道在元数据哪个表中

eac:

postgres=# CREATE USER eac WITH PASSWORD 'eac';
ERROR: role "eac" already exists
postgres=#
postgres=#
postgres=#
postgres=#
postgres=# create database eac owner=eac;
CREATE DATABASE
postgres=# grant all on database eac to eac;
GRANT
postgres=#

安装安装postgresql后应该自动创建postgres用户，我这个居然没有，so,重装[root@indigo ~]# rpm -qa |grep  postgrpostgresql-9.2.4-1.fc19.x86_64postgresql-libs-9.2.4-1.fc19.x86_64[root@indigo ~]# yum remove postgresql-9.2.4-1.fc19.x86_64[root@indigo ~]# yum remove postgresql-libs-9.2.4-1.fc19.x86_64postgresql 安装，简单几步就搞定，有其他问题的话可参考安装手册：如果要配 yum 源可在这里找，是个rpm 包，可以解压看看，估计是最简单的安装包了，我用的是fedora 19 ，已经不用再配了[root@indigo ~]# curl -O http://yum.postgresql.org/9.2/fedora/fedora-18-x86_64/pgdg-fedora92-9.2-6.noarch.rpm[root@indigo ~]# rpm -ivh pgdg-fedora92-9.2-6.noarch.rpmPreparing...                          ################################# [100%]    package pgdg-fedora92-9.2-6.noarch is already installedList 一下，找几个合适的装上：[root@indigo ~]# yum List postgres*[root@indigo ~]# yum install postgresql-server.x86_64 postgresql.x86_64 postgresql-libs.x86_64装完发现服务没起来：[root@indigo ~]# ps -ef | grep postgreroot      6645  4974  0 15:47 pts/1    00:00:00 grep --color=auto postgre而且也起不来：[root@indigo ~]# service postgresql startRedirecting to /bin/systemctl start postgresql.serviceJob for postgresql.service Failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.换 postgres 用户也没起来，看了一下 /var/lib/pgsql/data/ 目录下是空的：[root@indigo ~]# sudo -i -u postgres -bash-4.2$ /usr/bin/pg_ctl -D /var/lib/pgsql/data/ startserver starting-bash-4.2$ postgres cannot access the server configuration file "/var/lib/pgsql/data/postgresql.conf": No such file or directory需要 init:-bash-4.2$ initdb -D '/var/lib/pgsql/data/'[root@indigo data]# ls /var/lib/pgsql/database pg_clog pg_IDent.conf pg_notify pg_snapshots pg_subtrans pg_twophase pg_xlogglobal pg_hba.conf pg_multixact pg_serial pg_stat_tmp pg_tblspc PG_VERSION postgresql.conf再使用 postgres 用户启动：-bash-4.2$ /usr/bin/pg_ctl -D /var/lib/pgsql/data/ startserver starting这次起来了：[root@indigo pgsql]# su postgresbash-4.2$ psqlpsql (9.2.4)Type "help" for help.postgres=# \l List of databases name | Owner | EnCoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+-----------------------postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgrestemplate1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres(3 rows)

修改文件： /var/lib/pgsql/data/postgresql.conf有Listen 这行 改为 Listen_addresses = '*'一般 standard_coffforming_strings 也要关掉，standard_conforming_strings = off ，文档是这么说的：escape_string_warning (boolean)    When on,a warning is issued if a backslash (\) appears in an ordinary string literal ('...' Syntax) and standard_conforming_strings is off. The default is on.    Applications that wish to use backslash as escape should be modifIEd to use escape string Syntax (E'...'),because the default behavior of ordinary strings is Now to treat backslash as an ordinary character,per sql standard. This variable can be enabled to help locate code that needs to be changed.修改 /var/lib/pgsql/data/pg_hba.conf，文件格式：http://www.postgresql.org/docs/9.2/static/auth-pg-hba-conf.HTML添加：local    all             all                                     trusthost     all             all             0.0.0.0/0                       trustJDBC都用yum 装，表示惊奇，试了一下：[root@indigo all-in-one]# yum search postgre-jdbc结果被装到 /usr/share/java 去了安装前：[root@indigo java]# lsjline.jar  Js.jar  rhino-examples.jar  rhino.jar安装后：[root@indigo java]# lltotal 1568-rw-r--r--. 1 root root   62872 Feb 16 01:47 jline.jar-rw-r--r--. 1 root root 1069805 Aug  1 16:50 Js.jarlrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2ee.jar -> postgresql-jdbc.jarlrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2.jar -> postgresql-jdbc.jarlrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc3.jar -> postgresql-jdbc.jar-rw-r--r--. 1 root root  445346 Feb 18 01:07 postgresql-jdbc-9.2.1002.jarlrwxrwxrwx. 1 root root      28 Aug 11 18:46 postgresql-jdbc.jar -> postgresql-jdbc-9.2.1002.jar-rw-r--r--. 1 root root   18384 Aug  1 16:50 rhino-examples.jarlrwxrwxrwx. 1 root root       6 Aug 10 19:16 rhino.jar -> Js.jar[root@indigo java]# cp postgresql-jdbc-9.2.1002.jar /usr/lib/hive/lib[root@indigo java]# ln -s /usr/lib/hive/lib/postgresql-jdbc-9.2.1002.jar postgresql-jdbc.jarln: Failed to create symbolic link ‘postgresql-jdbc.jar’: file exists[root@indigo java]# ln -sf /usr/lib/hive/lib/postgresql-jdbc-9.2.1002.jar postgresql-jdbc.jar[root@indigo java]# lltotal 1568-rw-r--r--. 1 root root   62872 Feb 16 01:47 jline.jar-rw-r--r--. 1 root root 1069805 Aug  1 16:50 Js.jarlrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2ee.jar -> postgresql-jdbc.jarlrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2.jar -> postgresql-jdbc.jarlrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc3.jar -> postgresql-jdbc.jar-rw-r--r--. 1 root root  445346 Feb 18 01:07 postgresql-jdbc-9.2.1002.jarlrwxrwxrwx. 1 root root      46 Aug 11 18:58 postgresql-jdbc.jar -> /usr/lib/hive/lib/postgresql-jdbc-9.2.1002.jar-rw-r--r--. 1 root root   18384 Aug  1 16:50 rhino-examples.jarlrwxrwxrwx. 1 root root       6 Aug 10 19:16 rhino.jar -> Js.jar[root@indigo java]# su postgresbash-4.2$ /usr/bin/pg_ctl restart -w -m fast -D /var/lib/pgsql/datawaiting for server to shut down.... doneserver stoppedwaiting for server to start.... doneserver started创建用户，数据库，使用新创建用户导入sql脚本：postgres=# create user hive with password 'hive';CREATE RolEpostgres=# create database metastore owner=hive;CREATE DATABASEpostgres=# grant all privileges on database metastore to hive;GRANTpostgres=# \qbash-4.2$ psql -U hive -d metastorepsql (9.2.4)Type "help" for help.metastore=> \l                                  List of databases   name    |  Owner   | EnCoding |   Collate   |    Ctype    |   Access privileges   -----------+----------+----------+-------------+-------------+----------------------- metastore | hive     | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/hive             +           |          |          |             |             | hive=CTc/hive postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +           |          |          |             |             | postgres=CTc/postgres(4 rows)metastore=> select current_user; current_user-------------- hive(1 row)导入sql脚本：metastore=> \i /usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql.......psql:/usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql:1372: WARNING:  no privileges Could be revoked for "public"REVOKEpsql:/usr/lib/hive/scripts/metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql:1373: WARNING:  no privileges were granted for "public"GRANT重启：metastore=> \qbash-4.2$ /usr/bin/pg_ctl restart -w -m fast -D /var/lib/pgsql/datawaiting for server to shut down.... doneserver stoppedwaiting for server to start.... doneserver startedbash-4.2$

简单使用：

查看所有库：\l

查看所有表：\dt

查看一个表的结构： \d 表名

换数据库 \c dbname

postgres=# \c metastore
You are Now connected to database "metastore" as user "postgres".

创建数据库：

bash-4.2$ createdb book

bash-4.2$ psql book -c "SELECT '1'::cube";
ERROR: type "cube" does not exist
liNE 1: SELECT '1'::cube
^
出错，查看发现 contrib 目录是空的，先装个 contrib 再说

bash-4.2$ pg_config --sharedir
/usr/share/pgsql

bash-4.2$ ls /usr/share/pgsql/contrib/

[root@indigo contrib]# yum List postgre* | grep contri
postgresql-contrib.x86_64 9.2.4-1.fc19 fedora
postgresql92-contrib.x86_64 9.2.4-4PGDG.f19 pgdg92
[root@indigo contrib]# yum install postgresql-contrib.x86_64
只多了个 sepgsql.sql

bash-4.2$ ls /usr/share/pgsql/contrib/
sepgsql.sql
找了半天，说的都8.x ，9.0的，我这contrib下根本没有嘛，后来才发现在extension下：

[apache@indigo extension]$ ls /usr/share/pgsql/extension | grep cube
cube--1.0.sql
cube.control
cube--unpackaged--1.0.sql
导入：

bash-4.2$ psql -U postgres -d book
psql (9.2.4)
Type "help" for help.

book=# \i /usr/share/pgsql/extension/cube--1.0.sql;
Use "CREATE EXTENSION cube" to load this file.
book=# CREATE EXTENSION cube;
CREATE EXTENSION
book=#

bash-4.2$ /usr/bin/pg_ctl -D /var/lib/pgsql/data restart
waiting for server to shut down.............................................................
.. Failed
pg_ctl: server does not shut down
HINT: The "-m fast" option immediately disconnects sessions rather than
waiting for session-initiated disconnection.
bash-4.2$
bash-4.2$ /usr/bin/pg_ctl restart -w -m fast -D /var/lib/pgsql/data
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
bash-4.2$

重启后才好啊：

bash-4.2$ psql book -c "SELECT '1'::cube;"
cube
------
(1)
(1 row)

bash-4.2$

[root@indigo bin]# su postgres
bash-4.2$ /usr/bin/pg_ctl -D /var/lib/pgsql/data start
Could not change directory to "/home/apache/local/apps/live/wiznote/bin"
server starting
bash-4.2$ /usr/bin/pg_ctl restart -w -m fast -D /var/lib/pgsql/data