[[!toc levels=4]] ## PostgreSQL community http://www.postgresql.org/community/irc/ http://wiki.postgresql.org/wiki/IRC2RWNames ## PostgreSQL setup on CentOS 6 [root@git2 ~]# yum install postgresql-server [root@git2 ~]# psql psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? [root@git2 ~]# /etc/init.d/postgresql Usage: /etc/init.d/postgresql {start|stop|status|restart|condrestart|try-restart|reload|force-reload|initdb} [root@git2 ~]# /etc/init.d/postgresql start /var/lib/pgsql/data is missing. Use "service postgresql initdb" to initialize the cluster first. [FAILED] [root@git2 ~]# service postgresql initdb Initializing database: [ OK ] [root@git2 ~]# [root@git2 ~]# /etc/init.d/postgresql start Starting postgresql service: [ OK ] [root@git2 ~]# psql psql: FATAL: Ident authentication failed for user "root" [root@git2 ~]# [root@git2 ~]# su - postgres -bash-4.1$ psql psql (8.4.13) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privilege s -----------+----------+----------+-------------+-------------+------------------- ---- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postg res template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postg res (3 rows) postgres=# postgres=# \q -bash-4.1$ -bash-4.1$ pwd /var/lib/pgsql -bash-4.1$ wget -q http://www.commandprompt.com/ppbook/booktown.sql -bash-4.1$ ls -lh booktown.sql -rw-r--r-- 1 postgres postgres 42K Jan 11 2005 booktown.sql -bash-4.1$ psql -f booktown.sql CREATE DATABASE (snip) -bash-4.1$ psql psql (8.4.13) Type "help" for help. postgres=# \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privilege s -----------+----------+----------+-------------+-------------+------------------- ---- booktown | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postg res template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres : postgres=CTc/postg res (4 rows) postgres=# postgres=# \q -bash-4.1$ -bash-4.1$ pg_dumpall > /tmp/pg_dumpall.`date +%s`.sql -bash-4.1$ ls -lh /tmp/pg_dumpall.1349195444.sql -rw-r--r-- 1 postgres postgres 44K Oct 2 12:30 /tmp/pg_dumpall.1349195444.sql -bash-4.1$ -bash-4.1$ pg_dump --clean booktown > /tmp/pg_dump-booktown.`date +%s`.sql -bash-4.1$ ls -lh /tmp/pg_dump-booktown.1349196164.sql -rw-r--r-- 1 postgres postgres 46K Oct 2 12:42 /tmp/pg_dump-booktown.1349196164.sql -bash-4.1$ -bash-4.1$ psql booktown psql (8.4.13) Type "help" for help. booktown=# booktown=# \d (snip) public | subjects | table | postgres public | text_sorting | table | postgres booktown=# SELECT * FROM subjects; 0 | Arts | Creativity St 1 | Business | Productivity Ave (snip) ## PostgreSQL setup on Fedora 20 [root@localhost ~]# yum install postgresql-server (snip) [root@localhost ~]# systemctl start postgresql.service Job for postgresql.service failed. See 'systemctl status postgresql.service' and 'journalctl -xn' for details. [root@localhost ~]# [root@localhost ~]# systemctl status postgresql.service postgresql.service - PostgreSQL database server Loaded: loaded (/usr/lib/systemd/system/postgresql.service; disabled) Active: failed (Result: exit-code) since Sat 2014-01-18 08:08:38 EST; 12s ago Process: 4921 ExecStartPre=/usr/bin/postgresql-check-db-dir ${PGDATA} (code=exited, status=1/FAILURE) Jan 18 08:08:38 localhost.localdomain systemd[1]: Starting PostgreSQL database server... Jan 18 08:08:38 localhost.localdomain postgresql-check-db-dir[4921]: "/var/lib/pgsql/data" is missing or empty. Jan 18 08:08:38 localhost.localdomain systemd[1]: postgresql.service: control process exited, code=exited status=1 Jan 18 08:08:38 localhost.localdomain systemd[1]: Failed to start PostgreSQL database server. Jan 18 08:08:38 localhost.localdomain systemd[1]: Unit postgresql.service entered failed state. [root@localhost ~]# [root@localhost ~]# postgresql-setup initdb Initializing database ... OK [root@localhost ~]# [root@localhost ~]# systemctl start postgresql.service [root@localhost ~]# [root@localhost ~]# systemctl stop postgresql.service [root@localhost ~]# [root@localhost ~]# cp -a /var/lib/pgsql/data/pg_hba.conf /var/lib/pgsql/data/pg_hba.conf.orig [root@localhost ~]# # insecure... just for dev... [root@localhost ~]# vim /var/lib/pgsql/data/pg_hba.conf [root@localhost ~]# diff /var/lib/pgsql/data/pg_hba.conf.orig /var/lib/pgsql/data/pg_hba.conf 80c80 < local all all peer --- > local all all trust 82c82 < host all all 127.0.0.1/32 ident --- > host all all 127.0.0.1/32 trust [root@localhost ~]# [root@localhost ~]# systemctl start postgresql.service [root@localhost ~]# [root@localhost ~]# su - postgres -bash-4.2$ psql -c '\du' List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- postgres | Superuser, Create role, Create DB, Replication | {} -bash-4.2$ psql -c "CREATE ROLE pguser1 UNENCRYPTED PASSWORD 'secret1' NOSUPERUSER CREATEDB CREATEROLE NOINHERIT LOGIN" CREATE ROLE -bash-4.2$ psql -c '\du' List of roles Role name | Attributes | Member of -----------+------------------------------------------------+----------- pguser1 | No inheritance, Create role, Create DB | {} postgres | Superuser, Create role, Create DB, Replication | {} -bash-4.2$ -bash-4.2$ psql -c '\l' List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -----------+----------+----------+-------------+-------------+----------------------- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) -bash-4.2$ -bash-4.2$ psql -c "CREATE DATABASE pgdatabase1 WITH OWNER = pguser1" CREATE DATABASE -bash-4.2$ psql -c '\l' List of databases Name | Owner | Encoding | Collate | Ctype | Access privileges -------------+----------+----------+-------------+-------------+----------------------- pgdatabase1 | pguser1 | UTF8 | en_US.UTF-8 | en_US.UTF-8 | postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (4 rows) -bash-4.2$ See also: - Bug 771496 – On initial install, postgresql-server does not initialize /var/lib/pgsql/data - https://bugzilla.redhat.com/show_bug.cgi?id=771496 - http://serverfault.com/questions/350045/postgresql-service-initdb-doesnt-work