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:


Home | About | Sitemap | Recent changes | © 2017 Philip Durbin | @philipdurbin | philipdurbin@gmail.com