postgresql 入门使用记录

查看postgresql 安装情况
[root@localhost hive]# rpm -qa | grep postgresql
postgresql-9.2.4-1.fc19.x86_64
postgresql-libs-9.2.4-1.fc19.x86_64
postgresql-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 ROLE
postgres=# create database Metastore owner=hive;
CREATE DATABASE
postgres=# grant all on database Metastore to hive;
GRANT
postgres=# \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 restart
waiting for server to shut down.... done
server stopped
server starting

怎么老错：

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

改个密码

[root@localhost hive]# sudo -u postgres psql postgres
could not change directory to "/home/yuming/hive"
psql (9.2.4)
Type "help" for help.


postgres=# \password postgres
Enter new password: 
Enter it again: 
postgres=# 


[root@localhost hive]# sudo -u hive psql Metastore
could not change directory to "/home/yuming/hive"
psql (9.2.4)
Type "help" for help.

Metastore=> \t
Showing 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/salerecord
drwxr-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  postgr
postgresql-9.2.4-1.fc19.x86_64
postgresql-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_64

postgresql 安装，简单几步就搞定，有其他问题的话可参考安装手册：

如果要配 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.rpm
Preparing...                          ################################# [100%]
    package pgdg-fedora92-9.2-6.noarch is already installed

list 一下，找几个合适的装上：

[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 postgre
root      6645  4974  0 15:47 pts/1    00:00:00 grep --color=auto postgre

而且也起不来：
[root@indigo ~]# service postgresql start
Redirecting to /bin/systemctl start postgresql.service
Job 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/ start
server 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/data
base pg_clog pg_ident.conf pg_notify pg_snapshots pg_subtrans pg_twophase pg_xlog
global 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/ start
server starting

这次起来了：
[root@indigo pgsql]# su postgres
bash-4.2$ psql
psql (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/postgres
template1 | 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                                     trust

host     all             all             0.0.0.0/0                       trust

JDBC都用yum 装，表示惊奇，试了一下：

[root@indigo all-in-one]# yum search postgre-jdbc

结果被装到 /usr/share/java 去了

安装前：

[root@indigo java]# ls
jline.jar  js.jar  rhino-examples.jar  rhino.jar
安装后：
[root@indigo java]# ll
total 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.jar
lrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2ee.jar -> postgresql-jdbc.jar
lrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2.jar -> postgresql-jdbc.jar
lrwxrwxrwx. 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.jar
lrwxrwxrwx. 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.jar
lrwxrwxrwx. 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.jar
ln: 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]# ll
total 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.jar
lrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2ee.jar -> postgresql-jdbc.jar
lrwxrwxrwx. 1 root root      19 Aug 11 18:46 postgresql-jdbc2.jar -> postgresql-jdbc.jar
lrwxrwxrwx. 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.jar
lrwxrwxrwx. 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.jar
lrwxrwxrwx. 1 root root       6 Aug 10 19:16 rhino.jar -> js.jar

[root@indigo java]# su postgres

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
创建用户，数据库，使用新创建用户导入sql脚本：

postgres=# create user hive with password 'hive';
CREATE ROLE
postgres=# create database Metastore owner=hive;
CREATE DATABASE
postgres=# grant all privileges on database Metastore to hive;
GRANT
postgres=# \q

bash-4.2$ psql -U hive -d Metastore
psql (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"
REVOKE
psql:/usr/lib/hive/scripts/Metastore/upgrade/postgres/hive-schema-0.10.0.postgres.sql:1373: WARNING:  no privileges were granted for "public"
GRANT
重启：

Metastore=> \q
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$

简单使用：

查看所有库：\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

换数据库

原文链接：https://www.f2er.com/postgresql/196148.html

postgresql 入门使用记录

猜你在找的Postgre SQL相关文章