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