add code.org to intro and fix typo
[wiki.git] / postgresql.mdwn
1 [[!toc levels=4]]
2
3 ## PostgreSQL community
4
5 http://www.postgresql.org/community/irc/
6
7 http://wiki.postgresql.org/wiki/IRC2RWNames
8
9 ## PostgreSQL setup on CentOS 6 
10
11     [root@git2 ~]# yum install postgresql-server
12     [root@git2 ~]# psql 
13     psql: could not connect to server: No such file or directory
14             Is the server running locally and accepting
15             connections on Unix domain socket "/tmp/.s.PGSQL.5432"?
16     [root@git2 ~]# /etc/init.d/postgresql 
17     Usage: /etc/init.d/postgresql {start|stop|status|restart|condrestart|try-restart|reload|force-reload|initdb}
18     [root@git2 ~]# /etc/init.d/postgresql start
19
20     /var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
21                                                                [FAILED]
22     [root@git2 ~]# service postgresql initdb
23     Initializing database:                                     [  OK  ]
24     [root@git2 ~]# 
25     [root@git2 ~]# /etc/init.d/postgresql start
26     Starting postgresql service:                               [  OK  ]
27     [root@git2 ~]# psql 
28     psql: FATAL:  Ident authentication failed for user "root"
29     [root@git2 ~]# 
30     [root@git2 ~]# su - postgres
31     -bash-4.1$ psql
32     psql (8.4.13)
33     Type "help" for help.
34
35     postgres=# \l
36                                       List of databases
37        Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privilege
38     s   
39     -----------+----------+----------+-------------+-------------+-------------------
40     ----
41      postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
42      template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
43                                                                  : postgres=CTc/postg
44     res
45      template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
46                                                                  : postgres=CTc/postg
47     res
48     (3 rows)
49
50     postgres=# 
51     postgres=# \q
52     -bash-4.1$ 
53     -bash-4.1$ pwd
54     /var/lib/pgsql
55     -bash-4.1$ wget -q http://www.commandprompt.com/ppbook/booktown.sql
56     -bash-4.1$ ls -lh booktown.sql 
57     -rw-r--r-- 1 postgres postgres 42K Jan 11  2005 booktown.sql
58     -bash-4.1$ psql -f booktown.sql 
59     CREATE DATABASE
60     (snip)
61     -bash-4.1$ psql
62     psql (8.4.13)
63     Type "help" for help.
64
65     postgres=# \l
66                                       List of databases
67        Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privilege
68     s   
69     -----------+----------+----------+-------------+-------------+-------------------
70     ----
71      booktown  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
72      postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
73      template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
74                                                                  : postgres=CTc/postg
75     res
76      template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
77                                                                  : postgres=CTc/postg
78     res
79     (4 rows)
80
81     postgres=# 
82     postgres=# \q
83     -bash-4.1$ 
84     -bash-4.1$ pg_dumpall > /tmp/pg_dumpall.`date +%s`.sql
85     -bash-4.1$ ls -lh /tmp/pg_dumpall.1349195444.sql 
86     -rw-r--r-- 1 postgres postgres 44K Oct  2 12:30 /tmp/pg_dumpall.1349195444.sql
87     -bash-4.1$ 
88     -bash-4.1$ pg_dump --clean booktown > /tmp/pg_dump-booktown.`date +%s`.sql 
89     -bash-4.1$ ls -lh /tmp/pg_dump-booktown.1349196164.sql 
90     -rw-r--r-- 1 postgres postgres 46K Oct  2 12:42 /tmp/pg_dump-booktown.1349196164.sql
91     -bash-4.1$ 
92     -bash-4.1$ psql booktown
93     psql (8.4.13)
94     Type "help" for help.
95
96     booktown=# 
97     booktown=# \d
98     (snip)
99      public | subjects              | table    | postgres
100      public | text_sorting          | table    | postgres
101
102     booktown=# SELECT * FROM subjects;
103       0 | Arts             | Creativity St
104       1 | Business         | Productivity Ave
105     (snip)
106
107 ## PostgreSQL setup on Fedora 20
108
109     [root@localhost ~]# yum install postgresql-server
110     (snip)
111     [root@localhost ~]# systemctl start postgresql.service
112     Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.
113     [root@localhost ~]# 
114     [root@localhost ~]# systemctl status postgresql.service
115     postgresql.service - PostgreSQL database server
116        Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled)
117        Active: failed (Result: exit-code) since Sat 2014-01-18 08:08:38 EST; 12s ago
118       Process: 4921 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE)
119
120     Jan 18 08:08:38 localhost.localdomain systemd[1]: Starting PostgreSQL database server...
121     Jan 18 08:08:38 localhost.localdomain postgresql-check-db-dir[4921]: "/var/lib/pgsql/data" is missing or empty.
122     Jan 18 08:08:38 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1
123     Jan 18 08:08:38 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server.
124     Jan 18 08:08:38 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state.
125     [root@localhost ~]# 
126     [root@localhost ~]# postgresql-setup initdb
127     Initializing database ... OK
128     [root@localhost ~]# 
129     [root@localhost ~]# systemctl start postgresql.service
130     [root@localhost ~]# 
131     [root@localhost ~]# systemctl stop postgresql.service
132     [root@localhost ~]# 
133     [root@localhost ~]# cp -a /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.orig
134     [root@localhost ~]# # insecure... just for dev...
135     [root@localhost ~]# vim /var/lib/pgsql/data/pg_hba.conf
136     [root@localhost ~]# diff /var/lib/pgsql/data/pg_hba.conf.orig /var/lib/pgsql/data/pg_hba.conf
137     80c80
138     < local   all             all                                     peer
139     ---
140     > local   all             all                                     trust
141     82c82
142     < host    all             all             127.0.0.1/32            ident
143     ---
144     > host    all             all             127.0.0.1/32            trust
145     [root@localhost ~]# 
146     [root@localhost ~]# systemctl start postgresql.service
147     [root@localhost ~]# 
148     [root@localhost ~]# su - postgres
149     -bash-4.2$ psql -c '\du'
150                                  List of roles
151      Role name |                   Attributes                   | Member of 
152     -----------+------------------------------------------------+-----------
153      postgres  | Superuser, Create role, Create DB, Replication | {}
154
155     -bash-4.2$ psql -c "CREATE ROLE pguser1 UNENCRYPTED PASSWORD 'secret1' NOSUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN"
156     CREATE ROLE
157     -bash-4.2$ psql -c '\du'
158                                  List of roles
159      Role name |                   Attributes                   | Member of 
160     -----------+------------------------------------------------+-----------
161      pguser1   | No inheritance, Create role, Create DB         | {}
162      postgres  | Superuser, Create role, Create DB, Replication | {}
163
164     -bash-4.2$ 
165     -bash-4.2$ psql -c '\l'
166                                       List of databases
167        Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
168     -----------+----------+----------+-------------+-------------+-----------------------
169      postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
170      template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
171                |          |          |             |             | postgres=CTc/postgres
172      template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
173                |          |          |             |             | postgres=CTc/postgres
174     (3 rows)
175
176     -bash-4.2$ 
177     -bash-4.2$ psql -c "CREATE DATABASE pgdatabase1 WITH OWNER = pguser1"
178     CREATE DATABASE
179     -bash-4.2$ psql -c '\l'
180                                        List of databases
181         Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
182     -------------+----------+----------+-------------+-------------+-----------------------
183      pgdatabase1 | pguser1  | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
184      postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
185      template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
186                  |          |          |             |             | postgres=CTc/postgres
187      template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
188                  |          |          |             |             | postgres=CTc/postgres
189     (4 rows)
190
191     -bash-4.2$ 
192
193
194
195 See also:
196
197 - Bug 771496 – On initial install, postgresql-server does not initialize /var/lib/pgsql/data - https://bugzilla.redhat.com/show_bug.cgi?id=771496
198 - http://serverfault.com/questions/350045/postgresql-service-initdb-doesnt-work