PostgreSQL 9.3 tidbits

Just came across an article that listed some of the cool new features of postgresql database. I've listed out some of those that I liked and thought might be useful...

• pg_isready - New command-line utility to test whether a server is ready to accept connections.

$ pg_isready -p 5432 -h localhost
localhost:5432 - accepting connections

• \watch - Allows convenient re-execution of queries. 

postgres=# SELECT datname, query, usename FROM pg_stat_activity ORDER BY query_start DESC LIMIT 1; \watch 60

• \conninfo - Displays current connection information

postgres=# \conninfo
You are connected to database "postgres" as user "postgres" via socket in "/tmp" at port "5432".




• \l - List Databases

postgres=# \l

                                  List of databases

        Name         |  Owner   | Encoding | Collate | Ctype |   Access privileges

---------------------+----------+----------+---------+-------+-----------------------

 arts_v6             | postgres | UTF8     | C       | C     |

 enact_recreate_test | postgres | UTF8     | C       | C     |

 postgres            | postgres | UTF8     | C       | C     |

 quarrysb_13_0_0     | postgres | UTF8     | C       | C     |

 template0           | postgres | UTF8     | C       | C     | =c/postgres          +

                     |          |          |         |       | postgres=CTc/postgres

 template1           | postgres | UTF8     | C       | C     | =c/postgres          +

                     |          |          |         |       | postgres=CTc/postgres

(6 rows)

Installation and Setup of PostgreSQL in Ubuntu

First, install postgresql and commonly used add-on's. 

> sudo apt-get install postgresql postgresql-contrib

If you plan to use the pgAdmin tool as the GUI front-end for your database, then execute 

the following command: 

> sudo apt-get install pgadmin3

The basic installation creates a "postgres" user as the root user to access PostgreSQL. 

But it doesn't setup an password. So we'll have to setup a password for this user.

> sudo -u postgres psql postgres 

Set a password for the "postgres" database role using the command: 

# \password postgres 

You're all set up to play around and start using PostgreSQL in your linux environment now!

Create a database by running "createdb" command:

> sudo -u postgres createdb

If you have an import sql script, you can load that by running 

> sudo -u postgres psql <  

You can also execute sql commands by simply starting up the sql console via 

> psql

followed by 

# create database ; 

... 

To allow connections to your database server, you'd probably have to make a minor change to 

your pg_hba.conf file. 

Replace "peer" with "md5" in the following line in your 

/etc/postgresql/9.3/main/pg_hba.conf file: 

# Database administrative login by Unix domain socket

local    all       postgres                           peer

Reload the configuration changes for it to be effective:

> sudo /etc/init.d/postgresql reload

To enable postgresql to listen across networks other than local, modify the listen_address 

in the following file:

/etc/postgresql/current/main/postgresql.conf [listen_address = '*']