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