Version 3 (modified by joshuadf, 10 years ago) (diff)


PostgreSQL is a popular open-source RDBMS included in Red Hat Enterprise Linux 4. (In RHEL3, it was branded as "Red Hat Database" or rhdb.) It is not as friendly as MySQL, but performs better for many applications.


MySQL to PostgreSQL command-line hints

MySQL's command-line tool mysql uses SQL-like syntax such as SHOW DATABASES; for RDBMS information. PostgreSQL takes a different approach in psql-- meta-commands that begin with a backslash and do not need to end with a semicolon, or reference virtual relations beginning with pg_. Here are some of the most common:

MySQL mysql PostgreSQL psql
SHOW COLUMNS; \d table-name
USE db-name; \c db-name

Other meta-commands are explained in man psql or in the Red Hat Database Manual

Setting up a PostgreSQL server to accept network connections

The default RHEL4 PostgeSQL configuration will only accept local socket connections and not TCP/IP network connections (local sockets are both faster and in some ways more secure). Additionally, PostgeSQL has a complex host-based authentication mechanism to control access to databases. By default, PostgreSQL uses the operating system to provide the connecting username (ident authentication), so you have to be logged in as the local user postgres can connect.

For more information, see the PostgreSQL manual section on Authentication Methods

The following commands enable TCP/IP connections and add rules to the host-based authentication table (pg_hba.conf) to allow any user to connect from localhost, and the xbrain_user to connect from any machine on the subnet

# install and set the database to run at startup
up2date postgresql-server
chkconfig postgresql on
# run the other commands as postgres user
su - postgres
cd /var/lib/pgsql/data
cp postgresql.conf postgresql.conf.orig
# you must restart postgresql for the following to take effect
sed -i 's;#tcpip_socket = false;tcpip_socket = true;' postgresql.conf
cat >>/var/lib/pgsql/data/pg_hba.conf<<EOF
# TYPE  DATABASE        USER        IP-ADDRESS    IP-MASK           METHOD
host    all             all   md5
# we have to use "password" instead of "md5" for < unixODBC-2.2.11
host    all             xbrain_user     password
/etc/init.d/postgresql restart

Next, we need to create a user, in this example with the createuser tool.

createuser --no-adduser --no-createdb --pwprompt xbrain_user
# Enter password for new user:
# Enter it again:

After creating the user we test out the TCP/IP connection:

psql template1 -U xbrain_user -h localhost
# Password: 
# template1=> \q

Outputting CSV

The psql tool has many output options, including unaligned comma-separated output. For example:

db=# select * from "Family";
 ID | name | num 
  3 | abc  | 123
  5 | bar  |   5
(2 rows)

db=# \a \t \pset fieldsep ','
Output format is unaligned.
Showing only tuples.
Field separator is ",".

db=# select * from "Family";
6,PLE 1234,6