sourceforge -> github
[wiki.git] / postgresql.mdwn
1
2     [root@git2 ~]# yum install postgresql-server
3     [root@git2 ~]# psql 
4     psql: could not connect to server: No such file or directory
5             Is the server running locally and accepting
6             connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
7     [root@git2 ~]# /etc/init.d/postgresql 
8     Usage: /etc/init.d/postgresql {start|stop|status|restart|condrestart|try-restart|reload|force-reload|initdb}
9     [root@git2 ~]# /etc/init.d/postgresql start
10
11     /var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
12                                                                [FAILED]
13     [root@git2 ~]# service postgresql initdb
14     Initializing database:                                     [  OK  ]
15     [root@git2 ~]# 
16     [root@git2 ~]# /etc/init.d/postgresql start
17     Starting postgresql service:                               [  OK  ]
18     [root@git2 ~]# psql 
19     psql: FATAL:  Ident authentication failed for user "root"
20     [root@git2 ~]# 
21     [root@git2 ~]# su - postgres
22     -bash-4.1$ psql
23     psql (8.4.13)
24     Type "help" for help.
25
26     postgres=# \l
27                                       List of databases
28        Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privilege
29     s   
30     -----------+----------+----------+-------------+-------------+-------------------
31     ----
32      postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
33      template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
34                                                                  : postgres=CTc/postg
35     res
36      template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
37                                                                  : postgres=CTc/postg
38     res
39     (3 rows)
40
41     postgres=# 
42     postgres=# \q
43     -bash-4.1$ 
44     -bash-4.1$ pwd
45     /var/lib/pgsql
46     -bash-4.1$ wget -q http://www.commandprompt.com/ppbook/booktown.sql
47     -bash-4.1$ ls -lh booktown.sql 
48     -rw-r--r-- 1 postgres postgres 42K Jan 11  2005 booktown.sql
49     -bash-4.1$ psql -f booktown.sql 
50     CREATE DATABASE
51     (snip)
52     -bash-4.1$ psql
53     psql (8.4.13)
54     Type "help" for help.
55
56     postgres=# \l
57                                       List of databases
58        Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privilege
59     s   
60     -----------+----------+----------+-------------+-------------+-------------------
61     ----
62      booktown  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
63      postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
64      template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
65                                                                  : postgres=CTc/postg
66     res
67      template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
68                                                                  : postgres=CTc/postg
69     res
70     (4 rows)
71
72     postgres=# 
73     postgres=# \q
74     -bash-4.1$ 
75     -bash-4.1$ pg_dumpall > /tmp/pg_dumpall.`date +%s`.sql
76     -bash-4.1$ ls -lh /tmp/pg_dumpall.1349195444.sql 
77     -rw-r--r-- 1 postgres postgres 44K Oct  2 12:30 /tmp/pg_dumpall.1349195444.sql
78     -bash-4.1$ 
79     -bash-4.1$ pg_dump --clean booktown > /tmp/pg_dump-booktown.`date +%s`.sql 
80     -bash-4.1$ ls -lh /tmp/pg_dump-booktown.1349196164.sql 
81     -rw-r--r-- 1 postgres postgres 46K Oct  2 12:42 /tmp/pg_dump-booktown.1349196164.sql
82     -bash-4.1$ 
83     -bash-4.1$ psql booktown
84     psql (8.4.13)
85     Type "help" for help.
86
87     booktown=# 
88     booktown=# \d
89     (snip)
90      public | subjects              | table    | postgres
91      public | text_sorting          | table    | postgres
92
93     booktown=# SELECT * FROM subjects;
94       0 | Arts             | Creativity St
95       1 | Business         | Productivity Ave
96     (snip)