3 ## PostgreSQL community
5 http://www.postgresql.org/community/irc/
7 http://wiki.postgresql.org/wiki/IRC2RWNames
9 ## PostgreSQL setup on CentOS 6
11 [root@git2 ~]# yum install postgresql-server
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
20 /var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first.
22 [root@git2 ~]# service postgresql initdb
23 Initializing database: [ OK ]
25 [root@git2 ~]# /etc/init.d/postgresql start
26 Starting postgresql service: [ OK ]
28 psql: FATAL: Ident authentication failed for user "root"
30 [root@git2 ~]# su - postgres
37 Name | Owner | Encoding | Collation | Ctype | Access privilege
39 -----------+----------+----------+-------------+-------------+-------------------
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
45 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
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
67 Name | Owner | Encoding | Collation | Ctype | Access privilege
69 -----------+----------+----------+-------------+-------------+-------------------
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
76 template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
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
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
92 -bash-4.1$ psql booktown
99 public | subjects | table | postgres
100 public | text_sorting | table | postgres
102 booktown=# SELECT * FROM subjects;
103 0 | Arts | Creativity St
104 1 | Business | Productivity Ave
107 ## PostgreSQL setup on Fedora 20
109 [root@localhost ~]# yum install postgresql-server
111 [root@localhost ~]# systemctl start postgresql.service
112 Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details.
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)
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.
126 [root@localhost ~]# postgresql-setup initdb
127 Initializing database ... OK
129 [root@localhost ~]# systemctl start postgresql.service
131 [root@localhost ~]# systemctl stop postgresql.service
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
140 > local all all trust
142 < host all all 127.0.0.1/32 ident
144 > host all all 127.0.0.1/32 trust
146 [root@localhost ~]# systemctl start postgresql.service
148 [root@localhost ~]# su - postgres
149 -bash-4.2$ psql -c '\du'
151 Role name | Attributes | Member of
152 -----------+------------------------------------------------+-----------
153 postgres | Superuser, Create role, Create DB, Replication | {}
155 -bash-4.2$ psql -c "CREATE ROLE pguser1 UNENCRYPTED PASSWORD 'secret1' NOSUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN"
157 -bash-4.2$ psql -c '\du'
159 Role name | Attributes | Member of
160 -----------+------------------------------------------------+-----------
161 pguser1 | No inheritance, Create role, Create DB | {}
162 postgres | Superuser, Create role, Create DB, Replication | {}
165 -bash-4.2$ psql -c '\l'
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
177 -bash-4.2$ psql -c "CREATE DATABASE pgdatabase1 WITH OWNER = pguser1"
179 -bash-4.2$ psql -c '\l'
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
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