Oracle – Linux – Network

1. start, shutdown postgresql
[root@bb bb]# service postgresql start
Initializing database: [ OK ]
Starting postgresql service: [ OK ]
[root@bb bb]# chkconfig postgresql on
[root@bb bb]# su potsgres
su: user potsgres does not exist
[root@bb bb]# su – postgres

2. createdb, dropdb
-bash-3.1$ createdb test
CREATE DATABASE
-bash-3.1$ psql test
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

test=# \q

-bash-3.1$ dropdb test
DROP DATABASE

3. createuser, dropuser
-bash-3.1$ createuser bb
Shall the new role be a superuser? (y/n) y
CREATE ROLE

-bash-3.1$ dropuser bb
DROP ROLE

4. createuser & createdb
-bash-3.1$ createuser bb -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE

-bash-3.1$ createdb bb
CREATE DATABASE
-bash-3.1$ exit
logout

[root@bb ~]# su – bb
[bb@bb ~]$ psql
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help with psql commands
\g or terminate with semicolon to execute query
\q to quit

-bash-3.1$ createuser admin -P
Enter password for new role:
Enter it again:
Shall the new role be a superuser? (y/n) y
CREATE ROLE

5. edit configuration
error : postgresql fatal: ident authentication failed for user

[bb@bb ~]$ su – root
Password:
[root@bb ~]# vi /var/lib/pgsql/data/pg_hba.conf
# “local” is for Unix domain socket connections only
local all all password
host all all 127.0.0.1/32 password
host all all 172.20.0.0/16 password
host all all ::1/128 trust

[root@bb ~]# vi /var/lib/pgsql/data/postgresql.conf
listen_addresses = ‘*’

[root@bb ~]# service postgresql restart
Stopping postgresql service: [ OK ]
Starting postgresql service: [ OK ]
[root@bb ~]# exit

6. general command in PostgreSQL
\l :List databases
\c database-name :List databases
\d :List tables in database
\d table-name escribe table
select * from table-name :List table contents

[bb@bb ~]$ psql -U bb -d bb -W
Password for user bb:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
bb=# \l
List of databases
Name | Owner | Encoding
———-+———-+———-
bb | postgres | UTF8
postgres | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
test2 | bb | UTF8
(5 rows)

bb=# \c
Password:
You are now connected to database “bb” as user “bb”.
bb=# \d
No relations found.

7. select database
bb=# \c test2
Password for user bb:
You are now connected to database “test2″.
test2=# \d
List of relations
Schema | Name | Type | Owner
——–+——————-+———-+——-
public | barang | table | bb
public | barang_id_seq | sequence | bb

test2=# \q
[bb@bb ~]$

8. test connection
[admin@bb ~]$ psql -U admin -d test2 -W
Password for user admin:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
test2=# \q

9. test connection from ip
[admin@bb ~]$ psql -U admin -d test2 -h 172.20.88.88 -W
Password for user admin:
Welcome to psql 8.1.4, the PostgreSQL interactive terminal.
test2=# \q